T-SQL Tip: OVER and PARTITION BY

Here’s a quick summary of OVER and PARTITION BY (a window function, introduced back in SQL Server 2005), for the uninitiated or forgetful…

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside each. For example, this query:

SELECT schema_id
  , name
  , COUNT(*) OVER () as NumberOfTables 
FROM sys.tables 
ORDER BY schema_id, name;

Will return something like this:

schema_id	name	NumberOfTables
1	HELP	61
5	BackupCert	61
5	BackupDebug	61
5	BackupDebugLogDetails	61
5	BackupEncryption	61
…	…	…

Quick translation:

• COUNT(*) – get me the count of tables
• OVER – for the set of rows….
• () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”NumberOfTables” was the count of all schema_id and name in the resultset. We can break up that resultset into partitions with the use of PARTITION BY:

SELECT schema_id
   , name
   , count(*) OVER (PARTITION BY schema_id) as NumberOfTables 
FROM sys.tables 
ORDER BY schema_id, name;

My partition is by schema_id – each “window” of a single schema’s tables will be treated separately from each other “window”….I’ll get the count of tables for schema_id 1, and then the count for schema_id 5, and so on:

schema_id	name	NumberOfTables
1	HELP	1
5	BackupCert	58
5	BackupDebug	58
5	BackupDebugLogDetails	58
5	BackupEncryption	58
…	…	…

The translation here is:

• COUNT(*) – get me the count of tables
• OVER – for the set of rows….
• (PARTITION BY schema_id) – …that have the same schema_id.

---

Talk to us about a product demo today! We love talking shop.