June 27, 2006 at 9:56 am
I am using an Identity column instead of a PK on the tables to ensure uniqeness. How far is this ok?
What if am deleting any rows in between, do i need to use DBCC CHECKIDENT
June 27, 2006 at 10:18 am
It is OK as far as the data type that you use. This is one of the limitations of using Identity columns, they only last as long as the datatype. With SQL Server 2000, you can use BigINT. This will buy you some time as it will allow for a large number of rows. DBCC CHECKIDENT will allow you to reseed.
You may want to consider using the UniqueIdentifier datatype.
June 27, 2006 at 12:58 pm
The Identity is fine and, if in a UNIQUE index without nulls allowed, is effectively a PK. As noted, if your DB is exceptionally busy (so that you might run up against the 2,147,483,647 entry limit), then a Bigint is another way to go (allowing up to 9,223,372,036,854,775,807 individual rows). You can also use numeric fields as identity, which, with maximum size, would allow for 99,999,999,999,999,999,999,999,999,999,999,999,999 entries, which should be enough for most purposes.
No, you do *not* need to run CHECKIDENT after deletions in your table.
June 27, 2006 at 1:35 pm
"No, you do *not* need to run CHECKIDENT after deletions in your table."
Correct, you do not have to run CHECKIDENT (or any other method of removing gaps and islands from your sequence). But if you know that you are planning on having alot of delete activities, you may want to consider implementing some way of keeping gaps and islands to a minimum as gaps in sequence numbers on large tables can result in decreased performance for set-based solutions.
June 27, 2006 at 2:12 pm
And you are absolutely correct, John. Gapitis can be... well, not deadly, but certainly a head cold for your database.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply