New tsql commands in Sql Server 2012 (Part 3)

In the new version of the sql server 2012 it is possible to easily implement paging. To do so you need the offset x rows command (x defines the starting row of your query) and the fetch next x rows only command. (x defines the rowcount of your query).

declare @counter int = 0
while(@counter < 50)
begin
    select 
        id, 
        data, 
        iif(id <= 5, 'small', 'big')
    from 
        testData
    order by id
        offset @counter ROWS
    FETCH NEXT
        5 ROWS ONLY;
    set @counter = @counter + 5;
end

Result of the query.

As you see with the offset and the fetch next rows command it is very easy to generate custom paging at database level.

Advertisements


If you have a note or a question please write a comment.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s