Deprecated features

APX1094 – Non-ANSI outer join

Description:

This rule evaluates the T-SQL script for using Non-ANSI outer join syntax e.g. *=, =* that has been deprecated.

Use ANSI OUTER JOIN statement instead.

For more information visit

https://docs.microsoft.com/en-us/sql/sql-server/install/outer-join-operators-and-are-not-supported-in-90-or-later-compatibility-modes?view=sql-server-2014

Example script:

Message:

Statement uses Non-ANSI outer join

APX1119 – String = expression_alias

Description:

This rule evaluates the T-SQL script for using column aliasing where the name of the expression uses a string value that is deprecated in SQL Server 2005 or higher.

Use ’AS’ syntax instead.

For more information visit

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

Example script:

Message:

Deprecated feature, string for a column alias detected. Column: ‘*’

APX1125 – Constants in ORDER BY

Description:

This rule evaluates the T-SQL script for using Constants, as sort columns in the ORDER BY clause, that has been deprecated.

Use the column name instead.

For more information visit

https://www.sqlshack.com/overview-of-the-sql-order-by-clause/

Example script:

Message:

Deprecated feature, constant in ORDER BY clause, detected

APX1128 – Table hint without WITH keyword

Description:

This rule evaluates the T-SQL script for using table hints without the WITH keyword that is deprecated in SQL Server 2008 and higher.

Use WITH instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, table hints without WITH keyword, detected

APX1152 – Deprecated data types: TEXT, NTEXT and IMAGE

Description:

This rule evaluates the T-SQL script for using the TEXT, NTEXT and IMAGE data types that are deprecated in SQL Server 2008 and higher.

Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated datatype, ’*’, detected

APX1182 – Returning results from triggers

Description:

This rule evaluates the T-SQL script for returning results from the trigger that is deprecated in SQL Server 2008 and higher.

Call a stored procedure to return results instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, returned result from trigger, detected

APX1236 – Use of CREATE/DROP DEFAULT

Description:

This rule evaluates the T-SQL script for using CREATE/DROP DEFAULT that is deprecated in SQL Server 2008 and higher.

Use the DEFAULT keyword in CREATE/ALTER TABLE instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated, CREATE/DROP DEFAULT statement, detected

APX1237 – ANSI_NULLS or QUOTED_IDENTIFIER OFF

Description:

This rule evaluates the T-SQL script for using SET ANSI_NULLS or QUOTED_IDENTIFIER OFF that is deprecated in SQL Server 2008 and higher.

It is considered a best practice to have ANSI_NULLS and/or QUOTED_IDENTIFIER options set to ON.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, ANSI_NULLS OFF or QUOTED_IDENTIFIER OFF, detected

APX1242 – Use of @, @@, or @@ as Transact-SQL identifiers

Description:

This rule evaluates the T-SQL script for using @ or @@ or names that begin with @@ that are deprecated in SQL Server 2008 and higher.

Do not use @ or @@ or names that begin with @@ as identifiers.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, @ character, detected in an object name

APX1270 – SET CONCAT_NULL_YIELDS_NULL OFF

Description:

This rule evaluates the T-SQL script for using CONCAT_NULL_YIELDS_NULL that is deprecated in SQL Server 2008 and higher.

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error.

SET OFFSETS will be unavailable.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, CONCAT_NULL_YIELDS_NULL OFF, detected

APX1271 – SET ANSI_PADDING OFF

Description:

This rule evaluates the T-SQL script for using SET ANSI_PADDING OFF that is deprecated in SQL Server 2008 and higher.

SET ANSI_PADDING will always be set to ON.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, ANSI_PADDING OFF, detected

APX1272 – Space separator for table hints

Description:

This rule evaluates the T-SQL script for using spaces as separators for table hints.

Do not use spaces as separators for the table hints.

Use comma as separators instead.

For more information visit

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

Example script:

Message:

Deprecated feature, table hints, detected

APX1275 – Space separator for table hints

Description:

This rule evaluates the T-SQL script for using #, ## that is deprecated in SQL Server 2008 and higher.

Use at least one additional character instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecate feature, object uses only # character in name, detected

APX1282 – SET REMOTE_PROC_TRANSACTIONS

Description:

This rule evaluates the T-SQL script for using REMOTE_PROC_TRANSACTIONS that is deprecated in SQL Server 2008 and higher.

Use distributed queries that reference linked servers instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, REMOTE_PROC_TRANSACTIONS, detected

APX1283 – Numbered procedures

Description:

This rule evaluates the T-SQL script for using Numbered stored procedures that are deprecated in SQL Server 2008 and higher.

Do not use the numbered procedures. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, numbered procedures, detected

APX1289 – Three-part and four-part column references

Description:

This rule evaluates the T-SQL script for using Columns with more than two-part names that are deprecated in SQL Server 2008 and higher.

Use the Two-part names instead as it is the standard-compliant behavior.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, column with more then two part names, detected

APX1291 – Timestamp syntax

Description:

This rule evaluates the T-SQL script for using TImestamp syntax that is deprecated in SQL Server 2008 and higher.

Use rowversion data type syntax instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, Timestamp syntax, detected

APX1292 – Table option “text in row”

Description:

This rule evaluates the T-SQL script for using Table option “text in row” that is deprecated in SQL Server 2008 and higher.

Use varchar(max), nvarchar(max), and varbinary(max) data types instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms173530(v=sql.100).aspx

Example script:

Message:

Deprecate feature, ‘text in row’, detected

APX1293 – CREATE/DROP RULE

Description:

This rule evaluates the T-SQL script for using CREATE/DROP RULE that is deprecated in SQL Server 2008 and higher.

Use DEFAULT keyword in CREATE TABLE and ALTER TABLE instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, CREATE/DROP RULE, detected

APX1294 – sp_bindrule and sp_unbindrule usage

Description:

This rule evaluates the T-SQL script for using the sp_bindrule and sp_unbindrule stored procedures that are deprecated in SQL Server 2008 and higher.

Use Unique Constraints and Check Constraints instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated stored procedure, sp_bindrule, detected

APX1295 – fn_get_sql function usage

Description:

This rule evaluates the T-SQL script for using fn_get_sql that is deprecated in SQL Server 2008 and higher.

Use sys.dm_exec_sql_text instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated feature, fn_get_sql, detected

APX1296 – sp_estimated_rowsize_reduction_for_vardecimal usage

Description:

This rule evaluates the T-SQL script for using the sp_estimated_rowsize_reduction_for_vardecimal procedure that is deprecated in SQL Server 2008 and higher.

Use sp_estimate_data_compression_savings procedure instead.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimated-rowsize-reduction-for-vardecimal-transact-sql?view=sql-server-2017

and

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-2017

Example script:

Message:

Deprecated feature, sp_estimated_rowsize_reduction_for_vardecimal , detected

APX1297 – data types sp_addtype and sp_droptype usage

Description:

This rule evaluates the T-SQL script for using the sp_addtype and sp_droptype stored procedures that are deprecated in SQL Server 2008 and higher.

Use CREATE TYPE and DROP TYPE instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated data type, sp_addtype, detected

APX1298 – GROUP BY ALL clause usage

Description:

This rule evaluates the T-SQL script for using GROUP BY ALL clause that is deprecated in SQL Server 2008 and higher.

Use custom case-by-case solution with UNION or derived table instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated, GROUP BY ALL clause, detected

APX1299 – ROWGUIDCOL as a column name in DML statements

Description:

This rule evaluates the T-SQL script for using ROWGUIDCOL as a column name in DML statements that is deprecated in SQL Server 2008 and higher.

Use $rowguid instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated features, ROWGUIDCOL, detected

APX1300 – IDENTITYCOL as a column name in DML statements

Description:

This rule evaluates the T-SQL script for using IDENTITYCOL as a column name in DML statement that is deprecated in SQL Server 2008 and higher.

Use $identity instead.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated features, IDENTITYCOL, detected

APX1301 – DEFAULT keyword usage as default value

Description:

This rule evaluates the T-SQL script for using DEFAULT keyword as a default value that is deprecated in SQL Server 2008 and higher.

Do not use the word DEFAULT as a default value.

For more information visit

https://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Example script:

Message:

Deprecated use of, DEFAULT keyword as DEFAULT value, detected

APX1302 – Deprecated stored procedure

Description:

This rule evaluates the T-SQL script for using deprecated procedures.

Using deprecated stored procedures that are more likely to change, may lead to unpredictable results and they are unsupported if problems do arise.

Do not use deprecated stored procedures.

For more information visit

https://docs.microsoft.com/en-us/sql/sql-server/install/remove-references-to-deprecated-system-stored-procedures?view=sql-server-2014

Example script:

Message:

Use of a deprecated procedure, EXEC sp_addlogin ‘*’, ‘123qaZ’;, is detected

SQLShack

SQLShack

SQL Shack was created as a community service from ApexSQL, with the goal to share SQL Server knowledge through articles written by SQL Server professionals and community members
SQLShack

Latest posts by SQLShack (see all)