Is that the optimizer may apply the identity value BEFORE The ORDER BY
The example given:
SELECT EmpId, EmpSalary,
=IDENTITY(int, 1, 1)
ORDER BY EmpSalary desc
Can be readdressed as
SET NOCOUNT ON
SELECT *, RowNumber=IDENTITY(int, 1, 1)
SELECT TOP 100 PERCENT EmployeeID
ORDER BY EmployeeID) AS XXX
SELECT * FROM myTable99
DROP TABLE myTable99
I love the way they show how not to do it, but don't give a solution...except to wait for SQL 2005...when's the release?
the part about a distributed query comes from here --
Imagine that the Employees table has 100,000 rows and SQL Server breaks the query into four steps. Each step might be gathering its own set of rows and assigning identity values as SQL Server processes the rows. However, SQL Server wouldn't apply the ORDER BY clause until all four threads are finished gathering rows and SQL Server serializes each of the parallel streams back into a single step. In that context, using IDENTITY() might not give you the results you want.
yes, an identity is just an arbitrary number -- good of you to notice, because this is actually the crux of the MISuse of identity to impose sequence!
what meaning will it have? exactly!!!!!
in particular it won't necessarily reflect the correct order, will it