Hi guys, I am working on it. Few questions are not answered yet. It will be given after some time.

Q. Triggers:

à Type of triggers

1. After Triggers

2. Instead of Triggers

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT –-FOR UPDATE –-AFTER DELETE --INSTEAD OF DELETE/INSERT/UPDATE

//CODE HERE

1. Triggers can rollback transactions.

2. Trigger executes once all the records are inserted/updated/deleted, i.e. if you are inserting 1 lakh records then the trigger will fire only once not 1 lakh times.

3. Triggers can be executed on DML and DDL operations

4. DDL triggers fire only after the DDL statements that trigger them to run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

5. We can have nested triggers upto 32 level.

6. Truncate is a DDL statement, hence DDL trigger executes on truncate statement.

7. To enable/disable nested trigger (nested trigger = 0 -> can’t cascade, nested trigger = 1 -> can cascade)

EXEC sp_configure 'nested triggers', 1 ;

RECONFIGURE;

Q. How to disable/enable trigger.

--Disable all the triggers for a single database:

DISABLE TRIGGER UPDATE_CUSTOMER_PRIORITY ON SALES;

ENABLE TRIGGER UPDATE_CUSTOMER_PRIORITY ON SALES;

--Disable all the triggers for all servers:

DISABLE TRIGGER ALL ON ALL SERVER;

ENABLE TRIGGER ALL ON ALL SERVER;

Q. What is the difference between Join and Union?

· Join selects the data from two or more tables based on one condition, while union selects the data from two tables based on two different conditions. (Join is used to establish a conditional selection for two different tables, while union is used to select similar data based on different conditions.)

· Join selects columns from 2 or more tables, while union selects rows.

· Union combines the result of two select statements and generates one result set and then eliminates the duplicate rows from that result set.

Q. What is difference between ‘Union’ and ‘Union All’?

· Union All selects all the rows from all the select statements whereas Union eliminates the duplicate rows.

Q. What are the new features in SQL Server 2008?

· Initializing Variables: - We can declare and initialize the variables in single statement.

· Compound Assignment Operators: - Now we can use compound assignment operators like,

+= -= *= /= %=

· Enhanced convert function: - SQL Server 2008 has enhanced the convert function when you do the conversion between binary and hexadecimal.

· New Date and Time data types: -

· New Date and Time functions: -

· The Merge statement: -

· Filtered Indexes

· Change Data Capture

Q. What is filtered index in SQL Server 2008?

· Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table, that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

· When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX.

· - They can be created only as Nonclustered Index
- They can be used on Views only if they are persisted views.
- They cannot be created on full-text Indexes.

· Scenario, if we know that there is a column which has lot of records with value as NULL and we also have select statement executed with where clause using this column, then we can define a filtered index ignoring all the records where value is null from the index.

CREATE NONCLUSTERED INDEX NCI_Department ON HumanResources.Employee(EmployeeID) WHERE Title= 'Marketing Manager'

Q. What are full text indexes?

Full-text search refers to the functionality in SQL Server that supports full-text queries against character-based data. These types of queries can include words and phrases as well as multiple forms of a word or phrase. To support full-text queries, full-text indexes must be implemented on the columns referenced in the query. The columns can be configured with character data types (such as char and varchar) or with binary data types (such as varbinary and image). A full-text index is made up of word tokens that are derived from the text being indexed. For example, if the indexed text contains the phrase “tables can include indexes,” the full-text index would contain four tokens: “tables,” “can,” “include,” and “indexes.” Because the list of tokens can be easily searched, full-text queries can quickly locate the necessary records.

Q. What is the difference between User Defined Function and Stored Procedure?

· UDF can be used in SQL statements anywhere in where/having/select section, where as Stored procedures can’t be. SP uses execute or exec command to run.

· UDF will stop if an error occurs, while SP does not (provided exception handling is implemented).

· UDF can return a single statement, while SP can return multiple result set.

Q. Disadvantages of UDF?

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

· UDF Accepts Lesser Numbers of Input Parameters.

o UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.

· UDF Prohibit Usage of Non-Deterministic Built-in Functions

o Functions GETDATE() etc cannot be used in UDFs, but can be used in Stored Procedure

· UDF Returns Only One Result Set

o Due to this it can be used in SELECT statement but cannot return multiple result set like Stored Procedure

· UDF cannot Call Stored Procedure

o Only access to Extended Stored Procedure.

· UDF cannot Execute Dynamic SQL or Temporary Tables

o UDF cannot run dynamic SQL which are dynamically build in UDF. Temporary Tables cannot be used in UDF as well.

· UDF cannot Return XML

o FOR XML is not allowed in UDF

· UDF does not support SET options

o SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)

· UDF does not Support Error Handling

o RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

Advantages of Stored Procedures:

· SP’s reduces network traffic and latency, boosting application performance.

· SP’s execution plans be used, staying cached in SQL Server’s memory, reducing server overhead.

· Code reuse.

· Encapsulates logic

· It provides better security to your data.

Q. What are types of UDF?

Below are the types of UDF:

· Scalar User-Defined Function: - it returns the scalar data types. Text, nText, images, and time stamp data types are not supported. You can pass 0 to many (upto 1024) parameters and you get a return value.

· Inline Table-Valued User-Defined Function: - It returns a table data type and is an exceptional alternative to a view as the user-defined function can pass 0 to many parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying table.

· Multi-statement Table Value User-Defined Function: -

Q. What are Isolation Levels in SQL Server?

Isolation is a property that defines how/when the changes made by other transactions are visible to other concurrent operations. Isolation is one the ACID (Atomicity, Consistency, Isolation, Durability) properties.

Below are the ISOLATION LEVELS:

· Serializable

· REPEATABLE READS (phantom reads): - Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction

· READ COMMITTED: - This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it does not ensure that the data will not be changed before the end of the transaction.

· READ UNCOMMITTED (dirty reads):- When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

· SNAPSHOT isolation

Dirty Reads are reading the UnCommitted Data, while Phantom reads are repeatable reads.

NOLOCK hint can also cause the DIRTY READS.

The SNAPSHOT isolation level prevents phantom reads. READ UNCOMMITTED, READ COMMITTED and REPEATABLE READ all allow phantom reads. Aside from the SNAPSHOT isolation level, the SERIALIZABLE isolation level also prevents phantom reads.

Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read as a result of a deletion transaction. Similarly, the transaction's second or succeeding read shows a row that did not exist in the original read as the result of an insertion by a different transaction.

Q. Difference between Except and Not In operators?

Except returns all the distinct rows from left hand side table which are not present in right hand side table, where as Not In returns all the rows from left hand side table that are not present in right hand side table.

Except returns the Distinct rows, where as Not In doesn’t do so.

Q. System Databases

Master database holds information for all the databases on the SQL Server instance.

Msdb database stores information regarding database backups, SQL Agent Information, DTS package, SQL Server jobs and some replication information such as for log shipping.

Tempdb holds temporary objects such as global and local temporary tables and stored procedures.

Model database is a template database used in creation of any new user database created in the instance.

Resource database

Q. By how many ways we can rebuild the indexes?

1. DBCC DBREINDEX : - this is going to be removed from future versions of SQL Server, instead use ALTER INDEX

2. ALTER INDEX

3. UPDATE_STATISTICS

Q. What are dynamic queries?

="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value & ")") & " ORDER BY LastName"

This expression results in a query that includes a WHERE clause only if All is not selected. The WHERE clause includes the value from the Department parameter. You must use the generic query designer to create an expression.

Q. Clustered and Non-Clustered indexes, Covering indexes?

Q. What’s the difference between Primary Key and Unique Key?

· Both primary key and unique key enforces the uniqueness of the column on which they are defined.

· By default Primary Key creates a Clustered Index on the column, where as unique key creates a non-clustered index by default.

· Primary key doesn’t allow NULLs, where as Unique key allows the NULL value.

Primary Key creates a Clustered Index, but it doesn’t mean that creating clustered index creates a Primary Key.

Q. Temp table versus Temp variable?

Sr. No.	Table Variables	Temp Table
Performance Differences
1.	Table variables don’t participate in transactions, logging or locking. This means they are faster as they don’t require the overhead, but conversely you don’t get these features.	Temporary tables are real tables so you can create indexes, etc. If you have large amount of data for which accessing by index will be faster, then temporary tables are a good option.
2.	You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (e.g. make a function to split into a table of values on some arbitrary delimiter).	You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing data types over time, since you don't need to define your temp table structure upfront.
3.	A table variable can only have a primary index	A temp table can have indexes
4.	If speed is an issue Table variables can be faster	But if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.
5.	A table variables don't have column statistics, This means that the query optimizer doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows	Whereas temp tables do have column statistics so the query optimizer can choose different plans for data involving temp tables
6.	You cannot alter a Table variable with DDL statement (so you cannot create a non clustered index on a table variable). That makes every table variable a heap, or at best a table with a single, clustered index, and every table variable access a table scan (or clustered index scan)	Temp tables can be altered with DDL statements
7.	Use table variable if there less records for processing	Use temp table if you have huge record for processing
Syntactical Differences
8.	-To create Table Variable Declare @T Table (firstColumn varchar(100))	-To create Temp Table Create table #T (firstColumn varchar(100))
9.	-Insertion Operation Insert into @T select ‘some value’	-Insertion Operation Insert into #T select ‘some value’
10.	-select statement Select * from @T	-select statement Select * from #T

