Wednesday, October 23, 2013

What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
  1. TRUNCATE:
  • 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 log.
  • TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains.
  • The counter used by an identity for new rows is reset to the seed for the column.
  • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
  • TRUNCATE cannot be rolled back.
  • TRUNCATE is DDL Command.
  • TRUNCATE Resets identity of the table
  1. DELETE:
  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead.
  • If you want to remove table definition and its data, use the DROP TABLE statement.
  • DELETE Can be used with or without a WHERE clause
  • DELETE Activates Triggers.
  • DELETE can be rolled back.
  • DELETE is DML Command.
  • DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

What's the difference between a primary key and a unique key?

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

But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

What are different types of Collation Sensitivity?

  1. Case sensitivity - A and a, B and b, etc.
  2.  
  3. Accent sensitivity
  4.  
  5. Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
  6.  
  7. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

What are the different index configurations a table can have?

A table can have one of the following index configurations:
  1. No indexes 
  2. A clustered index
  3. A clustered index and many nonclustered indexes
  4. A nonclustered index
  5. Many nonclustered indexes

Tuesday, September 11, 2012

Schema Binding and Indexed Views in SQL Server

Problem


Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound".



Solution

The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.


In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.
So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.
To use schema binding for views, first we will create a sample table in AdventureWorks database then a view "vw_sampleView" is created on this new table.
Create sample table and sample view

USE AdventureWorks

GO

SELECT * INTO SampleTable

FROM sales.SalesOrderDetail

GO

CREATE VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS

SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate

FROM dbo.SAMPLETABLE

GO



--If the view already existed we could use this to add SCHEMABINDING

ALTER VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS

SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate

FROM dbo.SAMPLETABLE

GO
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
So here we are with a SCHEMABOUND view "sampleView" on base table "sampleTable". Now we will check that we are able to create an index on the view.

An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view

CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView]

(

[rowguid] ASC

)

GO



When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding

You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".

You will not be able to create clustered index on a view if the view references any nondeterministic functions.

You can not use aggregate functions when using schema binding.

You can not migrate the base table of a schema bound view.

--------------------------------------------------------------------------------
Now we are left with our second part of the problem, how schema preservation is implemented for the base table. Again we have our "sampleTable" and "vw_sampleView" with schema binding. We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

By using SSMS to alter the data type of a column in the base table"sampleTable", I get a warning message notifying me that because of the schema bound view this will not work.

To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.

On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.


Ref :-  http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/

Wednesday, September 5, 2012

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups?
A. ROW_NUMBER
B. RANK
C. DENSE_RANK
D. NTILE
E. None of the Above

Ans :- D

Explanation:-
The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The RANK ranking function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Just like the DENSE_RANK, in the RANK ranking function, if two or more rows tie for a rank, each tied rows receive the same rank. Unlike the DENSE_RANK, the RANK ranking function does not always return consecutive integers.

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. If two or more rows tie for a rank in the same partition, each tied rows receive the same rank.

Saturday, June 2, 2012

@@IDENTITY VS SCOPE_IDENTITY() VS IDENT_CURRENT

SELECT @@IDENTITY


It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.

SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.


Reference : Pinal Dave (http://blog.SQLAuthority.com)