Code Analysis

Deprecated syntax rules

T-SQL keywords or syntax discontinued by Microsoft.

DEP001 – Table hint without WITH keyword 

The WITH keyword before a table hint is missing.

Learn more about this rule

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. 

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule 

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. 

Learn more about this rule

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.

Learn more about this rule

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. 

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

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). 

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

DEP015 – READONLY and READWRITE options are deprecated 

READONLY and READWRITE options are no longer supported.

Use the READ_ONLY and READ_WRITE options instead.

Learn more about this rule

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.

Learn more about this rule

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. 

Learn more about this rule

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!

Learn more about this rule

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.

Learn more about this rule

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. 

Learn more about this rule

DEP021 – String literals as column aliases are deprecated 

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

Learn more about this rule

DEP022 – DROP INDEX with two-part name is deprecated 

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule

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.

Learn more about this rule


Didn't find what you were looking for?