Clever Geek Handbook

MS-SQL Column Values

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

My problem is that I have a table in which I use an integer id column as the invoice number, that is, it must always be unique and never reused. Therefore, using the GUID column type will not work without significant effort.

What I see is that the DB is filling in the blanks in the identity column. This will give me long-term problems. Is there a setting to disable this "padding"

+2


source to share


3 answers


SQL Server does not fill in blanks in identity fields by default, it will just keep incrementing in numbers as rows are inserted.

It is possible that you reset the id back to 1 and so you can see what you are describing.



May I suggest you post some code / db structure that shows your problem and looks for any code you might have to re-identify.

+1


source


It looks like something outside of SQL Server; SQL Server does not "come back" and reuse spaces in identifiers unless the table has been reloaded, but even then it will blindly increment one by one and probably return many repeated key errors as it hits rows with existing values.



Are you sure the column is identical? Is there something else that could be reassigned keys and / or enabled insertion for identification when creating rows?

+3


source


If I misunderstand your problem. If you create a primary key on your identity column or a unique constraint, you can avoid duplicate values.

For example:

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


source






More articles:


All Articles