SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : T-SQL, Surprise

What if null if null is null null null is null?

Hugo Kornelis — Sun, 30 Sep 2007 20:19:30 GMT

In this fourth and final part in my series about NULL, I’ll discuss some well-known and some less well-known functions and keywords that are specifically created to deal with NULL values. And I will, of course, explain why null if null is null null null is null. In case you have not yet read them, you can click these links to read the first, second, and third part.

IS NULL is not = NULL

I have already explained why tests for equality with NULL will always return Unknown instead of True or False. This holds true in all circumstances – even when both sides of the equation are NULL. That’s why the query below will not help you find the people for which no birthday is on file:

SELECT FirstName, LastName

FROM dbo.Persons

WHERE Birthday = NULL;

Even though no birthday is on file for Hugo Kornelis, my name will not be returned – because when evaluating “my” row, SQL Server will see a comparison between a missing value (my birthday) and a missing value (the NULL) – and when asked whether two unspecified values are equal, the only guaranteed correct answer is “I ain’t the faintest, dude” (for the British readers, that would be “I haven’t got the foggiest idea, old chap”).

Since there are numerous occasions where you want to find rows where values are missing, a special operator was introduced for this: IS NULL. Rewriting the query above as follows will return my name, because my Birthday is indeed NULL in this database.

SELECT FirstName, LastName

FROM dbo.Persons

WHERE Birthday IS NULL;

Note that the operator IS NULL will return True if the operand (Birthday in this case) is NULL, or False if it is any other value – an IS NULL test can therefore never result in Unknown. So to find values that are not missing, the expression can simply be negated to

WHERE NOT(Birthday IS NULL);

but there is also a shorthand form available that is much more common – in fact so much more that I don’t think I’ve ever seen the above version used!

WHERE Birthday IS NOT NULL;

Do not forget that the NULL keyword is an integral part of the IS [NOT] NULL operator. You can’t use IS or IS NOT as a replacement for = and <> and expect the same special treatment of NULL values that IS [NOT] NULL provides. That would require the use of the IS [NOT] DISTINCT FROM operator that is specified in SQL:1999 but not yet implemented in SQL Server (vote here if you’d like to see this changed!)

SET ANSI_NULLS { ON | OFF }

Some of you may have seen existing code where a predicate such as Column = NULL is used, and does actually return rows. This is probably legacy code that has not been maintained in a long time (and if it isn’t, it has to be written by a legacy developer who has not maintained his knowledge in a long time).

Very early versions of SQL Server were released before agreement was reached in the ANSI committee on all features. As a result, some features were implemented in a different way than what the ANSI committee turned out to describe, facing Microsoft developers with the challenge to upgrade to ANSI compliant behaviour without breaking existing code.

Behaviour of NULL comparisons in predicates is one such example where the original version of SQL Server “got it wrong”. So when SQL Server was changed to return Unknown on a comparison with NULL, the SET ANSI_NULL OFF option was introduced to force the “old” behaviour that would return False on NULL vs. non-NULL comparison, and True on NULL vs. NULL comparison.

The SET ANSI_NULL option has been marked as deprecated in SQL Server 2005, so if you find code that still relies on this setting, you’d better change it ASAP. This would of course be very easy if you only had to look for “= NULL” and “<> NULL”, and change them to “IS NULL” and “IS NOT NULL” – but unfortunately, expressions of the form “= expression” where expression can be NULL are also affected by the ANSI_NULL setting; these are much harder to identify or fix!

ISNULL is not COALESCE

Despite the name similarity, and despite the fact that in Access, ISNULL(xxx) is equivalent to xxx IS NULL, the T-SQL ISNULL function is completely different from the IS NULL predicate. ISNULL is used to return the first non-NULL from its two inputs. However, ISNULL is also a leftover from the days before the ANSI standard was finalised – the ANSI standard function that should be used to replace ISNULL is called COALESCE. Despite the similarities, there are also a couple of differences that you should be aware of before replacing all your occurrences of ISNULL to COALESCE.

One reason to prefer COALESCE of ISNULL, in addition to its adherence to standards, is that COALESCE can take any number of arguments whereas ISNULL only takes two. Both will return the first non-NULL argument, or NULL if all arguments are NULL. The unlimited number of arguments makes COALESCE a far better option when tasked to find the first non-NULL value from more than two inputs – for example, to find the first non-NULL of Arg1, Arg2, Arg3, and Arg4, I’d rather use

COALESCE(Arg1,Arg2,Arg3,Arg4)

than

ISNULL(Arg1,ISNULL(Arg2,ISNULL(Arg3,Arg4)))

Another major problem with ISNULL is how it deals with implicit conversions. COALESCE will, like any other T-SQL operation, use the rules of data type precedence to find the data type of its result – but ISNULL will always return a value of the data type of its first argument, making this the only T-SQL keyword that does not respect the precedence rules.

Unfortunately, there is one situation where you can not simply rip out ISNULL and replace it with COALESCE – and that is in the case of computed columns. If you attempt to create the tables below, you will see that Test_ISNULL is created without problems, whereas the attempt to add the index to Test_COALESCE fails.

CREATE TABLE Test_ISNULL

(Col1 int NOT NULL,

Col2 int NULL,

Col3 AS ISNULL(Col2, Col1) PRIMARY KEY);

CREATE TABLE Test_COALESCE

(Col1 int NOT NULL,

Col2 int NULL,

Col3 AS COALESCE(Col2, Col1) PRIMARY KEY);

I consider this to be a bug. Clearly, a computed column using COALESCE should inherit its nullability from the last argument, just as is the case with ISNULL. Please vote on Connect if you agree with me that this bug should be fixed, as a first step towards deprecating and removing the superfluous and confusing ISNULL function.

NULLIF, the forgotten one

The last NULL related keyword that I will cover here is also the least well-known (although some strange coincidence cause Jeff Smith to blog about it earlier this week). That may be due to the fact that it is only useful in a limited number of cases, and also not as easy to understand as the other ones.

The NULLIF function takes two arguments. It returns NULL if the first argument is equal to the second one – in all other cases, the first argument is returned unchanged. According to the SQL standards, NULLIF is actually a shorthand form for a CASE expression:

NULLIF(Arg1, Arg2)

is defined as equivalent to

CASE WHEN Arg1 = Arg2 THEN NULL ELSE Arg1 END

The most common use of NULLIF is to prevent runtime errors such as division by zero. For instance, the query below will fail if there are any rows with (Col1 + Col2) equal to zero:

SELECT IdCol, Col1 / (Col1 + Col2) AS Col1Ratio

FROM dbo.SampleTable;

One way to attempt to prevent this error is to exclude these rows:

SELECT IdCol, Col1 / (Col1 + Col2) AS Col1Ratio

FROM dbo.SampleTable

WHERE Col1 + Col2 <> 0;

However, this might fail because SQL Server might use an execution plan that calculates Col1Ratio before applying the WHERE clause – that is not a bug, the SQL standards explicitly allow the vendors such freedoms in the implementation of their products. And if other columns are displayed as well, completely omitting the rows is not a good way to tackle this problem.

This is where NULLIF comes to the rescue. In the query below, the divisor will be changed to NULL if it was 0, setting the result of the entire calculation to NULL instead of causing a runtime error.

SELECT IdCol, Col1 / NULLIF(Col1 + Col2, 0) AS Col1Ratio

FROM dbo.SampleTable;

If so desired, a COALESCE function can be added to change this NULL result back to a chosen numeric value – or it can be left as NULL to make it instantly visible that for this row, a value of Col1Ratio could not be computed.

Another problem area where NULLIF can be used is to check if values are distinct. I have already explained the difference between unequal and distinct in the third part of this series, and I have also presented this method for testing for distinctness:

WHERE OldValue <> NewValue

OR (OldValue IS NULL AND NewValue IS NOT NULL)

OR (OldValue IS NOT NULL AND NewValue IS NULL);

This is of course rather clumsy; many developers prefer to replace this with a shorter expression. That is very easy to do if there is some magic value that OldValue and NewValue can never be equal to – for instance, if both are always above 0, you could replace the three lines above with this single line:

