opc.three (3/1/2013)
GilaMonster (3/1/2013)
opc.three (3/1/2013)
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks.I can probably produce you an example where it is (row number over a non-unique column and filter on that row number comes to mind)
I am not picturing it. Wouldn't that require a derived table, in which case the ORDER BY in the ranking function would guarantee the correct result?
Was thinking something like this:
SELECT name,row_num
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name ),
name
FROM sys.tables
) tbls ( row_num, name )
WHERE row_num = 1;
name is unique.
When you do a row number over a column that is not unique, the order in which those row numbers are assigned to 'duplicate' values is not guaranteed, change the index that SQL's using and you can change the order that the row numbers are assigned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability