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

Question

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?

Answer 1

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.

Answer 2

ROW_NUMBER() OVER

returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

In your example, the resultset is being ordered by descending OrderDate and then the ROW_NUMBER function is applied to it. The resultset returned is only going to be where ROW_NUMBER is BETWEEN (@p0 + 1) and (@p0 + @p1)

As DOK has said, OVER clause is not used on it's own, but in conjunction with aggregate and ranking window functions.

From MSDN -

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of the rowset before the associated window function is applied.

Answer 3

OVER isn't used alone. It's used in conjunction with RANK, DENSE RANK, NTILE, and ROW NUMBER. In this case it's being used with ROW_NUMBER to retrieve only the records needed for display at the time, in this case the 10 items for this page.