WHERE COALESCE(OldValue, -1) <> COALESCE(NewValue, -1)

But what if any value can occur in the domain? In that case, you can use NULLIF to shorten the expression a bit:

WHERE NULLIF(OldValue, NewValue) IS NOT NULL

OR NULLIF(NewValue, OldValue) IS NOT NULL

Remember that NULLIF returns NULL if both arguments are equal, but also if the first argument is NULL. So for the expression above, both NULLIF expressions will be NULL if either NewValue is equal to OldValue, or both are NULL. If one of the two is NULL and the other is not, only one of the NULLIF expressions will be NULL. If neither is NULL and they are not equal, both NULLIF expression will be NOT NULL.

Do remember that neither of the three distinction tests presented above will allow the use of an index seek strategy. Until Microsoft implement IS [NOT] DISTINCT FROM, we’ll have to live with not being able to use an index seek for this.

What if null if null is null null null is null?

Now, I can finally answer this question – I only have to remove some spaces and add some commas and parentheses to change it to valid SQL Server syntax:

IF NULLIF(NULL, ISNULL(NULL, NULL)) IS NULL

Note that this is not a valid statement, as the IF statement misses its statement block. Don’t add it yet – first try to predict the results without running the code.

The result of ISNULL(NULL, NULL) should of course be NULL, since both arguments are NULL. That simplifies the IF statement to:

IF NULLIF(NULL, NULL) IS NULL

The result of NULLIF(NULL, NULL) should be NULL. Not because the two operands are equal (they are not – remember, NULL = NULL evaluates to Unknown!), but because in case of unequal operands, the first argument is returned. So now we have:

IF NULL IS NULL

which will of course evaluate to True.

Now complete the IF statement in the original query and run it to check our prediction:

IF NULLIF(NULL, ISNULL(NULL, NULL)) IS NULL

PRINT 'Prediction was correct';

ELSE

PRINT 'I goofed...';

Msg 8133, Level 16, State 1, Line 1

None of the result expressions in a CASE specification can be NULL.

So I didn’t predict the result correctly, nor did I goof … instead, I discovered an interesting caveat with NULLIF – or rather, with the CASE expression that this NULLIF expression expands to:

CASE WHEN NULL = NULL THEN NULL ELSE NULL END

The problem here, is that both the THEN (or rather, each THEN, as a CASE expression allows as many WHEN … THEN clauses as you wish) and the ELSE clause return the constant expression NULL. That this is the problem is easily verified:

SELECT CASE WHEN 1 = 2 THEN NULL ELSE NULL END

Msg 8133, Level 16, State 1, Line 1

None of the result expressions in a CASE specification can be NULL.

This error message is misleading. Result expressions of a CASE specification are allowed to be NULL; I’ve used that many times. The real reason that this particular query errors is related to how SQL Server determines the data type for a constant in the query. A numeric constant, like 42 or 3.14159265, is considered to be integer or numeric(9,8); a constant enclosed in quotes like ‘this’ is considered to be varchar(4). But since any data type allows the NULL “value”, the constant NULL can be of any data type and SQL Server has no way of guessing what you mean. This is usually solved by checking what data types are used around it. So in the expression

CASE WHEN 3 = NULL THEN NULL ELSE '3' END

the first NULL is considered to be of data type integer (as it is compared to the constant 3, which is integer as well), and the second NULL is considered to be varchar(1) (as both this NULL and the constant ‘3’ are possible results of the CASE expression).

Back to the error query – if all possible result expressions of the CASE expression are the constant NULL, SQL Server has no way of working out the data type of the result expression, and that is indeed an error – so SQL Server did the right thing in returning an error, it just chose a bad message! Interestingly, even though COALESCE is also defined as shorthand for a CASE expression, there is a specific error message for COALESCE(NULL,NULL) in SQL Server 2005 (maybe as a result of this connect entry?) – but instead of fixing the real problem, Microsoft chose to fix only the specific COALESCE case. I have of course filed a bug report on Connect for this misleading error message.

Anyway, now that I know the cause of the error message, I can also fix it, and run the code below to finally check if null if null is null null null is null:

-- Please, NEVER use this as a variable name in real code....

DECLARE @NULL int;

-- This superfluous assignment makes the code self-documenting.

SET @NULL = NULL;

-- Now check if null if null is null null null is null:

IF NULLIF(@NULL, ISNULL(@NULL, @NULL)) IS NULL

PRINT 'Prediction was correct';

ELSE

PRINT 'I goofed...';

Prediction was correct

The Beatles versus the Stones - the explanation

Hugo Kornelis — Tue, 16 Jan 2007 20:44:00 GMT

On December 31 of last year, I posted this brain teaser, promising to post the answer “in a few days”. Apparently, 15 is a few J.

In case you have forgotten what the puzzle was about and are too lazy to click the link above, the bottom line is that I created and populated two tables, with the same schema but different content. One held the first and last name of each of the Beatles, the other held first and last name of each of the Rolling Stones. I then queried both tables, using the same query in both cases. The query did not include an ORDER BY clause. It turned out that the results from the Beatles table were always returned in order of the clustered index, whereas the results from the Stones table was unordered. And that was the question I left you with – how can two tables that have the same schema show so different behavior?

The answer to this is that, of course, the two tables don’t display different behavior at all. In fact, they behave exactly the same – the results looked different but were not!

If you rerun the code to create the two tables and then issue the two SELECT statements again with the option to show the execution plan turned on, you will get this execution plan:

As you can see, both queries were executed with an identical plan: a scan of the nonclustered index that was created to check the UNIQUE constraint on the FirstName column. If you now change the original queries to include this column as well, you should not only see that the execution plan remains the same, but you should also see the explanation for the different behavior.

SELECT FirstName, LastName FROM Beatles;

SELECT FirstName, LastName FROM Stones;

FirstName LastName

-------------------- --------------------

George Harrison

John Lennon

Paul McCartney

Ringo Starr

FirstName LastName

-------------------- --------------------

Bill Wyman

Brian Jones

Charlie Watts

Keith Richards

Mick Jagger

As you see, both result sets are alphabetically ordered by first name. It is a shear coincidence that the alphabetic ordering of the Beatles by first name exactly matches the ordering by last name. So the results have never been returned in the order of the clustered index, but always in the order of the nonclustered index, for both tables. These orders just happen to be indistinguishable for one of the two tables.

Of course, this still leaves us with the question why the optimizer chooses to scan the nonclustered index for this query. That question is easily answered. You just have to consider which columns are included in each of the indexes.

A clustered index will always have the indexed columns in the root and intermediate pages, and all columns in the table in the leaf pages. A nonclustered index will also have the indexed columns in the root and intermediate pages, but its leaf pages contain only the indexed columns plus the columns of the clustered index (if any). For the Beatles and Stones tables, this means that the clustered indexes have LastName in the non-leaf pages, and all columns (both LastName and FirstName) in the leaf pages; the nonclustered indexes have FirstName in the non-leaf pages and add the clustered index key of LastName in the leaf pages.

Since the queries issued don’t have a WHERE clause, the optimizer can only consider plans using a scan operator. A scan will only touch the leaf pages of an index. In this particular case, all rows used in the query (just LastName) happen to be available in the leaf pages of both indexes. That means that the nonclustered index is a covering index for this query, and an extra lookup operator is not required to get the data. So the choice is between scanning either the clustered index or the nonclustered index.

In this case, both indexes have exactly the same amount of data in their leaf pages, so there is no reason to prefer one over the other. But that is very unusual – in 99% of all normal situations, a nonclustered index will have less data in its leaf pages than the clustered index. (Remember that a clustered index always includes ALL columns in the leaf pages!), and a nonclustered index will never have more data in its leaf pages. For that reason, the query optimizer will always favor an execution plan that uses a covering nonclustered index over a plan that uses a clustered index.

One interesting comment was made by Denis The SQL Menace: if you add WHERE LastName > '' to the queries, the order will match the clustered index for both queries. That makes sense. Remember that there is no constraint that disallows us from setting LastName equal to the empty string in one of the rows of the table, so there might be a row that doesn’t satisfy the WHERE clause. The nonclustered index scan used previously would still have to process that row; a clustered index seek would enable the engine to bypass that row. So I’m not surprised to see the execution plan change to a clustered index seek.

An even more intriguing observation was pointed out by Ahmed Charles. He added a computed column to the table and replaced the UNIQUE constraint with a UNIQUE INDEX, with the computed column as included column. Here’s how the Stones table looks after Ahmed’s modification:

CREATE TABLE Stones

(LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

FirstName varchar(20),

LastNameAgain AS CAST(LastName AS char(1597)));

CREATE UNIQUE INDEX IX_FirstName

ON Stones(FirstName)

INCLUDE (LastNameAgain);

Ahmed found that if the computed column is defined as char(1597) or more, the engine scans the clustered index, resulting in the last names being lasted alphabetically; as long as the computed column in char(1596) or less, the engine prefers to scan the nonclustered index. Why does the execution plan suddenly change if the computed column gets one additional character? Computed columns are not even supposed to be persisted, right?

Well, yes and no. Computed columns are indeed normally not stored, but computed when queried. But there are two things that can change that: adding the PERSISTED keyword to the column’s definition, or using the column in an index. In the first case, the value of the column is physically stored in the clustered index (or in the heap); in the latter case, the value is physically stored in each index that is defined on or includes the column. The latter is what happens here.

If you look at the data in the IX_FirstName index in a bit more details, we see that each leaf page entry will hold one char(1597) column (always 1597 bytes), two varchar(20) columns (number of bytes equal to actual length, plus 2 bytes each for length), plus 8 bytes of overhead per row (one byte for status flags, one byte for the NULL bitmap, two bytes for the total number of columns, two bytes for the number of variable-length columns, and two bytes for the row offset). So for each row, the amount of bytes used is equal to 1609 (1597 + (2 * 2) + 8) + the number of characters in the first and last name. That’s 1618 bytes for Bill Wyman, 1619 for Brian Jones, 1621 for Charlie Watts, 1622 for Keith Richards, and 1619 for Mick Jagger, making a grand total of 8099 bytes. Now each page can hold 8192 bytes, but 96 of them are reserved for the page header, so that leaves 8096 bytes for data. Three less than the number of bytes required to store the index data – so the index requires a second page. But if I reduce the length of the computed column by 1, the required amount per row drops 1 bytes; for all 5 rows combined, we need 5 bytes less – 8094 bytes, which does fit in a single page.

Just in case you want to check this for yourself without doing the calculations or running the DBCC PAGE commands, you can also execute

SELECT dpages FROM sysindexes WHERE name = 'IX_FirstName';

to see the number of leaf pages used by the index.

With this knowledge, I can new explain why the computed column changes the behavior of the query when the computed column exceeds 1596 bytes – an index scan will now require more page reads than a clustered index scan, so the optimizer will surely choose the latter.

With this knowledge, I can also predict some other interesting things:

· If I change the definition of the FirstName and LastName columns to char(20), I can reduce the computed column to 1574 bytes and I still get a clustered index scan;

· If I reduce the initial load of the table to just four of the five band members (for instance by omitting Brian Jones), I have to increase the length of the computed column to 2002 before I get a clustered index scan;

· But on the other hand, if I add all five band members to the table, then delete Brian Jones again, I still get the clustered index scan even with a computed column of 1597 bytes length. Once the second page has been allocated to the nonclustered index, it will stay allocated even if the data could in theory be compressed on a single page again. Reorganizing the index won’t change this, but rebuilding will.

The Beatles versus the Stones

Hugo Kornelis — Sun, 31 Dec 2006 11:49:00 GMT

Here’s a nice brain teaser, just before the end of the year. Despite the title, it is related to SQL Server, not to music!

A common misconception amongst SQL Server users is that a clustered index on a table will ensure that data is returned in the order implied by that index. I have lost count of the number of times I had to disprove this notion.

Of course, there are many cases where the rows returned by a query will be in the order of the clustered index. Here’s a quick illustration, using the lineup that The Beatles had during most of the 60s.

CREATE TABLE Beatles

(LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);

INSERT INTO Beatles (LastName, FirstName)

SELECT 'Lennon', 'John'

UNION ALL

SELECT 'McCartney', 'Paul'

UNION ALL

SELECT 'Harrison', 'George'

UNION ALL

SELECT 'Starr', 'Ringo';

SELECT LastName FROM Beatles;

DROP TABLE Beatles;

LastName

--------------------

Harrison

Lennon

McCartney

Starr

The results of this query are in alphabetical order of last name, the column used in the clustered index. Apparently, this is one of the very many cases where the order of the rows is implied by the clustered index, allowing the misconception that this is always the case to spread even further.

But an interesting thing happens if I use the exact same table definition to old and query the lineup of that other famous rock group of the 60s:

CREATE TABLE Stones

(LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);

INSERT INTO Stones (LastName, FirstName)

SELECT 'Jagger', 'Mick'

UNION ALL

SELECT 'Jones', 'Brian'

UNION ALL

SELECT 'Richards', 'Keith'

UNION ALL

SELECT 'Watts', 'Charlie'

UNION ALL

SELECT 'Wyman', 'Bill';

SELECT LastName FROM Stones;

DROP TABLE Stones;

LastName

--------------------

Wyman

Jones

Watts

Richards

Jagger

In this case, the names are returned in random order. That makes this a great example to really disprove the notion of a clustered index guaranteeing any output order. (Dare I say that we now finally have solid proof that the Stones are better than the Beatles? Or will that make me subject to loads of flames?)

What’s intriguing in this case is the difference in behaviour for the two examples. Apart from the table name, the two code snippets are exactly the same – and even renaming the tables won’t change the results. So here’s the brain teaser that I’ll leave you to ponder over your glass of champagne: what is the reason that the Beatles are, but the Stones are not returned in clustered index order?

I’ll post the answer in a few days.

On a more personal note, I want to apologize for not posting any new stuff during the last two months. I still have some good ideas in my scratchpad, but I need some time to polish them up to blog quality – and time is the one thing I have been lacking for the past two months. The bad news is that I will probably be short on time for the next month as well, but things are looking more sunny after that.

And with this being my last post of the year, I’ll also grab this opportunity to wish all readers of sqlblog.com a very great 2007, with lots of love and luck in your personal lifes, and lots of interesting SQL challenges and enticing performance gains at work.

Fun with ambiguous table names

Hugo Kornelis — Sat, 02 Sep 2006 18:24:00 GMT

Earlier today, I realised that Microsoft has forgotten to include some keywords in the list of reserved keywords. Now, a wise developer will still take care to omit those names when naming tables – but a bored developer can have loads of fun exploring the effects!

The keywords I am referring to are inserted and deleted. Everyone who ever coded a trigger knows that they refer to the pseudo-tables that hold the before and after image of all rows affected by the triggering DML statement. But since they’re not reserved keywords, it’s perfectly legal to name a column “inserted”.

Of course, things get confusing when you name your table “inserted” and create a trigger on that table – how is SQL Server supposed to know what you mean when you write “inserted”? As an example, look at the following code. Quiz question: try to predict the results before executing the code; let me know if your prediction was right.

CREATE TABLE inserted (a int PRIMARY KEY);

INSERT INTO inserted VALUES(1);

CREATE TRIGGER tst

ON inserted AFTER INSERT

AS SELECT * FROM inserted;

INSERT INTO inserted VALUES(2);

SELECT * FROM inserted;

DROP TABLE inserted;

My prediction was an error message because of the ambiguous table name. Boy was I wrong!

The results of the code above prove that SQL Server will use the pseudo-table if I write “inserted” in the inside of a trigger, even if there is a table with the same name. So what do I do if I need to refer to the rows in that table from a trigger?

Actually, that’s a lot easier than it sounds – just follow long-standing best practice: prefix all table names with owner (SQL Server 2000) or schema (SQL Server 2005). Change the example above to the one below to see how both the “real” table “inserted” and the pseudo-table can be used within the trigger:

CREATE TABLE inserted (a int PRIMARY KEY);

INSERT INTO inserted VALUES(1);

CREATE TRIGGER tst ON inserted AFTER INSERT

SELECT * FROM inserted;

SELECT * FROM dbo.inserted;

