Deprecated Commands in SQL Server 2005

By:   |   Comments (3)   |   Related: > Deprecated Features


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?


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





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
  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
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






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


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


Rebuild or reorganize indexes Leverage the ALTER INDEX syntax
4 sp_attach_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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 16, 2007 - 6:08:29 PM - DrDianaDee Back To Top (41)
Thank you.
Tracing back through SQL 2000 Books Online, I find that SQL 7.0 had roles, not groups.  So groups must have existed in SQL 6.5 and earlier, without roles.  As of SQL 7.0, sp_addgroup was maintained for backward compatibility.

Tuesday, October 16, 2007 - 3:33:54 PM - admin Back To Top (40)


To the best of my knowledge, groups existed in SQL Server 7.0 and SQL Server 2000.

Here is one reference to groups in SQL Server 2005 Books Online:

I believe if you search SQL Server 2005 or 2000 Books Online further you will find additional references.

Thank you,

The Team

Tuesday, October 16, 2007 - 1:21:13 PM - DrDianaDee Back To Top (39)

I am a Microsoft Certified Trainer and have seen "groups" referred to in the Microsoft Official Curriculum for SQL Server 2005.  However, I have not been able to find a reference to groups in Books Online.  Roles seem to be the only item mentioned. 

Where can I find a reference to groups?  What version of SQL Server had groups? 


Diana Dee

get free sql tips
agree to terms