Tag Archives: Select (SQL)

Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

OandF1
The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

How to use ‘WITH RESULT SETS’ in SQL Server 2012

In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
go
CREATE PROC Usp_products
AS
SELECT ProductID,Name,ProductNumber FROM Production.Product
ORDER BY ProductID

Let us now execute the above procedure and see the outcome
withresultset1Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
WITH RESULT SETS
(
(
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)
)
)

withresultset2So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

Format() function in SQL Server 2012

SQL Server 2012 introduces this new function called FORMAT which returns a value in the specified format and also can optionally apply a regional format. This function relies on the .NET Framework.

In this article we will explore this function and see how it can be implemented.

FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where we pass on the date value or a numeric value. The second parameter is the .NET Framework format string. The format parameter is case sensitive. The third parameter is the culture. This can be any culture supported by the .NET Framework.

Let us now explore this function with some examples:
1) Display the date using Bengali Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Bn-IN' )
 AS DateInBangla;

format12) Display the date using Tamil Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Ta-IN' ) AS
DateInTamil;

format23) Display an amount into different currency structures

SELECT FORMAT(100, 'C', 'en-GB') AS Pounds,
FORMAT(100, 'C', 'en-US') AS Dollars,
FORMAT(100, 'C', 'es-ES') AS Euro,
FORMAT(100, 'C', 'en-IN') AS Rs;
GO

format34) Display current date time in US format

SELECT FORMAT(GETDATE(), N'"Time now is "dddd MMMM dd, yyyy', 'en-US')
AS USTimeStamp;

format4For more details on this function please refer to this msdn link.

%d bloggers like this: