SQL Server Reference Guide

Forming Queries Part 4: SET Options

Last updated Sep 28, 2007.

As you can imagine, the database engine doesn't just accept data input and store the elements for later retrieval. There is an entire processing engine around accepting, changing and reviewing the data. There are a lot of parameters the engine uses to get at the data. Many of these parameters are set by Microsoft, and are hard-coded into the Query Processor. These parameters often have performance impacts.

But there are also quite a few parameters that you can use to change the behavior of how the processor will deal with the data. You can set many of these options at the server or database level, which means that any query that connects to the server will use these parameters. These "knobs" are available in the management tools for both SQL Server 2000 and 2005 in the "Properties" of each of the objects such as servers, databases and so on.

You can, however, "override" these server-level settings by specifying a SET command in a query. There are quite a few SET commands you can use to affect all kinds of behavior. In this overview I'll cover only a few of those that relate to some specific query behaviors, but there are quite a few that affect other things such as performance and other conditions. Here's a list of the SET options for SQL Server 2000 and 2005. Each has a link to the Books Online entry for that option show you can drill down a little further in each.

I want to be clear about the behavior of these options. There are always defaults set on the server by Microsoft after a "clean" or new installation. You can change these on the server, and any query that runs without a SET option will run with your server's settings. But if the query block, or transaction, contains one of the SET options, that behavior will override what is on the server. Let's take a quick example.

One of the examples I'll show you in a moment deals with how NULLs are handled. Let's assume that the server setting for these values are set to some option we'll call "A." If a query comes in that has a NULL in it, it will be handled in "A" way. But if the server says "A," and a query has a setting for NULLs we'll call "B," then the NULLs will be handled in "B" way. The very next query that comes in without any SET options gets the "A" setting from the server again.

Let's break down the more common SET options that you'll use in your queries, stored procedures and other code. These are by no means exhaustive, but they are the ones I've seen used in many situations. I'll cover some of the information about the setting, but not all. You should click on the links above

SET NOCOUNT

One of the most common settings you should include in your queries, code and stored procedures is SET NOCOUNT ON. By default, this setting is OFF. What this option does is either show (OFF) or not show (ON) the count of rows returned by the query. While this is useful in the query tools, it's not normally needed by an application. While it might sound trivial to provide this count, it is done by the engine – which means that when thousands of queries are processed, it counts each of the rows returned (even if that's just one row) thousands of times. To make matters worse, this doesn't count as a data element, but is transmitted via a message. That takes time and processing power, and almost every time it's data you don't care about. So you should almost always include this line in the top of your scripts:

SET NOCOUNT ON;
GO

SET ARITHABORT and SET ARITHIGNORE

I'm not going to spend a lot of time on these two options, but I want to mention them because the Books Online entries go into great detail about them, with samples and examples. I mention them here because you may either see them in use or need to use them yourself. They both deal with arithmetic handling, specifically for divide by zero errors.

As you know, if you divide a number by a zero value, you'll cause an error on a computer. Computers will happily try to do the work, but they don't always know how to deal with the results. In code, this is usually easy to catch, but it isn't always easy to find in a database program because you're often just pulling back rows from the database. Those rows may contain values you need, but one or two of them might have a zero you didn't anticipate, and if you don't use these settings appropriately you could end up with an error that is very difficult to find. Once again, look up the Books Online entries to see examples of how to find them.

SET TRANSACTION ISOLATION LEVEL

This option deals with the way the system locks the data as you read or change it. The default locking behavior is normally quite acceptable in SQL Server, but there are times when you want to override it. I've got another tutorial that explains this option that you can read here.

ANSI Settings

The ANSI settings (those with ANSI in the title) are also another set I'll direct you to Books Online for further explanations and examples. These settings deal with how certain data elements and behaviors such as NULL values are handled. Mostly you'll want to read up on these when you 're developing an application against multiple platforms like SQL Server and Oracle, or perhaps when you have data that you want to standardize your code around.

SET DATEFIRST and SET DATEFORMAT

These settings become very important when you are working internationally, or any time you cross date lines where there might be a question on date formats. The DATEFIRST option sets the first day of the week, for any day you wish.

The DATEFORMAT command has more to do with display, and it lets you show the date the way you want per query.

SET CONCAT_NULL_YIELDS_NULL

Although this is a setting that won't be around forever, if you migrate an application from SQL Server 2000 to 2005 you might want to be familiar with it. Let's assume that you have the following values:

FName: 'Buck'
MName: NULL
LName: 'Woody'
If you turn this option ON, then if you concatenate a value like this:
SELECT FName + MName + LName
You'll get this back:
NULL
If you turn it OFF, then you get:
'Buck Woody'

I'll explain more of these options as in other sections of the database guide, but for now read up and play with these settings in your applications so that you're familiar with the ones that you'll encounter quite frequently.

InformIT Articles and Sample Chapters

You can find more information about server, database and session settings in SQL Server in this free chapter from Transact-SQL Desk Reference: For Microsoft SQL Server.

Online Resources

It gets even more complicated. You can also set options in your connection layer. See here for more.