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 |
|
noamg
Posting Yak Master
215 Posts |
 Posted - 2012-05-29 : 10:37:08
|
| Hi,this is my table:CREATE TABLE GamesApp.Users ( UserID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, CellularNum VARCHAR(20) NULL, Email VARCHAR(200) NULL)the application call to SP, which run a simple insert statement:INSERT INTO GamesApp.Users( CellularNum, Email ) VALUES( @CellularNum, @Email ) SET @UserID = SCOPE_IDENTITY()sometime, I got a big gaps by the value in the identity column.012345671002 <<< --- !!!10031004why ? any idea ?Noam Graizer |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-29 : 11:00:44
|
| If a value is inserted manually that will reset the seed. Also if an insert fails the identity value will be used up. Could be that a batch of rows is failing insert so you get a missing range.Are any rows being deleted? That will not reset the seed.An identity shouldn't be considered to have consecutive values unless the insert is in a single transactionseehttp://www.simple-talk.com/sql/t-sql-programming/identity-columns/==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2012-05-29 : 11:26:15
|
| no reset to the seek, no bulk or batch.the only way to insert row is via the SP.P.S.: SQL 2012Noam Graizer |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-29 : 11:53:15
|
| Then must doing an uncommited insert to give gaps - are there periods where it fails and something continually retries - that's the only way I can see you can get a missing range like that without someone changing the seed.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-29 : 12:21:51
|
| I've read that SQL 2012 has a bug that can cause gaps if the server/service is reset. However, I've not been able to repro it myself. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
|
|
|
|
|
Identity column