BISQL # 37 :Checking, Seeding and Reseeding feature of Identity in SQL {Identity Part–IV}
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
And last post for Queries for Identity BISQL # 36 :Queries to Understand more on Identity aspect in SQL {Identity Part–III} « SQL Server Mentalist
Today we are going to discuss about Seeding and Reseeding feature of Identity in SQL
Once the table’s in use state we can reset, or reseed the column. In other words, we can change the column’s seed value at any time. For instance, we might reseed a column after deleting records or moving data to an archive table.
Please make note – truncate will not work if we have relationships so we need to delete + reseed.
According to me seeding and reseeding an identity column is easy and relatively safe, if we do it properly.
Seeding
We have already seen on my post BISQL # 34 :What is Identity IN SQL {Identity Part–I} « SQL Server Mentalist
But just of addition You can decrease identity values by specify a negative value for increment.
Checking and reseeding
For instance, if we copy all the table’s records to an archive table and then delete all the records in the source table, we might want to reseed the source table’s identity column, so we can control this sequence.
We have T-SQL’s DBCC CHECKIDENT as follows to reseed an identity column:
DBCC CHECKIDENT (tblName,RESEED, 0)
Remember Using the DELETE statement is not enough. The statement only deletes the data but not reset the identity column.So when we need to delete the data from the table and after execute the DBCC command with CHECKIDENT switch in order to reset the data as shown above.
In general syntax for same is as follow
DBCC CHECKIDENT
(
<table name>
[, [NORESEED | RESEED [, newreseedvalue]]]
)
WITH NO_INFOMSGS]
Following charts defines this statement’s optional parameters
Some more facts for Identity
“An explicit value for the identity column in table ‘Name_TABLE’ can only be specified when a column list is used and IDENTITY_INSERT is ON."
Usually, we will get this error while inserting the records/data into the identity column.
The reason for this error is,
"We can’t explicitly insert data into the identity. If we want to achieve the same, we should first execute the command Identify insert on the table is ON"
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 # 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
· BISQL # 42 : How to find count of all the records in all the table
Hope you will like this post on Checking, seeding & reseeding of Identity Part IV.
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 8, 2011 at 9:35 pmLink Resource # 14 : August 8 – August 10 « Dactylonomy of Web Resource
SQL Server Mentalist 