SQL Server - SQL Server 2012 New features

SQL-Server 2012 – T-Sql

In SQL Server 2012 Microsoft has added few T-SQL enhancements to make common tasks much easier. These features can be leverage in writing efficient SQL query.

String Functions:

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')

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. The Alternate solution for CAST/CONVERT function is:

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')

One new feature of SQL 2012 that helps to implement Paging logic in Query without using any ranking function called “Ad-hoc query paging implementation”.

We usually used ROW_NUMBER() function to handle paging functionality in SQL query.

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 specify 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.

THROW statement

·        New operator for enhanced error handling.

·        Can be instead of RAISERROR, however RAISERROR requires user defined message to be added to sys.messages table before you use it to raise an error.

·        THROW statement does not require error number to exist in system table.

·        Exception raised by THROW statement always set severity level to 16.

·        The statement before THROW statement needs to terminate with semicolon (;).

Syntax-THROW <error number>, <message>, <state>

Code Snippets –

THROW 51000, 'The record does not exist.', 1;

GO

DECLARE @TestRethrow TABLE

( ID INT PRIMARY KEY);

BEGIN TRY

INSERT @TestRethrow(ID) VALUES(1);

-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.

INSERT @TestRethrow(ID) VALUES(1);

END TRY

BEGIN CATCH

PRINT 'In catch block.';

THROW;

END CATCH;

TRY_CONVERT

·        New function to handle conversion issues/errors.

·        Returns a value to specified data type in case of success.

·        Return "NULL" in case of conversion fail.

Syntax-

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Code Snippets –

SELECT CONVERT(FLOAT, 'test')	Throws an error – Error converting data type varchar to float.
SELECT TRY_CONVERT(FLOAT, 'test')	Error is handled by returning NULL

IIF

·        Evaluate the Boolean expression and return the True or False value.

·        Can be used instead of CASE statement

Syntax-

IIF ( boolean_expression, true_value, false_value )

Code Snippets –

DECLARE @a INT = 1, @b INT = 0

SELECT CASE

            WHEN @a = @b

                  THEN 'True'

            ELSE 'False'

            END

SELECT IIF(@a = @b, 'True', 'False')