SQL Server 2012 TSQL enhancements – Part 3

In the previous article SQL Server 2012: TSQL enhancements – Part 2 we have seen Ad-hoc query paging feature of SQL 2012 that can be leverage to implement Paging functionality in query.

Today, we will see few T-SQL features that can be use in most application development.

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 need 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

  • A 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’)

If you like this post, please share your comments 🙂

Advertisements

SQL Server Memory Has Been Paged Out

Microsoft SQL Server manages memory dynamically based on the memory requirements of server load. But sometime you experience problem such as…

  • The performance of SQL Server decreases suddenly.
  • A computer that is running SQL Server stops responding for a short time.
  • A time-out occurs for applications that connect to SQL Server.
  • Problems occur when you run even simple commands or use applications on the system

This problem occurs because the Windows operating system pages out the working set of the SQL Server process. While looking for workaround for this problem I came across a very good article “How to reduce paging of buffer pool memory in the 64-bit version of SQL Server” by Microsoft Support Team which solved my problem.