How to create SEQUENCE in SQL Server 2012

Sequence ID generation is one of the common problems faced by SQL Server development community especially with high concurrent user scenarios. Though SQL Server supports different work arrounds currently, each one has its own pros and cons.

Microsoft has finally put an end to the long wait and introduced the SEQUENCE object in its latest version SQL Server 2012 (Similar to the Oracle Sequences).

SEQUENCE is a user defined object type which generates a sequence of numbers based on the increment definition.I have given the syntax and example below

SEQUENCE Syntax

CREATE SEQUENCE [SequenceName]
START WITH [StarNumber]
INCREMENT BY [IncrementNumber];

SEQUENCE Example

CREATE SEQUENCE EmployeeID
START WITH 1
INCREMENT BY 1;

INSERT INTO EmployeeTable ( ID ,Name, Age ,Sex)

SELECT NEXT VALUE FOR EmployeeID ,’sam’ ,’30’,’Male’

For generating Sequence on older version of SQL Server , refer to my earlier article http://www.samsudeenb.blogspot.in/2009/06/how-to-generate-sequence-number-in-sql.html

Plan Cache and Parameter sniffing in SQL Server 2008

I have recently encountered   performance problem with one of my production servers due to parameter sniffing in SQL Server 2008. It actually took us quite a bit time to find out the problem and I thought it would be really useful to share it. So what is parameter sniffing?

“Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed”.  In Other words the plan is never regenerated / optimized after the first execution which forces the optimizer to use the same plan irrespective of the parameters.

 Though there are many ways to identify the parameter sniffing the simplest behavior is there is a considerable difference in the query execution times when executed using SQL Query Analyzer and actual stored procedure call from the application.

I have given some of the commonly used solutions to solve the “Parameter sniffing”

Trace Flag

The simplest of all the solutions is to switch of the Parameter Sniffing using the trace flag 4136. The DBCC syntax is given below

DBCC TRACEON (4136)

Local Variable Substitution

We can force the SQL optimizer to temporarily suspend the Parameter Sniffing by using local variables substitution for the parameters and use only the local variables in the query as shown below

CREATE PROCEDURE pGetPatientName
(
      @P_PatientID VARCHAR(50),
      @P_Name VARCHAR(50)
)
AS
BEGIN

DECLARE @V_PatientID VARCHAR(50)

DECLARE @V_Name VARCHAR(50)

SET @V_Name = @P_Name

SET @V_PatientID = @P_PatientID

SELECT

ForeName,
SurName
FROM
Patient
WHERE PatientID = @V_PatientID
AND ForeName LIKE @V_Name+'%'

END

Recompile & Query Hints

You can also make the optimizer to force for the plan compilation using the options such as

      1.       Execute the query “With Recompile” option

2.       Alter the Procedure / Drop & Re Create Indexes

3.       Specifying Query Hints