|
|
You're in the
sql server msde
group:Resetting the count on a Primary Key at a certain record? Impossible?
sql server msde:
Hello,
Several consecutive records have been deleted out of an MSDE database and the program that uses the database doesn't like it. I'm curious if it's possible to... say... reset the primary key count so that the records all shift to fill the "dead space" left by the deleted records. I have the feeling that this may be humorous to some of you, but any input (including "you can't do this. that's the point of a primary key") is appreciated. Thanks! Matt
hi Matt,
[quoted text, click to view] Matt Brown - identify wrote: > Hello, > > Several consecutive records have been deleted out of an MSDE database > and the program that uses the database doesn't like it. I'm curious > if it's possible to... say... reset the primary key count so that the > records all shift to fill the "dead space" left by the deleted > records. > > I have the feeling that this may be humorous to some of you, but any > input (including "you can't do this. that's the point of a primary > key") is appreciated. > you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED option specified.. http://msdn2.microsoft.com/en-us/library/ms176057.aspx -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools --------- remove DMO to reply
[quoted text, click to view]
Matt Brown - identify wrote: > Hello, > > Several consecutive records have been deleted out of an MSDE database > and the program that uses the database doesn't like it. I'm curious > if it's possible to... say... reset the primary key count so that the > records all shift to fill the "dead space" left by the deleted > records. > > I have the feeling that this may be humorous to some of you, but any > input (including "you can't do this. that's the point of a primary > key") is appreciated. > ooopppsss... Hugo is right, completely right... I did not read with enougth attention and only saw the "identity" resetting requirement... I'm sorry.. I'm just curious why "..program that uses the database doesn't like it.." :D Agreeing completely with Hugo about the "keys" immutability, you can perhaps workaround that.. you could create a "temporary" table where you insert all the current valid rows of your table, truncate the original table in order to empty it and to reset the idientity table's value, and finally refill it with the "orphaned" rows.. so you can write something like INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...; TRUNCATE TABLE dbo.YourTable; INSERT INTO dbo.YourTable (all_columns_but_the_identity_col) SELECT all_columns_but_the_identity_col FROM tempTable WHERE... ORDER BY...; DROP TABLE tempTable; but, again, this is a "poor strategy"... you'll end up with gaps anyway, now or then.. and this "workaround" does not work if you have declarative referential integrity set on that table, if it's referenced by other rows in other tables... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools --------- remove DMO to reply
[quoted text, click to view]
On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote: >Hello, > >Several consecutive records have been deleted out of an MSDE database >and the program that uses the database doesn't like it. I'm curious >if it's possible to... say... reset the primary key count so that the >records all shift to fill the "dead space" left by the deleted >records. > >I have the feeling that this may be humorous to some of you, but any >input (including "you can't do this. that's the point of a primary >key") is appreciated. Hi Matt, You can't do this. That's the point of a primary key. Seriously - a primary key is intended to identify an entity. Think of what would happen if you would change your name. Or get a new SSN. Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED will affect new IDENTITY values, not existing values. I think that you were asking about the latter. -- Hugo Kornelis, SQL Server MVP
On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@virgilio.it>
[quoted text, click to view] wrote: > Matt Brown - identify wrote: > > > Hello, > > > Several consecutive records have been deleted out of an MSDE database > > and the program that uses the database doesn't like it. I'm curious > > if it's possible to... say... reset the primary key count so that the > > records all shift to fill the "dead space" left by the deleted > > records. > > > I have the feeling that this may be humorous to some of you, but any > > input (including "you can't do this. that's the point of a primary > > key") is appreciated. > > ooopppsss... > Hugo is right, completely right... I did not read with enougth attention and > only saw the "identity" resetting requirement... I'm sorry.. > I'm just curious why "..program that uses the database doesn't like it.." :D > Agreeing completely with Hugo about the "keys" immutability, you can perhaps > workaround that.. > you could create a "temporary" table where you insert all the current valid > rows of your table, truncate the original table in order to empty it and to > reset the idientity table's value, and finally refill it with the "orphaned" > rows.. > so you can write something like > INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...; > > TRUNCATE TABLE dbo.YourTable; > > INSERT INTO dbo.YourTable (all_columns_but_the_identity_col) > SELECT all_columns_but_the_identity_col > FROM tempTable > WHERE... ORDER BY...; > > DROP TABLE tempTable; > > but, again, this is a "poor strategy"... you'll end up with gaps anyway, now > or then.. and this "workaround" does not work if you have declarative > referential integrity set on that table, if it's referenced by other rows in > other tables... > -- > Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org > DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools > --------- remove DMO to reply I haven't checked this is a while. That's actually perfect. I can remove some recordsets, then reset the identity key count with this here DBCC CHECKIDENT WITH RESEED, then everything should progress as normal. Righto! Thanks, Matt
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |