Checking the facts

Question

Post reply

Checking the facts

Divine Flame

SSCoach

Points: 15943
More actions
March 19, 2013 at 7:21 am

#291248

Hi Friends,
Here I want to confirm something about IDENTITY columns. We know that adding an IDENTITY column to a table, just adds a column to the table with the sequential data (Starting from seed value then incrementing it by Increment value). The question is if the table already exists with some data in it, how SQL Server decides the identity value for each row?
Means, how it chooses the row for which the Indentity column value will be 1? Is there any order considerd in choosing the identity values for rows like the physical location of the row or time on which the rows are inserted? Or is it completely random?
I guess there is an order associated.
We can see this with this example:
CREATE TABLE TestingTheData
(
ID INT,
SomeData VARCHAR(500)
)
GO
INSERT INTO TestingTheData VALUES (1,'Data1')
INSERT INTO TestingTheData VALUES (2,'Data2')
INSERT INTO TestingTheData VALUES (4,'Data4')
INSERT INTO TestingTheData VALUES (3,'Data3')
INSERT INTO TestingTheData VALUES (5,'Data5')
INSERT INTO TestingTheData VALUES (6,'Data6')
INSERT INTO TestingTheData VALUES (8,'Data8')
INSERT INTO TestingTheData VALUES (7,'Data7')
INSERT INTO TestingTheData VALUES (10,'Data10')
INSERT INTO TestingTheData VALUES (9,'Data9')
GO
SELECT * FROM TestingTheData
GO
ALTER TABLE TestingTheData ADD SeqNo INT IDENTITY(1,1)
GO
SELECT * FROM TestingTheData
In this example rows get the identity value in the order they were inserted. As we can see that there is an order when generating the identity values.
Is it always guaranteed with Identity columns that they will be generated in the same order the rows were inserted?
Sujeet Singh

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

...Is it always guaranteed with Identity columns that they will be generated in the same order the rows were inserted?

No it's not. One of example can be this:

CREATE TABLE TestingTheData

(

ID INT,

SomeData VARCHAR(500)

CONSTRAINT PK_TestingTheData PRIMARY KEY CLUSTERED (ID)

)

GO

INSERT INTO TestingTheData VALUES (1,'Data1')

INSERT INTO TestingTheData VALUES (2,'Data2')

INSERT INTO TestingTheData VALUES (4,'Data4')

INSERT INTO TestingTheData VALUES (3,'Data3')

INSERT INTO TestingTheData VALUES (5,'Data5')

INSERT INTO TestingTheData VALUES (6,'Data6')

INSERT INTO TestingTheData VALUES (8,'Data8')

INSERT INTO TestingTheData VALUES (7,'Data7')

INSERT INTO TestingTheData VALUES (10,'Data10')

INSERT INTO TestingTheData VALUES (9,'Data9')

GO

SELECT * FROM TestingTheData

GO

ALTER TABLE TestingTheData ADD SeqNo INT IDENTITY(1,1)

GO

SELECT * FROM TestingTheData

So, when clustered index present, it will be done in order of this index.

For heap tables order is not guaranteed at all.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Divine Flame SSCoach Points: 15943 More actions · Answer 2

Eugene Elutin (3/19/2013)
For heap tables order is not guaranteed at all.

Thanks Eugene, this is what I wanted to confirm. So, I take it as the identity values will be generated randomly for data rows in case of heap tables. Is it correct?

Could you please provide any link\url where it is documented?

That will be more helpful.:-)

Sujeet Singh