Skip to main content

Posts

Showing posts from April, 2012

Capture Deprecated SQL Server code with SQL Profiler

While migrating your application from one version of SQL Server to another have you ever wondered how to identify the deprecated features in the new version? Manually going through hundreds of scripts is going to be tedious and time consuming. I am a lazy coder myself and would be interested only in an automated solution :) Until SQL Server 2005 we had only one way to identify it that is by making use of SQL Server Profiler . From SQL Server 2008 onwards we can make use of Extended Events as well. In this post lets see how to make use of SQL Server Profiler to identify the deprecated SQL Server code. Step 1: Open a SQL Server Instance and find out the session ID by executing the following script. This would come handy in SQL Profiler to filter out only information coming from this session. SELECT @@SPID Step 2: Open your SQL Server Profiler. Click on "Event Selections" tab and choose "Deprecation" event. Deprecation Announcement: Occurs

Clustered Index on an UniqueIdentifier column is costly

Generally having a clustered index on a UniqueIdentifier column is going to be costly. Even if we add some 5000 or 10000 records in the table the fragmentation level would be surely around 95+ % which means we have to very frequently REORGANIZE or REBUILD that index. So always i would suggest lets double check and be very sure that we need a GUID column and not an INT or BIGINT column. Fragmentation Level would be too heavy Lets create a sample to see how data is getting fragmented for different datatypes. ----------------------------------------------------- --Demo Table Creation Scripts ----------------------------------------------------- --Table with UniqueIdentifier as Clusetered Index CREATE TABLE dbo.TblUID ( Sno Uniqueidentifier NOT NULL DEFAULT NEWID (), FirstName VARCHAR (100) NOT NULL, DOB DATETIME NOT NULL, CONSTRAINT pk_tblUid PRIMARY KEY CLUSTERED (sno asc ) ); --Table with UniqueIdentifier as Clusetered Index CREATE TABLE dbo.TblSEQUID (