The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

Article translations Article translations
Article ID: 273586 - View products that this article applies to.
This article was previously published under Q273586
Expand all | Collapse all

On This Page

SUMMARY

When you use a SELECT INTO query with the IDENTITY function and an ORDER BY clause, the identity values that are generated are not guaranteed to have the same order as the order that is provided by the ORDER BY clause. For example, if the IDENTITY function starts with a seed of 1 and an increment of 1, the first row in the ordered result set could have a value different than 1, and the second row may have a value different than 2, and so forth.While the ordering may appear as you expect, the behavior is not guaranteed. The presence of TOP or SET ROWCOUNT also does not guarantee the order of the rows.

If you want the IDENTITY values to be assigned in a sequential fashion that follows the ordering in the ORDER BY clause, create a table that contains a column with the IDENTITY property and then run an INSERT .. SELECT … ORDER BY query to populate this table.

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

Properties

Article ID: 273586 - Last Review: November 2, 2007 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbhowto kbinfo kbpending KB273586

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com