SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Best Practices

Let's deprecate UPDATE FROM!

Hugo Kornelis — Sun, 09 Mar 2008 23:38:00 GMT

I guess that many people using UPDATE … FROM on a daily basis do so without being aware that they are violating all SQL standards.

All versions of the ANSI SQL standard that I checked agree that an UPDATE statement has three clauses – the UPDATE clause, naming the table to be updated; the SET clause, specifying the columns to change and their new values; and the optional WHERE clause to filter the rows to be updated. No FROM or JOIN – if you need data from a different table, use a subquery in the SET clause. The optional FROM and JOIN clauses were added by Microsoft, as an extension to the standard syntax (and just to make out lives more interesting, they invented different variations of the syntax for SQL Server and for Access). So when you are in the habit of using them, be prepared to review all your UPDATE statements when moving to Oracle, DB2, Sybase, MySQL, or even a different Microsoft database!

Standards? Bah, who cares?

Well, some do. Me for instance – I will never use proprietary syntax if I know a standard alternative, expect if using the latter has severe negative consequences. And maybe you will, one day, when your boss comes back from the golf course with the great news that he managed to convince a colleague (who just happens to work in an Oracle shop) to buy a copy of your company’s application instead of some off-the-shelf product. Or when there’s a great job opportunity for someone with cross platform skills. Or when you are asked to help out this new colleague with 10+ years of DB2 experience. One of the lesser known side effects of Murphy’s Law is that those who least expect having to move their database to another platform, will.

But even if you really don’t care about portability, there are other reasons to be wary of using UPDATE FROM. In fact, the most important reason why I dislike UPDATE FROM is not that it’s non-standard, but that it is just too easy to make mistakes with.

Correctness? Bah, who cares?

Well, most do. That’s why we test.

If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…

That’s all?

Well, almost. There’s one more thing. Probably not something you’ll run into on a daily base, but good to know nonetheless. If the target of the update happens to be a view instead of a base table, and there is an INSTEAD OF UPDATE trigger defined for the view, the UPDATE will fail with this error message:

Msg 414, Level 16, State 1, Line 1

UPDATE is not allowed because the statement updates view "v1" which participates in a join and has an INSTEAD OF UPDATE trigger.

Of course, most people will never run into this. But I did have the misfortune of doing so once – unfortunately, I discovered this limitation after rewriting several hundred ANSI standard UPDATE statements to the equivalent UPDATE FROM, and having to convert them all back after as much as a single test…

And that’s why you want to deprecate UPDATE FROM?

Well, no. The view with INSTEAD OF UPDATE trigger won’t affect many people. And the possibility of error can be somewhat thwarted by making sure (and double-checking) to always include all columns of the primary key (or a unique constraint) of the source table. So we’re back to the more principle point of avoiding proprietary syntax if there is an ANSI standard alternative with no or limited negative consequences. And in the case of UPDATE FROM, there are some cases where the standard syntax just doesn’t cut it.

One such scenario is when a file is read in periodically with updated information that has to be pushed into the main table. The code below sets up a simplified example of this – a table Customers, with SSN as its primary key, that stores address and lots of other information, and a table Moved, which is the staging table containing the contents of a file received from a third party listing new address for people who recently moved. I have also included the code to preload the tables with some mocked up data – the Customers table has 10,000 rows, and the Moved table has 3,000 rows, 1,000 of which match an existing row in the Customers table. The others don’t – those people are apparently not our customers.

CREATE TABLE Customers

(SSN char(9) NOT NULL,

Street varchar(40) NOT NULL,

HouseNo int NOT NULL,

City varchar(40) NOT NULL,

LotsOfOtherInfo char(250) NOT NULL DEFAULT (''),

PRIMARY KEY (SSN),

CHECK (SSN NOT LIKE '%[^0-9]%')

);

CREATE TABLE Moved

(SSN char(9) NOT NULL,

Street varchar(40) NOT NULL,

HouseNo int NOT NULL,

City varchar(40) NOT NULL,

PRIMARY KEY (SSN),

CHECK (SSN NOT LIKE '%[^0-9]%')

);

