CODEWITHSUNDEEP
sqlinsertidentityinsert-id
Kamarey
Kamarey

Reputation: 11069

Dependency in identity column values after multiple SQL inserts

If the next is right: There are SQL string with multiple inserts (using a stored procedure):

"EXEC SPInsertData ... EXEC SPInsertData ... EXEC SPInsertData ..."

The id in identity column, which is auto incremented, of every new record is smaller than the id of the next one.

E.g. after executing the given SQL string the id of the first record is smaller than id of the second record, and its id is smaller than id of the third record?

Upvotes: 0

Views: 517

Answers (2)

Raj More
Raj More

Reputation: 48018

By nature, autoincrements go forward with every insert by the amount of the increment.

MS SQL Server offers a way to create a reverse order. Take a look here

create table #test
(
    TestId INT IDENTITY (2, -1),
    DateTimeStamp   DateTime
)
GO
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
INSERT INTO #test (DateTimeStamp) Values (GETDATE());
GO
SELECT * FROM #test

Results:

TestId      DateTimeStamp
2           2009-07-28 15:02:09.200
1           2009-07-28 15:02:09.200
0           2009-07-28 15:02:09.200
-1          2009-07-28 15:02:09.200
-2          2009-07-28 15:02:09.203
-3          2009-07-28 15:02:09.203
-4          2009-07-28 15:02:09.203
-5          2009-07-28 15:02:09.207

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

Yes, if it's an auto-incrementing identity column that is correct

Upvotes: 1

Related Questions