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

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.

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.

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

Error : “The file ‘tempdev2′ cannot be removed because it is not empty.”

I have multiple data files created for Tempdb database let’s say tempdev2. Now when I try to remove additional mdf files from filegroup, I got below issue. SQL Server says..

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.
Cause : The reason for such behavior is there are some activities going on that is using files created your TempDB database. The general behavior is to restart the SQL server that will create your TempDB files and reset it. But the only problem with it is it will down the other environments / databases.
Fix :
USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

I hope this will help you!

Like this post and post comments if you like it.

Download SQL Server 2012 Developer Training Kit

The SQL Server 2012 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2012 database and BI solutions. The SQL Server 2012 Developer Training Kit is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2012 from a developer perspective…. download and read more on…http://www.microsoft.com/en-us/download/details.aspx?id=27721&WT.mc_id=rss_alldownloads_all