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

How to select records with in Date Range

Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below

Wrong comparison
SELECT *
FROM Table
WHERE StartDate >= @P_StartDate
AND EndDate =< @P_EndDate

This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below.

Modified Query
SELECT *
FROM Table
WHERE EndDate > = @P_StartDate
AND StartDate =< @P_EndDate

Parameterized sorting in SQL Server

Applications that allow users to sort data by different columns of the table might need to go for dynamic stored procedures or will end up in adding multiple procedures based on the number of combinations. In SQL Server we can achieve this easily through parameterized sorting.
As shown in the below example we can have a parameter which says the column on which the sort criteria can be applied and using the case statement we achieve the results without going for a dynamic /multiple stored procedures.

DECLARE @SortOrder INT
SET @SortOrder =1
SELECT ForeName,
SurName,
PASID
FROM PAtient
WHERE Forename LIKE 'A%'
ORDER BY CASE WHEN @SortOrder = 1 THEN ForeName
WHEN @SortOrder = 2 THEN SurName
ELSE PASID
END

Generate Index scripts using included columns

This article is a continuation of the previous article http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html with small enhancement to generate the index scripts with included columns. The below function “fGetIncludedIndex is used to get the included columns for a given index.

CREATE FUNCTION fGetIncludedIndex (
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @V_IncludedCol NVARCHAR(200)
select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') +
( select name from sys.syscolumns where id =a.Object_id and colid =a.column_id)
from sys.index_columns a
where object_name(a.object_id) =@objname
and a.index_id =@indid
and a.Is_included_column =1
order by a.index_column_id
return @V_IncludedCol
END

After creating the function in the DB make a small change to the existing query( highlighted in bold) to generate the index scripts with included columns

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

How to take week or month wise report in SQL Server

I was breaking my head whole day for writing a stored procedure which produces week wise report for plotting a revenue trend. I came across this solution which is fairly a simple one. Just thought of sharing this

The below code is written using the SQL Server built in functions DATEDIFF & DATEADD to produce the report

Select DATEADD(WK, datediff(WK, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(WK, datediff(WK 0, CollectionDate),0)
order by 1 asc

The logic here is to find out the corresponding week from Default date (01-01-1900) and adding the default date again to the result will get the corresponding week in date format. The same can be used for generating month wise report as well by just replacing "wk" with "MM"

Select DATEADD(MM, datediff(MM, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(MM, datediff(MM 0, CollectionDate),0)
order by 1 asc

Note: adding “0” will be automatically converted to default date “01-01-1900”

How to generate Sequence number in SQL Server

Sequence number generation is one of the common requirements in all the OLTP applications.SQL Server supports many ways to generate Sequence numbers. The below example explains how to generate multiple sequences dynamically using the SQL Server

Schema Design

This table will hold the configuration parameters for each of the Sequence Types (eg: PurchaseOrder, GRN etc).The column “SequenceName” column will have the unique code for each Sequence type and the “TableName” column is used to map the name of the “IDGenerator” table. (I.e. multiple IDGenerator tables can be created with the same structure and mapped accordingly)

ID Generation:
The below stored procedure pGetSEQID is used to generate the new sequence number .It accepts the table name as input and return the new sequence number. Since the IDGenerator table can be different for each ID type the stored procedure is written as dynamic.

CREATE PROCEDURE pGetSEQID (
@P_SEQTableName VARCHAR(30)
)
AS
BEGIN
DECLARE @V_SEQValue BIGINT
DECLARE @V_SQLString NVARCHAR(200)
DECLARE @V_ParmDefinition NVARCHAR(200)
DECLARE @V_SEQTableName VARCHAR(30)
SET @V_ParmDefinition = N'@V_SEQValue BIGINT OUTPUT'
SET @V_SQLString = N'INSERT INTO ' +
@P_SEQTableName +
N'(Status) values (''Y'') SELECT @V_SEQValue = SCOPE_IDENTITY()'
exec sp_executesql @V_SQLString,@V_ParmDefinition ,@V_SEQValue =@V_SEQValue OUTPUT
SELECT @V_SEQValue NewSequenceValue
END

EXEC pGetSEQID ‘PurchaseOrder’ will generate the Sequence Number for ID type purchase order

We can make use of Prefix and Suffix columns in the IDParamter table to generate the Sequence number with the required format
Eg: “BL0001” , BL100/0908

How to Select Data in Random Order in SQL Server

In number of scenarios we might want to do data sampling or select the data in a Random Order. SQL Server supports various options for data sampling. I have given some of the examples here

Using NEWID() :
SELECT TOP 10 ForeName
FROM Patient
ORDER BY NEWID()

Using PERCENT
SELECT TOP 10 ForeName
FROM (SELECT TOP 30 PERCENT ForeName
FROM Patient
ORDER BY ForeName ASC) AS Pat
ORDER BY 1 DESC

How to find the Database Restore Details in SQL Server 2008

It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files

SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id


You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below

WHERE
rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())
AND destination_database_name = ISNULL( "DB Name", destination_database_name)
ORDER BY rsh.restore_history_id DESC

SQL Server 2008 - Performance white paper

Microsoft has release its latest white paper on SQL Server 2008 performance trouble shooting.This article provides steps to diagonse the common performance problems faced by Microsoft CSS team.The following areas are covered in depth

• CPU Bottlenecks
• Memory Bottlenecks
• IO Bottlenecks
• Temp DB
• Slow Running Queries
• Extended Events
• Data Collector & MDV

The new features of SQL Server 2008 such as Extended Events & Data Collector are covered in detail .It is worth to have a look at this long running ( 102 pages) document. It is avilable for download at
http://msdn.microsoft.com/en-us/library/dd672789.aspx

SQL Server 2008 SP1- Released

Microsoft has released the latest service pack for SQL Server 2008 with the following key improvements

• SlipStream - The SQL Server 2008 and Service Pack 1 installation can be integrated and installed in a single step.
• Service Pack Uninstall – We can uninstall the service pack alone ( no need to un install the entire service)
• Report Builder 2.0 Click Once capability

It is available for download at

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

SQL Server 2008 DMV's Relationship mapping

Microsoft has introduced the concept of DMV’s (Dynamic Management Views) in SQL Server 2005 and concept is extended to SQL Server 2008 also with additional DMV's for mirroring , memory management etc.

Microsoft has recently released the latest “System Views Map” for SQL Server 2008 which shows the key system views and the relationships between them. You can download the latest System Views Map at

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

The updated “System views Map” for SQ Server 2005 also can be downloaded at

http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en

SQL Server Hot Fixes at one place

Microsoft is now days releasing lot of hot fixes for its products’ recently found one TechNet blog which is specific to hot fixes. This is one place where you can find the cumulative update of each hot fixes / updates released for SQL Server, also it has the release note information linked to the knowledge base articles.

This blog also contains hot fix information for other Microsoft products such as Windows, Visual Studio, and IE etc. The original blog can be find at

http://blogs.technet.com/hot/archive/tags/SQL+Server/default.aspx

Reasons for slower Delete in SQL Server

Delete statement plays a major role in many of the database maintenance activities. Extreme care should be taken before executing the DELETE statements
Before executing the DELETE statement it is better to check the “Estimated execution plan”, so that we can create proper indexes to speed up the execution .I have given some of the possible reasons for slower delete.

1. Locking / Blocking - If it is a production database process is having Lock / Block on the table

2. Fragmentation - The Index pages are fragmented due to excessive delete on the table (Defragment the Indexes and try again)

3. The table you are trying to delete is referred by many tables as foreign key and those columns are not indexed.

4. There might be hanging transactions on the table - Try to truncate the Log and do

5. You can also change the Recovery Mode to simple and try - but not advised in case of production DB

Also it is recommended to execute the DELETE statements in smaller batches so that the Log space & Roll back of the records can be easier as shown below

// assume you wanted to delete 100000 records, we can split them into 10 batches as
DECLARE @V_Count INT =0
WHILE @V_Count < 100000
BEGIN
DELETE TOP(10000)
FROM Table
WHERE COLUMN = Condition
SET @V_Count =@V_Count+10000
END

Sinlge user mode in SQL Server

I was trying to change the isolation level of my database using the ALTER DATABASE statement and the query was running for hours to execute it, because there were ongoing transactions in the db which is preventing the isolation change. I have solved this problem by taking the DB into single user mode before running the alter statement using below script

if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'
Exec(@sql)
end

Any changes to Database level properties in SQL Server can be done easily through the use of single user mode, which permits only one connection to be made to the database at any time.