Brad Bansner
asked on
table with very high identity numbers, decreased performance?
I have a table with current identity values of 4118223 and higher, its INT column with auto-increment. The table only has about 300,000 records in it, but performance on the table seems slow, even when just doing something such as deleting a single record. I'm wondering if the high INT values have something to do with it? I don't know if there is any way to reset the numbers or something. I believe SQL will never repeat an identity column value, is that correct?
Would appreciate any advice on this. Thank you!
Would appreciate any advice on this. Thank you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's going to be a 32-bit INT no matter what the actual number is, whether it is 1 or 2,000,000,001. It is always fetching the same amount of data so like @matthewspatrick said, "Not a blessed thing."
ASKER
What does "you need to put clustered indexes on your identity column" mean, or how do I do that?
Excuse my lack of understanding of terminology, but I don't know that I have it set as a Primary Key. I put:
Is Identity: Yes
Identity Increment: 1
Identity Seed: 1
So that query you just gave me, that will reset the column back to 1 again? Thanks!
Excuse my lack of understanding of terminology, but I don't know that I have it set as a Primary Key. I put:
Is Identity: Yes
Identity Increment: 1
Identity Seed: 1
So that query you just gave me, that will reset the column back to 1 again? Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, thanks guys.
You need to put clustered indexes on your identity column. If the identity column is already a primary key then you already have a index on it . So in this case - you need to rebuild your indexes.
alter index all on tablename rebuild
go