Sql-server – OVER and PARTITION BY for db size change

sql servert-sql

I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the snapshots being evaluated?

Here is the column:

( LAST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

- /* subtract */

FIRST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ) * 8 / 1048576.0 
AS usage_change_mb

Best Answer

It's the daily change, if the snapshots are taken daily (snapshot_time column is where I assume you're getting the snapshot time data).

Partitioning restarts computation each time a unique value for the column (or combination of columns) specified in the PARTITION BY is found. Even though you are sorting (ORDER BY instance, db, snapshot) you aren't aggregating data across the partitions in any way without an aggregation function preceding OVER().