INSERT INTO Customers(SSN, Street, HouseNo, City)

SELECT RIGHT(Number+1000000000,9), 'Street ' + CAST(Number AS varchar(10)),

Number, 'City ' + CAST(Number AS varchar(10))

FROM dbo.Numbers

WHERE Number BETWEEN 1 AND 30000

AND Number % 3 = 0;

INSERT INTO Moved(SSN, Street, HouseNo, City)

SELECT RIGHT(Number+1000000000,9), 'New street ' + CAST(Number AS varchar(10)),

Number * 2, 'New city ' + CAST(Number AS varchar(10))

FROM dbo.Numbers

WHERE Number BETWEEN 1 AND 30000

AND Number % 10 = 0;

Since ANSI-standard SQL does not allow a join to be used in the UPDATE statement, we’ll have to use subqueries to find the new information, and to find the rows that need to be updated, resulting in this query:

UPDATE Customers

SET Street = (SELECT Street

FROM Moved AS m

WHERE m.SSN = Customers.SSN),

HouseNo = (SELECT HouseNo

FROM Moved AS m

WHERE m.SSN = Customers.SSN),

City = (SELECT City

FROM Moved AS m

WHERE m.SSN = Customers.SSN)

WHERE EXISTS (SELECT *

FROM Moved AS m

WHERE m.SSN = Customers.SSN);

There’s a lot of duplicated code in here. And if we were getting data from a complicated subquery instead of the table Moved, it would be even worse (though we can at least put all the duplicated code in a CTE since SQL Server 2005). Of course, writing the code is done quickly enough once you master the use of copy and paste, but the code has to be maintained as well.

Maybe even worse is that the performance of this query just sucks – if you run this (enclosed in a BEGIN TRAN / ROLLBACK TRAN, so you can run the variations below without having to rebuild the original data) and check out the execution plan, you’ll see that the optimizer needs no less than five table scans (one for Customers, and four for Moved) and four merge join operators. And that, too, would be much worse if the source of the data had been a complex subquery (and no, using a CTE will not help the optimizer find a better plan – it just doesn’t understand that the four subqueries are similar enough that they can be collapsed.

Now, if Microsoft had chosen to implement row-value constructors (as defined in the ANSI standard), we could have simplified this to

UPDATE Customers

SET (Street, HouseNo, City)

= (SELECT Street, HouseNo, City

FROM Moved AS m

WHERE m.SSN = Customers.SSN)

WHERE EXISTS (SELECT *

FROM Moved AS m

WHERE m.SSN = Customers.SSN);

But this is invalid syntax in any version of SQL Server (including the latest CTP for SQL Server 2008), and I know of no plans to change that before SQL Server 2008 RTMs.

But with using the proprietary UPDATE FROM syntax, we can simplify this, and get a much better performance to boot. Here’s how the same update is written in non-portable code:

UPDATE c

SET Street = m.Street,

HouseNo = m.HouseNo,

City = m.City

FROM Customers AS c

INNER JOIN Moved AS m

ON m.SSN = c.SSN;

And now, the optimizer will produce a plan that scans each table only once and has only a single merge join operator. Some quick tests (with much more rows in the tables) show that it executes two to three times quicker than the ANSI standard version. For that performance gain, I will gladly choose the proprietary syntax over the standard!

What’s with the title of this post then? Why deprecate a fine feature?

Patience, we’re getting there. Bear with me.

All the above is true for versions of SQL Server up to SQL Server 2005. But SQL Server 2008 will change the playing field. It introduces a new statement, MERGE, that is specifically designed for situations where rows from a table source either have to be inserted into a destination table, or have to be used to update existing rows in the destination table. However, there is no law that prescribes that any MERGE should always actually include both an insert and an update clause – so with this new statement, we can now rewrite the above code as follows:

MERGE INTO Customers AS c

USING Moved AS m

ON m.SSN = c.SSN

WHEN MATCHED

THEN UPDATE

SET Street = m.Street,

HouseNo = m.HouseNo,

City = m.City;

As you can see, the source table and the join criteria are included only once, just as in the proprietary UPDATE FROM. The execution plan (tested on the February CTP, also known as CTP6) is also quite similar, including just a few extra operators that are specific to the new MERGE statement. What really surprised me, was that the plan for the MERGE statement was estimated to be about 65% cheaper (faster) than the corresponding UPDATE FROM statement. However, I think SQL Server is lying here – a quick test with more data shows only an extremely marginal advantage of MERGE over UPDATE FROM. This test was too limited to draw serious conclusions, but I am quite sure that there will not be a 65% saving by using MERGE over UPDATE FROM. (I do expect such a saving form either MERGE or UPDATE FROM over the ANSI-compliant UPDATE statement for this case).

The good news is that:

1) The MERGE statement is described in SQL:2003 and can thus be considered ANSI standard. (In fact, SQL Server implements a superset of the ANSI standard MERGE syntax: everything described in the syntax is implemented, but there are some non-standard extensions that make the command even more useful as well. However, the example above uses only the standard features and should hence run on each DBMS that conforms to the SQL:2003 version of MERGE).

