Primary Key Question

I have been developing a .NET Web app with an SQL Server 2005 Express database. Since I've been testing insert and delete code with a large data source, the Primary Key column which is also an autoincrement integer column is now at very large numbers (starting at over 40,000 now). I've tried doing a shrink on the database, but other then reducing the database size, it has done nothing to reduce the Primary Key numbers. Is there a command way to reduce the starting number back to 1 or do I need to completely reconstruct the database from scratch?

TIA

hi,

in order to "reset" the identity column value, you can "truncate" the table, that will implicitely reset the aut generated identity to your initial value via the
TRUNCATE TABLE schema.object;
statement... you will need high permissions on the object itself, and the table must not be referenced by a foreign key, ...
you can read further about requirements and permissions at http://msdn2.microsoft.com/en-us/library/ms177570.aspx...

or you can execute a
DBCC CHECKIDENT ('schema.object', RESEED);
command... start reading http://msdn2.microsoft.com/en-us/library/ms176057.aspx for further info and requirements..

regards

|||

Thanks - I'll give it a try.

Carl

|||

No dice. Neither of those options does it. That I want is, for example, a way of changing the lowest Primary/Identity Key value from 40,000 to 1. There are no records at lower values than 40,000. I'd also like to move all the Primary/Identity Key values down as well.

I suspect I need to effectively copy all the non-Primary/Identity Key data into a newly created database. If I do that, I think it is a characteristic of the INSERT statement that the Primary/Identity Key values will start at the seed values and autoincrement from there as rows are inserted and the incoming Primary/Identity Keys are ignored. I can then delete the original database and rename the new one to the original name.

Sound right?

Carl

|||

hi,

ok. you want to "re-assign" an autogenerated value starting from "1" to existing rows...

you actually do not have to "drop" the database...

just perform a "SELECT ... INTO" another table that will be created on the fly for you, as a temporary storage... then drop all rows from the "real" table and reset the identity value and repopulate the original table from the temp storage...

I mean something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.myData(

Id int NOT NULL IDENTITY PRIMARY KEY,

Data varchar(10) DEFAULT 'test data'

);

GO

PRINT 'this will add some data';

DECLARE @.i int;

SET @.i = 1;

INSERT INTO dbo.myData VALUES ( DEFAULT );

WHILE @.i < 20 BEGIN

INSERT INTO dbo.myData SELECT Data FROM dbo.myData;

SET @.i = @.i + 1;

END;

GO

SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;

GO

PRINT 'Deleting rows 1 to 200000';

DELETE FROM dbo.myData WHERE Id < 200000;

GO

SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;

PRINT 'now we do have an initial unused ''identity range''';

GO

PRINT 'Move rows to a temp table';

SELECT * INTO dbo.tempTable FROM dbo.myData;

PRINT 'truncating the original myData table';

PRINT 'this will actually reseed the identity value as well';

TRUNCATE TABLE dbo.myData;

SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;

GO

PRINT 'move data again to the original table, but this time omitting the Id col';

INSERT INTO dbo.myData (Data) SELECT Data FROM dbo.tempTable ORDER BY Id;

SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;

GO

PRINT 'deleting temp table';

DROP TABLE dbo.tempTable;

GO

PRINT 'final clean up';

DROP TABLE dbo.myData;

--<--

this will add some data

Count Max Min

-- -- --

524288 524288 1

Deleting rows 1 to 200000

Count Max Min

-- -- --

324289 524288 200000

now we do have an initial unused 'identity range'

Move rows to a temp table

truncating the original myData table

this will actually reseed the identity value as well

Count Max Min

-- -- --

0 NULL NULL

move data again to the original table, but this time omitting the Id col

Count Max Min

-- -- --

324289 324289 1

deleting temp table

final clean up

but this is usually not an efficient task you should do in production systems as, after all, key values are not interesting to human beings when autogenerated... you usually choose such a primary key for performance reasons or when you can not find an actual natural key in your data modelling desing phase (very bad ), so the actual value is not that big deal at all,, you just need it non repetetive and you get it as expected...

regards

|||

Thank you, Sir.

Yes, it did work and in the process I've learned more about SQL Express and T-SQ, important for a relative newbieL. Not only the original question I asked, but a seperate isuue I wasn't aware I was messing up until I kept getting an error message from the SELECT phrase in the second INSERT statement. I was insisting on putting parens around the column name list and SELECT didn't like that. When I ceased and desited, everything went smoothly. I initially tried this on the smaller of the two existing tables and each time it bombed out, it actually added to the PRIMARY IDENTITY Key. Since I expect more blanks from delete and insert statements as I develop the C# access code, that's not important now and I can fix it later. Reseeding the second table (the big one) went through without a hitch once I knew the right syntax. I expect that I'll need to write a script or something to do this and similar cleanup as I develop this app since I have more big tables to add with similar situations. Hopefully, I won't need to mess with it anymore when the app coding job's done, but at least I know how to do it now.

I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.

Thanks for the help. I think I'm off and running again

Carl

|||

hi Carl,

Speedo wrote:

....

I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.

wait before redesigning... I did not say that autogenearated keys are bad ..

they are just another (available) candidate key in our entity definition.. it should not be the only one, but such a candidate key can be good primary key, as it's very compact (and this is very usefull in relation where the referencing table must map to the referenced table) and this is a "nice to have" feature in indexes implementation...

very often database architechts implement a surrogate key in the design phase.. sometime these surrogate keys become primary keys and sometime not...

a nice article about nautural vs surrogate keys is available at http://www.informit.com/articles/article.asp?p=25862&redir=1&rl=1 , even if related to GUID columns..

regards