Runtime Control over Queries

Unlike stored procedures, which are prebuilt and stored in the SQL Server system tables, ad hoc SQL is formed at the time it’s needed: at runtime. Hence, it doesn’t suffer from some of the inflexible requirements of stored procedures. For example, say you want to build a user interface to a list of customers. You can add several columns to the SELECT clause, based on the tables listed in the FROM clause. It’s simple to build a list of columns into the user interface that the user can use to customize his or her own list. Then the program can issue the list request with only the columns in the SELECT list that are requested by the user. Because some columns might be large and contain quite a bit of data, it’s better to send back only the columns that the user really desires instead of also including a bunch of columns the user doesn’t care about.

For instance, consider that you have the following table to document contacts to prospective customers (it’s barebones for this example). In each query, you might return the primary key but show or not show it to the user based on whether the primary key is implemented as a surrogate or natural key—it isn’t important to our example either way. You can create this table in any database you like. In the sample code, I’ve created a database named architectureChapter.

 CREATE SCHEMA sales;

 GO

 CREATE TABLE sales.contact

 (

   contactId int CONSTRAINT PKsales_contact PRIMARY KEY,

   firstName varchar(30),

   lastName varchar(30),

   companyName varchar(100),

   salesLevelId int, --real table would implement as a foreign key

   contactNotes varchar(max),

   CONSTRAINT AKsales_contact UNIQUE (firstName, lastName, companyName)

 );

 --a few rows to show some output from queries

 INSERT INTO sales.contact

   (contactId, firstName, lastname, companyName, saleslevelId, contactNotes)

 VALUES( 1,'Drue','Karry','SeeBeeEss',1,

           REPLICATE ('Blah…',10) + 'Called and discussed new ideas'),

       ( 2,'Jon','Rettre','Daughter Inc',2,

           REPLICATE ('Yada…',10) + 'Called, but he had passed on'),

One user might want to see the person’s name and the company, plus the end of the contactNotes, in his or her view of the data:

 SELECT contactId, firstName, lastName, companyName,

        RIGHT(contactNotes,30) AS notesEnd

 FROM sales.contact;

So something like:

Another user might want (or need) to see less:

 SELECT  contactId, firstName, lastName, companyName

 FROM    sales.contact;

Which returns:

And yet another may want to see all columns in the table, plus maybe some additional information. Allowing the user to choose the columns for output can be useful. Consider how the file-listing dialog works in Windows, as shown in Figure 13-1.

You can see as many or as few of the attributes of a file in the list as you like, based on some metadata you set on the directory. This is a useful method of letting the users choose what they want to see. Let’s take this one step further. Consider that the contact table is then related to a table that tells us if a contact has purchased something:

 CREATE TABLE sales.purchase

 (

    purchaseId int CONSTRAINT PKsales_purchase PRIMARY KEY,

    amount numeric(10,2),

    purchaseDate date,

    contactId int

    CONSTRAINT FKsales_contact$hasPurchasesIn$sales_purchase

    REFERENCES sales.contact(contactId)

 );

 INSERT INTO sales.purchase(purchaseId, amount, purchaseDate, contactId)

 VALUES (1,100.00,'2012-05-12',1),(2,200.00,'2012-05-10',1),

        (3,100.00,'2012-05-12',2),(4,300.00,'2012-05-12',1),

        (5,100.00,'2012-04-11',1),(6,5500.00,'2012-05-14',2),

        (7,100.00,'2012-04-01',1),(8,1020.00,'2012-06-03',2);

Now consider that you want to calculate the sales totals and dates for the contact and add these columns to the allowed pool of choices. By tailoring the output when transmitting the results of the query back to the user, you can save bandwidth, CPU, and disk I/O. As I’ve stressed, values such as this should usually be calculated rather than stored, especially when working on an OLTP system.

In this case, consider the following two possibilities. If the user asks for a sales summary column, the client will send the whole query:

 SELECT contact.contactId, contact.firstName, contact.lastName,

                sales.yearToDateSales, sales.lastSaleDate

 FROM sales.contact AS contact

      LEFT OUTER JOIN

         (SELECT contactId,

                 SUM(amount) AS yearToDateSales,

                 MAX(purchaseDate) AS lastSaleDate

         FROM sales.purchase

         WHERE purchaseDate >= --the first day of the current year

                 DATEADD(day, 0, DATEDIFF(day, 0, SYSDATETIME() )

                    - DATEPART(dayofYear,SYSDATETIME() ) + 1)

         GROUP BY contactId) AS sales

         ON contact.contactId = sales.contactId

 WHERE contact.lastName Like 'Rett%';

Which returns:

If the user doesn’t ask for a sales summary column, the client will send only the bolded query:

 SELECT contact.contactId, contact.firstName, contact.lastName

           -- ,sales.yearToDateSales, sales.lastSaleDate

 FROM sales.contact AS contact

          --LEFT OUTER JOIN

          --  (SELECT contactId,

          --          SUM(amount) AS yearToDateSales,

          --          MAX(purchaseDate) AS lastSaleDate

          -- FROM   sales.purchase

          -- WHERE  purchaseDate >= --the first day of the current year

          --             DATEADD(Day, 0, DATEDIFF(Day, 0, SYSDATETIME() )

          --                 -DATEPART(dayofyear,SYSDATETIME() ) + 1)

          -- GROUP  by contactId) AS sales

          -- ON  contact.contactId = sales.contactId

 WHERE contact.lastName Like 'Karr%';

Which only returns:

Not wasting the resources to do calculations that aren’t needed can save a lot of system resources if the aggregates in the derived table were very costly to execute

In the same vein, when using ad hoc calls, it’s trivial (from a SQL standpoint) to build UPDATE statements that include only the columns that have changed in the set lists, rather than updating all columns, as can be necessary for a stored procedure. For example, take the customer columns from earlier: customerId, name, and number. You could just update all columns:

 UPDATE  sales.contact

 SET     firstName = 'Drew',

         lastName = 'Carey',

         salesLevelId = 1, --no change

         companyName = 'CBS',

         contactNotes = 'Blah…Blah…Blah…Blah…Blah…Blah…Blah…Blah…Blah…'

                         + 'Blah…Called and discussed new ideas' --no change

 WHERE contactId = 1;

But what if only the firstName and lastName columns change? What if the company column is part of an index, and it has data validations that take three seconds to execute? How do you deal with varchar(max) columns (or other long types)? Say the notes columns for contactId = 1 contain 3 MB each. Execution could take far more time than is desirable if the application passes the entire value back and forth each time. Using ad hoc SQL, to update the firstName column only, you can simply execute the following code:

 UPDATE  sales.contact

 SET     firstName = 'John',

         lastName = 'Ritter'

 WHERE   contactId = 1;

Some of this can be done with dynamic SQL calls built into the stored procedure, but it’s far easier to know if data changed right at the source where the data is being edited, rather than having to check the data beforehand. For example, you could have every data-bound control implement a “data changed” property, and perform a column update only when the original value doesn’t match the value currently displayed. In a stored-procedure-only architecture, having multiple update procedures is not necessarily out of the question, particularly when it is very costly to modify a given column.

One place where using ad hoc SQL can produce more reasonable code is in the area of optional parameters. Say that, in your query to the sales.contact table, your UI allowed you to filter on either firstName, lastName, or both. For example, take the following code to filter on both firstName and lastName:

 SELECT   firstName, lastName, companyName

 FROM     sales.contact

 WHERE    firstName Like 'J%'

   AND lastName Like 'R%';

What if the user only needed to filter by last name? Sending the '%' wildcard for firstName can cause code to perform less than adequately, especially when the query is parameterized. (I’ll cover query parameterization in the next section, “Performance.”)

 SELECT   firstName, lastName, companyName

 FROM     sales.contact

 WHERE    firstName LIKE '%'

   AND lastName LIKE 'Carey%';

If you think this looks like a very silly query to execute, you are right. If you were writing this query, you would write the more logical version of this query, without the superfluous condition:

 SELECT   firstName, lastName, companyName

 FROM     sales.contact

 WHERE    lastName LIKE 'Carey%';

This doesn’t require any difficult coding. Just remove one of the criteria from the WHERE clause, and the optimizer needn’t consider the other. What if you want to OR the criteria instead? Simply build the query with OR instead of AND. This kind of flexibility is one of the biggest positives to using ad hoc SQL calls.

For a stored procedure, you might need to write code that functionally works in a manner such as the following:

 IF @firstNameValue <> '%'

     SELECT firstName, lastName, companyName

     FROM   sales.contact

     WHERE  firstName LIKE @firstNameValue

       AND  lastName LIKE @lastNameValue;

 ELSE

     SELECT firstName, lastName, companyName

     FROM   sales.contact

     WHERE  lastName LIKE @lastNameValue;

Or do something messy like this in your WHERE clause so if there is any value passed in it uses it, or uses '%' otherwise:

 WHERE Firstname Like isnull(nullif(ltrim(@FirstNamevalue) +'%','%'),Firstname)

   and Lastname Like isnull(nullif(ltrim(@LastNamevalue) +'%','%'),Lastname)

Unfortunately though, this often does not optimize very well because the optimizer has a hard time optimizing for factors that can change based on different values of a variable—leading to the need for the branching solution mentioned previously to optimize for specific parameter cases. A better way to do this with stored procedures might be to create two stored procedures—one with the first query and another with the second query—especially if you need extremely high performance access to the data. You’d change this to the following code:

 IF @firstNameValue <> '%'

        EXECUTE sales.contact$get @firstNameValue, @lastNameValue;

 ELSE

        EXECUTE sales.contact$getLastOnly @lastNameValue;

You can do some of this kind of ad hoc SQL writing using dynamic SQL in stored procedures. However, you might have to do a good bit of these sorts of IF blocks to arrive at which parameters aren’t applicable in various datatypes. Because you know which parameters are applicable, due to knowing what the user filled in, it can be far easier to handle this situation using ad hoc SQL. Getting this kind of flexibility is the main reason that I use an ad hoc SQL call in an application (usually embedded in a stored procedure): you can omit parts of queries that don’t make sense in some cases, and it’s easier to avoid executing unnecessary code.

Flexibility over Shared Plans and Parameterization

Queries formed at runtime, using proper techniques, can actually be better for performance in many ways than using stored procedures. Because you have control over the queries, you can more easily build queries at runtime that use the same plans, and even can be parameterized as desired, based on the situation.

This is not to say that it is the most favorable way of implementing parameterization. (If you want to know the whole picture you have to read the whole section on ad hoc and stored procedures.) However, the fact is that ad hoc access tends to get a bad reputation for something that Microsoft fixed several versions back. In the following sections, Shared Execution Plans and Parameterization, I will take a look at the good points and the caveats you will deal with when building ad hoc queries and executing them on the server.

Low Cohesion, High Coupling

The number-one pitfall of using ad hoc SQL as your interface relates to what you learned back in Programming 101: strive for high cohesion, low coupling. Cohesion means that the different parts of the system work together to form a meaningful unit. This is a good thing, as you don’t want to include lots of irrelevant code in the system, or be all over the place. On the other hand, coupling refers to how connected the different parts of a system are to one another. It’s considered bad when a change in one part of a system breaks other parts of a system. (If you aren’t too familiar with these terms, I suggest you go to www.wikipedia.org and search for these terms. You should build all the code you create with these concepts in mind.)

When issuing T-SQL statements directly from the application, the structures in the database are tied directly to the client interface. This sounds perfectly normal and acceptable at the beginning of a project, but it means that any change in database structure might require a change in the user interface. This makes making small changes to the system just as costly as large ones, because a full testing cycle is required.

For example, consider that you’ve created an employee table, and you’re storing the employee’s spouse’s name, as shown in Figure 13-2.

Now, some new regulation requires that you have to include the ability to have more than one spouse, which necessitates a new table, as shown in Figure 13-3.

The user interface must immediately be morphed to deal with this case, or at the least, you need to add some code to abstract this new way of storing data. In a scenario such as this, where the condition is quite rare (certainly most everyone will have zero or one spouse), a likely solution would be simply to encapsulate the one spouse into the employee table via a view, change the name of the object the non-data tier accesses, and the existing UI would still work. Then you can add support for the atypical case with some sort of otherSpouse functionality that would be used if the employee had more than one spouse. The original UI would continue to work, but a new form would be built for the case where COUNT(spouse) > 1.

Batches of More Than One Statement

A major problem with ad hoc SQL access is that when you need to do multiple commands and treat them as a single operation, it becomes increasingly more difficult to build the mechanisms in the application code to execute multiple statements as a batch, particularly when you need to group statements together in a transaction. When you have only individual statements, it’s easy to manage ad hoc SQL for the most part. Some queries can get mighty complicated and difficult, but generally speaking, things pretty much work great when you have single statements per transaction. However, as complexity rises in the things you need to accomplish in a transaction, things get tougher. What about the case where you have 20 rows to insert, update, and/or delete at one time and all in one transaction?

Two different things usually occur in this case. The first way to deal with this situation is to start a transaction using functional code. For the most part, as discussed in Chapter 10, the best practice was stated as never to let transactions span batches, and you should minimize starting transactions using an ADO.NET object (or something like it). This isn’t a hard and fast rule, as it’s usually fine to do this with a middle-tier object that requires no user interaction. However, if something occurs during the execution of the object, you can still leave open connections and transactions to the server.

The second way to deal with this is to build a batching mechanism for batching SQL calls. Implementing the first method is self explanatory, but the second is to build a code-wrapping mechanism, such as the following:

 BEGIN TRY

 BEGIN TRANSACTION;

    <-- statements go here

 COMMIT TRANSACTION;

 END TRY

 BEGIN CATCH

    ROLLBACK TRANSACTION;

    THROW 50000, '<describe what happened>',1;

 END CATCH

For example, if you wanted to send a new invoice and line items, the application code would need to build a batch such as in the following code. Each of the SET @Action = … and INSERT statements would be put into the batch by the application with the rest being boilerplate code that is repeatable:

 SET NOCOUNT ON;

 BEGIN TRY

 BEGIN TRANSACTION;

       DECLARE @Action nvarchar(200);

       SET @Action = 'Invoice Insert';

       INSERT invoice (columns) values (values);

       SET @Action = 'First InvoiceLineItem Insert';

       INSERT invoiceLineItem (columns) values (values);

       SET @Action = 'Second InvoiceLineItem Insert';

       INSERT invoiceLineItem (columns) values (values);

       SET @Action = 'Third InvoiceLineItem Insert';

       INSERT invoiceLineItem (columns) values (values);

 COMMIT TRANSACTION;

 END TRY

 BEGIN CATCH

    ROLLBACK TRANSACTION;

    DECLARE @Msg nvarchar(4000);

    SET @Msg = @Action + ': Error was: ' + CAST(ERROR_NUMBER() as varchar(10)) + ':' +

                   ERROR_MESSAGE ();

    THROW 50000, @Msg,1 ;

 END CATCH

Executing multiple statements in a transaction is done on the server and the transaction either completes or not. There’s no chance that you’ll end up with a transaction swinging in the wind, ready to block the next user who needs to access the locked row (even in a table scan for unrelated items). A downside is that it does stop you from using any interim values from your statements (without some really tricky coding/forethought), but starting transactions outside of the batch you commit them is asking for blocking and locking due to longer batch times.

Starting the transaction outside of the server using application code is likely easier, but building this sort of batching interface is always the preferred way to go. First, it’s better for concurrency, because only one batch needs to be executed instead of many little ones. Second, the execution of this batch won’t have to wait on communications back and forth from the server before sending the next command. It’s all there and happens in the single batch of statements.

Security Issues

Security is one of the biggest downsides to using ad hoc access. For a user to use his or her own Windows login to access the server, you have to grant too many rights to the system, whereas with stored procedures, you can simply give access to the stored procedures. Using ad hoc T-SQL, you have to go with one of three possible security patterns, each with their own downsides:

• Use one login for the application: This, of course, means that you have to code your own security system rather than using what SQL Server gives you. This even includes some form of login and password for application access, as well as individual object access.
• Use application roles: This is slightly better: while you have to implement security in your code since all application users will have the same database security, at the very least, you can let SQL server handle the data access via normal logins and passwords (probably using Windows authentication). Using application roles can be better way to give a user multiple sets of permissions as having only the one password for users to log in with usually avoids the mass of sticky notes embossed in bold letters all around the office: payroll system ID: fred, password: fredsisawesome!
• Give the user direct access to the tables, or possibly views: This unfortunately opens up your tables to users who discover the magical world of Management Studio, where they can open a table and immediately start editing it without any of those pesky UI data checks or business rules that only exist in your data access layer that I warned you about.

Usually, almost all applications follow the first of the three methods. Building your own security mechanisms is just considered a part of the process, and just about as often, it is considered part of the user interface’s responsibilities. At the very least, by not giving all users direct access to the tables, the likelihood of them mucking around in the tables editing data all willy-nilly is greatly minimized. With procedures, you can give the users access to stored procedures, which are not natural for them to use and certainly would not allow them to accidentally delete data from a table.

The other issues security-wise are basically performance related. SQL Server must evaluate security for every object as it’s used, rather than once at the object level for stored procedures—that is, if the owner of the procedure owns all objects. This isn’t generally a big issue, but as your need for greater concurrency increases, everything becomes an issue!

SQL Injection

A big issue with ad hoc query being hacked by a SQL injection attack. Unless you (and/or your toolset) program your ad hoc SQL intelligently and/or (mostly and) use the parameterizing methods we discussed earlier in this section on ad hoc SQL, a user could inject something such as the following:

 ' + char(13) + char(10) + ';SHUTDOWN WITH NOWAIT;' + '--'

In this case, the command might just shut down the server if the user has rights, but you can probably see far greater attack possibilities. I’ll discuss more about injection attacks and how to avoid them in the “Stored Procedures” section. When using ad hoc SQL, you must be careful to avoid these types of issues for every call.

A SQL injection attack is not terribly hard to beat, but the fact is, for any use enterable text where you don’t use some form of parameterization, you have to make sure to escape any single quote characters that a user passes in. For general text entry, like a name, commonly if the user passes in a string like “O'Malley”, you know to change this to 'O''Malley'. For example, consider the following batch, where the resulting query will fail. (I have to escape the single quote in the literal to allow the query to execute.)

 DECLARE @value varchar(30) = 'O''Malley';

 SELECT 'SELECT '''+ @value + '''';

 EXECUTE ('SELECT '''+ @value + ''''),

This will return:

This is a very common problem that arises, and all too often the result is that the user learns not to enter a single quote in the query parameter value. But you can make sure this doesn’t occur by changing all single quotes in the value to double single quotes, like in the DECLARE @value statement:

 DECLARE @value varchar(30) = 'O''Malley', @query nvarchar(300);

 SELECT @query = 'SELECT ' + QUOTENAME(@value,''''),

 SELECT @query;

 EXECUTE (@query );

This returns:

Now, if someone tries to put a single quote, semicolon, and some other statement in the value, it doesn’t matter; it will always be treated as a literal string:

 DECLARE  @value varchar(30) = 'O''; SELECT ''badness',

          @query nvarchar(300);

 SELECT   @query = 'SELECT ' + QUOTENAME(@value,''''),

 SELECT   @query;

 EXECUTE  (@query );

The query is now, followed by the return:

However, what isn’t quite so obvious is that you have to do this for every single string, even if the string value could never legally (due to application and constraints) have a single quote in it. If you don’t double up on the quotes, the person could put in a single quote and then a string of SQL commands—this is where you get hit by the injection attack. And as I said before, the safest method of avoiding injection issues is to always parameterize your queries; though it is very hard to use a variable query conditions using the parameterized method, losing some of the value of using ad hoc SQL.

 DECLARE @value varchar(30) = 'O''; SELECT ''badness',

         @query nvarchar(300),

        @parameters nvarchar(200) = N'@value varchar(30)';

 SELECT  @query = 'SELECT ' + QUOTENAME(@value,''''),

 SELECT  @query;

 EXECUTE sp_executesql @Query, @Parameters, @value = @value;

If you employ ad hoc SQL in your applications, I strongly suggest you do more reading on the subject of SQL injection, and then go in and look at the places where SQL commands can be sent to the server to make sure you are covered. SQL injection is especially dangerous if the accounts being used by your application have too much power because you didn’t set up particularly granular security.

Note that if all of this talk of parameterization sounds complicated, it kind of is. Generally there are two ways that parameterization happens well. Either by building a framework that forces you to follow the correct pattern (as do most object-relational tools, thought they often force (or at least lead) you into sub-optimal patterns of execution, like dealing with every statement separately without transactions.) The second way is stored procedures. Stored procedures parameterize in a manner that is impervious to SQL injection (except, when you use dynamic SQL, which, as I will discuss later, is subject to the same issues as ad hoc access from any client.)

Difficulty Tuning for Performance

Performance tuning is generally far more difficult when having to deal with ad hoc requests, for a few reasons:

• Unknown queries: The application can be programmed to send any query it wants, in any way. Unless very extensive testing is done, slow or dangerous scenarios can slip through. With procedures, you have a tidy catalog of possible queries that might be executed. Of course, this concern can be mitigated by having a single module where SQL code can be created and a method to list all possible queries that the application layer can execute (however, that takes more discipline than most organizations have.).
• Often requires people from multiple teams: It may seem silly, but when something is running slower, it is always blamed on SQL Server first. With ad hoc calls, the best thing that the database administrator can do is use a profiler to capture the query that is executed, see if an index could help, and call for a programmer, leading to the issue in the next bullet.
• Recompile required for changing queries: If you want to add a tip to a query to use an index, a rebuild and a redeploy are required. For stored procedures, you’d simply modify the query without the client knowing.

These reasons seem small during the development phase, but often they’re the real killers for tuning, especially when you get a third-party application and its developers have implemented a dumb query that you could easily optimize, but since the code is hard coded in the application, modification of the query isn’t possible (not that this regularly happens; no, not at all; nudge, nudge, wink, wink).

SQL Server 2005 gave us plan guides (and there are some improvements in their usability in later versions) that can be used to force a plan for queries, ad hoc calls, and procedures when you have troublesome queries that don’t optimize naturally, but the fact is, going in and editing a query to tune is far easier than using plan guides.

Parameterization of Complex Plans

Stored procedures, unlike ad hoc SQL, always have parameterized plans for maximum reuse of the plans. This lets you avoid the cost of recompilation, as well as the advanced costs of looking for parameters in the code. Any literals are always literal, and any variable is always a parameter. This can lead to some performance issues as well, as occasionally the plan for a stored procedure that gets picked by the optimizer might not be as good of a plan as might be picked for an ad hoc procedure.

The interesting thing here is that, although you can save the plan of a single query with ad hoc calls, with procedures you can save the plan for a large number of statements. With all the join types, possible tables, indexes, view text expansions, and so on, optimizing a query is a nontrivial task that might take quite a few milliseconds. Now, admittedly, when building a single-user application, you might say, “Who cares?” However, as user counts go up, the amount of time begins to add up. With stored procedures, this has to be done only once. (Or perhaps a bit more frequently; SQL Server can create multiple copies of the plan if the procedure is heavily used.)

Stored procedure parameterization uses the variables that you pass the first time the procedure is called to create the best plan. This process is known as parameter sniffing. This is fine and dandy, except when you have a situation where you have some values that will work nicely for a query but others that work pitifully. Two different values that are being searched for can end up creating two different plans. Often, this is where you might pass in a value that tells the query that there are no values, and SQL Server uses that value to build the plan. When you pass in a real value, it takes far too long to execute. Using WITH RECOMPILE at the object level or the RECOMPILE statement-level hint can avoid the problems of parameter sniffing, but then you have to wait for the plan to be created for each execute, which can be costly. It’s possible to branch the code out to allow for both cases, but this can get costly if you have a couple of different scenarios to deal with. In still other cases, you can use an OPTIMIZE FOR to optimize for the common case when there are parameter values that produce less than adequate results, although presumably results that you can live with, not a plan that takes an hour or more to execute what normally takes milliseconds.

In some cases, the plan gets stale because of changes in the data, or even changes in the structure of the table. In stored procedures, SQL Server can recompile only the single statement in the plan that needs recompiled. While managing compilation and re-compilation can seem a bit complicated, there are a few caveats but they are very few and far between. You have several ways to manage the parameterization and you have direct access to the code to change it. For the person who has to deal with parameter issues, or really any sort of tuning issues, our next topic is about tuning procedures without changing the procedure’s public interface. You can use the tricks mentioned to fix the performance issue without the client’s knowledge.

Fine-Tuning Without Program Changes

Even if you didn’t have the performance capabilities of parameterization for stored procedures (say every query in your procedure was forced to do dynamic SQL), the ability to fine-tune the queries in the stored procedure without making any changes to the client code is of incredible value. Of course, this is the value of encapsulation, but again, fine-tuning is such an important thing.

Often, a third-party system is purchased that doesn’t use stored procedures. If you’re a support person for this type of application, you know that there’s little you can do other than to add an index here and there.

“But,” you’re probably thinking, “shouldn’t the third party have planned for all possible cases?” Sure they should, because while the performance characteristics of a system with 10 rows might turn out to be identical to one with 10,000, there is the outlier, like the organization that pumped 10 million new rows per day into that system that was only expected to do 10,000 per day. The fact is, SQL Server is built to operate on a large variety of hardware in a large variety of conditions. A system running on a one-processor laptop with its slow disk subsystem behaves exactly like a RAID 10 system with 20 high-speed, 32 GB, solid-state drives, right? (Another gold star if you just said something witty about how dumb that sounded.)

The answer is no. In general, the performance characteristics of database systems vary wildly based on usage characteristics, hardware, and data sizing issues. By using stored procedures, it’s possible to tweak how queries are written, as the needs change from small dataset to massive dataset. For example, I’ve seen many not so perfect queries that ran great with 10,000 rows, but when the needs grew to millions of rows, the queries ran for hours. Rewriting the queries using proper query techniques, or sometimes using temporary tables gave performance that was several orders of magnitude better. And I have had the converse be true, where I have removed temporary tables and consolidated queries into a single statement to get better performance. The user of the procedures did not even know.

This ability to fine-tune without program changes is very important. Particularly as a corporate developer, when the system is running slow and you identify the query or procedure causing the issue, fixing it can be either a one-hour task or a one-week task. If the problem is a procedure, you can modify, test, and distribute the one piece of code. Even following all of the rules of proper code management, your modify/test/distribute cycle can be a very fast operation. However, if application code has to be modified, you have to coordinate multiple groups (DBAs and programmers, at least) and discuss the problem and the code has to be rewritten and tested (for many more permutations of parameters and settings than for the one procedure).

For smaller organizations, it can be overly expensive to get a really good test area, so if the code doesn’t work quite right in production, you can tune it easily then. Tuning a procedure is easy, even modification procedures, you can just execute the code in a transaction and roll it back. Keep changing the code until satisfied, compile the code in the production database, and move on to the next problem. (This is not best practice, but it is something I have to do from time to time.)

High Initial Effort

Of all the pros and cons, initial effort is most often the straw that breaks the camel’s proverbial back in the argument for or against stored procedures. Pretty much every time I’ve failed to get stored procedure access established as the method of access, this is the reason given. There are many tools out there that can map a database to objects or screens to reduce development time. The problem is that they suffer from some or all of the issues discussed in the ad hoc SQL pitfalls.

It’s an indefensible stance that writing lots of stored procedures takes less time up front—quite often, it takes quite a bit more time for initial development. Writing stored procedures is definitely an extra step in the process of getting an application up and running.

An extra step takes extra time, and extra time means extra money. You see where this is going, because people like activities where they see results, not infrastructure. When a charismatic programmer comes in and promises results, it can be hard to back up claims that stored procedures are certainly the best way to go. The best defenses are knowing the pros and cons and, especially, understanding the application development infrastructure you’ll be dealing with.

Difficulty Supporting Optional Parameters in Searches

I already mentioned something similar to optional parameters earlier when talking about dynamic SQL. In those examples, all of the parameters used simple LIKE parameters with character strings. But what about integer values? Or numeric ones? As mentioned earlier in the ad hoc sections, a possible solution is to pass NULL into the variable values by doing something along the lines of the following code:

 WHERE (integerColumn = @integerColumn or @integerColumn is null)

    AND (numericColumn = @numericColumn or @numericColumn is null)

    AND (characterColumn Like @characterColumn);

Generally speaking, it’s possible to come up with some scheme along these lines to implement optional parameters alongside the rigid needs of procedures in stored procedures. Note too that using NULL as your get everything parameter value means it is then hard to get only NULL values. For character strings you can use LIKE '%'. You can even use additional parameters to state: @returnAllTypeFlag to return all rows of a certain type.

It isn’t possible to come up with a scheme, especially a scheme that can be optimized. However, you can always fall back on using dynamic SQL for these types of queries using optional parameters, just like I did in the ad hoc section. One thing that can help this process is to add the WITH RECOMPILE clause to the stored-procedure declaration. This tells the procedure to create a new plan for every execution of the procedure.

Although I try to avoid dynamic SQL because of the coding complexity and maintenance difficulties, if the set of columns you need to deal with is large, dynamic SQL can be the best way to deal with the situation. Using dynamically built stored procedures is generally the same speed as using ad hoc access from the client, so the benefits from encapsulation still exist.

Difficulty Affecting Only Certain Columns in an Operation

When you’re coding stored procedures without dynamic SQL, the code you’ll write is going to be pretty rigid. If you want to write a stored procedure to modify a row in the table created earlier in the chapter—sales.contact —you’d write something along the lines of this skeleton procedure (back in the architectureChapter database):

 CREATE PROCEDURE sales.contact$update

 (

    @contactId int,

    @firstName varchar(30),

    @lastName varchar(30),

    @companyName varchar(100),

    @salesLevelId int,

    @personalNotes varchar(max),

    @contactNotes varchar(max)

 )

 AS

    DECLARE @entryTrancount int = @@trancount;

    BEGIN TRY

          UPDATE sales.contact

          SET firstName = @firstName,

       lastName = @lastName,

       companyName = @companyName,

       salesLevelId = @salesLevelId,

       personalNotes = @personalNotes,

       contactNotes = @contactNotes

          WHERE contactId = @contactId;

    END TRY

    BEGIN CATCH

       IF @@trancount > 0

          ROLLBACK TRANSACTION;

       DECLARE @ERRORmessage nvarchar(4000)

       SET @ERRORmessage = 'Error occurred in procedure ''' +

                object_name(@@procid) + ''', Original Message: '''

                + ERROR_MESSAGE() + '''';

       THROW 50000,@ERRORmessage,1;

 END CATCH

A procedure such as this is fine most of the time, because it usually isn’t a big performance concern just to pass all values and modify those values, even setting them to the same value and revalidating. However, in some cases, validating every column can be a performance issue because not every validation is the same as the next.

For example, say that the salesLevelId column was a very important column for the corporate sales process. And it needed to validate in the sales data if the customer could, in fact, actually be that level. A trigger might be created to do that validation, and it could take a relatively large amount of time. Note that when the average operation takes 1 millisecond, 100 milliseconds can actually be “a long time.” It is all relative to what else is taking place and how many times a minute things are occurring. You could easily turn this into a dynamic SQL procedure, though since you don’t know if the value of salesLevel has changed, you will have to check that first:

 ALTER PROCEDURE sales.contact$update

 (

    @contactId int,

    @firstName varchar(30),

    @lastName varchar(30),

    @companyName varchar(100),

    @salesLevelId int,

    @personalNotes varchar(max),

    @contactNotes varchar(max)

 )

 WITH EXECUTE AS SELF

 AS

    DECLARE @entryTrancount int = @@trancount;

    BEGIN TRY

       --declare variable to use to tell whether to include the

       DECLARE @salesOrderIdChangedFlag bit =

                   CASE WHEN (SELECT salesLevelId

                            FROM sales.contact

                            WHERE contactId = @contactId) =

                                   @salesLevelId

                      THEN 0 ELSE 1 END;

    DECLARE @query nvarchar(max);

    SET @query = '

    UPDATE sales.contact

    SET firstName = ' + quoteName(@firstName,'''') + ',

          lastName = ' + quoteName(@lastName,'''') + ',

          companyName = ' + quoteName(@companyName, '''') + ',

          '+ case when @salesOrderIdChangedFlag = 1 then

          'salesLevelId = ' + quoteName(@salesLevelId, '''') + ',

          ' else '' end + 'personalNotes = ' + quoteName(@personalNotes,

    '''') + ',

          contactNotes = ' + quoteName(@contactNotes,'''') + '

       WHERE contactId = ' + cast(@contactId as varchar(10)) ;

       EXECUTE (@query);

    END TRY

    BEGIN CATCH

       IF @@trancount > 0

          ROLLBACK TRANSACTION;

       DECLARE @ERRORmessage nvarchar(4000)

       SET @ERRORmessage = 'Error occurred in procedure ''' +

                object_name(@@procid) + ''', Original Message: '''

       + ERROR_MESSAGE() + '''';

       THROW 50000,@ERRORmessage,1;

    END CATCH

This is a pretty simple example, and as you can see the code is already getting pretty darn ugly. Of course, the advantage of encapsulation is still intact, since the user will be able to do exactly the same operation as before with no change to the public interface, but the code is immediately less manageable at the module level.

An alternative you might consider would be an INSTEAD OF trigger to conditionally do the update on the column in question if the inserted and deleted columns don’t match:

 CREATE TRIGGER sales.contact$insteadOfUpdate

 ON sales.contact

 INSTEAD OF UPDATE

 AS

 BEGIN

    SET NOCOUNT ON;

    SET ROWCOUNT 0; --in case the client has modified the rowcount

    --use inserted for insert or update trigger, deleted for update or delete trigger

    --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

    --that is equal to number of merged rows, not rows being checked in trigger

    DECLARE @msg varchar(2000), --used to hold the error message

    --use inserted for insert or update trigger, deleted for update or delete trigger

    --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

    --that is equal to number of merged rows, not rows being checked in trigger

          @rowsAffected int = (SELECT COUNT(*) FROM inserted);

    -- @rowsAffected int = (SELECT COUNT(*) FROM deleted);

    --no need to continue on if no rows affected

    IF @rowsAffected = 0 RETURN;

    BEGIN TRY

          --[validation blocks]

          --[modification blocks]

          --<perform action>

    UPDATE contact

    SET firstName = inserted.firstName,

          lastName = inserted.lastName,

          companyName = inserted.companyName,

          personalNotes = inserted.personalNotes,

          contactNotes = inserted.contactNotes

    FROM sales.contact AS contact

             JOIN inserted

                on inserted.contactId = contact.contactId

    IF UPDATE(salesLevelId) --this column requires heavy validation

                      --only want to update if necessary

       UPDATE contact

       SET salesLevelId = inserted.salesLevelId

       FROM sales.contact AS contact

          JOIN inserted

             ON inserted.contactId = contact.contactId

       --this correlated subquery checks for rows that have changed

       WHERE EXISTS (SELECT *

                   FROM deleted

                   WHERE deleted.contactId =

                            inserted.contactId

                   AND deleted. salesLevelId <>

                            inserted. salesLevelId)

 END TRY

    BEGIN CATCH

          IF @@trancount > 0

                ROLLBACK TRANSACTION;

          THROW;

    END CATCH

 END;

This is a lot of code, but it’s simple. This is one of the rare uses of INSTEAD OF triggers, but it’s pretty simple to follow. Just update the simple columns and not the “high cost” columns, unless it has changed. The point of this is to note that the more encapsulated you get from the client, the more you can do in your code to modify the code to your needs. The stored procedure layer can be treated as a set of modules that return a set of data, save the state of some data, and so forth, without the client needing to know anything about the structure of anything other than the parameters and tabular data streams that are to be returned.

CLR User-Defined Functions

When the CLR was added to SQL Server, using it would have been worth the effort had it allowed you only to implement user-defined functions. Scalar user-defined functions that are highly computational are the sweet spot of coding SQL Server objects with CLR, particularly when you have more than a statement or two executing such functions. In fact, functions are the only type of objects that I have personally created and used using the CLR. I have seen some reasonable uses of several others, but they are generally fringe use cases. Those functions have been a tremendous tool for improving performance of several key portions of the systems I have worked with.

You can make both table value and scalar functions, and they will often be many times faster than corresponding T-SQL objects when there is no need for data other than what you pass in via parameters. CLR functions that have to query data via SQL become cumbersome to program, and usually coding them in T-SQL will simply be easier. Examples of functions that I have built using the CLR include:

• Date functions: Especially those for getting time zone information
• Comparison functions: For comparing several values to one another, like the date$getMax function shown earlier, though with more like thirty parameters instead of four.
• Calculation functions: Performing math functions or building a custom, soundex-style function for comparing strings and so forth.

Of all of the CLR object types, the user-defined functions are definitely the ones that you should consider using to replace your T-SQL objects, particularly when you are not interacting with data in SQL.

CLR Stored Procedures

As a replacement for extended stored procedures, .NET stored procedures provide a safe and relatively easy means to extend the functionality of SQL Server. Examples of extended stored procedures include xp_sendmail, xp_cmdshell, and xp_regread. Traditionally, extended stored procedures required writing code in a language such as C++ and ran in-process with the sqlservr.exe process. CLR stored procedures have the same capabilities as traditional, extended stored procedures, but they’re easier to code and run in the safer environment of managed code. Of course, the more external resources that you access, the less safe your CLR object will be. The fact is that extended stored procedures were very often a dangerous choice, no matter how many resources you used outside of SQL server.

Another means of extending SQL Server was using the extended stored procedures beginning with sp_OA%. These used OLE automation to allow you to access COM objects that could be used to do things that T-SQL was unable to accomplish on its own. These objects were always slow and often unreliable and the CLR is an admirable replacement for the sp_OA% procedures and COM objects. You can use the .NET Framework classes to perform the same functionality as COM objects can.

If you are iterating through a set (perhaps using a cursor) performing some row-wise logic, you might try using CLR and the SqlDataReader class as it can be faster than using cursors. That said, it’s best to start with T-SQL for your stored procedures that access SQL Server data, since there is very little that you cannot do using setwise manipulations, especially using ROW_NUMBER and the other ranking functions.

If performance of the stored procedure becomes a factor and there’s lots of necessary procedural and computational logic within the stored procedure, experimenting with rewriting the stored procedure using CLR might be worthwhile. Quite often, if you find yourself doing too much in T-SQL code, you might be doing too much in the data layer, and perhaps you should move the procedural and computational logic away from the database to the middle tier and use the T-SQL stored procedure(s) primarily for data-access routines.

CLR Triggers

If you choose to use triggers in your database architecture, they’re almost the exclusive domain of T-SQL. I felt this when I first heard of CLR triggers, and I feel this now. There is really not a good mainstream scenario where CLR triggers are going to be better than a well written T-SQL trigger. For any complex validation that might be done in a trigger, a better option is probably to use a CLR scalar function and call it from within a T-SQL trigger. Perhaps if there’s significant procedural logic in a trigger, .NET would be a better choice than T-SQL, but just hearing the phrase “significant procedural logic in a trigger” makes me shudder a bit.

Be careful when using complex triggers. Almost any complex activity in a trigger is probably a cause for moving to use some form of queue where the trigger pushes rows off to some layer that operates asynchronously from the current transaction.

User-Defined Aggregates

User-defined aggregates are a feature that have been desired for many years, and in 2005, with the introduction of the CLR, we were finally able to create our own aggregate functions. SQL Server already includes the most commonly used aggregate functions such as SUM, COUNT, and AVG. There might be a time when your particular business needs a special type of aggregate, or you need a complex statistical aggregate not supplied out of the box by SQL Server. Aggregates you create are not limited to numeric values either. An aggregate that I’ve always wanted is one similar to Sybase’s List() aggregate that concatenates a list of strings in a column, separating them by commas.

In the code download for this book, you will find the code for the following aggregate function, called string$list (I also include the assembly in the T-SQL code for those of you who are purists who think procedural code is for other people!). Once the function’s code is compiled and loaded into SQL server, you can do something like the following:

 SELECT dbo.string$list(name) as names

 FROM   (VALUES('Name'),('Name2'),('Name3')) AS Names (name)

This will return:

In informal testing, running code like this using a custom aggregate can give an order of magnitude performance improvement over the T-SQL alternatives of using XML or over any trick you can do with variables and concatenation. A good part of the reason that the CLR version runs faster is that the T-SQL version is going to run a query for each group of values desired, while the CLR version is simply aggregating the products returned from the single query, using techniques that are natural to the SQL engine.

Versions of Visual Studio 2005 Professional and higher have included a SQL Server project and template that includes the stub for the functions that must be included as part of the contract for coding a .NET user-defined aggregate. For each aggregate, you must implement the Init, Accumulate, Merge, and Terminate functions. Besides the obvious performance benefit, there’s also the flexibility benefit of being able to use such an aggregate with any column of any table. That is definitely unlike the T-SQL options where you need to hard-code which column and table are being aggregated.

CLR User-Defined Types

Microsoft has built several types into SQL Server 2008 using the CLR. These are the hierarchyId and the spatial types. The fact is, though, that you should hesitate to base all of your datatypes on CLR types. The intrinsic, built-in datatypes will suffice for nearly every situation you will find, but if you have a need for a richer type with some complex handling, they are definitely available.

The largest downside to the CLR UDTs is that in order to get the full experience using the types, you will need to have the client set up to use them. To access the properties and methods of a UDT on the client and take full advantage of the new datatype, each client must have a copy of the UDT available in an assembly accessible by the client. If the code for a UDT is updated on SQL Server, the UDT class that’s registered on each client that makes use of the UDT should be kept in sync with the server version to avoid any data problems. If the client does not have the UDT class available (like when you return the value of a column based on a UDT in Management Studio), the value will be returned as a hexadecimal value, unless you use the .ToString method on the type (which is a requirement for building a type). All in all, I generally steer clear of user defined types in pretty much all cases, because the management costs is typically way higher than the payoff.