Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
FROM Production.Product PP
ORDER BY pp.productid

The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
FROM Production.Product PP
ORDER BY pp.ProductID

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.


TRY_PARSE – Conversion function in SQL Server 2012

In this article I will discuss about the TRY_PARSE function in SQL Server 2012. Conversion functions helps us avoid errors when dealing with different data types. Let us understand TRY_PARSE with an example.

SELECT TRY_PARSE ('12-18-2013' AS datetime) AS Alpha
SELECT TRY_PARSE ('2013' AS decimal) AS Beta
SELECT TRY_PARSE ('2013.00' AS decimal) AS Gamma
SELECT TRY_PARSE ('2013.0000' AS float) AS Theta
SELECT TRY_PARSE ('Arsenal' AS float) AS Delta

The TRY_PARSE() function can convert any string value to a Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Lets have a look at the output of the above
TP2In the above case it would not convert ‘Arsenal’ to a date time or Numeric value and hence it results in NULL output. TRY_PARSE function is not a native SQL SERVER function, instead it is a CLR dependent function.

IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to use ‘WITH RESULT SETS’ in SQL Server 2012

In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
CREATE PROC Usp_products
SELECT ProductID,Name,ProductNumber FROM Production.Product

Let us now execute the above procedure and see the outcome
withresultset1Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)

withresultset2So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

Format() function in SQL Server 2012

SQL Server 2012 introduces this new function called FORMAT which returns a value in the specified format and also can optionally apply a regional format. This function relies on the .NET Framework.

In this article we will explore this function and see how it can be implemented.

FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where we pass on the date value or a numeric value. The second parameter is the .NET Framework format string. The format parameter is case sensitive. The third parameter is the culture. This can be any culture supported by the .NET Framework.

Let us now explore this function with some examples:
1) Display the date using Bengali Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Bn-IN' )
 AS DateInBangla;

format12) Display the date using Tamil Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Ta-IN' ) AS

format23) Display an amount into different currency structures

SELECT FORMAT(100, 'C', 'en-GB') AS Pounds,
FORMAT(100, 'C', 'en-US') AS Dollars,
FORMAT(100, 'C', 'es-ES') AS Euro,
FORMAT(100, 'C', 'en-IN') AS Rs;

format34) Display current date time in US format

SELECT FORMAT(GETDATE(), N'"Time now is "dddd MMMM dd, yyyy', 'en-US')
AS USTimeStamp;

format4For more details on this function please refer to this msdn link.

What is C2 Audit tracing in SQL Server

In this article I will discuss the C2 audit trace option in SQL Server.C2 is security-auditing level defined by the US Government’s Trusted Computer Security Evaluation Criteria (TCSEC) program. Microsoft added the C2 audit mode option to address government requirements that certain contractors document database activity and possible security policy violations. So what does this audit trace do and how can we enable\disable the audit trace.

This audit captures the following information on the SQL server.
1) End User Activity (All SQL commands, logins and logouts)
2) Security Events (Grant/Revoke/Deny, login/user/role add/remove/configure)
3) Utility Events (Backup/Restore/ Bulk Insert/BCP/DBCC commands)
4) Server Events (Shutdown, Pause, Start)

Let us now see how we can enable this audit trace
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'1'

Step 2: Restart the SQL Server

Once you have restarted the SQL Server, you will find a new trace has kicked off. If you run sys.traces on the server you will find an audit trace that is running
c2If you browse to the location of the trace and open the trace file and view the properties of the trace then you will see as below
c2_1So how can we stop this trace. If we try the traditional way of stopping a trace then it will result in an error. Snapshot below.
C2_3Follow the below steps to stop this trace and remove its definition from the server
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'0'

Step 2: Restart the SQL server

Few important points that we have to keep in mind before implementing this audit trace
1) This is resource consuming . Implement with caution on a busy OLTP system.
2) The max size of the trace file is set to 200 Mb. You cannot change this.
3) Once the size is breached, a new trace file generated.
4) If the directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag, or free up additional disk space for the audit log.
5) You cannot filter out events from this trace property, it captures everything.

CONCAT() function in SQL Server 2012

