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

How to retrieve TOP Distinct values in SQL Server

I have recently got a requirement from one of our customers to show recently used distinct items for a particular report. While writing the query, I found out it is not straight forward as there is a little cache “recent items” in the requirement.

This can be achieved by using a simple trick as shown in the below example. To illustrated the scenario, I have create a sample table with data

Sample Data

CREATE TABLE SaleItem ( ItemName NVARCHAR(30),SaleDate DATETIME)

insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Book',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pencil',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()
insert into SaleItem SELECT 'Pen',GETDATE()

SQL Query

WITH ItemList AS
(
SELECT
IL.ItemName,
IL.SaleDate,
ROW_NUMBER() OVER (ORDER BY IL.SaleDate DESC) as 'RankID'
FROM
SaleItem IL
)
SELECT DISTINCT TOP 30
IL.ItemName,
IL.SaleDate
FROM ItemList IL
WHERE NOT EXISTS (Select 1 FROM ItemList IL2
WHERE ( IL2.ItemName = IL.ItemName )
AND IL.RankID < IL2.RankID)
ORDER BY SaleDate DESC

The trick is assigning RankID to each row and eliminate the duplicate row  by comparing the RankID while selecting the records.

How to Create Linked Server between MYSQL and SQL Server

Steps to configure Linked Server

Step 1:

• Install the My SQL OBDC connector on the SQL Server machine (This will come default if you have already installed My SQL in the SQL Server machine. The setup file for the drive is attached with in the mail.
• Create an ODBC System DSN with the name “MYSQL” using the driver installed on the previous step as shown in the below image

 Step 2:

Create a linked server between the SQL Server and the My SQL Server using the steps given below.

• Connect to the SQL Server instance and expand to the section Linked Servers 
• Right click on the Linked Server and select the option “New Linked Server”

• Specify the connection details to the ODBC driver as shown below

Details

Linked Server : MYSQL
Provider : Microsoft OLE DB provider for ODBC drivers
Product Name : MYSQL
Data Source : MYSQL
Provider String : DRIVER={MySQL ODBC 5.1 Driver};SERVER=SAMSUDEEN;PORT=3306;DATABASE=salem_dbo;USER=root;PASSWORD=password
Catalog : salem_dbo

• Go to the Security Tab and give tab and give the MY SQL username & password under the option “Be made using this security context”
• Go to the Server options and set value true for RPC & RPC Out properties.

Step 3:

• Click OK to create the linked server. You can test the connection using the test connection option as shown below

How to run a SQL batch multiple times

Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below

CREATE TABLE TestData ( Name VARCHAR(100), Age INT)
GO
Insert into TestData
SELECT 'John' ,28
GO 100

In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions

How to recover a Database in suspect mode

I was struck with a database which is on suspect mode and need to recover it without any recent backups. I searched across the net and found these steps which are quite useful

Step 1:
Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 2:
Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.
ALTER DATABASE DatabaseName SET EMERGENCY;

Step 3:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 4:
Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

There are some best (simple) practices which prevents us from such failures. Below are some of them

• Backup your data frequently ( daily or once in two days)
• Have multiple backups. Move the backups to external drives or tapes frequently
• Validate that your backups are good by doing trial restores to alternate server
• Run CHECKDB regularly

Filter Index in SQL Server 2008

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.
A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index

Syntax
CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria

Example
CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL

Advantages of Filtered Index

• Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records
  
• Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.


• Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index

Analysis

I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6"

Normal Index

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)

Filterer Index to exclude records of Organisation =10

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) WHERE OwnerOrganisationUID <>10




Conclusion

Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate