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.

How to combine Multiple Rows into Single Column in SQL Server

I have come across a link where you can combine multiple rows into single column using the COALESCE function.It is very simple as shown below

CREATE TABLE WeekDays
([Name] varchar(40))
INSERT INTO WeekDays VALUES('Mon');
INSERT INTO WeekDays VALUES('Tue');
INSERT INTO WeekDays VALUES('Wed');

DECLARE @str VARCHAR(2000)
select @str = COALESCE(@str + ',', '') + [Name]
from WeekDays
SELECT @str

Output :Mon,Tue,Wed

You can see the original article on
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

How to import Database Schema to XML

I often get questions on how to import database schema into XML file for doing activities like Data Comaprison etc. I have written a small script to generate the database schema including the following

• Column Details ( Name, data type ,length etc)
• Primary Key
• Foreign Key
• Indexes

Sample Script

SELECT a.name TableName,
( SELECT
c.name ColumnName,type_name(c.xusertype) DataType,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN CAST(c.prec AS SMALLINT)
WHEN type_name(c.xusertype)='UNIQUEIDENTIFIER' THEN NULL
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST(IDENT_SEED(a.name) AS SMALLINT)
END
ELSE CAST(c.prec AS SMALLINT)
END DataLength,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN c.scale
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST( IDENT_INCR(a.name) AS INT)
END
ELSE NULL
END Scale,
CAST(c.isnullable AS BIT) As IsNullable,NULL AS DataDefault,NULL AS DefConstraintName,
CASE WHEN colstat=1 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsIdentColumn
FROM SYSColumns c
WHERE c.id = OBJECT_ID(a.name)
and a.id = c.id
AND C.CDEFAULT =0
FOR XML AUTO, TYPE
) columns,
(select 'UID' ColumnName,f.name PrimaryKeyName, f.type_desc PrimaryKeyType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =1
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
)PrimaryKey,
(Select
object_name(rkeyid) Parent_Table,object_name(fkeyid) Child_Table, object_name(constid) FKey_Name, c1.name FKey_Col,c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
where s.fkeyid = a.id
FOR XML RAW,TYPE
) ForeignKey,
(select f.name IndexName ,DBO.fGetIndexCols (object_NAME(f.object_id), f.index_id ) IndexColumn,
f.type_desc IndexType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =0
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
) Indexes
from sysobjects a
where a.xtype ='u'
FOR XML PATH('Table'), ROOT('TableDetails')

SQL Server 2008 – Inline variable initialization

Microsoft has extended the support of inline variable initialization feature from the programming languages in to T-SQL in SQL Server 2008.

When we want to declare and initialize a value to variable in T- SQL, we need to do it is two steps (declaration & initialization) as shown in the example

DECLARE @V_Value DATETIME
SET @V_Value =GETDATE()

In SQL Server 2008 this can be simplified by combining both the lines into a single steps as we do in programming languages

DECLARE @V_Value DATETIME = GETDATE()

Even though it is a very small feature, it helps the developers who have the technical background for programmig languages like JAVA, C#.NET , VB.NET etc

Turn Off - Prevent saving changes in SQL Server 2008

I was working with SQL Server 2008 for quite some time and noticed a strange behavior (which is not in previous versions of SQL Server) when I try to save a table in Management Studio that requires table to be dropped and recreated (e.g. Try to ADD a new NOT NULL column to the table which is having data) .I get the following warning message

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I though it is bug with SQL Server, but later cam to know it is the expected behavior and can be turned of by unchecking "Prevent saving changes that require table re-creation" in the Designer properties. Please refer to the below screen shot