Deprecated features - SQL Shack - articles about database auditing, server performance, data recovery, and more

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:

SELECT * FROM Employee, Department WHERE Employee.DepartmentID *= Department.DepartmentID;

SELECT * FROM Employee, Department WHERE Employee.DepartmentID =* Department.DepartmentID;

SELECT * FROM Employee, Department, Person WHERE Employee.DepartmentID =* Department.DepartmentID AND Employee.PersonID *= Person.ID;

SELECT * FROM Employee LEFT OUTER JOIN Departmen ON Employee.DepartmentID = Department.DepartmentID;

SELECT * FROM Employee RIGHT OUTER JOIN Departmen ON Employee.DepartmentID = Department.DepartmentID;

SELECT * FROM Employee FULL OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

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:

SELECT 'a1'=col1, 'a2' = col2 FROM FirstTable;

SELECT 'b1'=col1, col2 AS b2 FROM SecondTable;

SELECT 'c1'=col1, col2 AS 'c2' FROM ThirdTable;

SELECT 'd1'=col1, col2 AS "d2" FROM FourthTable;

SELECT 'col1' AS e1, col2 as 'c2' FROM FifthTable;

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:

SELECT * FROM FirstTable ORDER BY 'a';

SELECT * FROM SecondTable ORDER BY 'a', NULL;

SELECT * FROM ThirdTable ORDER BY 3;

SELECT * FROM FourthTable ORDER BY col1;

SELECT * FROM FifthTable ORDER BY col1, col2 DESC;

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:

SELECT * FROM FirstTable(NOLOCK);

SELECT * FROM SecondTable(NOWAIT);

SELECT * FROM ThirdTable (UPDLOCK ,PAGLOCK), SixthTable (UPDLOCK ,PAGLOCK);

SELECT * FROM SeventhTable(UPDLOCK ,PAGLOCK) INNER JOIN EightthTable (UPDLOCK ,PAGLOCK) ON SeventhTable.ID = EightthTable.RID;

SELECT * FROM FourthTable FourthTable (NOLOCK);

SELECT * FROM FifthTable FifthTable (NOLOCK, NOWAIT );

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:

CREATE TABLE [dbo].[Tbl_DepricateFirstFailedEx]

([ID] INT NOT NULL IDENTITY(0, 1),

[DATA] TEXT

);

CREATE TABLE [dbo].[ Tbl_DepricateSecondFailedEx]

([ID] INT NOT NULL IDENTITY(0, 1),

[DATA] NTEXT

);

CREATE TABLE [dbo].[ Tbl_DepricateThirdFailedEx]

([ID] INT NOT NULL IDENTITY(0, 1),

[DATA] IMAGE,

[DATA2] NTEXT,

[DATA3] TEXT

);

CREATE TABLE Tbl_DepricateFirstGoodEx

([ID] INT NOT NULL IDENTITY(0, 1),

[DATA] VARCHAR(100)

);

CREATE TABLE Tbl_DepricateSecondGoodEx

([ID] INT NOT NULL IDENTITY(0, 1),

[DATA] VARBINARY(100)

);

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:

CREATE TABLE Logins

(LoginName NVARCHAR(100) NOT NULL,

Pass NVARCHAR(50) NOT NULL

);

CREATE TRIGGER triggerResultFirstFailedEx ON Logins

FOR INSERT

SELECT *

FROM Logs;

CREATE TRIGGER triggerResultSecondFailedEx ON Logins

FOR UPDATE

SELECT *

FROM Logs;

SELECT *

FROM OldLogins;

CREATE TRIGGER triggerResultThirdFailedEx ON Logins

FOR DELETE

SELECT *

FROM Logins;

CREATE TRIGGER triggerResultThirdGoodEx ON Logins

FOR INSERT, UPDATE, DELETE

EXEC PassLog;

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:

CREATE DEFAULT df_FirstDF AS 'by-default';

CREATE DEFAULT df_SecondDF AS 25;

CREATE DEFAULT df_ThirdDF AS NULL;

DROP DEFAULT df_FourthDF;

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:

CREATE PROCEDURE proc_QUOTED_IDENTIFIERFirstFailedEx

WITH RECOMPILE

BEGIN

SET QUOTED_IDENTIFIER OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_ANSI_NULLSFailedEx

WITH RECOMPILE

BEGIN

SET ANSI_NULLS OFF;

SET ARITHABORT OFF;

SET ANSI_WARNINGS OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_QUOTED_IDENTIFIERSecondFailedEx

WITH RECOMPILE

BEGIN

SET QUOTED_IDENTIFIER OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_QUOTED_IDENTIFIERFirstGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

CREATE PROCEDURE proc_QUOTED_IDENTIFIERSecondGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

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:

DECLARE @ INT;

DECLARE @@ INT;

DECLARE @@bad INT;

DECLARE @FirstGood INT;

DECLARE @SecondGood INT;

DECLARE @ThirdGood INT;

DECLARE @FourthGood INT;

DECLARE @FifthGood INT;

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:

CREATE PROCEDURE proc_CONCAT_NULL_YIELDS_NULLFirstFailedEx

WITH RECOMPILE

BEGIN

SET ANSI_NULLS OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_CONCAT_NULL_YIELDS_NULLSecondFailedEx

WITH RECOMPILE

BEGIN

SET ARITHABORT OFF;

SET ANSI_WARNINGS OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_CONCAT_NULL_YIELDS_NULLThirdFailedEx

WITH RECOMPILE

BEGIN

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_CONCAT_NULL_YIELDS_NULLFirstGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

CREATE PROCEDURE proc_CONCAT_NULL_YIELDS_NULLSecondGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

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:

CREATE PROCEDURE proc_ANSI_PADDINGFirstFailedEx

WITH RECOMPILE

BEGIN

SET ANSI_NULLS OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

SET ANSI_PADDING OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_ANSI_PADDINGSecondFailedEx

WITH RECOMPILE

BEGIN

SET ARITHABORT OFF;

SET ANSI_WARNINGS OFF;

SET ANSI_PADDING OFF;

SET CONCAT_NULL_YIELDS_NULL OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_ANSI_PADDINGThirdFailedEx

WITH RECOMPILE

BEGIN

SET CONCAT_NULL_YIELDS_NULL OFF;

SET ANSI_PADDING OFF;

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_ANSI_PADDINGFirstGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

CREATE PROCEDURE proc_ANSI_PADDINGSecondGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

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:

SELECT * FROM FirstFailedTable as t1 WITH (READCOMMITTED READCOMMITTEDLOCK );

SELECT * FROM SecondFailedTable as t2 WITH (PAGLOCK HOLDLOCK);

SELECT * FROM FirstGoodTable as t3 WITH (READCOMMITTED, READCOMMITTEDLOCK );

SELECT * FROM SecondGoodTable as t4 WITH (PAGLOCK, HOLDLOCK);

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:

CREATE TABLE #

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE ##

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE #a

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE ##b

(ID INT,

Name VARCHAR(10)

);

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:

SET ANSI_NULLS OFF;

SET FORCEPLAN OFF;

SET REMOTE_PROC_TRANSACTIONS ON;

SET CONCAT_NULL_YIELDS_NULL OFF;

SET ANSI_PADDING OFF;

SET REMOTE_PROC_TRANSACTIONS OFF;

SET FORCEPLAN OFF;

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:

CREATE PROCEDURE proc_NumSPFirstFailedEx;1

BEGIN

DECLARE MyFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyFailedCursor;

FETCH NEXT FROM MyFailedCursor;

END;

CREATE PROCEDURE proc_NumSPSecondFailedEx;2

BEGIN

DECLARE MySecondFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MySecondFailedCursor;

FETCH NEXT FROM MySecondFailedCursor;

END;

CREATE PROCEDURE proc_NumSPThirdFailedEx;3

BEGIN

DECLARE MyThirdFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyThirdFailedCursor;

FETCH NEXT FROM MyThirdFailedCursor;

CLOSE MyThirdFailedCursor;

END;

CREATE PROCEDURE proc_NumSPFirstGoodEx

BEGIN

DECLARE MyFirstGoodCursor CURSOR LOCAL

FOR SELECT *

FROM FirstTable;

OPEN MyFirstGoodCursor;

FETCH NEXT FROM MyFirstGoodCursor;

CLOSE MyFirstGoodCursor;

DEALLOCATE MyFirstGoodCursor;

END;

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:

SELECT dbo.FirstTable.Val1 FROM dbo.FirstTable;

SELECT dbo.SecondTable.Val1 FROM dbo.SecondTable;

SELECT dbo.ThirdTable.Val1 FROM dbo.ThirdTable;

SELECT FourthTable.Val1 FROM dbo.FourthTable;

SELECT Val1 FROM dbo.FifthTable;

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:

CREATE TABLE FirstTable (ID int, Name varchar(10), timestamp);

CREATE TABLE SecondTable (ID int, Name varchar(10), timestamp);

CREATE TABLE ThirdTable (ID int, Name varchar(10), timestamp);

CREATE TABLE FourthTable (ID int, Name varchar(10), ver rowversion, CONSTRAINT pk1 PRIMARY KEY CLUSTERED (ID));

CREATE TABLE FifthTable (ID int, Name varchar(10), ver rowversion, CONSTRAINT pk2 PRIMARY KEY CLUSTERED (ID));

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:

EXEC sp_tableoption FirstTable, 'text in row', 'ON';

EXEC sp_tableoption SecondTable, 'text in row', 'OFF';

EXEC sp_tableoption ThirdTable, 'text in row', '0';

EXEC sp_tableoption FourthTable, 'large value types out of row', '0';

EXEC sp_tableoption FifthTable, 'large value types out of row', '1';

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:

CREATE TABLE FirstTable

(ID INT,

Name VARCHAR(10),

timestamp

);

CREATE TABLE SecondTable

(ID INT,

Name VARCHAR(10),

timestamp

);

CREATE RULE rule1 AS @val1 >= $0

AND @val1 < $100;

CREATE TABLE ThirdTable

(ID INT,

Name VARCHAR(10),

timestamp

);

CREATE RULE rule2 AS @value LIKE '__-%[0-9]';

CREATE TABLE FourthTable

(ID INT,

Name VARCHAR(10),

ver ROWVERSION,

CONSTRAINT pk1 PRIMARY KEY CLUSTERED(ID)

);

DROP RULE rule2;

CREATE TABLE FidthTable

(ID INT,

Name VARCHAR(10),

ver ROWVERSION,

CONSTRAINT pk2 PRIMARY KEY CLUSTERED(ID)

);

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:

EXEC sp_tableoption FirstTable, 'text in row', 'ON';

EXEC sp_tableoption SecondTable, 'text in row', 'OFF';

EXEC sp_tableoption ThirdTable, 'text in row', '0';

EXEC sp_bindrule 'rule1', SisthTable;

EXEC sp_tableoption FourthTable, 'large value types out of row', '0';

EXEC sp_tableoption FidthTable, 'large value types out of row', '1';

EXEC sp_bindrule 'rule2', SeventhTable;

EXEC sp_unbindrule 'test3';

EXEC sp_unbindrule test4;

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:

DECLARE @handle1 varbinary(64);

DECLARE @handle2 varbinary(64);

SELECT @handle1 = sql_handle FROM sys.dm_exec_requests WHERE session_id = 12;

SELECT @handle2 = sql_handle FROM sys.dm_exec_requests WHERE session_id = 13;

SELECT * FROM sys.fn_get_sql(@handle1);

SELECT * FROM sys.fn_get_sql(@handle2);

SELECT * FROM ThirdTable;

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:

EXEC sp_tableoption FirstTable, 'text in row', 'ON';

EXEC sp_tableoption SecondTable, 'text in row', 'OFF';

EXEC sp_tableoption ThirdTable, 'text in row', '0';

EXEC sp_estimated_rowsize_reduction_for_vardecimal 'test1';

EXEC sp_estimated_rowsize_reduction_for_vardecimal 'test2';

EXEC sp_tableoption FourthTable, 'large value types out of row', '0';

EXEC sp_tableoption FifthTable, 'large value types out of row', '1';

EXEC sp_estimated_rowsize_reduction_for_vardecimal 'test3';

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:

EXEC sp_tableoption FirstTable, 'text in row', 'ON';

EXEC sp_tableoption SecondTable, 'text in row', 'OFF';

EXEC sp_tableoption ThirdTable, 'text in row', '0';

EXEC sp_addtype FirstType, 'varchar(100)', 'NOT NULL';

EXEC sp_addtype SecondType, datetime, 'NULL';

EXEC sp_tableoption FourthTable, 'large value types out of row', '0';

EXEC sp_tableoption FifthTable, 'large value types out of row', '1';

EXEC sp_droptype ThirdType;

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:

SELECT Val11, Max(val2) as max2 FROM FirstTable GROUP BY Val11;

SELECT Val12, Max(val3) as max3 FROM SecondTable GROUP BY Val12;

SELECT Val13, Max(val4) as max3 FROM ThirdTable GROUP BY Val13;

SELECT Val14, Max(val4) as max4 FROM FourthTable GROUP BY ALL Val14;

SELECT Val15, Max(val5) as max5 FROM FifthTable GROUP BY ALL Val15;

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:

SELECT ROWGUIDCOL FROM FirstTable;

SELECT ROWGUIDCOL FROM SecondTable;

SELECT ROWGUIDCOL FROM ThirdTable;

SELECT $rowguid FROM FourthTable;

SELECT * FROM FifthTable;

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:

SELECT IDENTITYCOL, Col1 FROM FirstTable;

SELECT IDENTITYCOL, Col2 FROM SecondTable;

SELECT MAX(IDENTITYCOL) AS Col3 FROM ThirdTable;

SELECT MIN($IDENTITY) AS Col4 FROM FourthTable;

SELECT MIN($IDENTITY) AS Col5 FROM FifthTable;

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:

CREATE TABLE FirstTable (Id int PRIMARY KEY, Col1 varchar(10) DEFAULT 'DEFAULT' );

CREATE TABLE SecondTable (Id int PRIMARY KEY, Col1 varchar(10) DEFAULT 'DEFAULT' );

CREATE DEFAULT df_FirstDF AS 'DEFAULT';

CREATE DEFAULT df_SecondDF AS 'DEFAULT';

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:

EXEC sp_tableoption FirstTable, 'text in row', 'ON';

EXEC sp_tableoption SecondTable, 'text in row', 'OFF';

EXEC sp_tableoption ThirdTable, 'text in row', '0';

EXEC sp_addrole 'FirstRole';

EXEC sp_addrole 'SecondRole';

EXEC sp_addlogin 'FirstUser', '123qaz';

EXEC sp_addlogin 'Second User', '123qaZ';

Message:

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

Author
Recent Posts

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