T-SQL keywords or syntax discontinued by Microsoft.

DEP001 – Table hint without WITH keyword

The WITH keyword before a table hint is missing.

DEP002 – WRITETEXT, UPDATETEXT and READTEXT statements are deprecated

There is no good reason for using the TEXT datatype any more.

These functions are used in support of the TEXT datatype, which  was replaced in SQL Server 2005 by the far more versatile VARCHAR(MAX). This is compatible with string functions, and is actively supported. 

DEP003 – GROUP BY ALL clause is deprecated.

GROUP BY ALL clause is not supported for remote tables and is no longer actively maintained.

Use a custom case-by-case solution with UNION or a derived table. The results you get are more intuitive: it is easier to know when a zero category will appear or not.

DEP004 – COMPUTE and COMPUTE BY clauses are deprecated

COMPUTE and COMPUTE BY are archaic ways of doing printed reports. They do not produce conventional results.

Use GROUPING, ROLLUP or CUBE instead, to produce results that can be stored for further processing or aggregation.

DEP005 – FASTFIRSTROW table hint is deprecated

FASTFIRSTROW table hint is old syntax now replaced with the better OPTION (FAST 1).

Use [FAST n] instead. FASTFIRSTROW  allows data to begin streaming to the user earlier, at a risk of a cost in performance.  OPTION (FAST 1) is equivalent but you can, with this hint, specify the number of ‘fast’ rows. 

DEP006 – SETUSER statement is deprecated

SETUSER statement is there only for backward-compatibility.

Since SQL Server 2008, it has been possible to use EXECUTE AS, which is much clearer.

DEP007 – TAPE as backup device is deprecated

Support for tape backup devices will eventually be removed.

It is unusual to use a tape backup as these devices  are comparatively expensive and unreliable. If code has a TAPE dumpdevice defined, it is probably legacy code that must be removed. 

DEP008 – PASSWORD/MEDIAPASSWORD options in BACKUP/RESTORE statement are deprecated

Since SQL Server 2012, the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups.

It is important to password-protect backups. However, the protection that this option afforded was not up to current standards. Properly set-up ACLs on the directory provides stronger protection, as does a third-party backup solution.

DEP009 – DBCC DBREINDEX statement is deprecated

The use of DBCC DBREINDEX is no longer recommended.

Use ALTER INDEX REBUILD instead. It has many more options, and supports current types of index.

DEP010 – DBCC CONCURRENCYVIOLATION is deprecated

An attempt to execute this command returns error 2526.

No recent version of edition of SQL Server includes a workload governor; therefore the command has been removed.

DEP011 – DBCC INDEXDEFRAG is deprecated

DBCC INDEXDEFRAG is deprecated.

ALTER INDEX REORGANIZE can perform index defragmentation without taking the database offline, allowing users to access the tables being defragmented (enterprise edition only). 

DEP012 – DBCC SHOWCONTIG is deprecated

DBCC SHOWCONTIG is no longer the supported way of investigating index fragmentation.

DBCC SHOWCONTIG only displays the first partition of the specified table or index and does not display row-overflow storage information or spatial indexes. It does not show  off-row data types, such as NVARCHAR(MAX), VARCHAR(MAX), VARBINARY(MAX), and XML.

Use sys.dm_db_index_physical_stats instead.

DEP013 – Deprecated SET options

These SET options are deprecated: ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, REMOTE_PROC_TRANSACTIONS, OFFSETS.

It is a bad idea to set ANSI_NULLS OFF or ANSI_PADDING OFF since this is not supported for certain indexes such as indexed views and indexes on computed columns. CONCAT_NULL_YIELDS_NULL OFF causes expressions that assume this behaviour to give strange results. REMOTE_PROC_TRANSACTIONS should not be used because remote stored procedures are deprecated so, instead of issuing remote stored procedure calls, use distributed queries that reference linked servers. The OFFSETS options will soon be unavailable.

DEP014 – SET ROWCOUNT option is deprecated

SET ROWCOUNT option was replaced by the TOP(N) syntax of the SELECT statement.

SET ROWCOUNT set the limit to the number of rows returned for the rest of the batch, whereas TOP(n) within the SELECT statement sets the number of rows to return by the query. The latter is much safer and easier.

DEP015 – READONLY and READWRITE options are deprecated

READONLY and READWRITE options are no longer supported.

Use the READ_ONLY and READ_WRITE options instead.

DEP016 – TORN_PAGE_DETECTION option is deprecated

The TORN_PAGE_DETECTION option only checks the first 2 bytes in every 512-byte sector.

The only worthwhile page verification option is CHECKSUM.

DEP017 – NON-ANSI join (*= or =*) is used

Non-ANSI joins (via *= or =*) are deprecated – use LEFT/RIGHT OUTER join syntax instead.

The *= or *= syntax is maintained only for backward compatibility with legacy systems and is rightly incomprehensible to most SQL developers. 

DEP018 – ALL option in GRANT/DENY/REVOKE statement is deprecated

The  ALL keyword is highly confusing as it doesn’t add all permissions. It is legacy code that hasn’t kept pace with new versions of SQL Server. Don’t use it!

DEP019 – System table or view is deprecated

The whole system of metadata views was found to be inadequate, and was replaced in SQL Server 2005.  They are maintained purely for legacy code. There is no good reason to ever use them, and plenty of reasons to avoid them. They have not been updated to reflect the current system.

DEP020 – Numbered procedures are deprecated

Numbered procedures are deprecated – do not use numbered procedures.

This was always a bad idea, that allowed you to overload procedures by creating more than one version of a stored procedure, and which allow you to delete them in one command. It never worked well. 

DEP021 – String literals as column aliases are deprecated

Aliases should always be valid identifiers. String aliases are not part of the SQL standard.

DEP022 – DROP INDEX with two-part name is deprecated

DEP025 – System stored procedure is deprecated

There is never a good reason to use undocumented system stored procedures and even some documented ones, such as sp_addalias, sp_addgroup, sp_changegroup, sp_dropgroup and sp_helpgroup have been replaced with better ways of doing it.

DEP026 – Three-part and four-part column references in SELECT list are deprecated

Three-part and four-part column references in SELECT list are deprecated – two-part names is the standard-compliant behavior.

Sometimes, when a table is referenced in another database or server, programmers believe that the two or three-part table name needs to be applied to the columns. This is unnecessary and meaningless. Just the table name is required for the columns.

DEP027 – System function is deprecated

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

DEP028 – The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF.