sql server new users: reseed primary key doesn't work -- sql server new users

You're in the

sql server new users

group:

reseed primary key doesn't work

reseed primary key doesn't work

André
3/18/2007 12:00:00 AM

sql server new users:

Hi,

i use sql server express 2005. I defined a table "mytable" with a primary
key (OrderId , type INT). I want to reseed the value of the primary key
(starting with 1).
The table is in a .mdf file, so i first attached it (with sql server
management studio express) and then running this query:
DBCC CHECKIDENT ('dbo.mytable', RESEED, 1)

I get this:
"checking identity information: current identity value '168', current column
value '1'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."

But when doing: "select OrderId from dbo.mytable"
the first record still starts with value 105 till 168.

How to change the value of OrderId, beginning at 1 ?

Thanks
André

Re: reseed primary key doesn't work

Tom Moreau
3/18/2007 12:00:00 AM

Using RESEED doesn't change the existing values. It sets the next identity
value to be used.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
Hi,

i use sql server express 2005. I defined a table "mytable" with a primary
key (OrderId , type INT). I want to reseed the value of the primary key
(starting with 1).
The table is in a .mdf file, so i first attached it (with sql server
management studio express) and then running this query:
DBCC CHECKIDENT ('dbo.mytable', RESEED, 1)

I get this:
"checking identity information: current identity value '168', current column
value '1'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."

But when doing: "select OrderId from dbo.mytable"
the first record still starts with value 105 till 168.

How to change the value of OrderId, beginning at 1 ?

Thanks
André

Re: reseed primary key doesn't work

Hari Prasad
3/18/2007 12:09:33 PM

Hello,

Steps:-

1. Using below command copy the data into a new table

SELECT * INTO New_Table_name FROM Table_Name

(Make sure that all data is there is new table)

2. Truncate the existing table using below command

TRUNCATE TABLE Table_name

3. Issue the below command to RESEED the value in Original table

DBCC CHECKIDENT ('dbo.mytable', RESEED, 1)

4. From the copied table move the data into original table. In the column
list do not include Identity column.

INSERT INTO Table_Name(Col2,COl2,Col3) SELECT Col2,Col3,Col4 FROM New_Table

(Please do not inlcude Identity column in Insert and Select column list.)

Thanks
Hari

[quoted text, click to view]

Re: reseed primary key doesn't work

André
3/18/2007 5:13:07 PM

Ok, thanks, but how can i change the values then?

"Tom Moreau" <tom@dont.spam.me.cips.ca> schreef in bericht
news:O7dJvwVaHHA.4000@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

Re: reseed primary key doesn't work

André
3/18/2007 8:26:18 PM

Great, i'll try.
Thanks

"Hari Prasad" <hari_prasad_k@hotmail.com> schreef in bericht
news:ekqOKBYaHHA.4000@TK2MSFTNGP02.phx.gbl...
[quoted text, click to view]

Re: reseed primary key doesn't work

Anthony Thomas
3/24/2007 1:09:47 PM

Although a reasonable reload strategy, you should know that by executing the
TRUNCATE TABLE command, any IDENTITY attributes are already reseeded to
their original values. You do not need to issue the DBCC CHECKIDENT
function.

If you only executed a DELETE <table_name> statement, then you would need to
execute DBCC CHECKIDENT in order to reseed any IDENTITY attributes.

Here is a got'cha for SQL Server 2000. Only members of db_ddladmin or
db_owner are authorized to issue either TRUNCATE TABLE or DBCC CHECKIDENT
although anyone can be granted DELETE <table_name> rights, which ends up
being sort of redundant.

For SQL Server 2005, you can grant those special permissions without making
users full members of either of those default roles.

So why would one want to issue the DELETE + DBCC CHECKIDENT combination
instead of TRUNCATE TABLE? Because DELETE is fully logged, where TRUNCATE
TABLE only logs the metadata page deallocation actions. So, it depends on
what sort of transaction logging you want to maintain. Although, there may
be equally valuable alternative reasons; I just can't come up with any
others at the moment.

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn