BISQL # 36 :Queries to Understand more on Identity aspect in SQL {Identity Part–III}
Hi Friends,
Continuing from my last post on basics of identity :BISQL # 34 :What is Identity IN SQL {Identity Part–I} « SQL Server Mentalist
Also post on identity function BISQL # 35 : Deep dive into Identity Functions in SQL {Identity Part–II} « SQL Server Mentalist
Today we are going to discuss about Some query related to Identity aspect in SQL
Although the Identity column is very useful in maintain the data in the table, sometimes we need to set this constraint off like when we importing the data into the database.
Following are some constraint related configuration query for Identity
1.Set Identity Off
To set the Identity column property off, the following command is used.
Set Identity_insert <Table name> Off
2.Set Identity On
To set the Identity column property On, the following command is used.
Set Identity_insert <Table name> On
3.Reseed Identity
We can also reset the values of the identity column to some different value with the help of the following command
dbcc checkident (Tablename, reseed, 10)
From above we can conclude some points for Identity
Identity column in the Sql server is a very useful property and it can be used to retrieve the data very quickly specially in the table where no primary key is defined.
Note
- Only one Identity column is possible for a table.
- In case of truncate command on a table the identity column value is reset to 0.
For more interesting find on related topic on daily basis you can visit my link resource website :Link Resource # 11 August 1 – August 2 « Dactylonomy of Web Resource
Hope this helps !!
For more interesting information on SQL we can also look into similar topics such as
· BISQL # 37 :Checking, Seeding and Reseeding of Identity {Identity Part–IV}
· BISQL # 38 : Introduction To Function – {SQL Server Function Part – I }
· BISQL # 39 : Details of Row set functions – {SQL Server Function Part – II}
· BISQL # 40 : SQL Server Code Name “Denali” CTP3 Product Guide
· BISQL # 41 : Difference Between Char And Varchar
Hope you will like this post on Queries to understand on Identity aspect Part III.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
Copyright © 2011 – 2012 Vishal Pawar
-
August 3, 2011 at 9:39 amBISQL # 37 :Checking, Seeding and Reseeding feature of Identity in SQL {Identity Part–IV} « SQL Server Mentalist
SQL Server Mentalist 