Sharing experience of Microsoft build 15 seminar (Mumbai)

8th June 2015, Monday….started early (5:45 AM) to attend Microsoft’s build 15 seminar in Mumbai.



The seminar was hosted by 3 key members…

Pete Brown – Technical Evangelist
Shen Chauhan – Senior Program Manager
KiranKumar Balijepalli – Senior Program Manager

The seminar was mainly focused on deep dive into Windows 10 developer platform for app & web developers wherein Pete Brown, Shen Chauhan demonstrated multiple platforms, new features in Windows 10, Visual Studio 2015 features such as UWP, Machine Learning API, Cassandra, IOT, Raspberry pi technology. The main learning area was Universal Windows Platform (UWP), that can run across wide variety of devices, which provides the guaranteed core API layer across devices. You can create a single app package that can be installed onto a wide range of devices. Pete Brown & Shen Chauhan demonstrated some of the good coding sessions & exciting demos which helped a lot to understand new features and advantages.



I was expecting more about Cloud technology. However, KiranKumar covered bit of Microsoft Azure wherein he elaborated more about the SQL Azure, NOSQL data management services such as Cassandra, DocumentDB, MongoDB. Also, covered a very quick overview on Azure Data lake Service, Elastic SQL Database Pool for azure.

Overall, it was informative seminar wherein I personally come to know about many new things that Microsoft was working on and also planning in near feature . Thanks to Microsoft for bringing in Build 15 in Mumbai!

Check How Old are you? by Microsoft.

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;






INSERT @TestRethrow(ID) VALUES(1);

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

INSERT @TestRethrow(ID) VALUES(1);



PRINT ‘In catch block.’;






  • 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



  • 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…

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.


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
OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS
FETCH NEXT @RowsCountPerPage ROWS only
SET @PageNumber = 2; SET @RowsCountPerPage = 5;
SELECT * FROM @tblSampleData
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:


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


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:

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.