Q. Creating indexes on temporary tables

You can define indexes on temporary tables. In many cases, these indexes can improve the performance of queries that use tempdb. The optimizer uses these indexes just like indexes on ordinary user tables. The only requirements are:

· The table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics.

· The index must exist while the query using it is optimized. You cannot create an index and then use it in a query in the same batch or procedure.

· The optimizer may choose a suboptimal plan if rows have been added or deleted since the index was created or since update statistics was run.

Providing an index for the optimizer can greatly increase performance, especially in complex procedures that create temporary tables and then perform numerous operations on them.

UPDATE STATISTICS Sales.SalesOrderDetail

exec sp_updatestats

Q. Can we create Indexes on table variable?

· No. "Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables." You can implicitly create a unique clustered index by marking a column as a primary key.

· You can only add indexes on table variables by adding PRIMARY KEY and UNIQUE constraints when you declare them. But that can take you a long way.

· A hint is that if you plan to put lots of data into your table variable, consider a temp table instead. Temp tables has statistics, and if you will use the table as input to a query, SQL Server will have more information to work from.

Q. Manager -> Employee hierarchy?

select (a.First_Name + ' ' + a.Last_Name) as EMPLOYEE_NAME,(b.First_Name + ' ' + B.Last_Name) as MANAGER_NAME fromemployee a

left outer join employee b on (b.Employee_ID = a.Manager_ID)

Q. Delete duplicate records query?

with CTE (First_Name, last_name, duplicatecount)

(

select First_Name, Last_Name, row_number() over(PARTITION byFirst_Name, last_name order by First_Name, last_name) asduplicatecount from Employee

)

delete from CTE where duplicatecount > 1

Q. Nth highest salary?

--Nth Highest

SELECT * FROM Employee E1

WHERE (N-1) = (SELECT COUNT(DISTINCT(E2.Salary)) FROMEmployee E2

WHERE E2.Salary > E1.Salary)

--Nth Lowest

SELECT * FROM Employee E1

WHERE (N-1) = (SELECT COUNT(DISTINCT(E2.Salary)) FROMEmployee E2

WHERE E2.Salary < E1.Salary)

select * from Employee

select * from (select Employee_ID, Salary, DENSE_RANK()

over (order by salary desc) as rankid from Employee) t

where rankid = N

Q. Delete versus Truncate?

Sr. No.	Truncate	Delete
1	Truncate is faster and uses fewer system and transaction log resources than delete. Truncate removes the data by deallocating the data pages used to store the table's data and only the page deallocations are recorded in the transaction logs.	Delete remove rows one at a time and records an entry in the transaction log each deleted row.
2	Truncate removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. Identity column get reset.	Identity column does not get reset in delete command. If you want to remove the table definition than use DROP command.
3	You cannot use the TRUNCATE TABLE on a table referenced by a Foreign Key constraint. Because TRUNCATE TABLE is not logged and cannot activate a trigger.	DELETE activates a trigger.
5	TRUNCATE is a DDL command	DELETE is a DML command
6	TRUNCATE TABLE cannot have a where clause	DELETE command can be used with or without where clause.

Q. What is normalization?

à Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuringdata dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

· Eliminate duplicative columns from the same table.

· Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

· Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

· Meet all the requirements of the second normal form.

· Remove columns that are not dependent upon the primary key.

Q. What is a view?

à Views are used as security mechanisms in databases. Because it restricts the user from viewing certain column and rows. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. The rest of the data is totally abstract from the end user.

CREATE VIEW [View Name]

[SELECT Statement]

DROP VIEW SAMPLEVIEW

Types of Views:

· System Views

o Information Schema View

o Catalog View

o Dynamic Management View (DMV)

· User Defined Views

o Simple View

o Complex View

When to Use a View

