SQL - When would an empty OVER clause be used?

Question

I'm analyzing some code that utilizes empty OVER clauses in the contest of Count().

Example:

SELECT 
        ROW_NUMBER() OVER (ORDER BY Priority DESC) AS RowID,
        CAST((COUNT(*) OVER() / @pagesize) AS Int) AS TotalPages,

I'm trying to understand why the empty OVER clause is being used here.

There are other standard select elements below those two lines I listed above, and when I remove the empty OVER clause from the second the TotalPages line, I get errors like this:

Column 'TableA.Priority' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

As soon as I put the OVER() back, the error is gone.

My understanding of the OVER clause is very limited... I feel Like I understand what's going on in the RowID line... but the TotalPages line just baffles me.

Answer 1

Say our table is employees:

+-----------+-------+---------+
| badge_num | name  | surname |
+-----------+-------+---------+
|         1 | John  | Smith   |
|         2 | Mark  | Pence   |
|         3 | Steve | Smith   |
|         4 | Bob   | Smith   |
+-----------+-------+---------+

Running

SELECT surname, COUNT(*)
FROM employees
GROUP BY surname;

we'll get:

+---------+----------+
| surname | COUNT(*) |
+---------+----------+
| Smith   |        3 |
| Pence   |        1 |
+---------+----------+

While running

SELECT surname, COUNT(*) OVER()
FROM employees
GROUP BY surname;

we'll get:

+---------+-----------------+
| surname | COUNT(*) OVER() |
+---------+-----------------+
| Smith   |               2 |
| Pence   |               2 |
+---------+-----------------+

In the second case, in each row we are just counting the number of rows of the whole select (not the single partition).

Answer 2

OVER() is part of analytic function and define partitions in your recordset. OVER() is just one partition and applied to the whole dataset

i.e. COUNT(*) OVER() will return in each row how many records in your dataset.

look to this http://msdn.microsoft.com/en-us/library/ms189461.aspx