MS-SQL Filling in Identity column values

Question

I'm building an ASP.Net/MVC application using SQL 2008 Developer edition and a DB in Sql2005 compatibility mode. Using Entity Framework as DAL.

My problem is that I have a table where I'm using the integer identity column in a like an Invoice Number, that is, it always has to be unique and never reused. So using the GUID column type won't work without a substantial effort.

What I'm seeing is that the DB is filling in the gaps in the identity column. This will cause me long term problems. Is there a setting to disable this "filling in"

Answer 1

That sounds like something outside SQL server; SQL server does not "go back" and re-use gaps in identities unless the table's been reseeded, but even then it will blindly increment one-by-one and probably return a lot of duplicate key errors as it hits rows with existing values.

Are you sure the column is an identity? Is there anything else that might be re-assigning keys and/or turning on identity insert when creating rows?

Answer 2

SQL Server does not fill in the gaps of an identity field by default it will just keep going up in numbers as you insert rows.

It is possible to reset the identity back to 1 and therefore you may then see what you are describing.

Can I suggest you post some code / db structure that shows your problem and search for any code you may have that my perform an identity reseed.

Answer 3

Unless I am not understanding your issue correctly. If you create a primary key on your identity column, or a unique constraint, you can avoid the issue of duplicate values.

For example:

create table TableName
(
    InvoiceID int identity(1,1) not null primary key
)