T-SQL Tuesday
@banerjeeamit is the host this month for the blog party known as #tsql2sday in Twitterverse. Please click on the T-SQL TUESDAY image to jump to his blog TroublshootingSQL and read about the blog party history as well as the T-SQL topic. Be sure to either subscribe to his blog or return later to read the roundup of all the blog party posts. Also for a roundup of the roundups read A Year of Tuesdays: T-SQL Tuesday Meta-Roundup written by @adammachanic
Option-B
In line with T-SQL topic option B, this post is about an issue resolved because the T-SQL did not follow a best practice. The best practice was fully qualifying object names and more specifically, the qualifying of a column name. There was an attempt in the T-SQL to qualify the column name but used a table prefix to a column alias which is a no-no. Somehow the T-SQL still compiled and was saved as a stored procedure. Surprised? I was, so if you want to double check the facts then read the post Why can’t we have column alias in ORDER BY? written by @TiborKaraszi.
Switch
After contemplating the potential ramifications of posting the original code, self-preservation convinced me to switch it out for this mockup code:
SELECT dbo.table_demo.column_date, DATEDIFF(d, dbo.table_demo.column_date, ‘2000-12-31’) AS column_day
FROM dbo.table_demo
ORDER BY dbo.table_demo.column_day ASC; /* no column exists named column_day in table dbo.table_demo */
Bug
The problem arose when the database containing the stored procedure was changed from database compatibility level 80 to 100. There is a bug in SQL Server 2000 which has carried over into SQL Server 2008 R2 via the database compatibility level 80. Basically, the query optimizer ignores all name prefixes of a multiple part column name in the ORDER BY. For the devout skeptics out there, you can triple check the facts in SQL Server Books Online article ALTER DATABASE Compatibility Level (Transact-SQL) under column Compatibility-level setting of 80 in the third row of the first table.
Finger-pointing
Although the developer incorrectly qualified the column alias by prefixing it with a table, the finger should be pointed at Microsoft. Instead of saving the stored procedure the engine should have thrown Error MSSQLSERVER_207 (Invalid column name ‘%.*ls’.) since the column alias name was prefixed with a table which did not have a column of that same name. If the column alias name had also been the name of an existing column then the stored procedure would have saved in a database with any compatibility level. The difference is that in a database with compatibility level higher than 80 the actual table column would have been used in the ORDER BY where as in a database with compatiblity level 80 the aliased column would have been used in the ORDER BY. The good news is that this should all be behind us because database compatibility level 80 was one of the Deprecated Database Engine Features in SQL Server 2008 R2.
Two
There were two best practices mentioned in this post. The first is the well-known: Fully qualify database objects; Chapter 14 — Improving SQL Server Performance; Worst Practice – Not Qualifying Objects With The Owner written by @chrishedgate; Performance Impact of Procedure Calls without Owner Qualification — SQL Server 2000 written by @linchi_shea; Owner/schema qualifying object names written by @TiborKaraszi; Defensive database programming: qualifying column names. written by @AlexK_v. The second is my suggestion: Avoid alias names that are names of existing objects. Whether it is a server alias, table alias or column alias, using the name of an existing object adds unnecessary and avoidable confusion to the already difficult task of troubleshooting.
People
Amit Banerjee
Adam Machanic
Chris Hedgate
Linchi Shea
Tibor Karaszi
Alexander Kuznetsov
Script
/*
******************************************************
********** Script to demonstrate difference **********
********** between 80 and 100 compatibility **********
********** levels for ORDER BY column alias **********
********** works best using results to text **********
******************************************************
*/
/*
***********************************************
********* Setup database environment **********
***********************************************
*/
CREATE DATABASE [deleteme] ON
PRIMARY
( NAME = N’deleteme’, FILENAME = N’C:\Windows\Temp\deleteme.mdf’ , SIZE = 10240KB , FILEGROWTH = 10240KB )
LOG ON
( NAME = N’deleteme_log’, FILENAME = N’C:\Windows\Temp\deleteme.ldf’ , SIZE = 10240KB , FILEGROWTH = 10240KB );
GO
USE [deleteme];
GO
SET NOCOUNT ON;
GO
CREATE TABLE dbo.table_demo(column_date smalldatetime PRIMARY KEY CLUSTERED);
GO
INSERT INTO dbo.table_demo(column_date )
SELECT CONVERT(smalldatetime, ‘2000-04-04’) AS column_date
UNION ALL SELECT ‘2000-02-02’
UNION ALL SELECT ‘2000-01-01’
UNION ALL SELECT ‘2000-03-03’;
GO
ALTER DATABASE [deleteme] SET COMPATIBILITY_LEVEL = 80;
GO
/*
***********************************************
********* Four queries as test cases **********
***********************************************
*/
CREATE PROCEDURE dbo.invalid_column_name AS
PRINT ‘USING INVALID COLUMN NAME SHOULD NOT EXECUTE’;
EXEC (‘SELECT dbo.table_demo.column_date, DATEDIFF(d, dbo.table_demo.column_date, ”2000-12-31”) AS column_day FROM dbo.table_demo ORDER BY dbo.table_demo.column_day ASC;’);
GO
CREATE PROCEDURE dbo.unbindable_multipart_identifier AS
PRINT‘USING UNBINDABLE MULTIPART IDENTIFIER SHOULD NOT EXECUTE’;
EXEC (‘SELECT dbo.table_demo.column_date, DATEDIFF(d, dbo.table_demo.column_date, ”2000-12-31”) AS column_day FROM dbo.table_demo ORDER BY FAKE_TABLE_NAME.column_day ASC;’);
GO
CREATE PROCEDURE dbo.ambigous_column_name AS
/* create a column with the same name as column alias */
ALTER TABLE dbo.table_demo ADD column_day AS DAY(column_date);
PRINT ‘USING AMBIGUOUS COLUMN NAME SHOULD NOT EXECUTE’;
EXEC (‘SELECT dbo.table_demo.column_date, DATEDIFF(d, dbo.table_demo.column_date, ”2000-12-31”) AS column_day, dbo.table_demo.column_day FROM dbo.table_demo ORDER BY column_day ASC;’);
/* remove column with the same name as column alias */
ALTER TABLE dbo.table_demo DROP COLUMN column_day;
GO
CREATE PROCEDURE dbo.table_column_sort AS
/* create a column with the same name as column alias */
ALTER TABLE dbo.table_demo ADD column_day AS DAY(column_date);
PRINT ‘USING VALID COLUMN NAME SHOULD EXECUTE AND SORT ASCENDING BY TABLE COLUMN (day of month) NOT ALIAS COLUMN (days left in year)’;
EXEC (‘SELECT dbo.table_demo.column_date, DATEDIFF(d, dbo.table_demo.column_date, ”2000-12-31”) AS column_day FROM dbo.table_demo ORDER BY dbo.table_demo.column_day ASC;’);
/* remove column with the same name as column alias */
ALTER TABLE dbo.table_demo DROP COLUMN column_day;
GO
/*
***********************************************
********* Exec individual test cases **********
***********************************************
*/
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();
GO
EXEC dbo.invalid_column_name;
GO
EXEC dbo.unbindable_multipart_identifier;
GO
EXEC dbo.ambigous_column_name;
GO
EXEC dbo.table_column_sort;
GO
ALTER DATABASE [deleteme] SET COMPATIBILITY_LEVEL = 100;
GO
USE [deleteme];
GO
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();
GO
EXEC dbo.invalid_column_name;
GO
EXEC dbo.unbindable_multipart_identifier;
GO
EXEC dbo.ambigous_column_name;
GO
EXEC dbo.table_column_sort;
GO
/*
***********************************************
********* Clean database environment **********
***********************************************
*/
DROP TABLE dbo.table_demo;
GO
USE [master];
GO
ALTER DATABASE [deleteme] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [deleteme];
GO
sqlmashup 