How to Auto-Increment a Non-Identity Column in SQL Server 2005 Without ORDER BY?

Generating Auto-Increment IDs in SQL Server 2005 without ORDER BY

The need to insert data from one table into another while auto-incrementing a non-identity column can be a challenge. The standard approach involves using a cursor or creating a table variable, but these can be inefficient.

In the absence of a legitimate ORDER BY field, the ROW_NUMBER function can be employed. However, the absence of an explicit ordering does not guarantee the preservation of the original data order. This is because "not ordered" is distinct from "retaining original order."

To address this issue, a solution involves avoiding explicit ordering as follows:

INSERT dbo.TargetTable (ID, FIELD)
SELECT
   Row_Number() OVER (ORDER BY (SELECT 1))
      + Coalesce(
         (SELECT Max(ID) FROM dbo.TargetTable WITH (TABLOCKX, HOLDLOCK)),
         0
      ),
   FieldValue
FROM dbo.SourceTable
WHERE {somecondition};

This approach calculates the Max ID and inserts data while acquiring locks to avoid concurrency errors.

However, note that using an identity column is strongly recommended as it eliminates the need for locking and ensures optimal concurrency. Identity columns provide a more efficient and robust solution for auto-incrementing IDs in SQL Server.

The above is the detailed content of How to Auto-Increment a Non-Identity Column in SQL Server 2005 Without ORDER BY?. For more information, please follow other related articles on the PHP Chinese website!