Home > Query, SQL > BISQL # 37 :Checking, Seeding and Reseeding feature of Identity in SQL {Identity Part–IV}

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

image_thumb

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

image

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

         | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                    Copyright © 2011 – 2012 Vishal Pawar

About these ads
Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,115 other followers

%d bloggers like this: