Deprecated syntax rules
Published 16 November 2017
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 anymore.
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 sets 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 to control.
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
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. 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.
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.
