New features are added with every release of SQL Server and as a result, some features get removed or deprecated. Deprecated features are features that are still operational (for backward compatibility) but will be removed in a future version. Deprecated features can be of two types: those that will be deprecated in a future version and those that will be deprecated in the next version.
In this article, we will explore how to track deprecated code and correct it. I will also share our observations when we performed the same operation on a live production database. Please feel free to retweet and share this link with fellow developers.
In this article, we will explore how to track deprecated code and correct it. I will also share our observations when we performed the same operation on a live production database. Please feel free to retweet and share this link with fellow developers.
Tracking Deprecated Code in your Database
Tracking deprecated features can help you identify potential future bugs and upgrade and compatibility problems in your database. SQL Server 2008 provides the following methods to detect deprecated code in your database:
- SQL Server: Deprecated Features performance counter
- Deprecation category of trace events that includes the Deprecation Announcement and Deprecation Final Support event classes, which can be monitored using SQL Server Profiler or SQL Trace. You can use event notification and track these events via a stored procedure.
- sqlserver.deprecation_announcement and the sqlserver.deprecation_final_support Extended events
- Performance Monitor Tool (perfmon.exe) to display the value of counters for SQL Server: deprecated features object.
- SQL Trace Collector to automate the collection of deprecation events
Observe the following code which uses the Deprecated Feature performance counter and gives a feel of how the deprecation feature works:
OUTPUT
The sys.dm_os_performance_counters dynamic management view (DMV) maintains a count every time a deprecated feature is used. The code shown above is for a named instance MSSQL$<instancename>. For a SQL Server default instance, just replace the above code with this one
object_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'
AND cntr_value > 0;
Another way of using this performance counter is by using PerfMon tool (Start > Run > Perfmon) on your machine. For example lets add the sp_dboption counter from the SQLServer:Deprecated Features performance object as shown below.
To test the counter, fire the following statement in SSMS which uses the deprecated feature sp_dboption
USE master
EXEC sp_dboption 'Umbra', 'read only', 'FALSE'
GO
and note the deviation and counter value when the query is fired. Microsoft recommends to use ALTER DATABASE instead of sp_dboption.
SET READ_ONLY
GO
It’s always best to log and save your results and the SQL Profiler gives you options to do that.
Note: If you are upgrading your SQL Server 2000 installation to SQL Server 2008, then remember to specifically search for the features that were deprecated in SQL Server 2005, since those features may no more exist in SQL Server 2008. Google/Bing is your friend here and use them to search for removed features. Another way is to look at the Backwards Compatibility section in the SQL server 2005 Books Online Installation guide and remove the deprecated features listed in that section.
What to do once Deprecated Code is detected?
Microsoft has created a page that describes all the deprecated features and has listed alternative techniques to future-proof your code. Although in the beginning of this article, I have shown only a partial screenshot of the deprecated features used in a live production database, we actually encountered over 50 deprecated features that we were using. I have attempted to list some important ones randomly and hope that this list will be useful to you too and would save you time if you were to deal with deprecated code in your database.
Here’s a list of our observations:
- The COMPUTE or COMPUTE BY syntax is deprecated since it created a new summarized result set which was difficult for apps to consume. Instead now use GROUP BY with ROLLUP which occurs once per compilation.
- While using locks in SQL Server 2008, although sp_lock and sys.syslockinfo are still available, their use has been deprecated and instead SQL Server favors the Sys.dm_tran_locks.
- While performing locks use the rowversion data type instead of the timestamp data type
- The text, ntext, and image data types are being deprecated (since SQL 2005) in favor of varchar(max), nvarchar(max) and varbinary(max) data types.
- Instead of sp_change_users_login, use ALTER USER WITH LOGIN
- Compatibility levels 60, 65, and 70 are deprecated and support is provided only for compatibility levels 80 and higher
- DBCC DBREINDEX has been deprecated and replaced with the ALTER INDEX REBUILD command and DBCC INDEXDEFRAG has been replaced with ALTER INDEX REORGANIZE
- The sp_depends system stored procedure and the sys.sql_dependencies catalog view have been deprecated. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead
- Use of WRITETEXT or UPDATETEXT is deprecated when modifying text, ntext, or image data types.
- In the CREATE FULLTEXT CATALOG arguments, rootpath has been deprecated.
- Full-Text Search stored procedures are deprecated. Use CREATE FULLTEXT CATALOG, ALTER FULLTEXT CATALOG, and DROP FULLTEXT CATALOG instead
- If you are using XML, XDR (XML Data Reduced) schemas is deprecated
- SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT options are deprecated and replaced with the SET SHOWPLAN_XML option
- SET STATISTICS PROFILE option has been deprecated and replaced with the SET STATISTICS XML option
- The old syntax of RAISERROR syntax specifying the error number and message number is deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead use the new syntax RAISEERROR(50005, 10, 1) which allows you to specify the messageid, severity and state)
- In earlier editions owner.tablename was supported. In SQL 2008, this is deprecated. If an object belongs to a schema, then it must be referenced with its associated schema name so the syntax changes to schemaname.tablename.
- The sp_attach_db stored procedure has been deprecated and you should use CREATE DATABASE with FOR ATTACH clause
- sp_addlogin is replaced with CREATE LOGIN. sp_defaultdb, sp_defaultlanguage, sp_password are all replaced with ALTER LOGIN
- sp_droplogin and sp_revokelogin is replaced with DROP LOGIN
- sp_adduser, sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess are replaced with CREATE USER, DROP USER and ALTER USER
- sp_addrole and sp_droprole are replaced with CREATE ROLE and DROP ROLE
- The ALL clause has been deprecated.
- Log shipping is deprecated and replaced with Database Mirroring
- The SQL Server Surface Area Configuration tool is deprecated and replaced with Policy-Based Management.
- Remote servers will soon be deprecated and you should no longer use sp_addserver, sp_addremotelogin, sp_remoteoption and sp_helpremotelogin. Use Linked server instead.
- DUMP and LOAD have been replaced with BACKUP and RESTORE
- sp_helpdevice is replaced by sys.backupdevices catalog view
- The FASTFIRSTROW table hint is deprecated
- String literals as column aliases – Microsoft discourages the use of string as a column alias in a SELECT statement as it occurs per compilation. So instead of 'RetValue'= @ret_val, you should use @ret_val as ‘RetValue’
These were just some of the deprecated features we encountered and shared with you. For a detailed list, check this documentation.
Here’s a mantra - Rewrite any pre-written code that uses deprecated features, and avoid using any deprecated features in new development! It will make your life as a database guy, easier.
About The Author
1 comment:
I am curious why I see two calls to my api controller in fiddler?
Post a Comment