Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Deprecated Commands in SQL Server 2005


By:   |   Read Comments (3)   |   Related Tips: More > Deprecated Features

Attend these FREE MSSQLTips webcasts >> click to register


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
  • 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

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

 



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 16, 2007 - 6:08:29 PM - DrDianaDee Back To Top
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.
 
):-D

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

Diana,

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 MSSQLTips.com Team


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

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? 

Thanks.

Diana Dee


Learn more about SQL Server tools