2) The MERGE statement will return an error message if I mess up my join criteria so that more than a single row from the source is matched:

Msg 8672, Level 16, State 1, Line 1

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

3) The MERGE statement will gladly accept a view with an INSTEAD OF UPDATE trigger as the target of the update.

So as you see, MERGE allows me to achieve what I previously could achieve only with an ANSI standard UPDATE statement with lots of duplicated code and lousy performance, or with a UPDATE FROM statement that hinders portability, introduces a higher than normal risk of errors going unnoticed through QA right into the production database, and has some odd limitation on views with INSTEAD OF UPDATE triggers. None of these downsides and limitations apply to MERGE. And if there are any other problems with MERGE, I have yet to find them.

With this alternative available, I fail to see any reason why the proprietary UPDATE FROM syntax should be maintained. In my opinion, it can safely be marked as deprecated in SQL Server 2008. It should of course still work, as “normal” supported syntax in both SQL Server 2008 and the next version, and in at least one version more if the database is set to a lower compatibility – but it should be marked as deprecated, and it should eventually be removed from the product. Why waste resources on maintaining that functionality, when there is an alternative that is better in every conceivable way? I’d much rather see the SQL Server team spend their time and energy on more important stuff, such as full support for row-value constructors and full support for the OVER() clause. Or maybe even on releasing Service Pack 3 for SQL Server 2005!

How NOT to pass a lot of parameters

Hugo Kornelis — Thu, 04 Oct 2007 07:07:00 GMT

Did you know that SQL Server allows stored procedures to have up to 2100 parameters? And more important: do you care? Well, some people do care, and Joe Celko seems to be one of them.

If you are a regular reader of SQL Server newsgroups, you probably know Joe Celko from his always unfriendly and often incorrect replies. Here is a typical example, one that I have seen several times recently, in a paraphrased form:

Question: I want to send a list of values to my stored procedure, but WHERE ColumnName IN (@ValueList) does not work – how to solve this?

Answer: SQL Server can handle over 1000 parameters. You should use WHERE ColumnName IN (@Parm1, @Parm2, …, @ParmN).

Joe Celko is the only one I have ever seen giving this advise. Many people will then jump into the discussion, challenging Joe’s advise. To which Joe will always reply that he has received a smart stored procedure that will solve a Sudoku puzzle, and that takes 81 parameters (one for each cell in the puzzle) as its input – unfortunately, Joe has so far refused to actually publish his code to let other people verify his claims.

The test setup

I still wanted to see for myself how passing 81 parameters into a stored procedure compares to other methods of passing in the same input, so I wrote three simple test procedures. Each of these procedures takes a Sudoku puzzle as input, but in three different forms. Each of the three then uses the input to populate a temporary table (#Problem) with the puzzle, and then performs a pretty standard pivot query to output the puzzle in the usual form.

After verifying that all of the procedures worked as expected, I uncommented the pivot query to reduce the output for my performance tests. I then set the tests. I selected two real Sudoku puzzles (an easy one, with 34 cells given, and a hard one with only 27 cells given) and added two nonsensical ones of my own (one with only 5 cells given, and one with 72 cells). For each combination of a puzzle and a procedure, I coded a loop that calls the procedure a thousand times and records the elapsed time in a table. These twelve loops were than enclosed in an endless loop. Once satisfied with the code, I hit the execute button, pushed my laptop out of the way and went on to other stuff.

Some 24-odd hours later, I interrupted the test script. Each of the twelve “thousand calls” tests had been executed 400 times. I dusted of a query I originally wrote for testing the performance of code for the Spatial Data chapter in “Expert SQL Server 2005 Development” to calculate the average duration per single call, disregarding the fastest and slowest 10% of the measurements to exclude the influence of semi-random other activities on my laptop.

(Note that all code to create the stored procedures and run the tests is in the attachment to this post, so you can always repeat these tests on your machine.)

The contenders

The first contender is of course the procedure with 81 parameters that Joe Celko is such an avid fan of. Creating this procedure involved a lot of copying and pasting, a lot of editing numbers in the copied and pasted code, and a lot of tedious debugging until I had finally found and corrected all locations where I had goofed up the copy and paste or where I had failed to edit a number after pasting. The resulting code is very long, tedious to read and maintain, and screams “Hey! You forgot to normalize these repeating groups into their own table” all the way. Manually typing the EXEC statements to call this procedure with test data was also very cumbersome and error-prone. In a real situation, the procedure would probably be called from some user-friendly front end code. I’m far from an expert in front end code, but I expect this code to be very long as well, since it has to check and optionally fill and pass 81 parameters.

The second contender uses a pretty standard CSV string as input, with the additional requirement that each value in the CSV is three characters: row@, column#, value. The procedure uses a variation of one of the scripts found on Erland Sommarskog’s site to parse the CSV list into a tabular format. This code is lots shorter, and as a result easier on the eyes and easier to maintain. Typing in the EXEC statements for testing is still pretty cumbersome (though I found a way to cheat – simply copy the parameter list for the Celko version, do some global search and replace to remove various characters, and the end was exactly the string I needed to call this procedure). The front end code will probably be lots shorter, since it can use a simple loop to process the input and build the CSV parameter.

The third and last contender takes a CHAR(81) parameter as input. The first 9 characters of this parameter describe the top row, using a space to depict an empty cell; the second set of 9 characters is for the second row, and so forth. Parsing this parameter turned out to be even easier than parsing the CSV parameter. Another observation I made is that is was much easier to manually enter the parameter for the tests – just read the puzzle left to right and bottom to top and type either a number or a space for each cell. This was absolutely painless, and I didn’t make a single mistake. Of course, this is irrelevant for the expected real situation where the parameter is built by the front end – the code to do this will probably be about as complex as that for the CSV parameter.

Performance results

If you’re as eager to see the test results, you’ll probably have skipped the previous section. No problem, just promise to go back and read it later, m’kay?

Test version	Joe Celko’s 81 parameters	Single CSV parameter	Single CHAR(81) parameter
Almost empty (5 cells)	1.08 ms	1.40 ms	1.05 ms
Hard puzzle (27 cells)	1.80 ms	1.78 ms	1.35 ms
Easy puzzle (34 cells)	2.04 ms	1.90 ms	1.45 ms
Almost full (72 cells)	3.34 ms	2.56 ms	1.99 ms

As you see, using lots of parameters is faster than using a single CSV parameter only if you don’t actually pass values in these parameters. As soon as you use the parameters, performance of a procedure with lots of parameters deteriorates quickly.

You can also see that the CHAR(81) parameter wins in all cases.

Network bandwidth

My testing was all carried out on my laptop. The results will for the most part me a result of the time needed to process the input, not on network capacity. However, it is easy to see by just looking at the EXEC statements that the CHAR(81) version uses the least network resources, Celko’s version with 81 parameters uses the most, and the CSV versions sits nicely in between.

Final thoughts

You may have noted that I have not included a version with an XML input parameter in my tests. I probably should have done that, but I have to admit that I still have so much to learn on how to handle XML in a SQL Server database that I didn’t feel comfortable enough to sit down and write one myself. But your submissions are welcomed – if you feel that you can write an efficient version of this procedure that accepts its input in XML format, do not hesitate to write me. As soon as I can spare the time to set up the laptop for another all-nighter of performance testing, I’ll rerun the script with your XML solution included and post the results back to this site,

While writing this post, I found a newsgroup posting by Joe Celko where he reveals a snippet of “his” Sudoku solver. And guess what? I was wrong when I thought that I could guess how his procedure looks. It turns out that he does not use defaults for his parameter; you always have to supply them all, using 0 for an empty cell. I didn’t want to repeat all the tests at this time. I expect that this will reduce performance even more, though not by much – but it will also cause a huge increase in network usage!

I also saw that the parameters in Joe Celko’s version were declared as integer, so that each parameter will use 4 bytes instead of just 1. This will definitely affect both the performance of the procedure and the network pressure.

Conclusion

If you have to pass a long list of parameters to a stored procedure or function, you should not use a long collection of parameters. It makes the code harder to write and maintain, prone to subtle errors, longer (which will affect parse and compile time, though I did not include this in my test), uses far more network resources than any of the other alternatives (except, maybe, XML), and gets terribly slow as more parameters are actually used.

Joe celko will probably find that he too can shorten the amount of code in his Sudoku solver *and* increase performance by using a different strategy to pass the puzzle. Of course, in the case of solving a Sudoku, those two 2 milliseconds extra execution time won’t really matter, not the few hundred extra bytes travelling over the network. But if you ever encounter a similar multi-parameter problem in a procedure that will be called from a web page that will get hundreds of hits per second, those 2 milliseconds and those extra bytes in the network can suddenly become a huge bottleneck!

SQL Server may support up to 2100 parameters – but that does not imply that it is a good idea to actually use them!

Can you trust your constraints?

Hugo Kornelis — Thu, 29 Mar 2007 14:21:12 GMT

SQL Server allows you to temporarily disable your CHECK and FOREIGN KEY constraints. I would not recommend this as part of your regular production schedule, but there are certainly cases where this comes in handy.

One example is if you’re copying thousands of rows from another table, and you already know with 100% certainty that the data doesn’t violate any constraint. The overhead of checking could impact performance of your process; in the worst case making the execution time exceed your maintenance window. Disabling the constraints can result in a tremendous reduction of execution time.

Another example would be the case where you’re importing lots of data from a third party; the raw data might violate some constraints, but these errors are all corrected as part of the process before the constraints are re-enabled. I would personally prefer to import the raw data to a staging table, do the cleansing there and only then copy the data to the production table – which of course takes us back to the first example J.

However, there is a large caveat that many people seem to be unaware of – if you don’t take care while re-enabling the constraints, you might end up disabling the query optimizer’s potential to generate the best possible execution plan for your queries!

Let’s first look at the abridged syntax for disabling end re-enabling a constraint:

ALTER TABLE <tablename>

NOCHECK CONSTRAINT <constraintname>;

ALTER TABLE <tablename>

WITH { CHECK | NOCHECK }

CHECK CONSTRAINT <constraintname>;

The first syntax disables a constraint and the second syntax re-enables it. Note the “WITH {CHECK | NOCHECK}” clause. Specifying WITH CHECK signifies to SQL Server that you want it to check the re-enabled constraint for all rows in the table; if one or more fail to satisfy the constraint, the ALTER TABLE command fails. Specifying WITH NOCHECK (the default for existing constraints) means that existing rows are not checked. This is of course faster, but it has a severe side effect that you should really be aware of: you may know with 100% certainty that all rows in the table still abide by the constraint, but SQL Server doesn’t know this. As soon as you enable a constraint without checking the existing rows, SQL Server will mark the constraint as “not trusted”. This means that the query optimizer will no longer use it’s knowledge of the constraint to optimize your queries.

To see the effects of this, I set up a simple test with one table, three columns, and a CHECK constraint:

CREATE TABLE dbo.Test

(KeyColumn int NOT NULL,

CheckColumn int NOT NULL,

LongColumn char(4000) NOT NULL,

CONSTRAINT PK_Test PRIMARY KEY (KeyColumn),

CONSTRAINT CK_Test CHECK (CheckColumn > 0)

);

INSERT INTO dbo.Test (KeyColumn, CheckColumn, LongColumn)

SELECT 1, 1, REPLICATE('a', 4000)

UNION ALL

SELECT 2, 2, REPLICATE('b', 4000)

UNION ALL

SELECT 3, 3, REPLICATE('c', 4000)

UNION ALL

SELECT 4, 4, REPLICATE('d', 4000)

UNION ALL

SELECT 5, 5, REPLICATE('e', 4000)

UNION ALL

SELECT 6, 6, REPLICATE('f', 4000)

UNION ALL

SELECT 7, 7, REPLICATE('g', 4000);

If I now execute a query that, because of the CHECK constraint can’t possibly return any rows, I’ll get an execution plan that doesn’t even touch the table:

SELECT KeyColumn, CheckColumn

FROM dbo.Test

WHERE CheckColumn < 0

AND LEFT(LongColumn, 5) = RIGHT(LongColumn, 5);

But let’s now examine what happens if I, for whatever reason, disable and later re-enable the constraint:

ALTER TABLE dbo.Test

NOCHECK CONSTRAINT CK_Test;

-- Imagine something actuallly happening here

ALTER TABLE dbo.Test

CHECK CONSTRAINT CK_Test;

Because I didn’t specify the “WITH [CHECK | NOCHECK]” clause when re-enabling the constraint, the command defaults to not checking the existing population. As a result, SQL Server now feels that it can no longer trust this constraint, as can be seen in the catalog view sys.check_constraints:

SELECT LEFT(name, 20) AS name, is_not_trusted

FROM sys.check_constraints;

name is_not_trusted

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

CK_Test 1

Since SQL Server no longer trusts this constraint, running the query that searches for negative values in CheckColumn will now cause an execution plan to be created that scans the entire table:

SET STATISTICS IO ON;

SELECT KeyColumn, CheckColumn

FROM dbo.Test

WHERE CheckColumn < 0

AND LEFT(LongColumn, 5) = RIGHT(LongColumn, 5);

KeyColumn CheckColumn

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

Table 'Test'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To prevent this from happening, always make sure to use WITH CHECK when you re-enable a constraint:

ALTER TABLE dbo.Test

WITH CHECK CHECK CONSTRAINT CK_Test;

It looks a bit silly at first, the CHECK from the WITH CHECK option directly followed by the CHECK command to re-enable the constraint – but hey, I didn’t create the syntax, you know! You’ll get used to it, eventually.

Anyway, this results in the constraint being trusted again. The example query above will now, once more, return an empty result set without ever actually reading as much as a single page of the table’s data.

SELECT LEFT(name, 20) AS name, is_not_trusted

FROM sys.check_constraints;

name is_not_trusted

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

CK_Test 0

Now whereas this example might seem a bit contrived, it should be noted that this also applies to FOREIGN KEY constraints. I chose to use a CHECK constraint here to keep the sample code relatively short, but using a trusted FOREIGN KEY constraint, the optimizer can actually completely remove joined tables from a query! That is of course much more a real-world scenario than searching for a value that’s not permitted – and one that can have an even bigger impact on performance!!

So the bottom line is to always make sure that you include the WITH CHECK option when re-enabling a constraint. Or, to conclude with an awful pun, always double-check that there’s a double CHECK in the command.