New features for SQL within SQL Server 2012
Part four of a four-part series of blogs

SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.

  1. SQL Server 2012 - What's New when Writing SQL
  2. Storing Files in SQL Server 2012
  3. What's New in SQL 2012 - New Functions
  4. 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:

List of films with id

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:

Two results of SEQUENCE

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.

 

  1. SQL Server 2012 - What's New when Writing SQL
  2. Storing Files in SQL Server 2012
  3. What's New in SQL 2012 - New Functions
  4. Sequences and Page Data using OFFSET / FETCH NEXT (this blog)
This blog has 0 threads Add post