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 when you use a feature that will be removed from future version of SQL Server, but will NOT be removed from the next major release of SQL Server.
Deprecation Final Support: Occurs when you use a feature that will be removed from the next major release of SQL Server.
Next click on "Column Filters" and set SPID filter to 61 (or what ever number you got in Step 1)
Step 3: Now lets go back to the window where we ran SELECT @@SPID and create some sample scripts which contains deprecated commands in it.
/*
Some random scripts to demonstrate how deprecated code can be captured using SQL Server Profiler
*/
SET NOCOUNT ON;
--Here TEXT, IMAGE are deprecated
CREATE TABLE tblDeprecationLocator
(
Sno INT,
Remarks TEXT,
Profilepic IMAGE
)
GO
--This is deprecated. Better alternative is to use ALTER INDEX
DBCC DBREINDEX('tblDeprecationLocator')
GO
--Using hints without WITH keyword is deprecated. It should be WITH (NOLOCK)
SELECT * FROM tblDeprecationLocator (NOLOCK)
GO
USE Master
GO
--here Truncate_only option is deprecated
BACKUP TRAN TestBed WITH TRUNCATE_ONLY
GO
Now when we execute these scripts SQL Profiler would capture all the deprecated code and provide appropriate message as shown below.
1. The TEXT, NTEXT, and IMAGE data types will be removed in a future version of SQL Server. Avoid using them in new development work, and plan to modify applications that currently use them. Use the varchar(max), nvarchar(max), and varbinary(max) data types instead.
2. DBCC DBREINDEX will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. Use ALTER INDEX instead.
3. Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.
4. BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
In the next post we would see how to make use of Extended Events in SQL Server 2008.
Conclusion:
For greatest longevity of your applications, avoid using features that cause the deprecation announcement event class (or) the deprecation final support event class.
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 when you use a feature that will be removed from future version of SQL Server, but will NOT be removed from the next major release of SQL Server.
Deprecation Final Support: Occurs when you use a feature that will be removed from the next major release of SQL Server.
Next click on "Column Filters" and set SPID filter to 61 (or what ever number you got in Step 1)
Step 3: Now lets go back to the window where we ran SELECT @@SPID and create some sample scripts which contains deprecated commands in it.
/*
Some random scripts to demonstrate how deprecated code can be captured using SQL Server Profiler
*/
SET NOCOUNT ON;
--Here TEXT, IMAGE are deprecated
CREATE TABLE tblDeprecationLocator
(
Sno INT,
Remarks TEXT,
Profilepic IMAGE
)
GO
--This is deprecated. Better alternative is to use ALTER INDEX
DBCC DBREINDEX('tblDeprecationLocator')
GO
--Using hints without WITH keyword is deprecated. It should be WITH (NOLOCK)
SELECT * FROM tblDeprecationLocator (NOLOCK)
GO
USE Master
GO
--here Truncate_only option is deprecated
BACKUP TRAN TestBed WITH TRUNCATE_ONLY
GO
Now when we execute these scripts SQL Profiler would capture all the deprecated code and provide appropriate message as shown below.
1. The TEXT, NTEXT, and IMAGE data types will be removed in a future version of SQL Server. Avoid using them in new development work, and plan to modify applications that currently use them. Use the varchar(max), nvarchar(max), and varbinary(max) data types instead.
2. DBCC DBREINDEX will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. Use ALTER INDEX instead.
3. Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.
4. BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
In the next post we would see how to make use of Extended Events in SQL Server 2008.
Conclusion:
For greatest longevity of your applications, avoid using features that cause the deprecation announcement event class (or) the deprecation final support event class.
Comments