MORE INFORMATION
The identity values that are generated depend on the position of the
GetIdentity() function in the query tree (showplan), which may change due to optimizer changes, parallel query plans or the presence of TOP/SET ROWCOUNT. While you may see scenarios in which a SELECT INTO with an
IDENTITY function and an ORDER BY clause produce values in an order that you desire this behavior is not guaranteed and may change without warning. The following scenarios involve a
SELECT INTO statement and an
IDENTITY function.
Consider a table that
is named
OldTable with the following values.
Col1 Col2
------- --------
1 A
11 F
7 G
17 I
2 Z
Scenario 1
In this scenario, the SELECT INTO query uses an IDENTITY function
and an ORDER BY clause.
SELECT Col1, Col2, ID=IDENTITY (int, 1, 1)
INTO NewTable
FROM OldTable
Order By Col1
NewTable may be inserted with rows in one of the following two
ways.
Method 1
Col1 Col2 ID
------- -------- --------
1 A 4
2 Z 2
7 G 5
11 F 3
17 I 1
You may notice that the IDENTITY value that SQL Server generates
is not in the same order as the ORDER BY column (
Col1) in the query.
Method 2
Col1 Col2 ID
------- -------- -------
1 A 1
2 Z 2
7 G 3
11 F 4
17 I 5
In this case, you may notice that the IDENTITY value that SQL
Server generates is in exactly the same order as the ORDER BY column (
Col1) in the query. However, this is coincidental and is not the
guaranteed order you will receive every time the query is run.
Scenario 2
The following SELECT INTO queries use the IDENTITY function and an
ORDER BY clause, with a TOP operator or a SET ROWCOUNT statement.
SELECT TOP 3 Col1, Col2, ID=IDENTITY (int, 1, 1)
INTO NewTable
FROM OldTable
Order By Col1
-or-
SET ROWCOUNT 3
SELECT Col1, Col2, ID=IDENTITY (int, 1, 1)
INTO NewTable
FROM OldTable
Order By Col1
NewTable may be inserted with rows in one of the following three possible
ways:
Method 1
Col1 Col2 ID
----- ----- -----
1 A 2
2 Z 1
7 G 3
In this case, you may see that the IDENTITY value that SQL Server
generates is not in the same order as the ORDER BY column (
Col1) in the query.
Method 2
Col1 Col2 ID
------ ------ ------
1 A 1
2 Z 2
7 G 3
In this case, you may notice that the IDENTITY value that SQL
Server generates is exactly in the same order as the ORDER BY column (
Col1) in the query. However, this is coincidental and is not the
guaranteed order you will receive every time the query is run.
Method 3
Col1 Col2 ID
------ ------ ------
1 A 4
2 Z 2
7 G 5
In this case, the IDENTITY value that SQL Server generates is not
in the same order as the ORDER BY column (
Col1) in the query. Additionally, the data inserted does not meet the
SEED and INCREMENT requirements specified in the IDENTITY function in the query
(SEED=1, INCREMENT=1).
Actually, the
IDENTITY function generates
identity values correctly based on the SEED and INCREMENT parameters. However,
the identity value generation occurs before the rows are sorted based on the
ORDER BY clause. Therefore, when you use a TOP operator or a SET ROWCOUNT
statement, the rows inserted in the resultant table (
NewTable) seem to have incorrect identity values. The identity values SQL
Server generates might not match the SEED and INCREMENT parameters of the
IDENTITY function.
Here is an example that involves an INSERT INTO SELECT FROM with ORDER BY clause.
Consider a table that is named
OldTable with the following values:
Col1 Col2
------- --------
1 S
11 F
7 G
17 I
2 z
Below is the target table named
NewTable (ID is an identity column)
ID (identity) Col1 Col2
--------------- ------- ------
The following INSERT INTO SELECT FROM query with an ORDER BY clause will guarantee that column ID in
NewTable is in the same order as
Col1.
INSERT INTO NewTable (Col1, Col2) SELECT Col1, Col2 FROM OldTable ORDER BY Col1
ID (identity) Col1 Col2
------------- ------ ------
1 1 S
2 2 z
3 7 G
4 11 F
5 17 I
Note ID identity column is generated to have same order as of
Col1. However, INSERT INTO doesn't guarantee the physical order of either ID or
Col1 in
NewTable. To retrieve the data in desired order, an ORDER BY clause is required as shown by following SELECT statement:
SELECT * from NewTable ORDER BY ID