Deprecated syntax rules
Published 16 November 2017
T-SQL keywords or syntax discontinued by Microsoft.
The WITH keyword before a table hint is missing.
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.
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.
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.
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.
SETUSER statement is there only for backward-compatibility.
Since SQL Server 2008, it has been possible to use EXECUTE AS, which is much clearer.
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.
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.
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.
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.
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).
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.
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.
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.
READONLY and READWRITE options are no longer supported.
Use the READ_ONLY and READ_WRITE options instead.
The TORN_PAGE_DETECTION option only checks the first 2 bytes in every 512-byte sector.
The only worthwhile page verification option is CHECKSUM.
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.
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!
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.
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.
Aliases should always be valid identifiers. String aliases are not part of the SQL standard.
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.
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.
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.
The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF.