MS SQL SERVER 2012 NEW FEATURES:OFFSET and FETCH – New in SQL Server 2012

In this article we shall learn how to limit the numbers of rows returned by a query using OFFSET and FETCH clause introduced in SQL Server 2012. The following query is executed against AdventureWorks Database.

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID

The following is the query result set when the above T-SQL code is executed.

Observe that the data is displayed sorted by the column SalesOrderDetailID, which is very much clear from the ORDER BY clause at the end of the query. This query will display all records in that table as we have not included any filtering condition.

You might be aware that using TOP clause we can restrict the query result set to as many rows we want it to display. Instead of the top n records, if we wanted to return x number of records from the middle of the result set, sorted on a particular column, we would have to do some additional manipulations and retrieve them. 

In SQL Server 2012, this can be achieved by using the OFFSET and FETCH clause at the end of the Select query, after ORDER BY clause. Let’s take a look at this sample query to understand this better..

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID 
 OFFSET 5 ROW
 FETCH NEXT 10 ROW ONLY

The OFFSET clause sets how many rows needs to be skipped before displaying the result set. In this case we gave OFFSET 5 ROW, so the first 5 rows are skipped. FETCH NEXT x ROW ONLY, displays the next x records, if there are records inside the table. In our example script, we used 10 so 10 rows are displayed. If we mention 10000000 rows, and if there are only 1000 rows in the table, then only 995 rows will be displayed. A small clarification, the keywords ROW or ROWS both will work the same.

Note: Please note that this is correct as of SQL Server 2012 RC0.

CONCAT – New String Function in SQL Server 2012

CONCAT is a new String function introduced in SQL Server 2012. This function returns an output which is a concatenated string value of the argument values passed in the function. The function would need a minimum of 2 values to be passed.

Let’s take a look at the function. In order to use this function, we shall first create a table and insert few records. The following script is used to the task of creating a table and inserting the data into that table.

CREATE TABLE [dbo].[Customer_Address](
 [CustomerID] [bigint] NULL,
 [Address1] [varchar](50) NULL,
 [Address2] [varchar](50) NULL,
 [ZipCode1] [char](5) NULL,
 [ZipCode2] [char](4) NULL
 )

INSERT INTO Customer_Address
VALUES
(1,'#1','Moon Walk Drive', 24578,2881 ),
(2,'#2','Roof Top Lane', 54856,5421 ),
(3,'#3','Full Thottle Blvd', 90425,5782 ),
(4,'#4','Drive Slow Road', 18854,6502 )

First let us look at the data in the table by doing a Select * on the table. The pic below displays the result set.

Now let us run the following query which uses the CONCAT function to concatenate the Address1 ad Address2 columns as a single column and also concatenate the 5 character length ZipCode1 and 4 character length Zipcode2 and display a single column output. Note that I am going to use a space ‘ ‘, and a hyphen ‘-’ in order to display the concatenated column in a meaningful way.

SELECT CustomerID, CONCAT(Address1,' ',Address2) AS Address,
CONCAT(ZipCode1,'-',ZipCode2) AS ZIP
FROM Customer_Address

The below picture shows the result of the query we have just executed.

In previous Versions of SQL Server, you could concatenate string values using a +. If order to achieve the same result you can write the following code.

SELECT CustomerID, Address1 + ' ' + Address2 AS Address,
ZipCode1 + '-' + ZipCode2 AS ZIP
FROM Customer_Address