Problem
Should I really be concerned about the deprecated features\commands that I use in SQL Server? In earlier versions, I have heard that commands will no longer be available and I have seen references in documentation. Thus far my scripts always seem to work with the new versions of SQL Server. Recently, I have heard rumors that Microsoft is no longer going to support the particular features once they are designated as deprecated features. So is Microsoft really going to deprecate the features and am I really going to have modify all of my scripts?
Solution
To the best of my knowledge, in the past Microsoft has indicated that particular SQL Server features\commands would no longer be available and those commands have remained in the product. Now Microsoft is indicated that once a feature is considered deprecated that the feature will be removed in the following product release. By in large, the items that are removed are replaced with new and better features and Microsoft does offer sufficient warnings well in advance. To answer your second question, I cannot say whether or not you will need to re-write all of your scripts because that really depends on what commands you are using. One way to address that specific need is with the SQL Server 2005 Upgrade Advisor, which will outline problematic code and provide additional details for correction.
What are some of the common deprecated features impacting SQL Server 2008?
| ID | Deprecated Feature | Explanation | Recommended Feature |
| 1 | Backup Log WITH NOLOG Backup Log with TRUNCATE ONLY | Clear the entries in the current database transaction log | Set the database recovery model to simple to clear the database log chain |
| 2 | DUMP DATABASE | DUMP LOG LOAD DATABASE | LOAD LOG BACKUP TRANSACTION BACKUP DATABASE with PASSWORD BACKUP DATABASE with MEDIAPASSWORD | Backup and restore of the database or transaction log with optional features | Issue BACKUP and RESTORE statements |
| 3 | 60 compatibility level 65 compatibility level 70 compatibility level | Configures a database to operate in an earlier compatibility level
| Use the current compatibility level based on the version of the product in use i.e. 90 for SQL Server 2005 |
| 4 | DBCC CONCURRENCYVIOLATION | For the SQL Server 2000 Desktop Edition or Personal Edition, this DBCC command indicates when the 5 concurrent batches limitation is exceeded | Use SQL Server 2005 Express Edition |
| 5 | sp_addalias sp_dropalias sp_addgroup sp_changegroup sp_dropgroup sp_helpgroup | Groups and aliases to support SQL Server security | Leverage roles to manage security in SQL Server 2005 and beyond |
| 6 | SETUSER | Allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user | Leverage the EXECUTE AS command |
| 7 | Remote Servers sp_addserver | A remote server allows a client connected to one SQL Server instance to execute a stored procedure on another instance of SQL Server without establishing another connection | Leverage Linked Servers |
Source – Deprecated Database Engine Features in SQL Server 2005
Which features are currently marked as deprecated and suspected to be removed in the version of SQL Server after the SQL Server 2008 release i.e. SQL Server 2010?
Below is a list of features that I have recently seen at customer sites, so I know they are used in the field. As such, be sure to stop using the deprecated feature and start using the recommended feature. For the full list of features reference the URLs in the ‘Next Steps’ section below.
| ID | Deprecated Feature | Explanation | Recommended Feature |
| 1 | DBCC SHOWCONTIG | Determine the index and table, pages, page usage, fragmentation, etc. | Leverage the sys.dm_db_index _physical_stats DMV |
| 2 | table_name.index_name syntax in DROP INDEX | Table and index reference has changed | index_name ON table_name syntax in DROP INDEX |
| 3 | DBCC DBREINDEX DBCC INDEXDEFRAG | Rebuild or reorganize indexes | Leverage the ALTER INDEX syntax |
| 4 | sp_attach_db sp_attach_single_file_db | Attach a database file as opposed to restoring the file | CREATE DATABASE statement with the FOR ATTACH option |
| 5 | sp_renamedb | Rename the database from an old name to a new name | Use the MODIFY NAME parameter in the ALTER DATABASE command |
| 6 | sp_fulltext_catalog | Modify an existing full text catalog | Issue CREATE, ALTER or DROP FULLTEXT CATALOG statements |
| 7 | fn_get_sql | Capture the full text of a spid | Leverage the sys.dm_exec_sql_text DMV |
Source – Deprecated Database Engine Features in SQL Server 2005
Next Steps
- To me the first step in resolving the usage of deprecated features is to determine where the problem exists. This can be easily found by the SQL Server 2005 Upgrade Advisor. With this information, you should be able to determine where the problem resides and how much time will be needed for correction.
- As you move code into production, be sure to validate the code does not use any objects or syntax that is scheduled to be deprecated. If so, save yourself the time in the long run and correct the code before it is deployed. More often than not, once one set of code uses a particular technique it gets re-used often. As such, correct the problem when it is small before it propagates across the environment.
- In addition, as new features of SQL Server are released, do not be surprised by old features being deprecated. So be sure to follow the latest and greatest techniques.
- For additional information on SQL Server deprecated features, reference the following articles:
- Deprecated Database Engine Features in SQL Server 2005
- Deprecated Features in SQL Server 2005 Integration Services
- Deprecated Features in SQL Server 2005 Replication
- Deprecated Features in SQL Server 2005 Reporting Services
- Deprecated Analysis Services Functionality in SQL Server 2005
- Notification Services Backward Compatibility
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.