how do i... reseed a sql server identity column tech_republic

How do I... Reseed a SQL Server identity column? | TechRepublic

http://www.techrepublic.com/blog/datacenter/how-do-i-reseed-a-sql-server-identity-column/406[08/29/2012 3:38:07 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

How do I... Reseed a SQLServer identity column?

Data MaskingSoftwareSecure your sensitive data with Grid-Tools'Enterprise Data Maskingwww.grid-tools.com

Multilingual WebCenterTranslate Content Inside WebCenter LingotekCollaborative Translationlingotek.com/oracle

SQL Server TrainingAccess Videos, Articles and More. Join theSSWUG.ORG Community Today!www.sswug.org

Keep Up with TechRepublic

By Susan HarkinsJuly 15, 2008, 11:53 AM PDT

Microsoft SQL Servers identity column generates sequential values for newrecords using a seed value. The term seed refers to the internal value SQLServer uses to generate the next value in the sequence. By default, an identitycolumns first value is 1 and each new value increments by one (1, 2, 3, 4, andso on).

You can control the columns first and subsequent values, by specifying those values when youcreate the column. For instance, you might want to begin with a purchase order number of 1001rather than 1.

Once the tables in production you can reset, or reseed the column. In other words, you canchange the columns seed value at any time. For instance, you might reseed a column afterdeleting records or moving data to an archive table.

The good news is that seeding and reseeding an identity column is easy and relatively safe, if youdo it correctly. Its common to seed a new table or to reseed a production, but empty table.However, as a general rule, its not a great idea to reseed an identity column in a table thatcontains data without some serious checks and balances.

Throughout this article, I use SQL Server Express and Management Studio Express because bothare free and therefore available to all readers. However, the logic and instructions are the same inSQL Server.

This blog post is also available in the PDF format as a TechRepublic Download.

About identityDevelopers and writers alike tend to refer to SQL Servers identity column property as a data type.Its actually a column property. In purpose, its similar to Microsoft Access AutoNumber column,although SQL Servers identity column is more flexible. However, there are a few restrictions:

An identity column must be one of the following numeric data types: decimal, int, numeric,smallint, bigint, or tinyint.

An identity column cant accept or store NULL.

Each table can contain only one identity column.

When you create an identity column, you specify two values: Identity Seed and Identity Increment.The seed value specifies the columns first (or next) value. SQL Server adds the increment value

Follow this blog:

Five Apps

Google in the Enterprise

Subscribe Today

Follow us however you choose!

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BkTfX0tQ9ULaGIomSlAKRm4HoD57Rp7gCzoDL0TDAjbcBgKYdEAEYASDaxfUDKAM4AFD-8-7uB2B1oAGeg_L-A7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFVaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9hdm9pZGluZy1jdXJzb3JzLXdpdGgtc3FsLXNlcnZlci0yMDA1LzQxMoACAakCp_uqo32Iuj6oAwHoA9cC6AMf9QMCAABE4AbEzU8&num=1&sig=AOD64_0YV4QiqNfZkgg35HE3waOz2xZn4Q&client=ca-cnet-techrepublic-content&adurl=http://www.grid-tools.com/products/enterprise_data_masking.phphttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BkTfX0tQ9ULaGIomSlAKRm4HoD57Rp7gCzoDL0TDAjbcBgKYdEAEYASDaxfUDKAM4AFD-8-7uB2B1oAGeg_L-A7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFVaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9hdm9pZGluZy1jdXJzb3JzLXdpdGgtc3FsLXNlcnZlci0yMDA1LzQxMoACAakCp_uqo32Iuj6oAwHoA9cC6AMf9QMCAABE4AbEzU8&num=1&sig=AOD64_0YV4QiqNfZkgg35HE3waOz2xZn4Q&client=ca-cnet-techrepublic-content&adurl=http://www.grid-tools.com/products/enterprise_data_masking.phphttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BkTfX0tQ9ULaGIomSlAKRm4HoD57Rp7gCzoDL0TDAjbcBgKYdEAEYASDaxfUDKAM4AFD-8-7uB2B1oAGeg_L-A7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFVaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9hdm9pZGluZy1jdXJzb3JzLXdpdGgtc3FsLXNlcnZlci0yMDA1LzQxMoACAakCp_uqo32Iuj6oAwHoA9cC6AMf9QMCAABE4AbEzU8&num=1&sig=AOD64_0YV4QiqNfZkgg35HE3waOz2xZn4Q&client=ca-cnet-techrepublic-content&adurl=http://www.grid-tools.com/products/enterprise_data_masking.phphttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BkTfX0tQ9ULaGIomSlAKRm4HoD57Rp7gCzoDL0TDAjbcBgKYdEAEYASDaxfUDKAM4AFD-8-7uB2B1oAGeg_L-A7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFVaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9hdm9pZGluZy1jdXJzb3JzLXdpdGgtc3FsLXNlcnZlci0yMDA1LzQxMoACAakCp_uqo32Iuj6oAwHoA9cC6AMf9QMCAABE4AbEzU8&num=1&sig=AOD64_0YV4QiqNfZkgg35HE3waOz2xZn4Q&client=ca-cnet-techrepublic-content&adurl=http://www.grid-tools.com/products/enterprise_data_masking.phphttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BkTfX0tQ9ULaGIomSlAKRm4HoD57Rp7gCzoDL0TDAjbcBgKYdEAEYASDaxfUDKAM4AFD-8-7uB2B1oAGeg_L-A7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFVaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9hdm9pZGluZy1jdXJzb3JzLXdpdGgtc3FsLXNlcnZlci0yMDA1LzQxMoACAakCp_uqo32Iuj6oAwHoA9cC6AMf9QMCAABE4AbEzU8&num=1&sig=AOD64_0YV4QiqNfZkgg35HE3waOz2xZn4Q&client=ca-cnet-techrepublic-content&adurl=http://www.grid-tools.com/products/enterprise_data_masking.phphttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BinzW0tQ9ULaGIomSlAKRm4HoD9frwq0C38fDvy_AjbcBsJ8aEAIYAiDaxfUDKAM4AFCV9vq0_v____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBVWh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvYXZvaWRpbmctY3Vyc29ycy13aXRoLXNxbC1zZXJ2ZXItMjAwNS80MTKAAgGoAwHoA9cC6AMf9QMCAABE4AargN8D&num=2&sig=AOD64_0glwP1fkK84SKpQbpPho1oAsa7Sg&client=ca-cnet-techrepublic-content&adurl=http://www.lingotek.com/oraclehttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BinzW0tQ9ULaGIomSlAKRm4HoD9frwq0C38fDvy_AjbcBsJ8aEAIYAiDaxfUDKAM4AFCV9vq0_v____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBVWh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvYXZvaWRpbmctY3Vyc29ycy13aXRoLXNxbC1zZXJ2ZXItMjAwNS80MTKAAgGoAwHoA9cC6AMf9QMCAABE4AargN8D&num=2&sig=AOD64_0glwP1fkK84SKpQbpPho1oAsa7Sg&client=ca-cnet-techrepublic-content&adurl=http://www.lingotek.com/oraclehttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BinzW0tQ9ULaGIomSlAKRm4HoD9frwq0C38fDvy_AjbcBsJ8aEAIYAiDaxfUDKAM4AFCV9vq0_v____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBVWh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvYXZvaWRpbmctY3Vyc29ycy13aXRoLXNxbC1zZXJ2ZXItMjAwNS80MTKAAgGoAwHoA9cC6AMf9QMCAABE4AargN8D&num=2&sig=AOD64_0glwP1fkK84SKpQbpPho1oAsa7Sg&client=ca-cnet-techrepublic-content&adurl=http://www.lingotek.com/oraclehttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BinzW0tQ9ULaGIomSlAKRm4HoD9frwq0C38fDvy_AjbcBsJ8aEAIYAiDaxfUDKAM4AFCV9vq0_v____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBVWh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvYXZvaWRpbmctY3Vyc29ycy13aXRoLXNxbC1zZXJ2ZXItMjAwNS80MTKAAgGoAwHoA9cC6AMf9QMCAABE4AargN8D&num=2&sig=AOD64_0glwP1fkK84SKpQbpPho1oAsa7Sg&client=ca-cnet-techrepublic-content&adurl=http://www.lingotek.com/oraclehttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BOAnR0tQ9ULaGIomSlAKRm4HoD421nrUCxeeO8zjAjbcB8JMJEAMYAyDaxfUDKAM4AFCeh7GO_f____8BYHWgAdSZvf8DsgEUd3d3LnRlY2hyZXB1YmxpYy5jb23IAQHaAVVodHRwOi8vd3d3LnRlY2hyZXB1YmxpYy5jb20vYmxvZy9kYXRhY2VudGVyL2F2b2lkaW5nLWN1cnNvcnMtd2l0aC1zcWwtc2VydmVyLTIwMDUvNDEygAIBqAMB6APXAugDH_UDAgAAROAG1OEF&num=3&sig=AOD64_1uIHdNmHKMKNtWBLFAnxcShfoEdg&client=ca-cnet-techrepublic-content&adurl=http://www.sswug.org/membersignup.aspx%3Fpg%3Dgawhttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BOAnR0tQ9ULaGIomSlAKRm4HoD421nrUCxeeO8zjAjbcB8JMJEAMYAyDaxfUDKAM4AFCeh7GO_f____8BYHWgAdSZvf8DsgEUd3d3LnRlY2hyZXB1YmxpYy5jb23IAQHaAVVodHRwOi8vd3d3LnRlY2hyZXB1YmxpYy5jb20vYmxvZy9kYXRhY2VudGVyL2F2b2lkaW5nLWN1cnNvcnMtd2l0aC1zcWwtc2VydmVyLTIwMDUvNDEygAIBqAMB6APXAugDH_UDAgAAROAG1OEF&num=3&sig=AOD64_1uIHdNmHKMKNtWBLFAnxcShfoEdg&client=ca-cnet-techrepublic-content&adurl=http://www.sswug.org/membersignup.aspx%3Fpg%3Dgawhttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BOAnR0tQ9ULaGIomSlAKRm4HoD421nrUCxeeO8zjAjbcB8JMJEAMYAyDaxfUDKAM4AFCeh7GO_f____8BYHWgAdSZvf8DsgEUd3d3LnRlY2hyZXB1YmxpYy5jb23IAQHaAVVodHRwOi8vd3d3LnRlY2hyZXB1YmxpYy5jb20vYmxvZy9kYXRhY2VudGVyL2F2b2lkaW5nLWN1cnNvcnMtd2l0aC1zcWwtc2VydmVyLTIwMDUvNDEygAIBqAMB6APXAugDH_UDAgAAROAG1OEF&num=3&sig=AOD64_1uIHdNmHKMKNtWBLFAnxcShfoEdg&client=ca-cnet-techrepublic-content&adurl=http://www.sswug.org/membersignup.aspx%3Fpg%3Dgawhttp://googleads.g.doubleclick.net/aclk?sa=l&ai=BOAnR0tQ9ULaGIomSlAKRm4HoD421nrUCxeeO8zjAjbcB8JMJEAMYAyDaxfUDKAM4AFCeh7GO_f____8BYHWgAdSZvf8DsgEUd3d3LnRlY2hyZXB1YmxpYy5jb23IAQHaAVVodHRwOi8vd3d3LnRlY2hyZXB1YmxpYy5jb20vYmxvZy9kYXRhY2VudGVyL2F2b2lkaW5nLWN1cnNvcnMtd2l0aC1zcWwtc2VydmVyLTIwMDUvNDEygAIBqAMB6APXAugDH_UDAgAAROAG1OEF&num=3&sig=AOD64_1uIHdNmHKMKNtWBLFAnxcShfoEdg&client=ca-cnet-techrepublic-content&adurl=http://www.sswug.org/membersignup.aspx%3Fpg%3Dgawhttp://cbsiprivacy.custhelp.com/app/answers/detail/a_id/1272/http://search.techrepublic.com.com/index.php?q=sql+server&t=1http://www.techrepublic.com/search?q=susan+harkinshttp://search.techrepublic.com.com/index.php?q=sql+server&t=1http://downloads.techrepublic.com.com/abstract.aspx?docid=376267http://techrepublic.com.com/5264-1-0.html?query=application%20developmenthttp://search.techrepublic.com.com/index.php?q=microsoft+access&t=1http://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fhow-do-i-reseed-a-sql-server-identity-column%2F406&frequency=weekly&rss_type=38http://www.facebook.com/TechRepublichttp://twitter.com/techrepublichttps://plus.google.com/115467927011694256253/http://www.linkedin.com/groups?gid=38635http://digg.com/techrepublichttp://www.techrepublic.com/rssfeedshttps://market.android.com/details?id=com.lionbridge.android.techrepublichttp://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



View All

Ask a Question

View All

Media Gallery

to the last identity value to generate the next value, in sequence.

For instance, a seed value of 1 and an increment value of 2 will generate the values 1, 3, 5, 7, andso on. By default, both the seed and incremental values are 1. Business rules often require a bit ofcustomization.

SeedingUsing Management Studio is probably the easiest way to seed an identity column when you createthe table. In this case, its a simple matter of setting the appropriate property value, as you cansee in Figure A. You can also use Management Studio to reseed an existing identity column.

Figure A

Seed an identity column when you create it in Management Studio.

If you create the table using code, you can easily seed the tables identity column using theTransact-SQL (T-SQL) CREATE TABLE statement in the following form:

CREATE TABLE tablename

(

columnname datatype identity [(seed, increment)

[NOT FOR REPLICATION]],

[columnname ...]

)

In the code, datatype is a numeric column. Both seed and increment are optional and the defaultvalue for both is 1.

Figure B shows the result of using CREATE TABLE to create a table named Orders and settingthe OrderID columns identity seed and increment values to 100 and 10, respectively. As you cansee, the first identity value is 100 and each subsequent value increases by 10. (You can decreaseidentity values by specify a negative value for increment.)

Figure B

Hot Questions

Hot Discussions

More Galleries

More Videos

