SQL Server 2005 ROW_NUMBER() without ORDER BY

by

You can avoid specifying an 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};

However, please note that is merely a way to avoid specifying an ordering and does NOT guarantee that any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BY in the outer query. To fully understand this, one must realize that the concept “not ordered (in a particular way)” is not the same as “retaining original order” (which IS ordered in a particular way!). I believe that from a pure relational database perspective, the latter concept does not exist, by definition (though there may be database implementations that violate this, SQL Server is not one of them).

The reason for calculating the Max in the query and for adding the lock hints is to prevent errors due to a concurrent process inserting using the same value you plan to use, in between the parts of the query executing. The only other semi-reasonable workaround would be to perform the Max() and INSERT in a loop some number of times until it succeeds (still far from an ideal solution). Using an identity column is far superior. It’s not good for concurrency to exclusively lock entire tables, and that is an understatement.

Note: Many people use (SELECT NULL) to get around the “no constants allowed in the ORDER BY clause of a windowing function” restriction. For some reason, I prefer 1 over NULL. What you use is up to you.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. SQL update query using joins
  4. How to update two tables in one statement in SQL Server 2005?
  5. Referring to a Column Alias in a WHERE Clause
  6. SQL query to select dates between two dates
  7. Convert Month Number to Month Name Function in SQL
  8. Create a date from day month and year with T-SQL
  9. SET versus SELECT when assigning variables?
  10. Selecting COUNT(*) with DISTINCT
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. SQL – Subtracting a depleting value from rows
  16. How to print VARCHAR(MAX) using Print Statement?
  17. What’s the best way to select the minimum value from several columns?
  18. Concatenate values based on ID
  19. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  20. Splitting delimited values in a SQL column into multiple rows
  21. SELECT FOR UPDATE with SQL Server
  22. varbinary to string on SQL Server
  23. Can an INNER JOIN offer better performance than EXISTS
  24. SQL server identity column values start at 0 instead of 1
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment