Search results matching tags 't-sql' and 'Best Practices'

Bad habits to kick : ORDER BY ordinal

AaronBertrand — Tue, 06 Oct 2009 14:49:00 GMT

A few weeks ago, I wrote a post about forming a new habit: always terminate statements with semi-colons. Today I thought I would start a series on kicking bad habits that many of us have developed over time. I provided a little more background on this series over on my company's site. Today's topic: using ordinal numbers in our ORDER BY clauses.

At least once a week, I catch myself using ordinal position to define order. For example, I might have this:

SELECT foo, bar
  FROM dbo.splunge
  ORDER BY 1, 2 DESC;

This is lazy shorthand, and is bound to get screwed up at some point unless I have encrypted the stored procedure and threw away the key. Invariably, someone at some point is going to come in and add a column, remove a column, or change the order of the columns.

Remember that a lot of us produce stored procedures to be consumed by essentially "dumb" clients - Windows forms or web applications that lack the ability to perform any ordering on their own. So often the ORDER BY clause of a resultset coming back from a stored procedure is pretty important. When editing a stored procedure that has an error-prone ORDER BY clause like the above, it is very easy to make changes to the SELECT list without even looking at the ORDER BY clause, or the reverse. Imagine the web developer using the above procedure asks to change the SELECT list. Now he wants mort, foo, bar. (Note that order of the columns themselves should be irrelevant to the application, since it should be using column name and not ordinal references, but in some cases we like certain columns to appear in a certain order when we are debugging in SSMS and the like.) So assume you do exactly as the developer asks, and edit the SELECT clause and alter the procedure:

SELECT mort, foo, bar
  FROM dbo.splunge
  ORDER BY 1, 2 DESC;

Oops! Now the developer is complaining that his UI is broken because the order is all messed up. This would not have happened if you had used the following syntax in the first place:

SELECT mort, foo, bar
  FROM dbo.splunge
  ORDER BY foo, bar DESC;

I know it is hard to spell out those column names when you're doing ad hoc stuff, but getting in the habit will potentially save your bacon someday. And you can't use the excuse that you have very complicated expressions in your SELECT list, because ORDER BY is the only place in the entire query where you can reference an alias you provided. So the following is completely legal:

SELECT foo = RTRIM(CONVERT(INT, ABS(foo) % 2))
  FROM dbo.splunge
  ORDER BY foo;

There is nothing lost by being explicit, except for a few keystrokes. But there is plenty to lose by not doing so - including some patience for the developer running to your cube like their head is on fire. Ideally, when you are making changes to existing code, you examine the entire statement to ensure consistency. But try to be proactive and protect yourself against the inevitable rushed and possibly botched emergency fix.

I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code. Up next: using loops to populate large tables.

disallow results from triggers

drsql — Sun, 07 Jun 2009 02:38:06 GMT

A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers. Triggers are one of my favorite subjects, and you will find a lot of good uses of them in my book (triggers are also well named, as poor usage of them will allow you to shoot your foot off in a heartbeat as I discussed in my earlier post: http://sqlblog.com/blogs/louis_davidson/archive/2008/07/13/triggers-evil.aspx).

One of the main ways you can mess up your system is to have triggers that return results, often by accident, that the client doesn’t know about. There are three kinds of return values that are interesting:

Result sets
Raiserror messages
Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases. It is never a good idea to return data from a trigger, but it is actually pretty useful for testing. In the following code, I will test this setting and show how it will affect returning data to the client from the aforementioned methods.

What will the setting do? It will raise an error if you try to do a result set. It will automatically suppress rowcount messages (without the need for SET NOCOUNT ON), but will allow error messages (I won’t cover it here, but error messages can cause issues with transactions in triggers, particularly when coupled with TRY…CATCH blocks).

To demonstrate the setting, I will use tempdb. Checking the system setting for your server, use:

SELECT value
FROM sys.configurations
WHERE name = 'disallow results from triggers'

This will probably return, unless you have discovered the setting before I did, in which case you probably aren’t reading any longer, so there.

---------
0

To show you the effect of this setting , let's build the following scenario. The trigger will return 3 types of values

the contents of the table named triggerResult
the rowcount of rows affected from an insert statement like you would normally have in a database
the rowcount of the rows from the insert statement into a temp table

To start, we create 2 tables, one as the “main” table, and another that will hold the results of a side effect causing query:

--primary test table
create table triggerResult
(
    triggerResultId int primary key
)
--holds our side effect to prove the trigger executed
create table triggerResultSideEffect
(
    triggerResultId int
)

Then we will create the trigger that gives us several different types of output

create trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    --returns a result set
    select triggerResultId
    from   triggerResult

    --side effect like you might expect in a trigger
    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end
Now we will test out the trigger by inserting one row into the triggerResult table:

insert into triggerResult
values (1)

This returns (the final rows affected is from the original statement):

triggerResultId
---------------
1

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Now try a multi-row operation, to see the difference

insert into triggerResult
values (2),(3),(4)

This returns the following (which you can clearly now see the different number of rows that were affected by each statement, 4 from the one that returns all of the rows in the table, 3 from the side effect query that inserts the rows from the inserted table.

triggerResultId
---------------
1
2
3
4

(4 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(3 row(s) affected)

Now, change the setting to disallow trigger results (you may need to do allow advanced options)

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'disallow results from triggers',1
RECONFIGURE

Now, just to make sure of where our data is, we know that there are 4 rows in the triggerResult table. Let’s check the triggerResultSideEffect table contents:

SELECT *
FROM triggerResultSideEffect
ORDER BY triggerResultId

This returns, showing all of the values we have inserted:

triggerResultId
---------------
1
2
3
4

Now, trying to run the statement with the same trigger:

insert into triggerResult
values (5)

This will cause the following error message:

Msg 524, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 6
A trigger returned a resultset and the server option 'disallow results from triggers' is true.

Altering the trigger, just remove the statement that returns a result set:

alter trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    ----returns a result set
    --select triggerResultId
    --from   triggerResult
    --side effect like you might expect in a trigger

    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end

Now, re-executing the statement with no results being returned:

insert into triggerResult
values (5)

This simply returns the error message that are returned, and the rows affected message from the insert statement:

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Just to make sure that the rows were created:

select *
from triggerResult

select *
from triggerResultSideEffect

This returns:

triggerResultId
---------------
1
2
3
4
5

(5 row(s) affected)

triggerResultId
---------------
1
4
3
2
5

(5 row(s) affected)

Which shows that the data was inserted..

Note that books online states: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

This means that the ability to return result sets from a trigger will go away in the next version of SQL Server. Returning results from a trigger is certainly not something that has ever been a good practice, so if this does in fact occur, it is a good setting to set in your development servers and begin to use.

My stored procedure "best practices" checklist

AaronBertrand — Thu, 30 Oct 2008 21:40:00 GMT

When developing stored procedures, there seems to be a lot of emphasis on "get it done fast." Which means type all lower case, pay little attention to formatting, and sometimes throw best practices out the window. Personally, I would rather front-load my development time; I think that the costs I pay in initial development far outweigh what I might have paid in maintenance down the road. Making readable and maintainable code that also performs well and is delivered in a timely manner is something that a lot of us strive for, but we don't always have the luxury. But I have found that it is very easy to fall into the good kind of development habits.

A popular adage is, "you can have it fast, cheap, or good. Pick two." I contend that if you develop habits like these and use them in all of your database programming, the time difference between following those methods and doing it the "lazy" way will be negligible at most; and so, fast and good go hand in hand, rather than trade off for one another.

Once in a while this "disorder" slows me down. I come across code that someone else wrote (almost exclusively it is someone I no longer work with), and I can't even bear to look at it without first re-writing it. Here is a fake but realistic example of the kinds of procedures I see:

create proc foo(@i int,@bar int=null,@hr int output,@xd datetime) as
declare @c varchar
declare @s nchar(2)
declare @x int
set @grok='Beverly'
set @korg='MA'
set @x=5
select customers.customerid,firstname,lastname,orderdate from customers join orders on
customers.customerid=orders.customerid where status=@i or status<=@bar and orderdate<=@xd
set @hr = @@rowcount 
select customers.customerid,count(*) from customers left join orders on 
customers.customerid=orders.customerid where customers.city=@c and customers.state=@s 
group by customers.customerid having count(*)>=@x
return (@@rowcount)

This kind of feels like the 5th grade all over again, but when I get handed code like this, I start immediately visualizing one of those "find all of the things wrong with this picture" exercises, and feel compelled to fix them all. So, what is wrong with the above sample, you may ask? Well, let me go through my own personal (and quite subjective) subconscious checklist of best practices when I write my own stored procedures. I have never tried to list these all at once, so I may be all over the place, but hopefully I will justify why I choose to have these items on my checklist in the first place.

======================

Upper casing T-SQL keywords and built-in functions

I always use CREATE PROCEDURE and not create procedure or Create Procedure. Same goes for all of the code throughout my objects... you will always see SELECT, FROM, WHERE and not select, from, where. I just find if much more readable when all of the keywords are capitalized. It's not that hard for me to hold down the shift key while typing these words, and there are even IDEs that will do this kind of replacement for you (for example, Apex SQLEdit has a handy "mis-spelled keyword replacement" feature that I think could be used for this purpose also). This is probably one of the few areas where Celko and I actually agree. :-)

======================

Using a proper and consistent naming scheme

Obviously "foo" is a horribly ridiculous name for a procedure, but I have come across many that were equally nondescript. I like to name my objects using {target}_{verb}. So for example, if I have a Customers table, I would have procedures such as:

    dbo.Customer_Create
    dbo.Customer_Update
    dbo.Customer_Delete
    dbo.Customer_GetList
    dbo.Customer_GetDetails

This allows them to sort nicely in Object Explorer / Object Explorer Details, and also narrows down my search quickly in an IntelliSense (or SQLPrompt) auto- complete list. If I have a stored procedures named in the style dbo.GetCustomerList, they get mixed up in the list with dbo.GetClientList and dbo.GetCreditList. You could argue that maybe these should be organized by schema, but in spite of all the buzz, I have not developed a need or desire to use schemas in this way. For most of the applications I develop, ownership/schema is pretty simple and doesn't need to be made more complex.

Of course I NEVER name stored procedures using the sp_ prefix. See Brian Moran's article in SQL Server Magazine back in 2001. Or just ask anybody. :-) I also avoid other identifying object prefixes (like usp_). I don't know that I've ever been in a situation where I couldn't tell that some object was a procedure, or a function, or a table, and where the name really would have helped me all that much. This is especially true for the silly (but common) "tbl" prefix on tables. I don't want to get into that here, but I've always scratched my head at that one. Views may be the only place where I think this is justified, but then it should be a v or View_ prefix on the views only; no need to also identify tables... if it doesn't have a v or View_ prefix, it's a table!

More important than coming up with a proper naming scheme (because that is mostly subjective), it is much more important that you apply your naming scheme consistently. Nobody wants to see procedures named dbo.Customer_Create, dbo.Update_Customer and dbo.GetCustomerDetails.

======================

Using the schema prefix

I always specify the schema prefix when creating stored procedures. This way I know that it will be dbo.procedure_name no matter who I am logged in as when I create it. Similarly, my code always has the schema prefix on all object references. This prevents the database engine from checking for an object under my schema first, and also avoids the issue where multiple plans are cached for the exact same statement/batch just because they were executed by users with different default schemas.

======================

Using parentheses around parameter list

I am not a big fan of using parentheses around the parameter list. I can't really explain it, as I am a proponent of consistency, and this is the syntax required when creating user-defined functions. But I wanted to mention it because you will not see any of my stored procedures using this syntax. I'm open to change if you can suggest a good enough reason for me to do so.

======================

Lining up parameter names, data types, and default values

I find this much easier to read:

CREATE PROCEDURE dbo.User_Update
   @CustomerID     INT,
   @FirstName      VARCHAR(32)     = NULL,
   @LastName       VARCHAR(32)     = NULL,
   @Password       VARCHAR(16)     = NULL,
   @EmailAddress   VARCHAR(320)    = NULL,
   @Active         BIT             = 1,
   @LastLogin      SMALLDATETIME   = NULL
AS
BEGIN
...

...than this:

CREATE PROCEDURE dbo.User_Update
@CustomerID INT,
@FirstName VARCHAR(32) = NULL,
@LastName VARCHAR(32) = NULL,
@Password VARCHAR(16) = NULL,
@EmailAddress VARCHAR(320) = NULL,
@Active BIT = 1,
@LastLogin SMALLDATETIME = NULL
AS
BEGIN
...

======================

Using spaces and line breaks liberally

This is a simple one, but in all comparison operators I like to see spaces between column/variable and operator. So instead of @foo int=null or where @foo>1 I would rather see @foo INT = NULL or WHERE @foo > 1.

I also tend to place at least a carriage return between individual statements, especially in stored procedures where many statements spill over multiple lines.

Both of these are just about readability, nothing more. While in some interpreted languages like JavaScript, size is king, and compressing / obfuscating code to make it as small as possible does provide some benefit, in T- SQL you would be hard-pressed to find a case where this comes into play. So, I lean to the side of readability.

======================

Avoiding data type / function prefixes on column / parameter names

I often see prefixes like @iCustomerID, @prmInputParameter, @varLocalVariable, @strStringVariable. I realize why people do it, I just think it muddies things up. It also makes it much harder to change the data type of a column when not only do you have to change all the variable/parameter declarations but you also have to change @iVarName to @bigintVarName, etc. Otherwise the purpose of the prefixed variable name loses most of its benefit. So, just name the variable for what it is. If you have a column EmailAddress VARCHAR(320), then make your variable/parameter declaration @EmailAddress VARCHAR(320). No need to use @strEmailAddress ... if you need to find out the data type, just go to the declaration line!

======================

Using lengths on parameters, even when optional

I occasionally see people define parameters and local variables as char or varchar, without specifying a length. This is very dangerous, as in many situations you will get silent truncation at 30 characters, and in a few obscure ones, you will get silent truncation at 1 character. This can mean data loss, which is not very good at all. I have asked that this silent truncation at least become consistent throughout the product (see Connect #267605), but nothing has happened yet. Fellow MVP Erland Sommarskog has gone so far as to ask for the length declaration to become mandatory (see Connect #244395) and, failing that, feels that this should be something that raises a warning when using his proposed SET STRICT_CHECKS ON setting (see http://www.sommarskog.se/strict_checks.html#nodefaultlength).

======================

Listing output parameters last

My habit is to list OUTPUT parameters last. I am not sure why that is exactly, except that it is the order that I conceptually think about the parameters... in then out, not the other way around.

======================

Using BEGIN / END liberally

I have seen many people write stuff like this:

CREATE PROCEDURE dbo.ProcedureA
AS
   SELECT * FROM foo;
   GO
   SELECT * FROM bar;
GO

They create the procedure, maybe don't notice the extra resultset from bar (or shrug it off), and then wonder why they only get results from foo when they run the procedure. If they had done this:

CREATE PROCEDURE dbo.ProcedureA
AS
BEGIN
   SELECT * FROM foo;
   GO
   SELECT * FROM bar;
END
GO

Because GO is not a T-SQL keyword but rather a batch separator for tools like Query Analyzer and SSMS, they would have received these error messages, one from each batch:

Msg 102, Level 15, State 1, Procedure ProcedureA, Line 4
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.

Yes, errors are bad, and all that, but I would rather have this brought to my face when I try to compile the procedure, then later on when the first user tries to call it.

======================

Using statement terminators

I have quickly adapted to the habit of ending all statements with proper statement terminators (;). This was always a habit in languages like JavaScript (where it is optional) and C# (where it is not). But as T-SQL gets more and more extensions (e.g. CTEs) that require it, I see it becoming a requirement eventually. Maybe I won't even be working with SQL Server by the time that happens, but if I am, I'll be ready. It's one extra keystroke and guarantees that my code will be forward-compatible.

======================

Using SET NOCOUNT ON

I always add SET NOCOUNT ON; as the very first line of the procedure (after BEGIN of course). This prevents DONE_IN_PROC messages from needlessly being sent back to the client after every row-affecting statement, which increases network traffic and in many cases can fool applications into believing there is an additional recordset available for consumption.

NOTE

wrote about it here

======================

Using local variables

When possible, I always use a single DECLARE statement to initialize all of my local variables. Similarly, I try to use a single SELECT to apply values to those variables that are being used like local constants. I see code like this:

declare @foo int
declare @bar int
declare @x int
set @foo = 5
set @bar = 6
set @x = -1

And then some more declare and set statements later on in the code. I find it much harder to track down variables in longer and more complex procedures when the declaration and/or assignments can happen anywhere... I would much rather have as much of this as possible occurring in the beginning of the code. So for the above I would rather see:

DECLARE 
   @foo    INT,
   @bar    INT,
   @x      INT;

SELECT
   @foo    = 5,
   @bar    = 6,
   @x      = -1;

As a bonus, in SQL Server 2008, the syntax now supports changing the above into a single statement:

DECLARE
   @foo    INT = 5,
   @bar    INT = 6,
   @x      INT = -1;

So much nicer. However, it still leaves a lot to be desired: I also always use meaningful variables names, rather than @i, @x, etc.

Also, some people like listing the commas at the beginning of each new line, e.g.:

DECLARE
   @foo    INT = 5
   ,@bar   INT = 6
   ,@x     INT = -1;

Not just in variable declarations, but also in parameter lists, columns lists, etc. While I will agree that this makes it easier to comment out individual lines in single steps, I find the readability suffers greatly.

======================

Using table aliases

I use aliases a lot. Nobody wants to read (never mind type) this, even though I have seen *many* examples of it posted to the public SQL Server newsgroups:

SELECT 
   dbo.table_X_with_long_name.column1,
   dbo.table_X_with_long_name.column2,
   dbo.table_X_with_long_name.column3,
   dbo.table_X_with_long_name.column4,
   dbo.table_X_with_long_name.column5,
   dbo.table_H_with_long_name.column1,
   dbo.table_H_with_long_name.column2,
   dbo.table_H_with_long_name.column3,
   dbo.table_H_with_long_name.column4
FROM
   dbo.table_X_with_long_name
INNER JOIN
   dbo.table_H_with_long_name
ON
   dbo.table_X_with_long_name.column1 = dbo.table_H_with_long_name.column1
   OR dbo.table_X_with_long_name.column1 = dbo.table_H_with_long_name.column1
   OR dbo.table_X_with_long_name.column1 = dbo.table_H_with_long_name.column1
WHERE
   dbo.table_X_with_long_name.column1 >= 5
   AND dbo.table_X_with_long_name.column1 < 10;

But as long as you alias sensibly, you can make this a much more readable query:

SELECT 
   X.column1,
   X.column2,
   X.column3,
   X.column4,
   X.column5,
   H.column1,
   H.column2,
   H.column3,
   H.column4
FROM
   dbo.table_X_with_long_name AS X
INNER JOIN
   dbo.table_H_with_long_name AS H
ON
   X.column1 = H.column1
   OR X.column2 = H.column2
   OR X.column3 = H.column3
WHERE
   X.column1 >= 5
   AND X.column1 < 10;

The "AS" when aliasing tables is optional; I have been trying very hard to make myself use it (only because the standard defines it that way). When writing multi-table queries, I don't give tables meaningless shorthand like a, b, c or t1, t2, t3. This might fly for simple queries, but if the query becomes more complex, you will regret it when you have to go back and edit it.

======================

Using column aliases

I buck against the trend here. A lot of people prefer to alias expressions / columns using this syntax:

SELECT [column expression] AS alias

I much prefer:

SELECT alias = [column expression]

The reason is that all of my column names are listed down the left hand side of the column list, instead of being at the end. It is much easier to scan column names when they are vertically aligned.

In addition, I always use column aliases for expressions, even if right now I don't need to reference the column by an alias. This prevents me from having to deal with multiple errors should I ever need to move the query into a subquery, or cte, or derived table, etc.

======================

Using consistent formatting

I am very fussy (some co-workers use a different word) about formatting. I like my queries to be consistently readable and laid out in a predictable way. So for a join that includes a CTE and a subquery, this is how it would look:

WITH cte AS 
(
    SELECT 
         t.col1,
         t.col2,
         t.col3
    FROM
         dbo.sometable AS t
)
SELECT
    cte.col1,
    cte.col2,
    cte.col3,
    c.col4
FROM
    cte
INNER JOIN
    dbo.Customers AS c
    ON c.CustomerID = cte.col1
WHERE EXISTS
(
    SELECT 1
        FROM dbo.Orders o
        WHERE o.CustomerID = c.CustomerID
)
AND c.Status = 'LIVE';

Keeping all of the columns in a nice vertical line, and visually separating each table in the join and each where clause. Inside a subquery or derived table, I am less strict about the visual separation, though I still put each fundamental portion on its own line. And I always use SELECT 1 in this type of EXISTS() clause, instead of SELECT * or SELECT COUNT(*), to make it immediately clear to others that the query inside does NOT retrieve data.

======================

Matching case of underlying objects / columns

I always try to match the case of the underlying object, as I can never be too certain that my application will always be on a case-sensitive collation. Going back and correcting the case throughout all of my modules will be a royal pain, at best. This is much easier if you are using SQL Server 2008 Management Studio against a SQL Server 2008 instance, or have invested in Red-Gate's SQL Prompt, as you will automatically get the correct case when selecting from the auto-complete list.

======================

Qualifying column names with table/alias prefix

I always qualify column names when there is more than one table in the query. Heck, sometimes I even use aliases when there is only one table in the query, to ease my maintenance later should the query become more complex. I won't harp on this too much, as fellow MVP Alex Kuznetsov treated this subject a few days ago.

======================

Using RETURN and OUTPUT appropriately

I never use RETURN to provide any data back to the client (e.g. the SCOPE_IDENTITY() value or @@ROWCOUNT). This should be used exclusively for returning stored procedure status, such as ERROR_NUMBER() / @@ERROR. If you need to return data to the caller, use a resultset or an OUTPUT parameter.

======================

Avoiding keyword shorthands

I always use full keywords as opposed to their shorthand equivalents. "BEGIN TRAN" and "CREATE PROC" might save me a few keystrokes, and I'm sure the shorthand equivalents are here to stay, but something just doesn't feel right about it. Same with the parameters for built-in functions like DATEDIFF(), DATEADD() and DATEPART(). Why use WK or DW when you can use WEEK or WEEKDAY? (I also never understood why WEEKDAY become DW in shorthand, instead of WD, which is not supported. DW likely means DAYOFWEEK but that is an ODBC function and not supported directly in T-SQL at all. That in and of itself convinced me that it is better to take the expensive hit of typing five extra characters to be explicit and clear.) Finally, I always explicitly say "INNER JOIN or "LEFT OUTER JOIN"... never just "join" or "left join." Again, no real good reason behind that, just habit.

======================

Using parentheses liberally around AND / OR blocks

I always group my clauses when mixing AND and OR. Leaving it up to the optimizer to determine what "x=5 AND y = 4 OR b = 3" really means is not my cup of tea. I wrote a very short article about this a few years ago.

======================

So, after all of that, given the procedure I listed at the start of the article, what would I end up with? Assuming I am using SQL Server 2008, and that I can update the calling application to use the right procedure name, to use sensible input parameter names, and to stop using return values instead of output parameters:

CREATE PROCEDURE dbo.Customer_GetOlderOrders
   @OrderStatus        INT,
   @MaxOrderStatus     INT = NULL,
   @OrderDate          SMALLDATETIME,
   @RC1                INT OUTPUT,
   @RC2                INT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
       @City           VARCHAR(32) = 'Beverly',
       @State          CHAR (2)    = 'MA',
       @MinOrderCount  INT         = 5;

   SELECT
       c.CustomerID,
       c.FirstName,
       c.LastName,
       c.OrderDate
   FROM
       dbo.Customers c
   INNER JOIN
       dbo.Orders o
       ON c.CustomerID = o.CustomerID
   WHERE   
       (
           o.OrderStatus       = @OrderStatus
           OR o.OrderStatus    <= @MaxOrderStatus
       )
       AND o.OrderDate         <= @MaxOrderDate;

   SET @RC1 = @@ROWCOUNT;

   SELECT
       c.CustomerID,
       OrderCount = COUNT(*)
   FROM
       dbo.Customers c
   LEFT OUTER JOIN
       dbo.Orders o
       ON c.CustomerID = o.CustomerID
   WHERE
       c.City = @City
       AND c.State = @State
   GROUP BY
       c.CustomerID
   HAVING
       COUNT(*) >= @MinOrderCount;

   SET @RC2 = @@ROWCOUNT;

   RETURN;
END
GO

Okay, so it LOOKS like a lot more code, because the layout is more vertical. But you tell me. Copy both procedures to SSMS or Query Analyzer, and which one is easier to read / understand? And is it worth the three minutes it took me to convert the original query? It took me a few hours to convert this list from my subconscious to you, so hopefully I have helped you pick up at least one good habit. And if you think any of these are BAD habits, please drop a line and let me know why!

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!

Nondeterministic UPDATE

Linchi Shea — Tue, 13 Nov 2007 19:15:00 GMT

Under the description for UPDATE in SQL Server 2000 and 2005 Books Online, you can find the following statement (thanks to SQL Server MVP Steve Kass for pointing me to this passage):

The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

BOL goes on to give a simple example of such an UPDATE. However, the example doesn't give you a real feel for the nondeterministic behavior. Although the result is supposed to be undefined and indeed you don't know what value UPDATE will end up assigning, the result does come out consistently the same no matter how many times you execute it. At least during my limited number of test runs, UPDATE seems to always pick the first value. But of course you can't count on that because a different execution plan may pick a different value.

Still, it would be nice to see a nondeterministic UPDATE statement actually picks a different value to drive it home that you should not use it in your app, unless of course your app logic wants to take advantage of this particular non-determinism (which is difficult to imagine).

Recently, however, I happened to run into an interesting case where the nondeterministic behavior of UPDATE can be easily reproduced on a multi-core server when query parallelism is used. Here's the script to see the behavior.

First, run the following script to create two tables and a stored procedure in any user database on a SQL Server 2005 instance:

CREATE TABLE t1 (
	c1 int         NULL,
	c2 varchar(50) NULL,
	rate int       NULL
)
go
CREATE INDEX c1_ind ON t1 (c1)
go

CREATE TABLE t2 (
	c1 int           NULL,
	c2 varchar(50)   NULL,
	rate int         NOT NULL
)
go

drop proc testProc
go
create proc testProc
as
set nocount on
begin tran
  UPDATE t1
     SET rate = t2.rate
    FROM t1 With (Index (c1_ind)), t2
   WHERE t1.c1 = t2.c1
     and t1.c2 = t2.c2

  SELECT 'count_0' = count(*) FROM t1 WHERE rate = 0;
  SELECT 'count_1' = count(*) FROM t1 WHERE rate = 1;
  SELECT 'rate_sum' = sum(rate) FROM t1
rollback tran
go

Second, populate the two tables with data using the following script:

WITH tmp (c1, c2,c3) as (
    SELECT 1,1,1
    UNION ALL
    SELECT cast(rand(c3)*1000000 as int)%200, 
           cast(rand(c3)*1000000 as int)%200, 
           c3 + 1
      FROM tmp
     WHERE c3 < 100000
)
INSERT t1
SELECT c1, CAST(c2 as varchar(50)), NULL
  FROM tmp
OPTION (maxrecursion 0);

WITH tmp (c1, c2, c3) as (
    SELECT 0,0,0
    UNION ALL
    SELECT c1 + 1, c2 + 1, c3 + 1
      FROM tmp
     WHERE c1 < 200
)
INSERT t2
SELECT cast(c1 as char(6)), 
       cast(c2 as varchar(50)), 
       0                         -- c3 value = 0
  FROM tmp
OPTION (maxrecursion 0);

-- give each c1 value a second c3 value = 1
INSERT t2
SELECT c1, c2, 1
  FROM t2;
go

Finally, run the stored procedure multiple times to see that different results are produced:

EXEC testProc;

Apparently, nondeterminism in query processing gives rise to the different results. A few observations are noteworthy:

The non-determinstic behavior is not a result of different query execution plan. At least, the reported execution plan remains the same for all my test runs.
If you disable query parallelism by setting max degree of parallelism to 1, the stored procedure will produce the same results.
On a SQL Server 2000 instance, you probably won't get a parallel plan, and thus won't see different results.
The size of the table t1 is significant. On my test server, populating table t1 with 70,000 still leads to a parallel query plan, thus different results in multiple executions of the stored procedure. But if I only populate the table with 50,000 rows, I don't get an query parallelism, and I get consistent results. So SQL Server query optimizer is sensitive to the table size in deciding whether to use a parallel plan.

T-SQL Cursors -- the Case of the Published TPC-E Tests

Linchi Shea — Tue, 09 Oct 2007 03:39:00 GMT

Within the SQL Server community, there is so much publicity on T-SQL set-oriented processing as good practice and the use of T-SQL cursors as bad practice that T-SQL cursors are effectively being seen as a plague to be avoided when in fact it's not the use of T-SQ cursors, but their misuse, that should be avoided. To be fair, I must say that I have seen set-minded folks coming out to exhibit a more balanced view.

I'm not here to engage in a new debate on the pros and cons of T-SQL cursors or when they should be used. Such a debate is necessarily a contentious one at the best, and the voice for the set-oriented approach is so loud that any other arguments or suggestions risk getting drowned out.

What I do want to highlight in this post is that T-SQL cursors are used heavily in all the three TPC-E test results published to date (see www.tpc.org). As of 2007/10/09, all the published TPC-E official tests were obtained with SQL Server 2005 X64 edition and with essentially the same database schema. If you look at the full disclosure reports for these TPC-E results (available at www.tpc.org), you'll find the stored procedures that were written to implement the TPC-E transactions, and of the 26 stored procedures, nine used T-SQL cursors.

Of course, the fact that these TPC-E implementations used T-SQL cursors doesn't mean that T-SQL cursors should be generally recommended. But these published TPC-E results do serve as a few data points to highlight the traps of such unqualified statement as "avoid using cursors."

Yeah, the fact that these TPC-E implementations used T-SQL cursors doesn't even mean cursors are the best possible solution for their business logic. However, note that TPC benchmarking is a highly competitive business. Significant resources are being invested by the test sponsors and the DBMS vendors to get the best performance numbers for their respective systems. You can safely assume that whatever design and configurations that are used in a published TPC benchmark are not there by accident, but are deliberately chosen after many alternative designs and configurations have been explored, considered, and tested.

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!