SSL redirection3

Switching from a Job to a career inthe IT field: Need an IT pro'sadvice

3

windows 7 won't shutdown andkeeps switching on

2

can anyone suggest if any suchsoftware exist with similarfunctionality?

2

Should developers be sued forsecurity holes?

221

The sitting duck that is opensource

79

Five fast Windows desktop searchutilities

27

Is the death knell sounding fortraditional antivirus?

30

PHOTO GALLERY (1 of 15)Curiosity's autonomous'seven minutes of...

VIDEO (1 of 13)Cracking Open: HTC Titan II

http://www.techrepublic.com/forum/questions/hothttp://www.techrepublic.com/forum/questions/posthttp://www.techrepublic.com/forum/questions/posthttp://www.techrepublic.com/forum/discussions/hothttp://www.facebook.com/TechRepublichttp://twitter.com/techrepublichttps://plus.google.com/115467927011694256253/http://www.linkedin.com/groups?gid=38635http://digg.com/techrepublichttp://www.techrepublic.com/rssfeedshttps://market.android.com/details?id=com.lionbridge.android.techrepublichttp://itunes.apple.com/us/app/techrepublic/id426493569http://www.techrepublic.com/photoshttp://www.techrepublic.com/videoshttp://www.techrepublic.com/forum/questions/101-394302/ssl-redirectionhttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394299/windows-7-wont-shutdown-and-keeps-switching-onhttp://www.techrepublic.com/forum/questions/101-394299/windows-7-wont-shutdown-and-keeps-switching-onhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/discussions/102-394196http://www.techrepublic.com/forum/discussions/102-394196http://www.techrepublic.com/forum/discussions/102-394277http://www.techrepublic.com/forum/discussions/102-394277http://www.techrepublic.com/forum/discussions/102-394303http://www.techrepublic.com/forum/discussions/102-394303http://www.techrepublic.com/forum/discussions/102-394285http://www.techrepublic.com/forum/discussions/102-394285http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899



Start a Discussion

Blog Archive

Use CREATE TABLE to seed an identity column.

Checking and reseedingFor instance, if you copy all the tables records to an archive table and then delete all the recordsin the source table, you might want to reseed the source tables identity column, so you cancontrol the sequence. Use T-SQLs DBCC CHECKIDENT as follows to reseed an identity column:

DBCC CHECKIDENT

(

tablename

[, [NORESEED | RESEED [, newreseedvalue]]]

)

[WITH NO_INFOMSGS]

Table A defines this statements optional parameters.

Table A: DBCC CHECKIDENT

Parameter Description

NORESEEDReturns the current identity value and the current maximum value ofthe identity column, without reseeding. These values are usually (andshould be) the same.

RESEEDChanges the current identity value, using the maximum value in theidentity column, if the current identity value is less than the maximumidentity value stored in the identity column.

newreseedvalue

Specifies the new seed value when reseeding. If the table is empty,the first identity value (after executing DBCC CHECKIDENT) will equalnewreseedvalue. If the table contains data, the next identity value willequal newreseedvalue + the current increment value (the default is 1).This behavior is new to SQL Server 2005 (and remains in 2008). SQLServer 2000 always increments the seed value.

WITH NO INFOMSGS Suppresses all informational messages.

August 2012

July 2012

June 2012

May 2012

April 2012

March 2012

February 2012

January 2012

December 2011

November 2011

October 2011

September 2011

August 2011

July 2011

June 2011

http://www.techrepublic.com/forum/discussions/posthttp://www.techrepublic.com/forum/discussions/posthttp://www.techrepublic.com/blog/datacenter/201208http://www.techrepublic.com/blog/datacenter/201207http://www.techrepublic.com/blog/datacenter/201206http://www.techrepublic.com/blog/datacenter/201205http://www.techrepublic.com/blog/datacenter/201204http://www.techrepublic.com/blog/datacenter/201203http://www.techrepublic.com/blog/datacenter/201202http://www.techrepublic.com/blog/datacenter/201201http://www.techrepublic.com/blog/datacenter/201112http://www.techrepublic.com/blog/datacenter/201111http://www.techrepublic.com/blog/datacenter/201110http://www.techrepublic.com/blog/datacenter/201109http://www.techrepublic.com/blog/datacenter/201108http://www.techrepublic.com/blog/datacenter/201107http://www.techrepublic.com/blog/datacenter/201106



Technically, DBCC CHECKIDENT checks and corrects an identity value. Simply put, use it to learnthe current identity value or to reseed an existing identity column.

ChecksOnce a tables in production, you might want to determine the seed value, as shown in Figure C.Executing DBCC CHECKIDENT with no argument other than the tables name returns thecolumns current identity value, which is the last value generated and stored in the table (seeFigure B).

Figure C

Return an identity columns current and maximum values.

These two values will usually be the same. If theyre not, reset the identity value to avoid errors orgaps in the sequence of values.

BalancesSQL Server wont execute a statement that duplicates existing identity values if the identity columnis part of a primary key. Figure D shows what can happen when an identity column isnt part of aprimary key. In this case, DBCC CHECKIDENT reseeds OrderID and accepts a new record.However, the identity column generates a duplicate value. Its important to remember that anidentity column does not guarantee uniqueness.

Figure D



Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

What can happen when an identity column isnt part of a primary key.

When the identity column is part of a primary key, SQL Server protects the validity of the existingdata by returning a violation error, as shown in Figure E. Now, the question is, did SQL Serverreset the seed value or not it did. You can reseed the column to a value that has the potentialto create a duplicate, but SQL Server wont let you enter the record. Thats why you must becareful to check existing identity values before reseeding.

Figure E

A violation error.

When reseeding identity values, keep the following behaviors in mind:

Setting the newreseed value below existing values will eventually generate an error if thecolumn is part of a primary key.

Use the exact seed value when running DBCC CHECKIDENT against a new table that hasnever contained data. For instance, a newreseed value of 10 will generate an initial identityvalue 10.

When resetting an empty table (after deleting records), the identity column will generatenewreseed + 1. If the next identity value should be 1, use a newreseed value of 0.

Deleting records from a table using DELETE doesnt reset an identity column; usingTRUNCATE TABLE does. TRUNCATE TABLE is usually faster than DELETE, but in this case,not necessarily the best choice for the job, unless you mean to reset the identity column.

Reseeding basicsReseeding an identity column is a common task, but you must know the basics or you might getinto trouble. Specifically, an identity column can generate duplicate values. Knowing when andwhy is the key to keeping an identity column in check.

Susan Sales Harkins is an independent consultant and the author of several articles and books ondatabase technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express,with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy areAutomating Microsoft Access 2003 with VBA, Upgraders Guide to Microsoft Office System2003, ICDL Exam Cram 2, and Absolute Beginners Guide to Microsoft Access 2003 all byQue. Currently, Susan volunteers as the Publications Director for Database Advisors athttp://www.databaseadvisors.com. You can reach her at [email protected].

Disabling CustomerExperience ImprovementProgram tasks in WindowsServer 2008

Measure Dell M1000e andM600 Power Consumption

http://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/blog/datacenter/disabling-customer-experience-improvement-program-tasks-in-windows-server-2008/384http://www.techrepublic.com/blog/datacenter/disabling-customer-experience-improvement-program-tasks-in-windows-server-2008/384http://www.techrepublic.com/blog/datacenter/disabling-customer-experience-improvement-program-tasks-in-windows-server-2008/384http://www.techrepublic.com/blog/datacenter/disabling-customer-experience-improvement-program-tasks-in-windows-server-2008/384http://www.techrepublic.com/blog/datacenter/disabling-customer-experience-improvement-program-tasks-in-windows-server-2008/384http://www.techrepublic.com/blog/datacenter/measure-dell-m1000e-and-m600-power-consumption/410http://www.techrepublic.com/blog/datacenter/measure-dell-m1000e-and-m600-power-consumption/410http://www.techrepublic.com/blog/datacenter/measure-dell-m1000e-and-m600-power-consumption/410



10Comments

Add Your Opinion

See All CommentsMy Contacts

Join Login

See all comments

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

Join the conversation!Follow via:

Staff Picks Top Rated Most Recent

View in thread

IDENTITY columnssqllion 20th Aug 2011

All features of SQL Server IDENTITY property of columns. And a handy procedure tomonitor all Identity columns. http://www.sqllion.com/2011/08/identity-in-sql/

0Votes

I had to execute the procedure twice for each table, first it set the current column andnext time it set the current identity. I did it through Visual Studio though so that mighthave something to do... Read Whole Comment +

View in thread

Useful, Thanksepsilonv 24th Aug 2010

0Votes

For more information on Identifying and listing all the IDENTITY columns present in agiven database with filtering option available for specific tables, refer the below link:...Read Whole Comment +

View in thread

RE: How do I... Reseed a SQL Server identity column?sqllion Updated - 21st Aug 2011

0Votes

http://www.techrepublic.com/forum/discussions/102-269088http://www.techrepublic.com/forum/discussions/102-269088http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/forum/discussions/102-269088http://www.techrepublic.com/forum/discussions/102-269088/rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fforum%2Fdiscussions%2F102-269088%2Frss&title=Discussion+on+How+do+I...+Reseed+a+SQL+Server+identity+column%3F+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fhow-do-i-reseed-a-sql-server-identity-column%2F406&frequency=weekly&rss_type=42http://www.techrepublic.com/forum/discussions/102-269088-3485356http://www.techrepublic.com/members/profile/6130658http://www.sqllion.com/2011/08/identity-in-sql/http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-269088-3348038http://www.techrepublic.com/members/profile/6503836http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-269088-3105031http://www.techrepublic.com/members/profile/6130658http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote

techrepublic.comHow do I... Reseed a SQL Server identity column? | TechRepublic

VyLWlkZW50aXR5LWNvbHVtbi80MDYA: form1: email: newsletters: e059:INTERNAL_NEWSLETTERnewsletters_(1): e062:INTERNAL_NEWSLETTERbutton0: q: button3:

how do i... reseed a sql server identity column tech_republic

Documents