There are a number of scenarios where we will like to create our own View:

1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.

2. To control access to rows and columns of data.

Schema BindSchema Binding Option

If we create a View with the SCHEMABINDING option, it will lock the tables being referred to by the View and restrict all kinds of changes that may change the table schema (no Alter command). While creating a schema binding View, we can't mention "Select * from tablename" with the query. We have to mention all column names for reference. Also we need to add the schema name (like dbo.) before the name in view.

Encryption

This option encrypts the definition. This option encrypts the definition of the View. Users will not be able to see the definition of the View after it is created. This is the main advantage of the View where we can make it secure:

CREATE VIEW DemoView

With ENCRYPTION

SELECT

EmpID,

EmpName

FROM dbo.EmpInfo

Note: Once the View is encrypted, there is no way to decrypt it again.

Parametrized Views: Parametrized views can be achieved by creating an in-line table valued function.

CREATE FUNCTION dbo.GetCertificate(@CertificateID int)
RETURNS TABLE
AS
RETURN SELECT CertificationID, CertificationName
FROM dbo.vw_TMS_Certifications
WHERE CertificationID = @CertificationID
GO

Indexed View:

An indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation.

Indexed views require a more rigid definition of what they will return. For this reason, we cannot use a wildcard (*) in the underlying query. Each column must be individually called out; also, each table named in the query must be in 2 part dot notation (dbo.tablename), referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.

CREATE VIEW DemoSampleView

With SCHEMABINDING

SELECT

EmpID,

EmpName

FROM dbo.EmpInfo

Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine.

create unique clustered index IX_DemoSampleView_EmpIdon DemoSampleView(EmpID)

drop index IX_DemoSampleView_EmpId on DemoSampleView

Q. Types of different joins?

Self Join:

Q. What is data warehousing?

Q. difference between where clause and having clause?

1. HAVING can be used only with a select statement, whereas WHERE clause can be used in any DML statement.

2. HAVING is typically used with GROUP BY clause, when GROUP BY is not used HAVING behave likes a WHERE clause.

Q. What is SQL profiler?

Q. What is QUOTED_IDENTIFIER?

Q. What is MERGE statement?

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);

CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target

MERGE INTO dbo.tbl_Target AS t

USING dbo.tbl_Source AS s

ON t.id = s.id

WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN

--Row exists and data is different

UPDATE SET t.name = s.name, t.qty = s.qty

WHEN NOT MATCHED THEN

--Row exists in source but not in target

INSERT VALUES (s.id, s.name, s.qty)

WHEN SOURCE NOT MATCHED THEN

--Row exists in target but not in source

DELETE OUTPUT$action, inserted.id, deleted.id

Q. In what scenarios can SQL go for full table scan?

Q. what is required before CTE and what all restrictions are there for CTE?

Q. How to search for a text in SQL objects?

DECLARE @Search varchar(255)

SET @Search=',DAILY_IN.[SERVICE]'

SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m

INNER JOIN sys.objects o

ON m.object_id=o.object_id

WHERE m.definition Like '%'+@Search+'%'

Q. can we use order by clause in sub-query?

à Order by clause cannot be used in sub queries unless you are using Top or For XML in sub query.

Q. Pivot functions in SQL?

Q. Checklist for Index Optimization

à below is the list:

1. Create index on frequently used columns in T-SQL code. Columns used in where, order by and group by are good candidate for indexes. Create index on column which are used in JOIN condition.

2. Remove any unnecessary indexes. As index occupies hard drive space as well as it decreases the performance of all inserts, updates, deletes to the table.

3. Smaller index key gives better performance than index key which covers large data or many columns.

4. Index on integer columns gives better performance than varchar columns.

5. Clustered index must exist before creating Non-Clustered index.

6. Clustered index must be created on single column which is not changing and narrow in size. Best candidate is primary key.

7. Non-clustered indexes increases performance of the query that returns fewer rows and rows has wide selectivity spectrum.

8. Each table must have one clustered index.

9. If column have low selectivity avoid creating index on that column as it slow down the rows modification and system will not get benefit from index.

10. Multiple columns index or covered index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.

11. Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create index.

12. Rebuild indexes frequently using ALTER INDEX and De-Fragment index to keep performance optimal for indexes.

Q. Can we create Non-Unique Clustered Index?

à Yes, SQL Server internally adds a four length value to make it unique.

--Create Non-Unique Clustered Index