In this article I will discuss the new function concat() in SQL Server 2012. This function performs a concatenation operation. We need to pass CONCAT() a number of string arguments and it will concatenate, or join them together and return an output string.
The basic syntax is as follows:
CONCAT ( string_value1, string_value2 [, string_valueN ] )

Let us now understand with an example how we can use this function and how it is different from the concatenation operator. Let us create a new table and insert some values in the table and then use a query that will use this function

FirstName varchar(20) NOT NULL,
MiddleName varchar(20) NULL,
LastName varchar(20) NOT NULL
('Sachin', 'Ramesh', 'Tendulkar'),
('Diego', 'Armando', 'Maradona')
,('Diego', Null, 'Maradona')
SELECT CONCAT(FirstName + ' ', MiddleName + ' ', LastName) AS
FROM MyConcatTable

The output of the above as shown below
concat1The concat function joins the columns and returns a single string. Now the big question is how is it different from the below

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS CustomerName
FROM MyConcatTable

Let us run the above code and see the outcome
concat2The 3rd value in the table has a null in the middle name column. Normal concatenation would not handle that thus resulting in a null output but the concat() would automatically remove the null and join the next corresponding string value.

I hope this short article was helpful in understanding the new function in SQL Server 2012 called concat().

Life is a SEQUENCE of events – Part 2

In my previous article on Sequence we went through how we can create and implement Sequence.In this short article I will demonstrate how we can achieve the following:

1) How to get the current value of the Sequence
2) How to get the next Sequence Value
3) Restarting the Sequence Value
4) Dropping a Sequence object

1) current value of the Sequence
To find the current value of a Sequence we need to query the system view sys.sequences.

USE AdventureWorks2012
SELECT Current_Value FROM sys.sequences
WHERE name= 'MySequence1' -- specify the name of the sequence

2) get the next Sequence Value:

use Adventureworks
AS NextSequenceValue

3) Restarting the Sequence Value:

USE [AdventureWorks2012]
ALTER SEQUENCE [dbo].[MySequence1]

4) Dropping a Sequence object:

use Adventureworks2012

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

USE mydb
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
INSERT INTO Mytable1 VALUES (2,'Abhishek')
INSERT INTO Mytable2 VALUES (1,'Pandit')
INSERT INTO Mytable2 VALUES (2,'Agrawal')

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery

Step 2: Now run the following code to find the Tables available in the backup

Use Master
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference:

Life is a SEQUENCE of events

In this article I will discuss this new feature in SQL Server 2012 called Sequence and how we can use this feature.

Sequence in SQL Server 2012 is a database object and can be considered as an alternative to Identity property which we set at a table level. The primary difference between the two is that sequence is scoped to the entire database while Identity is scoped to a specific column in a specific table.

If we expand the programmability node under a specific database in SQL Server 2012 then the last object that we would find is Sequences.
seq1The primary purpose of sequence is that it can used between different tables within the database and help maintain a synced copy of seed values between multiple tables that are referencing each other as it is not limited to a single table.
Another objective where Sequence can be very handy is performance gain because using identity means that SQL has to fetch the next value from the disk whereas sequences would be residing in the memory in case it is used with the cache option which we will see later.

Let us now understand with an example how we can create and implement sequence.

Creating a Sequence:
Step 1: Right click on the Sequence object under the Programmability node
Step 2: Select ‘New Sequence’ and you will see the below dialog box
seq2Let us go through and understand each field in this dialog box before proceeding

a) Sequence name: This is where you give a name of the sequence. By default it picks up the date and time of the sequence creation.
b) Sequence schema: The schema to which you would assign the sequence.
c) Data type: The data type of the sequence. By default it is bigint.
d) Start Value: The starting value of the sequence
e) Increment By: The value by which you want to increment the series.
f) Minimum value: The least value of the series. This comes into play when you cycle the series.
g) Maximum value : The upper bound of the series post which the series will either cycle or stop.
h) Cycle : This property specifies whether the series should restart from the minimum value or throw an error when its minimum or maximum value is exceeded. The default cycle value for new sequence objects is NO CYCLE.
i) Cache: This increases performance by minimizing the number of disk IO that are required to generate sequence numbers. If a cache is set to 100 is chosen, SQL Server does not keep 100 individual values cached. It only caches the current value and the number of values left in the cache.

Step 3: Lets now fill up the dialog box and create our first sequence.
seq3The TSQL for the same is given below

USE [AdventureWorks2012]
CREATE SEQUENCE [dbo].[MySequence1]
AS [int]

When you click on ok button or you run the code, you will find a new sequence created under the Sequences node.

Now let us implement this object and understand how it works:
Step 1: Create a new table

USE [AdventureWorks2012]
Fname VARCHAR(30),
Lname VARCHAR(30),

Step 2: Now lets insert some values into the table

(NEXT VALUE FOR Mysequence1, 'Sanchayan','Pandit')
(NEXT VALUE FOR Mysequence1, 'Abhishek','Agrawal')

Lets have a look at the rows inserted
seq5As you observe now, the id field now has incremented by 1 starting with 1000.

So this is how we can implement Sequence in SQL Server 2012. I will deep dive into Sequence in my next article and we will understand how we can play around this new feature in SQL Server 2012.



In this article I will discuss about the ‘Optimize for Unknown’ query hint that was introduced in SQL Server 2008 and how we can use it.

So what is ‘OPTIMIZE for UNKNOWN’?
‘OPTIMIZE for UNKNOWN’ directs the query optimizer to use the standard rules it uses if no parameter values have been passed.The optimizer will use statistical data instead of the initial values for all variables when the query is compiled and optimized.

Now the big question is how does this help? Well ‘OPTIMIZE for UNKNOWN’ is one of the mechanisms which helps us to avoid parameter sniffing.

So what is parameter sniffing?
I will explain this with an example. I will use the Adventureworks2012 database for demonstration purpose on SQL Server 2012. However you can use any other version but not lower than SQL Server 2008.

Let us run the below query on the database and observe the output and execution plan

USE AdventureWorks2012
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744

Row Count: 13
ofu1Now lets take a look at one of the aspects of the execution plan of the above query. Make a note that the actual number of rows is 13 and the estimated number of rows is 44.
ofu2Let us have a look at the plan xml for the run time values and compiled time values:
ofu3Now let us run another query with a different value.

USE AdventureWorks2012
SELECT * FROM Sales.SalesOrderDetail
Where ProductID = 707

Let us observe the row count, execution plan and plan xml values as we did for the previous query
Row Count:
ofu4Execution plan: Make a note that the estimated number of rows is still 44.5 which was for the parameter used in the previous query i.e 744
ofu5Plan XML for Compiled and Runtime values: Make a note here that the compiled value used by the query optimizer here to create the plan is 744 which was the value that the previous query used.
ofu6This is called parameter sniffing where the optimizer sniffs the current parameter value during compilation.

Can this cause trouble?
Yes, it can. When a non-similar parameter is passed when a plan is compiled for the first time, the plan that the optimizer will find in the memory may not be the optimal one for that parameter passed. This will can result in a plan that is suboptimal and can cause a devastating effect on performance.

So how can we use ‘OPTIMIZE for UNKNOWN’ to avoid parameter sniffing. let us understand that with the below example.

We are going to run the above 2 queries here also but with the Optimize query hint in the second query:

USE AdventureWorks2012
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 707

Observations post execution of the two queries:
1)  It creates 2 different plans:
ofu72) Estimated and Actual row calculation for the two queries respectively:
Query 1:
ofu8Query 2:
ofu93) Compiled value and RunTime value from Plan xml:
Query 1: With parameter value 744
ofu10  Query 2: With parameter value 707 and query hint (Optimize for Unknown)
ofu11 So with the use of the query hint the optimizer does not use the value that it got from the cache but generates a new plan based on the available stats in the database.

What are the kind of stored procedures that can be a victim of Parameter Sniffing?
A)  SP’s which has optional parameters
B)  SP’s which has parameters in range operators.

How to find the last execution details of a stored procedure in SQL Server

In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT) AS qp
WHERE st.text like '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
FROM sys.dm_exec_query_stats AS qs
sys.dm_exec_sql_text(qs.sql_handle)AS st
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%Name of Stored Proc%'

How to increase the number of SQL Server logs

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
EL5Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
EXEC dbo.sp_cycle_agent_errorlog

This will re-initialize the current log and start a new log with the current date time stamp
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
b) Click on OK

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

%d bloggers like this: