1

If I have a window function which uses something like

SELECT *, row_number() OVER (ORDER BY something) FROM table

should the results be ordered?

I am currently testing out my queries in Microsoft SQL Server, and it certainly seems to be ordered, but I know that this product has the tendency to order rows when it hasn’t been asked.

Is this the standard behaviour?

5

Without a presentation ORDER BY clause, standard behavior returns a set in no particular order.

I think Itzik Ben-Gan explains it best and even without one of his books, you can find the explanation in this article. About 12 paragraphs down, next to the first code sample, the text describes how the ORDER BY in the window function serves a logical function to describe the set, and how that is completely separate from a presentation ORDER BY clause.

A related question appears here Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

3

This depends on the execution plan.

enter image description here

This query scans the clustered index, then sorts the results by the ordered column to be able to apply sequence object, and then returns the results to the client. That is why you see your final results ordered.

But if we look at the second execution plan (I forced the merge join just to show how the possibility of the final result to be in another order)

SELECT *
FROM dbo.ProjectAuditTrial
INNER MERGE JOIN (
SELECT *, row_number() OVER (ORDER BY MajorVersion) AS rownum FROM dbo.Project
) A ON A.ProjectID = ProjectAuditTrial.ProjectID

enter image description here

It this query our final results are not ordered by Window Functions ordering column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.