Categories
MSSQL Server

MSSQL 2012 New T-SQL Features

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1