SQL Server 2012 TSQL enhancements – Part 2

In my previous article SQL Server 2012 TSQL enhancements – Part 1 we went through String function – CONCAT and FORMAT introduced in SQL Server 2012. In this article we will see new feature of SQL 2012 that helps to implement Paging logic in Query without using any ranking function called “Ad-hoc query paging implementation”.

Developer usually used ROW_NUMBER() function to handle paging functionality in SQL query. For example…

SELECT *
FROM( SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS sequencenumber, * FROM Customers
) ASTempTable
WHERE sequencenumber > 10 and sequencenumber <= 20

The Ad-Hoc Paging feature of SQL 2012 allows you to returns a block data. For example, the first execution of the query may return first 10 records, the next execution returns the next 10 records and so on. We all are familiar with ORDER BY clause – returns sorted data. Microsoft has extended ad-hoc paging feature to existing ORDER BY clause by adding two arguments – OFFSET and FETCH Next.

OFFSET                 – Specifies the number of rows to skip before it starts to return rows from the query

FETCH NEXT    – Specifies the number of rows to return after the OFFSET clause has been processed. FETCH NEXT is compatible only with OFFSET clause.

Syntax-

ORDER BY order_by_expression  [ ASC | DESC ]

[OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }]

[FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]

 

Code Snippets:

declare @tblSampleData TABLE(Id int identity(1, 1)primary key, Value varchar(10))
insert @tblSampleData(Value)
values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p')
--Example 1
select * from @tblSampleData order by Id
--Example 2
select * from @tblSampleData order by Id offset 5 rows
--Example 3
select * from @tblSampleData order by Id offset 0 rows fetch next 5 rows only
--Example 4
select * from @tblSampleData order by Id offset 5 rows fetch next 5 rows only
--Example 5
DECLARE @PageNumber INT = 1, @RowsCountPerPage INT = 5;
SELECT * FROM @tblSampleData
ORDER BY ID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS only
SET @PageNumber = 2; SET @RowsCountPerPage = 5;
SELECT * FROM @tblSampleData
ORDER BY ID
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS only

The above code snippets specifies the use of OFFSET and FETCH clause.

Example 1 – Return all rows sorted by ID column.

Example 2 – Uses the OFFSET clause to skip first 5 rows and return remaining rows.

Example 3 – Uses the OFFSET clause to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

Example 4 – Uses the OFFSET clause  to skip first 5 row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

Example 5 – This example shows how to implement paging logic using variables.

Try above code snippets in query analyzer.

Advertisements

SQL Server 2012 TSQL enhancements – Part 1

SQL Server 2012 is the major release of Microsoft database server. In this release Microsoft has added few T-SQL enhancements to make common tasks much easier. These features can be leverage in writing efficient SQL query.

In this article we will cover new string functions introduced to replace CAST,CONVERT,DATEPART functions in some cases.

You can run below code snippets in query analyzer.

Let’s start with TSQL 2012 – Part 1…

String Functions:

1. CONCAT

a. Concatenate two or more string values.

b. Till SQL 2008 we leverage “+” operator in string expressions to concatenates two or more character, columns or binary strings. E.g. “FirstName + ‘ ‘ + LastName”

c. This function accepts comma separated values that implicitly converts to string type and concatenate them into single string.

Syntax – CONCAT ( string_value1, string_value2 [, string_valueN ] )

Code snippets:
PRINT ‘Current Time ‘ + GETDATE()
Throws an error message.

PRINT ‘Current Time ‘ + CAST(GETDATE() AS VARCHAR(20))
We have to explicitly convert DATETIME to string type as SQL only accepts string values in concatenation.

PRINT CONCAT(‘Current Time ‘, GETDATE())

SELECT CONCAT(1, 2, 3, 4, 5)

SELECT CONCAT(‘Concat’, ‘ ‘, ‘Example’)

2. FORMAT

a. Returns a value formatted into specified format.

b. Accepts the culture parameter which is optional.

c. This function can be mostly used to convert Date/Time and number values as string. Alternate solution for CAST/CONVERT function.

Syntax – FORMAT ( value, format [, culture ] )

Code Snippets:
DECLARE @date DATETIME = GETDATE()

SELECT FORMAT(@date, ‘dd’)

SELECT FORMAT(@date, ‘yyyy/mm/dd’)

SELECT FORMAT(@date, ‘hh:mm’)

Feel free to share your comments on this post.

Resolving Sort Warnings

While googling for “Sort Warning” in execution plan I came a across a very nice article by Margarita Naumova. In this article Margarita has explained very well what exactly the Hash and Sort warning are and how they cause performance problem in SQL SERVER.
Further, How memory grant is related to Hash and Sort warning and the solution such as updating statistics, using compile hints etc. can be leverage to remove these warning.

Link:http://blogs.technet.com/b/magi/archive/2011/03/01/inside-hash-and-sort-warnings.aspx