BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.
- SQL Server 2012 - What's New when Writing SQL
- Storing Files in SQL Server 2012
- What's New in SQL 2012 - New Functions
- Sequences and Page Data using OFFSET / FETCH NEXT (this blog)
Posted by Andy Brown on 08 May 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Sequences and Page Data using OFFSET / FETCH NEXT
SQL Server 2012 has introduced two natty ways of working with chunks of records: creating Sequence objects, and fetching blocks of records.
Creating Sequences of Numbers
A sequence is an object in SQL Server which contains (as the name suggests) a sequence of numbers. It is similar to an identity column in this respect:
Here the FilmId column provides a sequence (although I'm not sure what happened to number 4!).
You can create a sequence in a similar way to creating an identity column in a table:
-- create a sequence of first ten numbers
CREATE SEQUENCE FirstTenNumbers
AS int
START WITH 1
INCREMENT BY 1
MAXVALUE 10
SELECT NEXT VALUE FOR FirstTenNumbers AS FirstNumber
SELECT NEXT VALUE FOR FirstTenNumbers AS SecondNumber
The SQL above would produce the following output:
The SELECT commands list out the first two numbers in the sequence.
You can use sequences to avoid messing about with WHILE / WEND loops and counters, to simulate identity columns and wherever your imagination leads you!
Fetching Pages of Data by Specifiying an Offset
A common requirement of SQL queries is to return a page of data for displaying in a GridView or similar control. Previously you could do this using a messy query involving the ROW_NUMBER() function, but it's now much easier:
-- show rows number 11-15 from
-- the films table
SELECT
FilmName,
FilmReleaseDate
FROM
tblFilm
ORDER BY
FilmName
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;
By varying the number of rows to offset from the start and the number of rows to fetch, you can get at any block of rows from the underlying table.
- SQL Server 2012 - What's New when Writing SQL
- Storing Files in SQL Server 2012
- What's New in SQL 2012 - New Functions
- Sequences and Page Data using OFFSET / FETCH NEXT (this blog)