SQL SERVER – Explicitly Inserting a value into an Identity Column

When I am working with the .Net developers,there arise a situation to insert a value into the Identity column.I think most most of the developers know this answer because most people come around the situation and the solution is

SET IDENTITY_INSERT table_name ON –setting here that we are going

— to  insert a explicit value – to  the   the identity column

Note: A very interesting thing is that we can set this command to only one table at a time in  a Database. If we want to use it for another table,  we have to add SET IDENTITY_INSERT table_name OFF to the existing table where the identity insert is on.

HEAP VS CLUSTERED VS NON-CLUSTERED

I hope everyone knows , in Sql Server the data are stored in the data pages.

HEAP:

A table with no clustered index is called a heap.

Consider a table ‘test’ which contain two columns ID, Name which doesn’t contain a Clustered index. So the data stored in the data page is same as we see in the table.

TABLE

ID NAME
4 A
5 B
3 C
1 D

Data Page

ID NAME
4 A
5 B
3 C
1 D

Clustered Index:

If we put a clustered index on the column ID i.e. here Primary key, then the data stored in the data pages in a sorted order of the column specified. The clustered index are organized as

B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. The leaf node contains the data pages.

TABLE

ID(PK) NAME
4 A
5 B
3 C
1 D

Data Page

In data page the data is stores as follows,

ID(PK) NAME
1 D
3 C
4 A
5 B

Non-Clustered Index:

Non clustered index also organized as the B-tree Structure.

  • The data rows of the underlying table are not sorted and stored in order based on their non-clustered keys.
  • The leaf layer of a non-clustered index is made up of index pages instead of data pages.
  • If the table is heap, the row locater is a pointer to the row.
  • If the table is having the clustered index ,the row-locater is the clustered index key.

Reference & Images: http://msdn.microsoft.com/en-us/library/ms177443.aspx

If you like this post Please make a comment.