- Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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.
SQL Server 2000 SET Options
SQL Server 2005 SET Options
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
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.
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.
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.
It gets even more complicated. You can also set options in your connection layer. See here for more.