INSERT INTO inserted VALUES(2);

SELECT * FROM inserted;

DROP TABLE inserted;

Intriguingly, you can even join inserted and dbo.inserted in a single query and refer to columns from both tables, as long as you keep repeating the dbo qualifier each time you refer to a column from the real table. How’s that for hard to grasp coding, huh?

However, things get even more interesting if we leave SQL Server 2000 behind and explore one of the new features SQL Server 2005 has to offer: the OUTPUT clause for INSERT, UPDATE and DELETE statements. Since the SQL Server development team decided to overload the (still unreserved) keywords inserted and deleted with a second meaning, things start to get really interesting here!

The real problem in SQL Server 2005 (and the exact issue that caused me to start investigating this issue) is that you don’t even need to choose your table names badly to run into trouble. Regardless of table name, you are challenged by the ambiguity of the inserted keyword as soon as you have to use the OUTPUT clause within a trigger. (In fact, exactly that happened to me at work yesterday; this was what prompted me to do some further investigation today). Here’s a simplified example – anyone care to take a bet on the outcome?

CREATE TABLE testtab (pk int NOT NULL PRIMARY KEY,

a char(1) NOT NULL,

b char(1) NOT NULL);

CREATE TRIGGER testtrig

ON testtab AFTER INSERT

AS UPDATE testtab

SET a = inserted.b,

b = inserted.a

OUTPUT inserted.a, inserted.b

FROM inserted

INNER JOIN testtab

ON testtab.pk = inserted.pk;

INSERT INTO testtab (pk, a, b) VALUES (1, 'a', 'b');

DROP TABLE testtab;

Running the code above shows that in this OUTPUT clause, the inserted keyword is taken to refer to the new version of the rows affected by the UPDATE statement, not to the trigger’s pseudo-table that holds the newly inserted rows. Now, what should I do if I actually wanted to output data from the trigger’s pseudo-table here? I can’t use dbo.inserted here, since that would refer to a real table. And yet I should be able to refer to the pseudo-table, as the documentation of the OUTPUT clause clearly states that tables used in the FROM clause can also be used in the OUTPUT clause.

The only solution I could find is to use an alias in the FROM clause, so that we can use the alias to refer to the inserted pseudo-table in the OUTPUT clause:

CREATE TABLE testtab (pk int NOT NULL PRIMARY KEY,

a char(1) NOT NULL,

b char(1) NOT NULL);

CREATE TRIGGER testtrig

ON testtab AFTER INSERT

AS UPDATE testtab

SET a = i.b,

b = i.a

OUTPUT i.a, i.b

FROM inserted AS i

INNER JOIN testtab

ON testtab.pk = i.pk;

INSERT INTO testtab (pk, a, b) VALUES (1, 'a', 'b');

DROP TABLE testtab;

Now to get really overboard with ambiguity, I decided to create an example that refers to three versions of inserted on a single line – the new rows in the UPDATE statement, the rows in the trigger’s pseudo-table and the rows in the permanent table named “inserted”. Please, don’t ever try to do this at home, and even less at work – unless you are writing a blog or if you want to see your code on The Daily WTF.

CREATE TABLE inserted (pk int NOT NULL PRIMARY KEY,

a char(1) NOT NULL,

b char(1) NOT NULL,

c int NOT NULL);

CREATE TABLE other (pk int NOT NULL PRIMARY KEY,

a char(1) NOT NULL,

b char(1) NOT NULL,

c int NOT NULL)

INSERT INTO other (pk, a, b, c) VALUES (1, 'a', 'b', 1)

CREATE TRIGGER ugly

ON inserted AFTER INSERT

AS UPDATE dbo.inserted

SET c = 5;

UPDATE other

SET a = i.b,

b = i.a,

c = dbo.inserted.c + 1

OUTPUT inserted.a, i.b, dbo.inserted.c

FROM other

INNER JOIN inserted AS i

ON i.pk = other.pk

INNER JOIN dbo.inserted

ON inserted.pk = i.pk;

INSERT INTO inserted (pk, a, b, c) VALUES (1, 'a', 'b', 1);

DROP TABLE inserted;

DROP TABLE other;