CREATE CLUSTERED INDEX [IX_Employee] ON [dbo].[Employee]

(

[id] ASC

)

--Create Unique Clustered Index

CREATE UNIQUE CLUSTERED INDEX [IX_Employee] ON [dbo].[Employee]

(

[id] ASC

)

Q. What is output clause in SQL Server?

à OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005

Example 1: Use OUTPUT clause into table with insert statement.

CREATE TABLE TESTDATA

(

ID INT,

NAME VARCHAR(50)

)

DECLARE @DATA TABLE

(

ID INT,

NAME VARCHAR(50)

)

INSERT INTO TESTDATA

OUTPUT inserted.ID, inserted.NAME INTO @DATA

SELECT 1, 'A'

UNION

SELECT 2, 'B'

UNION

SELECT 3, 'C'

SELECT * FROM @DATA

SELECT * FROM TESTDATA

DROP TABLE TESTDATA

Example 2: OUTPUT clause with insert statement, without using any table variable or temporary table.

CREATE TABLE TESTDATA

(

ID INT,

NAME VARCHAR(50)

)

INSERT INTO TESTDATA

OUTPUT inserted.ID, inserted.NAME

SELECT 1, 'A'

UNION

SELECT 2, 'B'

UNION

SELECT 3, 'C'

DROP TABLE TESTDATA

Example 3: OUTPUT clause into table with update statement.

CREATE TABLE TESTDATA

(

ID INT,

NAME VARCHAR(50)

)

DECLARE @DATA TABLE

(

ID INT,

NAME VARCHAR(50),

OLD_ID INT,

OLD_NAME VARCHAR(50)

)

INSERT INTO TESTDATA

SELECT 1, 'A'

UNION

SELECT 2, 'B'

UNION

SELECT 3, 'C'

UPDATE TESTDATA

SET NAME = 'Z'

OUTPUT inserted.ID, inserted.NAME, deleted.ID,deleted.NAME INTO @DATA

WHERE ID IN (1, 2, 3)

SELECT * FROM @DATA

SELECT * FROM TESTDATA

DROP TABLE TESTDATA

Example 4: OUTPUT clause into table with DELETE statement.

CREATE TABLE TESTDATA

(

ID INT,

NAME VARCHAR(50)

)

DECLARE @DATA TABLE

(

ID INT,

NAME VARCHAR(50)

)

INSERT INTO TESTDATA

SELECT 1, 'A'

UNION

SELECT 2, 'B'

UNION

SELECT 3, 'C'

DELETE TESTDATA

OUTPUT deleted.ID, deleted.NAME INTO @DATA

WHERE ID IN (3)

SELECT * FROM @DATA

SELECT * FROM TESTDATA

DROP TABLE TESTDATA

To release the space after deleting any column from a table.

DBCC CLEANTABLE (RAJNISH,"DBO.EMPINFO", 0)

WITH NO_INFOMSGS;

ALTER TABLE DBO.EMPINFO

REBUILD

Difference between SQL Server 2005/2008/2008 R2/Denali

Difference between SSIS 2005/2008/2008 R2/Denali

SSRS

How to take database backup and restore.

Functions, triggers, cursors

Query optimization, performance tuning, indexes, database designing

If a truncate table statement has been executed in a transaction and that transaction is rollback later on, then truncate statement will also be reverted, bcoz SQL Server does logs the dealocation of data page.

To release the memory

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'max server memory (MB)', N'2000'

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

Data Types:

SQL Server provides both datatypes to store character information. For the most part the two datatypes are identical in how you would work with them within SQL Server or from an application. The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in your database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages.

Excel always stores the data in Unicode format.

Datatype	Min	Max	Storage	Notes
Bigint	-2^63	2^63 – 1	8 bytes
Int	-2,147,483,648	2,147,483,647	4 bytes
Smallint	-32,768	32,767	2 bytes
Tinyint	0	255	1 bytes
Bit	0	1	1 bytes
Decimal	-10^38+1	10^38-1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes
Float	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53
DateTime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes
Char	0 chars	8000 chars	Defined Width
Varchar	0 chars	8000 chars	2 bytes + no. of characters
Varchar(max)	0 chars	2^31 chars	2 bytes + no. of characters
Text			4 bytes + no. of characters
NChar	0 chars	4000 chars	Defined width * 2
Nvarchar	0 chars	4000 chars

declare @test varchar(2)

declare @first varchar(4)

declare @second varchar(4)

declare @third varchar(3)

select @first = ISNULL(@test, 'test')

select @second = coalesce(@test, 'test')

select @third = coalesce(@test, 'test')

print @first

print @second

print @third

Output:

test

tes

Ranking Functions:

· ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

SELECT NAME, GENDER FROM #TEMP1

ORDER BY ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BYNAME), GENDER

· RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

· DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

select * from (

select firstname, surname, salary, dense_rank() over (orderby salary desc) rankid from Employee) rs

where rankid < 3

· NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

Distributes the rows in an ordered partition into a specified number of groups. It can be used for paging the big resultset.

Q. What is CTE and what are advantages of CTE?

CTE is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is like a derived table, which is not a stored object and last for the duration of query.

Points to be noted for CTE:

· CTE are not replacement of the Temp Table or Temp Variable Table

· Always begin CTE with semi-comma

· The scope of the CTE is limited to very first SELECT statement

· Usually the execution plan of the CTE like sub-query but there are cases when it can be different as well

· Use MAXRECURSION to prevent infinite loop in recursive CTE

Advantages of CTE:

· Using CTE improves the readability and makes maintenance of complex queries easy.

· The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.

· CTE can be defined in functions, stored procedures, triggers and even views.

· After a CTE is defined, it can be used as a table or view and can SELECT, INSERT, UPDATE and DELETE data.

Recursive CTE:

USE AdventureWorks

;WITH Emp_CTE AS (

SELECT EmployeeID, ContactID, LoginID, ManagerID,Title, BirthDate

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.ContactID, e.LoginID,e.ManagerID, e.Title, e.BirthDate

FROM HumanResources.Employee e

INNER JOIN Emp_CTE ecte

ON ecte.EmployeeID = e.ManagerID

)

SELECT *

FROM Emp_CTE

Multiple CTE:

/* Method 1 */

;WITH CTE1 AS (SELECT 1 AS Col1),

CTE2 AS (SELECT 2 AS Col2)

SELECT CTE1.Col1,CTE2.Col2

FROM CTE1

CROSS JOIN CTE2

/* Method 2 */

;WITH CTE1 AS (SELECT 1 AS Col1),

CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)

SELECT CTE1.Col1,CTE2.Col2

FROM CTE1

CROSS JOIN CTE2

Recursive CTE with limitation:

USE AdventureWorks

;WITH Emp_CTE AS (

SELECT EmployeeID, ContactID, LoginID, ManagerID, Title,BirthDate

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID,e.Title, e.BirthDate

FROM HumanResources.Employee e

INNER JOIN Emp_CTE ecte

ON ecte.EmployeeID = e.ManagerID

)

SELECT *

FROM Emp_CTE OPTION (MAXRECURSION 5)

Now if your CTE goes beyond 5th recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.

For example if you change MAXRECURSION to value 3. It will throw following error.

The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

In summary MAXRECUSION is good way to protect your CTE to go into infinite loop.

Set Operators:

· UNION

· UNION ALL

· INTERSECT: The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT operator does not distinguish betweenNULLs. The INTERSECT operator removes duplicate rows from the final result set. The INTERSECT ALL operator does not remove duplicate rows from the final result set.

· INTERSECT ALL

· EXCEPT

“TOP” Clause:

è We can update or delete data from a table by using the “TOP” clause.

è We can pass variable to change the number of the rows on the fly.

declare @n int

set @n=3

select top (@n) * from MyTable2

è The TOP clause also comes with another feauture called TIES. If you would like to include similar criteria together when using TOP clause, then TIES can be used.

For example the following query returns the TOP 10 % of the table.

select top 10 percent

from employee order by managerid desc

But we know that there is another row with the same managerid values. Now let us include the TIES option in the query.

select top 10 percent with ties

from employee order by managerid desc

select left('abcd', 2)

--ab

select right('abcd', 2)

--cd

“APPLY” Clause:

We can also consider CROSS APPLY clause as INNER APPLY as it will use outer (main) query as an input of sub-query or function and will return the result set. In CROSS APPLY we will be getting full set of left side query (main query) and its corresponding value from right side query or function, if it is not available in right side query or function, it will return NULL.

CROSS APPLY clause works like an inner apply whereas OUTER APPLY works like a Left apply.

SELECT p.ProductID, p.Name, p.ProductNumber

, ca.Quantity, ca.LocationID

FROM

Production.Product AS p

CROSS/OUTER APPLY

(SELECT TOP(10) * FROM Production.ProductInventory

WHERE ProductID = p.ProductID) ca

WHERE

p.ProductID in (1,2,3,531,706)

ORDER BY

p.ProductID ASC

SELECT p.ProductID, p.Name, p.ProductNumber

, pmi.Quantity, pmi.LocationID

FROM

Production.Product AS p

CROSS APPLY

dbo.fn_GetMax_ProductItem(p.ProductID, 2) AS pmi

WHERE

p.ProductID in (1,2,3,531,706)

ORDER BY

p.ProductID ASC

Diff. between @@IDENTITY, SCOPE_IDENTITY & IDENT_CURRENT:

è IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

USE AdventureWorks;

SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;

The IDENT_CURRENT function returns NULL when the function is invoked on an empty table or on a table that has no identity column.

è @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

è SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

USE AdventureWorks;

IF OBJECT_ID(N't6', N'U') IS NOT NULL

DROP TABLE t6;

IF OBJECT_ID(N't7', N'U') IS NOT NULL

DROP TABLE t7;

CREATE TABLE t6(id int IDENTITY);

CREATE TABLE t7(id int IDENTITY(100,1));

CREATE TRIGGER t6ins ON t6 FOR INSERT

BEGIN

INSERT t7 DEFAULT VALUES

END;

--End of trigger definition

SELECT id FROM t6;

--id is empty.

SELECT id FROM t7;

--ID is empty.

--Do the following in Session 1

INSERT t6 DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('t7');

/* Returns value inserted into t7, that is in the trigger.*/

SELECT IDENT_CURRENT('t6');

/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('t7');

/* Returns the last value inserted into t7.*/

“PIVOT”:

USE AdventureWorks

select distinct con.FirstName + ' ' + con.LastName CustomerName, p.Name, s.LineTotal

into #temp1

from [Sales].[SalesOrderDetail] s

join Sales.SalesOrderHeader sh

on sh.SalesOrderID = s.SalesOrderID

join Sales.Customer c

on c.CustomerID = sh.CustomerID

join Sales.Individual i

on i.CustomerID = c.CustomerID

join Person.Contact con

on con.ContactID = i.ContactID

join Production.Product p

on p.ProductID = s.ProductID

select distinct name

into #temp2

from #temp1

order by 1

declare @sql nvarchar(max),

@col nvarchar(max)

select @col = coalesce(@col, '') + QUOTENAME(name) + ','

from (

select distinct name

from #temp2

) x

set @col = LEFT(@col, len(@col) - 1)

set @sql = N'SELECT CustomerName, $COL$ FROM #TEMP1

PIVOT (SUM(LineTotal) FOR NAME IN ($COL$)) AS PVT';

SET @sql = REPLACE(@SQL, '$COL$', @COL)

EXEC sp_executesql @sql;

drop table #temp1

drop table #temp2

“UNPIVOT”:

USE AdventureWorks

select distinct con.FirstName + ' ' + con.LastName CustomerName, p.Name, s.LineTotal

into #temp1

from [Sales].[SalesOrderDetail] s

join Sales.SalesOrderHeader sh

on sh.SalesOrderID = s.SalesOrderID

join Sales.Customer c

on c.CustomerID = sh.CustomerID

join Sales.Individual i

on i.CustomerID = c.CustomerID

join Person.Contact con

on con.ContactID = i.ContactID

join Production.Product p

on p.ProductID = s.ProductID

select distinct name

into #temp2

from #temp1

order by 1

declare @sql nvarchar(max),

@col nvarchar(max)

select @col = coalesce(@col, '') + QUOTENAME(name) + ','

from (

select distinct name

from #temp2

) x

set @col = LEFT(@col, len(@col) - 1)

set @sql = N'SELECT CustomerName, NAME, LineTotal FROM (SELECT CustomerName, $COL$ FROM #TEMP1

PIVOT (SUM(LineTotal) FOR NAME IN ($COL$)) AS PVT) AS P

UNPIVOT (LineTotal FOR NAME IN ($COL$)) as unpvt';

SET @sql = REPLACE(@SQL, '$COL$', @COL)

EXEC sp_executesql @sql;

drop table #temp1

drop table #temp2

Will a dataset converted to Pivot when revert to UnPivot the new dataset is similar to the original dataset.

è Yes, but in some cases it might not. It depends on the logic we have used generating the Pivot. If during pivot we don’t have any filter while merging the data, we will not be able to revert it.

GROUP BY WITH ROLLUP/WITH CUBE in SQL Server

select con.FirstName + ' ' + con.LastName CustomerName,p.Name Items

, convert(float, sum(s.LineTotal)) Total

from [Sales].[SalesOrderDetail] s

join Sales.SalesOrderHeader sh on sh.SalesOrderID =s.SalesOrderID

join Sales.Customer c on c.CustomerID = sh.CustomerID

join Sales.Individual i on i.CustomerID = c.CustomerID

join Person.Contact con on con.ContactID = i.ContactID

join Production.Product p on p.ProductID = s.ProductID

where con.ContactID in (16067, 15414)--, 16761, 4708)

group by con.FirstName + ' ' + con.LastName, p.Name

order by 1

Normal Group By statement will provide the above result set. If we want below result then we need to use ROLLUP or CUBE clause.

select case when grouping(con.FirstName + ' ' + con.LastName)= 1

then 'All Customers'

else con.FirstName + ' ' + con.LastName

end CustomerName

, case when grouping(p.Name) = 1

then 'All Items'

else p.Name

end Items

, convert(float, sum(s.LineTotal))

from [Sales].[SalesOrderDetail] s

join Sales.SalesOrderHeader sh on sh.SalesOrderID =s.SalesOrderID

join Sales.Customer c on c.CustomerID = sh.CustomerID

join Sales.Individual i on i.CustomerID = c.CustomerID

join Person.Contact con on con.ContactID = i.ContactID

join Production.Product p on p.ProductID = s.ProductID

where con.ContactID in (16067, 15414)

group by con.FirstName + ' ' + con.LastName, p.Name

with rollup

order by 1

select case when grouping(con.FirstName + ' ' + con.LastName)= 1

then 'All Customers'

else con.FirstName + ' ' + con.LastName

end CustomerName

, case when grouping(p.Name) = 1

then 'All Items'

else p.Name

end Items

, convert(float, sum(s.LineTotal))

from [Sales].[SalesOrderDetail] s

join Sales.SalesOrderHeader sh on sh.SalesOrderID =s.SalesOrderID

join Sales.Customer c on c.CustomerID = sh.CustomerID

join Sales.Individual i on i.CustomerID = c.CustomerID

join Person.Contact con on con.ContactID = i.ContactID

join Production.Product p on p.ProductID = s.ProductID

where con.ContactID in (16067, 15414)

group by con.FirstName + ' ' + con.LastName, p.Name

with cube

order by 1

Logical Query Processing Phases – Order of Statement Execution:

1. FROM

2. ON

3. OUTER

4. WHERE

5. GROUP BY

6. CUBE | ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

Physical Query Processing might differ to Logical Query processing due to below reasons:

· If our query is using Temporary tables or Temporary Variable.

· If our query is having where clause on a column which is having indexes.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

SPARSE COLUMN:

Advantages of SPARSE column are:

· INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.

· SPARSE column can take advantage of filtered Indexes, where data are filled in the row.

· SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

· SPARSE column does not have IDENTITY or ROWGUIDCOL property.

· SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.

· SPARSE column can not have default value or rule or computed column.

· Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.

· Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.

· A table operation which involves SPARSE column takes performance hit over regular column.

Datepart:

D - Day of the month

Day - Day of the month

DD - Day of the month

DayOfYear - Day of the year

DY - Day of the year

Y - Day of the year

DW - WeekDay

W - WeekDay

WeekDay - WeekDay

HH - Hour of the day (24 hr format)

HOUR - Hour of the day (24 hr format)

M - Month of the year

MM - Month of the year

MONTH - Month of the year

MCS - MicroSeconds

MicroSecond - MicroSeconds

MI - Minutes

Minute - Minutes

N - Minute

MilliSecond - MilliSeconds

MS - MilliSeconds

NANOSECOND - NanoSeconds

NS - NanoSeconds

Q - Quarter of the year

QQ - Quarter of the year

Quarter - Quarter of the year

S - Second

Second - Second

SS - Second

Week - Week of the year

WK - Week of the year

WW - Week of the year

YEAR - Year

YY - Year

YYYY - Year

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan (table scan):
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

Type of Constraints:

· Not NULL: -

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

· Unique Key: -

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

· Primary Key: -

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

· Foreign Key: -

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

· Check: -

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

· Default:-

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

Learning Code & Activity

Sql Developer Questions

Q. Creating indexes on temporary tables

No comments:

Post a Comment