Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLMark
Starting Member
18 Posts |
 Posted - 2009-05-28 : 04:32:13
|
| Hi all!I've a table with a counter coloumn (id) and a primary key (code), something like this:CREATE TABLE MyTable [id] [int] IDENTITY(1,1) NOT NULL, [code] [varchar](6) NOT NULL, ....CONSTRAINT [MyTable] PRIMARY KEY CLUSTERED ( [code] ASC) ON [PRIMARY]My question is: why if I generate a Primary Key violation (for example duplicating code) SQL Server "eats" one id leaving a hole between the id counters?-- Starting with an empty table:INSERT INTO MyTable (code, ...)VALUES (A, ...)-- At this time record is inserted with id = 1INSERT INTO MyTable (code, ...)VALUES (B, ...)-- At this time another record is inserted with id = 2INSERT INTO MyTable (code, ...)VALUES (A, ...)-- This cause an excpetion so no record are insertedINSERT INTO MyTable (code, ...)VALUES (C, ...)-- Another record is inserted but with id = 4 (WHY id = 3 WAS EATEN?)Thanks... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-28 : 06:38:28
|
It's because SQL Server needs to take one value due to concurrency.If some error occurs, the value is discarded. If it was not, then maybe two inserts may fail due to fighting for same ID value.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SQLRatankalwa
Starting Member
42 Posts |
Posted - 2009-06-01 : 12:48:48
|
| Because SQL Server has to create the next Identity value for the Insert statement to process, Only after the insert statement is executed, the validation occurs and the insert statement is terminated with an error.Ratan KalwaSQL Server Professionals User Grouphttp://www.revalsys.com |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-01 : 15:21:36
|
| Isn't that the same thing Peter stated 4 days ago?Terry-- Procrastinate now! |
|
|
|
|
|
|
|
|
Why PK violation eats one id counter?