[SOLVED] What exactly does the "Over" Clause in T-Sql do?

What exactly does the "Over" Clause in T-Sql do?

I'm starting to mess with ASP.NET MVC and came across the issue of wanting to do some basic Paging. So I used this simple Linq statement (using the Northwind Database) to get 10 items for a specific page:

var q = (from p in db.Orders
                     orderby p.OrderDate descending
                     select p).Skip(currentPage * pageAmount).Take(pageAmount);

It works well, but then when I debugged, I was curious what the actual SQL was generated by Linq to Sql. This was the output:

SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[OrderDate] DESC) AS [ROW_NUMBER], [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Now I'm not SQL expert at all, which is why I've never seen the "OVER" clause. I'm just curious from a high level what exactly it does and when is it used?

Solution

It tells the ROW_NUMBER function how to assign a number to each row, based on the expression within the parentheses. The rows don't actually get sorted according to this number within the subquery, though. That's what the sort in the outer query is for.