Tables
Updated : August 1, 2001
Chapter 6 from Inside Microsoft SQL Server 7.0 by Kalen Delaney, published by Microsoft Press
In this chapter, we'll look at some in-depth implementation examples. But let's start with a basic introduction to tables. Simply put, a table is a collection of data about a specific entity (person, place, or thing) that has a discrete number of named attributes (for example, quantity or type). Tables are at the heart of Microsoft SQL Server and the relational model in general. Tables are easy to understand—they're just like the everyday lists you make for yourself. In SQL Server, a table is often referred to as a base table to emphasize where data is stored. Calling it a base table also distinguishes the table from a view, a virtual table that's an internal query referencing one or more base tables.
Attributes of a table's data (such as color, size, quantity, order date, and supplier's name) take the form of named columns in the table. Each instance of data in a table is represented as a single entry, or row (formally called a tuple). In a true relational database, every row in a table is unique, and each row has a unique identifier called the primary key. (SQL Server, in accordance with the ANSI SQL standard, doesn't require that you make a row unique or declare a primary key. However, because both of these concepts are central to the relational model, you should always implement them.)
Most tables will have some relationship to other tables. For example, in an order-entry system, the orders table likely has a customer_number column in which it keeps track of the customer number for an order; customer_number also appears in the customer table. Assuming that customer_number is a unique identifier, or the primary key, of the customer table, a foreign key relationship is established by which the orders and customer tables can subsequently be joined.
So much for the 30-second database design primer. You can find plenty of books that discuss logical database and table design, but this isn't one of them. We assume that you understand basic database theory and design and that you generally know what your tables will look like. The rest of this chapter discusses the internals of tables in SQL Server and implementation considerations.
On This Page
Creating Tables
Internal Storage—The Details
Indexes
User-Defined Datatypes
Identity Property
Constraints
Altering a Table
Temporary Tables
Summary
Creating Tables
SQL Server uses the ANSI SQL standard CREATE TABLE syntax. SQL Server Enterprise Manager provides a front-end, fill-in-the-blanks table designer, which might make your job easier. Ultimately, the SQL syntax is always sent to SQL Server to create a table. You can create a table directly using a tool such as OSQL, ISQL, or the Query Analyzer; from SQL Server Enterprise Manager; or using a third-party data modeling tool (such as ERwin or Microsoft Visual InterDev) that transmits the SQL syntax under the cover of a friendly interface.
In this chapter, we emphasize direct use of the data definition language (DDL) rather than discussing the interface tools. You should keep all DDL commands in a script so that you can run them easily at a later time to re-create the table. (Even if you use one of the friendly front-end tools, it's critical that you can later re-create the table.) SQL Server Enterprise Manager and other front-end tools can create and save operating system files with the SQL DDL commands necessary to create the object. This DDL is essentially source code, and you should treat it as such. Keep a backup copy. You should also consider keeping these files under version control using a source control product such as Microsoft Visual SourceSafe or PVCS from Micro Focus (formerly INTERSOLV).
At the basic level, creating a table requires little more than knowing what you want to name it, what columns it will contain, and what range of values (domain) each column will be able to store. Here's the basic syntax for creating the customer table, with three fixed-length character (char) columns. (Note that this table definition isn't necessarily the most efficient way to store data because it always requires 46 bytes per entry plus a few bytes of overhead regardless of the actual length of the data.)
CREATE TABLE customer ( name char(30), phone char(12), emp_id char(4) )
This example shows each column on a separate line for readability. As far as the SQL Server parser is concerned, whitespaces created by tabs, carriage returns, and the Spacebar are identical. From the system's standpoint, the following CREATE TABLE example is identical to the one above; but it's harder to read from a user's standpoint:
CREATE TABLE customer (name char(30), phone char(12), emp_id char(4))
This simple example shows just the basics of creating a table. We'll see many more detailed examples later in this chapter.
Naming Tables and Columns
A table is always created within a database and is owned by one particular user. Normally, the owner is the user who created the table, but anyone with the sysadmin or db_owner role can create a table owned by another user. A database can contain multiple tables with the same name, as long as the tables have different owners. The full name of a table has three parts, in this form:
database.owner.tablename
For example, say that a user (with the username Kalen) created a sample customer table in the pubs sample database. This user's table would have the pubs.kalen.customer three-part name. (If this user is also the database owner, pubs.dbo.customer would be her table's name because dbo is the special username for the database owner in every database.)
The first two parts of the three-part name specification have default values. The default for the name of the database is whatever database context you're currently working in. The table owner actually has two possible defaults. If no table owner name is specified when referencing a table, SQL Server will assume that either you or the owner of the database owns the table. For example, if our hypothetical user owns the customer table and her database context is the pubs, she can refer to the table simply as customer.
Note: To access a table owned by anyone other than yourself or the database owner, you must include the owner name along with the table name.
Column names should be descriptive, and because you'll use them repeatedly, you should avoid wordiness. The name of the column (or any object in SQL Server, such as a table or a view) can be whatever you choose, as long as the name conforms to the SQL Server rules for identifiers: it must consist of a combination of 1 through 128 letters, digits, or the symbols #, $, @, or _. (For more specific identifier rules, see "Using Identifiers" in SQL Server Books Online*.* The discussions there and in the Microsoft SQL Server Transact-SQL and Utilities Reference are true for all SQL Server object names, not just for column names.)
Reserved Keywords
Certain reserved keywords, such as table, create, select, and update, have special meaning to the SQL Server parser, and collectively they make up the SQL language implementation. If at all possible, you shouldn't use reserved keywords for your object names. In addition to the SQL Server reserved keywords, the SQL-92 standard has its own list of reserved keywords. In some cases, this list is more restrictive than SQL Server's list; in other cases, it's less restrictive. SQL Server Books Online has a complete list of both SQL Server's reserved keywords and the SQL-92 standard's list.
Watch out for SQL-92's reserved keywords. They aren't reserved keywords in SQL Server yet, but they could become reserved keywords in a future SQL Server version. Using a SQL-92 reserved keyword might require that you alter your application before upgrading it if the word has become a SQL Server reserved keyword.
Delimited Identifiers
You can't use keywords in your object names unless you use a delimited identifier. In fact, if you use a delimited identifier, not only can you use keywords as identifiers, but you can use any other string—whether it follows the rules for identifiers or not—as an object name. This includes spaces and other nonalphanumeric characters normally not allowed. Two types of delimited identifiers exist:
Bracketed identifiers are delimited by square brackets ([object name]).
Quoted identifiers are delimited by double quotation marks ("object name").
You can use bracketed identifiers in any environment, but to use quoted identifiers, you must enable a special option using SET QUOTED_IDENTIFIER ON. Once you have turned on QUOTED_IDENTIFIER, double quotes will always be interpreted as referencing an object. To delimit string or date constants, you have to use single quotes. Let's look at some examples. Because column is a reserved keyword, the first statement that follows would be illegal in all circumstances, and the second one would be illegal unless QUOTED_IDENTIFIER was on. The third statement would be legal in any circumstances.
CREATE TABLE customer (name char(30), column char(12), emp_id char(4)) CREATE TABLE customer (name char(30), "column" char(12), emp_id char(4)) CREATE TABLE customer (name char(30), [column] char(12), emp_id char(4))
The ODBC driver that comes with SQL Server sets the option QUOTED_IDENTIFIER to ON by default, but some of the SQL Server–specific tools will set it to OFF. You can determine whether this option is on or off for your session by executing the following command:
DBCC useroptions
Some History
Prior to version 7.0 of SQL Server, the documentation discussed two categories of special words, which were called keywords and reserved words. The keywords were already part of the product, and reserved words were listed as having a good chance of becoming a part of the product in a future version.
The rationale behind reserved words was that for version 6.0, the developers needed to add a lot of new keywords to support the latest functionality. In general, these words hadn't been reserved in version 4.21. Adding so many new keywords made it tough for many sites, because they had to modify their applications by changing these keywords in order to upgrade. Although it probably seemed capricious and unwarranted to add keywords that simply might be used in the future, the developers knew that some new functionality would be added in future versions.
Through version 6.5, the documentation continued to designate reserved words for future use. Now in version 7.0, SQL Server is following the ANSI SQL guidelines much more closely. So instead of documenting separate lists of reserved words and keywords, the documentation can just rely on what ANSI has already specified.
If you're using the Query Analyzer, you can check your setting by either running the command on the previous page or choosing the Configure command from the File menu and examining the Connection tab.
Theoretically, you could always use delimited identifiers with all object and column names, and then you'd never have to worry about reserved keywords. However, we don't recommend this. Many third-party tools for SQL Server don't handle quoted identifiers well. Using quoted identifiers might make upgrading to future versions of SQL Server more difficult. During the upgrade process to SQL Server 7, objects such as views and stored procedures are automatically dropped and re-created so that they include the structures of the latest version.
When you use quoted identifiers, upgrading can't be fully automated and you must tell the Upgrade Wizard whether it should assume that QUOTED_IDENTIFIER is on. If you don't know, or if you have a mixture of objects, you can tell the wizard to use a mixed mode. This means that the Upgrade Wizard will first try interpreting all objects with double quotation marks as though the option is on, and only if that's unsuccessful will it assume that the option is off for that object. This interpretation might not always result in the stored procedure or view having the same meaning as was intended.
Rather than using delimited identifiers to protect against reserved keyword problems, you should simply adopt some simple naming conventions. For example, you can precede column names with the first few letters of the table name and an underscore (_). Not only does this naming style make the column or object name more readable, but it also greatly reduces your chances of encountering a keyword or reserved word conflict.
Naming Conventions
Many organizations and multiuser development projects adopt standard naming conventions, which is generally good practice. For example, assigning a standard moniker of cust_id to represent a customer number in every table clearly shows that all the tables share common data. On the other hand, if an organization used several monikers in the tables to represent a customer number, such as cust_id, cust_num, customer_number, and customer_#, it wouldn't be as obvious that these monikers represented common data.
One naming convention is the Hungarian-style notation for column names. Hungarian-style notation is a widely used practice in C programming, whereby variable names include information about their datatypes. Hungarian-style notation uses names such as sint_nn_custnum to indicate that the custnum column is a small integer (smallint of 2 bytes) and is NOT NULL (doesn't allow nulls). Although this practice makes good sense in C programming, it defeats the datatype independence that SQL Server provides; therefore, we recommend against using it.
Suppose, for example, that after the table is built and applications have been written, you discover that the custnum column requires a 4-byte integer (int) instead of a 2-byte small integer. You can re-create the table relatively easily and define the column as an int instead of a smallint. (Alternatively, you can use the ALTER TABLE command to modify the datatype of the existing table.) SQL Server stored procedures will handle the different datatype automatically. Applications using ODBC, OLE DB, or DB-Library that bind the retrieved column to a character or integer datatype will be unaffected. The applications would need to change if they bound the column to a small integer variable because the variable's type would need to be larger. Therefore, you should try not to be overly conservative with variable datatypes, especially in your client applications. You should be most concerned with the type on the server side; the type in the application can be larger and will automatically accommodate smaller values. By overloading the column name with datatype information, which is readily available from the system catalogs, the insulation from the underlying datatype is compromised. (You could, of course, change the datatype from a smallint to an int, but then the Hungarian-style name would no longer accurately reflect the column definition. Changing the column name would then result in the need to change application code, stored procedures, or both.)
Datatypes
SQL Server provides many datatypes, as shown in Table 6-1 on the following page. Choosing the appropriate datatype is simply a matter of mapping the domain of values you need to store to the corresponding datatype. In choosing datatypes, you want to avoid wasting storage space while allowing enough space for a sufficient range of possible values over the life of your application.
Datatype Synonyms
SQL Server syntactically accepts as datatypes both the words listed as synonyms and the base datatypes shown in Table 6-1, but it uses only the type listed as the datatype. For example, you can define a column as character(1), character, or char(1), and SQL Server will accept them all as valid syntax. Internally, however, the expression is considered char(1), and subsequent querying of the SQL Server system catalogs for the datatype will show it as char(1), regardless of the syntax that you used when you created it.
Globally Unique Identifiers
Most available datatypes in SQL Server are well documented, so we'll leave it up to you to find additional details in the product documentation. However, one special datatype merits a bit more discussion.
Using a globally unique identifier (GUID)—also called a universal unique identifier (UUID)—is becoming an important way to identify data, objects, software applications, and applets in distributed systems. A GUID is a 128-bit (16-byte) value generated in a way that, for all practical purposes, guarantees uniqueness worldwide, even among disconnected computers. SQL Server 7 supports a datatype called uniqueidentifier for storing a globally unique identifier. The Transact-SQL language supports the system function NEWID(), which can be used to generate a uniqueidentifier value. A column or variable of datatype uniqueidentifier can be initialized to a value in one of the following two ways:
Using the system-supplied function NEWID()
Using a string constant in the following form (32 hexadecimal digits separated by hyphens as shown): xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (Each x is a hexadecimal digit in the range 0 through 9 or a through f.)
Table 6-1 SQL Server datatypes.
This datatype can be quite cumbersome to work with, and the only operations that are allowed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL. (The online product documentation states that only the equality and inequality operators, = and <>, are allowed for uniqueidentifier columns, but this is incorrect. In fact, you can even sort by a uniqueidentifier column.) However, using this datatype internally can have several advantages.
One advantage is that the values are guaranteed to be globally unique for any machine on a network, because the last six bits of a uniqueidentifier value make up the node number for the machine. On a machine with a network interface card (NIC), the node is the unique IEEE 802 identifier of that card. On a machine without a NIC (for example, a home computer that connects to the Internet via modem), the node is a pseudo-random, 48-bit value that isn't guaranteed to be unique now but is highly likely to be unique for the near future.
Another advantage is that the list of uniqueidentifier values can't be exhausted. This is not the case with other datatypes frequently used as unique identifiers. In fact, this datatype is used internally by SQL Server for row-level merge replication. A uniqueidentifier column can have a special property called the ROWGUIDCOL property; at most, one uniqueidentifier column can have this property per table. The ROWGUIDCOL property can be specified as part of the column definition in CREATE TABLE and ALTER TABLE ADD column or added or dropped for an existing column through ALTER TABLE ALTER COLUMN.
A uniqueidentifier column with the ROWGUIDCOL property can be referenced using the keyword ROWGUIDCOL in a query. This is similar to referencing an identity column through the IDENTITYCOL keyword. The ROWGUIDCOL property does not imply any automatic value generation, and if automatic value generation is needed, the NEWID() function should be defined as the default value of the column. You can have multiple uniqueidentifier columns per table, but only one of them can have the ROWGUIDCOL property. You can use the uniqueidentifier datatype for whatever reason you come up with, but if you're using one to identify the current row, an application must have a generic way to ask for it without needing to know the column name. That's what the ROWGUIDCOL property does.
Variable-Length vs. Fixed-Length Datatypes
Deciding to use a variable-length or a fixed-length datatype isn't always straightforward or obvious. As a general rule, variable-length datatypes are most appropriate when you expect significant variance in the size of the data for a column and when the data in the column won't be frequently changed.
Using variable-length datatypes can yield important storage savings. Choosing them can sometimes result in a minor performance loss and at other times can result in improved performance. A row with variable-length columns requires special offset entries to be internally maintained. These entries keep track of the actual length of the column. Calculating and maintaining the offsets requires slightly more overhead than a pure fixed-length row, which needs no such offsets at all. This task requires a few addition and subtraction operations to maintain the offset value. However, the extra overhead of maintaining these offsets is generally inconsequential, and this alone would not make a significant difference on most, if not all, systems.
On the other hand, using variable-length columns can sometimes improve performance because they can allow more rows to fit on a page. But the efficiency results from more than simply requiring less disk space. A data page for SQL Server is 8 KB (8192 bytes), of which 8096 bytes are available to store data. (The rest is for internal use to keep track of structural information about the page and the object to which it belongs.) One I/O operation brings back the entire page. If you can fit 80 rows on a page, a single I/O operation brings back 80 rows. But if you can fit 160 rows on a page, one I/O operation is essentially twice as efficient. In operations that scan for data and return lots of adjacent rows, this can amount to a significant performance improvement. The more rows you can fit per page, the better your I/O and cache-hit efficiency will be.
For example, consider a simple customer table. Suppose you could define it in two ways, fixed-length and variable-length, as shown in Figure 6-1 and in Figure 6-2 on the following page.
.gif "Figure 6-1: A customer table with fixed-length columns.")
Figure 6-1: A customer table with fixed-length columns.
.gif "Figure 6-2: A customer table with variable-length columns.")
Figure 6-2: A customer table with variable-length columns.
Columns that contain addresses, names, or Internet URLs all have data that varies significantly in length. Let's look at the differences between choosing fixed-length columns vs. choosing variable-length columns. In Figure 6-1, using all fixed-length columns, every row uses 304 bytes for data, regardless of the number of characters actually inserted into the row. Furthermore, SQL Server needs an additional 10 bytes of overhead for every row in this table, so the rows will need a total of 314 bytes for storage. But assume that even though the table must accommodate addresses and names up to the specified size, on average, the actual entries are only half the maximum size.
In Figure 6-2, assume that for all the variable-length (varchar) columns, the average entry is actually only about half the maximum. Instead of a row length of 304 bytes, the average length is 184 bytes. This length is computed as follows: the smallint and char(2) columns total 4 bytes. The varchar columns' maximum total length is 300, half of which is 150 bytes. And a 2-byte overhead exists for each of nine varchar columns, for 18 bytes. Add 2 more bytes for any row that has one or more variable-length columns. In addition, this row requires another 10 bytes of overhead, regardless of the presence of variable-length fields. (This is the same 10 bytes of overhead needed in the case of all fixed-length columns; in other words, all rows have these same 10 bytes of constant overhead.) So the total is 4 + 150 + 18 + 2 + 10, or 184. (The actual meaning of each of these bytes of overhead will be discussed later in this chapter.)
In the fixed-length example in Figure 6-1, you always fit 25 rows on a data page (8096/314, discarding the remainder). In the variable-length example in Figure 6-2, you can fit an average of 44 rows per page (8096/184). The table using variable-length columns will consume about half as many pages in storage, a single I/O operation will retrieve almost twice as many rows, and a page cached in memory is twice as likely to contain the row you're looking for.
When choosing lengths for columns, don't be wasteful—but don't be cheap, either. Allow for future needs, and realize that if the additional length doesn't change how many rows will fit on a page, the additional size is free anyway. Consider again the examples in Figures 6-1 and 6-2. The cust_id was declared as a smallint, meaning that its maximum positive value is 32,767 (unfortunately, SQL Server doesn't provide any unsigned int or unsigned smallint datatypes), and it consumes 2 bytes of storage. Although 32,767 customers might seem like a lot to a new company, the company might be surprised by its own success and, in a couple of years, find out that 32,767 is too limited.
The database designers might regret that they tried to save 2 bytes and didn't simply make the datatype an int, using 4 bytes but with a maximum positive value of 2,147,483,647. They'll be especially disappointed if they realize they didn't really save any space. If you compute the rows-per-page calculations just discussed, increasing the row size by 2 bytes, you'll see that the same number of rows still fit on a page. The additional 2 bytes are free—they were simply wasted space before. They never cause fewer rows per page in the fixed-length example, and they'll rarely cause fewer rows per page even in the variable-length case.
So which strategy wins? Potentially better update performance, or more rows per page? Like most questions of this nature, no one answer is right. It depends on your application. If you understand the tradeoffs, you'll be able to make the best choice. Now that you know the issues, this general rule merits repeating: variable-length datatypes are most appropriate when you expect significant variance in the size of the data for that column and when the column won't be updated frequently.
Much Ado About NULL
The issue of whether to allow NULL has become an almost religious one for many in the industry, and no doubt the discussion here will outrage a few people. However, our intention isn't to engage the philosophical debate. Pragmatically, dealing with NULL brings added complexity to the storage engine, because SQL Server keeps a special bitmap in every row to indicate which nullable columns actually are NULL. If NULLs are allowed, SQL Server must decode this bitmap for every row accessed. Allowing NULL also adds complexity in application code, which can often lead to bugs. You must always add special logic to account for the case of NULL.
You, as the database designer, might understand the nuances of NULL and three-valued logic when used in aggregate functions, when doing joins, and when searching by values. However, you need to consider whether your development staff understands as well. We recommend, if possible, that you use all NOT NULL columns and define default values (discussed later in this chapter) for missing or unknown entries (and possibly make such character columns varchar if the default value is significantly different in size from the typical entered value).
In any case, it's good practice to explicitly declare NOT NULL or NULL when creating a table. If no such declaration exists, SQL Server assumes NOT NULL. (In other words, no NULLs will be allowed.) However, you can set the default to allow NULLs by using a session setting or a database option. The ANSI SQL standard says that if neither is specified, NULL should be assumed, but as mentioned, this isn't SQL Server's default. If you script your DDL and then run it against another server that has a different default setting, you'll get different results if you don't explicitly declare NULL or NOT NULL in the column definition.
Several database options and session settings can control SQL Server's behavior regarding NULL values. You set database options using the system procedure sp_dboption. And you enable session settings for one connection at a time using the SET command.
The database option ANSI null default corresponds to the two session settings ANSI_NULL_DFLT_ON or ANSI_NULL_DFLT_OFF. When the ANSI null default database option is false (the default setting for SQL Server), new columns created with the ALTER TABLE and CREATE TABLE statements are, by default, NOT NULL if the nullability status of the column isn't explicitly specified. SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON are mutually exclusive options, yet both options exist to determine whether the database option should be overridden. When on, each option forces the opposite option off. Neither option, when set off, turns the opposite option on. Instead, turning an option off only discontinues the current on setting.
Use the function GETANSINULL() to determine the default nullability for your current session. This function returns 1 when new columns will allow null values and the column or datatype nullability isn't explicitly defined when the table is created or altered. We strongly recommend declaring NULL or NOT NULL explicitly when you create a column. This removes all ambiguity and ensures that you're in control of how the table will be built regardless of the default nullability setting.
The database option concat null yields null corresponds to the session setting SET CONCAT_NULL_YIELDS_NULL. When CONCAT_NULL_YIELDS_NULL is on, concatenating a NULL value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is off, concatenating a NULL value with a string yields the string itself. (The NULL value is treated as an empty string.) For example, SELECT 'abc' + NULL yields abc. If the session level setting isn't specified, the value of the database option concat null yields null applies. Also, if SET CONCAT_NULL_YIELDS_NULL is off, SQL Server uses the concat null yields null setting of sp_dboption.
The database option ANSI nulls corresponds to the session setting SET ANSI_NULLS. When true, all comparisons to a null value evaluate to NULL (unknown). When false, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. In addition, when this value is TRUE, your code must use the condition IS NULL to determine whether a column has a NULL value. When this value is FALSE, SQL Server allows "= NULL" as a synonym for "IS NULL" and "<> NULL" as a synonym for "IS NOT NULL." You can see this behavior yourself by looking at the titles table in the pubs database. The titles table has two rows with a NULL price. The first batch of statements that follows, when executed from the Query Analyzer, should return two rows, and the second batch should return no rows:
-- First batch will return 2 rows use pubs set ansi_nulls off go select * from titles where price = null go --Second batch will return no rows use pubs set ansi_nulls on go select * from titles where price = null go
A fourth session setting is ANSI_DEFAULTS. Setting this to ON is a shortcut for enabling both ANSI_NULLS and ANSI_NULL_DEFAULT_ON, as well as other session settings not related to NULL handling. SQL Server's ODBC driver and the SQL Server OLE DB provider automatically set ANSI_DEFAULTS to ON. You can change the ANSI_NULLS setting when defining your DSN. Two of the client tools supplied with SQL Server (Query Analyzer and the text-based osql) use the SQL Server ODBC driver but then internally turn off some of these options. To see which options are enabled for the tool you're using, you can run the following command:
DBCC USEROPTIONS
Here's a sample of the output it might return:
Set Option Value -------------------------- ------------------------- textsize 64512 language us english dateformat mdy datefirst 7 ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET
Warning: Although internally SQL Server's default behavior is to not allow NULLs unless specifically declared in the CREATE TABLE statement, you might never see this behavior in action. Because SQL Server Query Analyzer, the basic tool for submitting SQL code to SQL Server, is ODBC-based, it automatically turns on the ANSI_NULL_DEFAULT_ON option. This setting means that all your new columns will allow NULLs by default. We can't overemphasize that your best bet for avoiding confusion is to always state explicitly in your table definition whether NULLs should be allowed.
The database compatibility level controls two additional aspects of how SQL Server handles NULL values, as determined by the system procedure sp_dbcmptlevel. If the compatibility level is set to 70, the nullability of bit columns without explicit nullability is determined by either the session setting of SET ANSI_NULL_DFLT_ON or SET ANSI_NULL_DFLT_OFF or the database setting of ANSI null default. In 60 or 65 compatibility mode, bit columns created without an explicit NULL or NOT NULL option in CREATE TABLE or ALTER TABLE are created as NOT NULL.
The database compatibility level also controls whether SQL Server interprets an empty string (two single quotes with nothing between them) as either a single space or as a true empty string. In compatibility level 60 or 65, SQL Server interprets empty strings as single spaces. If the compatibility level is 70, SQL Server interprets empty strings as truly empty, that is, a character string with no characters in it. Sometimes this empty space is referred to as a NULL, but SQL Server doesn't treat it like a NULL. SQL Server marks NULLs internally as NULLS, but an empty string is actually stored as a variable-length character field of 0 length.
In 60 or 65 compatibility mode, the empty string ('') is interpreted as a single space in INSERT or assignment statements on varchar data. In concatenating varchar, char, or text data, the empty string is interpreted as a single space. This means that you can never have a truly empty string. The only alternative in 60 or 65 compatibility mode would be to define the field as allowing NULLs, and use a NULL in place of an empty string.
As you can see, you can configure and control the treatment and behavior of NULL values several ways, and you might think it would be impossible to keep track of all the variations. If you try to control every aspect of NULL handling separately within each individual session, you can cause immeasurable confusion and even grief. However, you'll notice that most of the issues become moot if you follow a few basic recommendations:
Never allow NULL values inside your tables.
Include a specific NOT NULL qualification in your table definitions.
Make sure all your databases are running in 70 compatibility mode.
If you must use NULLs in some cases, you can minimize problems by always following the same rules, and the easiest rules to follow are the ones that ANSI already specifies.
Internal Storage—The Details
This section covers system catalogs and the internal data storage of tables. Although you can use SQL Server effectively without understanding the internals, understanding the details of how SQL Server stores data will help you develop efficient applications. (If you don't need this in-depth information, you can skip this discussion and proceed to the section on indexes on p. 246.)
When you create a table, one or more rows are inserted into a number of system catalogs to manage that table. At a minimum, rows are added to the sysobjects, sysindexes, and syscolumns system catalogs (tables). When you define the new table with one or more constraints, rows are added to the sysreferences and sysconstraints system tables.
For every table created, a single row that contains—among other things—the name, object ID, and owner of the new table is added to the sysobjects table. The sysindexes table will gain a single row that contains pointers to the first data page that the new table uses and information regarding the table's size, including the number of pages and rows currently being used. The syscolumns table will gain one row for each column in the new table, and each row will contain information such as the column name, datatype, and length. Each column receives a column ID, which directly corresponds to the order in which you specified the columns when you created the table. That is, the first column listed in the CREATE TABLE statement will have a column ID of 1, the second column will have a column ID of 2, and so on. Figure 6-3 shows the rows added to the sysobjects, sysindexes, and syscolumns system tables when you create a table. (Not all columns are shown for each table.)
Notice in the syscolumns output in the figure that the xoffset column contains negative numbers in some rows. (Ignore the offset column. It's used only to store row structure information for older versions of SQL Server.) Any column that contains variable-length data will have a negative xoffset value in syscolumns. The negative numbers are assigned to the variable-length columns in decreasing order (--1, --2, --3, and so on) in the order in which the column is specified in the CREATE TABLE statement. You can see in Figure 6-3 that the employee last name (emp_lname) is the first variable-length column in the table.
.gif "Cc917720.f06wh03(en-us,TechNet.10).gif")
Figure 6-3: Catalog information stored after creating a table .
Data Pages
Data pages are the structures that contain all of a table's nontext and image data. As with all other types of pages in SQL Server, data pages have a fixed size of 8 KB, or 8192 bytes. Data pages consist of three major components: the page header, data rows, and the row offset array, as shown in Figure 6-4.
Page Header
As you can see in Figure 6-4, the page header occupies the first 96 bytes of each data page (leaving 8096 bytes for data and row offsets). Table 6-2 on the following page shows the information contained in the page header.
Data Rows
Following the page header is the area in which the table's actual data rows are stored. The maximum size of a single data row is 8096 bytes. A data row can't span multiple pages (except for nontext and image columns, which are stored in their own separate pages). The number of rows stored on a given page will vary depending on the structure of the table and on the data being stored. A table that has all fixed-length columns will always store the same number of rows per page; variable-length rows will store as many rows as will fit based on the actual length of the data entered. Keeping row length compact allows more rows to fit on a page, thus reducing I/O and improving the cache-hit ratio.
.gif "Figure 6-4: The structure of a data page.")
Figure 6-4: The structure of a data page.
Table 6-2 Information contained in the page header.
Field |
Contains |
|---|---|
pageID |
File number and page number of this page in the database. |
nextPage |
File number and page number of the next page, if this page is in a page chain. |
prevPage |
File number and page number of the previous page, if this page is in a page chain. |
objID |
ID of the object to which this page belongs. |
lsn |
Log sequence number (LSN) value used for changes and updates to this page. |
slotCnt |
Total number of slots used on this page. |
level |
Level of this page in an index (always 0 for leaf pages). |
indexId |
Index ID of this page (always 0 for data pages). |
freeData |
Byte offset of the first free space on this page. |
pminlen |
Number of bytes in fixed-length portion of rows. |
freeCnt |
Number of free bytes on page. |
reservedCnt |
Number of bytes reserved by all transactions. |
xactreserved |
Number of bytes reserved by the most recently started transaction. |
tornBits |
1 bit per sector for detecting torn page writes (discussed in Chapter 5). |
flagBits |
2-byte bitmap that contains additional information about the page. |
Row Offset Array
The row offset array is a block of 2-byte entries, each of which indicates the offset on the page on which the corresponding data row begins. Every row has a 2-byte entry in this array, and these 2 bytes were included in the discussion on page 224 in which we mentioned the 10 overhead bytes needed by every row. Although these bytes aren't stored in the row with the data, they do impact the number of rows that will fit on a page.
The row offset array indicates the logical order of rows on a page. For example, if a table has a clustered index, SQL Server will store the rows in the order of the clustered index key. This doesn't mean that the rows will be physically stored on the page in the order of the clustered index key, but that slot 0 in the offset array will refer to the first row in the order, slot 1 will refer to the second row, and so forth. As we'll see shortly when we examine an actual page, the offset of these rows can be anywhere on the page.
There's no internal global row number for every row in a table. You can use the combination of page number and slot number on the page to uniquely identify each row in a table.
Examining Data Pages
You can view the contents of a data page by using the DBCC PAGE statement, which allows you to view the page header, data rows, and row offset table for any given data page in a database. (Only a system administrator can use DBCC PAGE.) But because you typically won't need to view the content of a data page, you won't find much about DBCC PAGE in the SQL Server documentation. Nevertheless, in case you want to use it, here's the syntax:
DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] )
The DBCC PAGE command includes the parameters shown in Table 6-3. Figure 6-5 on the following page shows sample output from DBCC PAGE. Note that DBCC TRACEON (3604) instructs SQL Server to return the results to the client instead of to the error log, as is the default for many of the DBCC commands that deal with internals issues.
Table 6-3 Parameters of the DBCC PAGE command.
Parameter |
Description |
|
|---|---|---|
dbid |
|
ID of the database containing the page. |
dbname |
|
Name of the database containing the page. |
filenum |
|
File number containing the page. |
pagenum |
|
Page number within the file. |
printopt |
|
Optional print option; takes one of these values: |
|
0 |
Default; print the buffer header and page header. |
|
1 |
Print the buffer header, page header, each row separately, and the row offset table. |
|
2 |
Print the buffer and page headers, page as a whole, and the offset table. |
cache |
|
Optional; location of page; takes one of these values: |
|
0 |
Print the page as found on disk. |
|
1 |
Default; print the page as found in cache (if it resides in cache); otherwise, retrieve and print the page from disk. |
.gif "Cc917720.f06wh05a(en-us,TechNet.10).gif")
.gif "Cc917720.f06wh05b(en-us,TechNet.10).gif")
Figure 6-5: Sample output from DBCC PAGE.
As you can see, the output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array). The Buffer section shows information about the buffer for the given page. (A buffer in this context is an in-memory structure that manages a page.)
The Page Header section in Figure 6-5 displays the data for all the header fields on the page. (Table 6-2 shows the meaning of most of these fields.) The Data section contains information for each row. For each row, DBCC PAGE indicates the slot position of the row and the offset of the row on the page. The page data is then divided into three parts. The left column indicates the byte position within the row where the displayed data occurs. The next four columns contain the actual data stored on the page, displayed in hexadecimal. The right column contains a character representation of the data. Only character data will be readable in this column, although some of the other data might be displayed.
The Offset Table section shows the contents of the row offset array at the end of the page. In the figure, you can see that this page contains 23 rows, with the first row (indicated by slot 0) beginning at offset 1585 (0x631). The first row physically stored on the page is actually row 6, with an offset in the row offset array of 96. DBCC PAGE displays the rows in slot number order, even though, as you can see by the offset of each of the slots, that isn't the order in which the rows physically exist on the page.
Structure of Data Rows
A table's data rows have the general structure shown in Figure 6-6. The data for all fixed-length columns is stored first, followed by the data for all variable-length columns. Table 6-4 on page 238 shows the information stored in each row.
Status Bits A contains a bitmap indicating properties of the row. The bits have the following meaning:
Bit 0 Versioning information; in SQL Server 7, it's always 0.
Bits 1 through 3 Taken as a 3-bit value, 0 indicates a primary record, 1 indicates a forwarded record, 2 indicates a forwarded stub, 3 indicates an index record, 4 indicates a blob fragment, 5 indicates a ghost index record, and 6 indicates a ghost data record. (We'll discuss forwarding and ghost records in Chapter 8.)
Bit 4 Indicates that a NULL bitmap exists; in SQL Server 7, a NULL bitmap is always present, even if no NULLs are allowed in any column.
Bit 5 Indicates that variable-length columns exist in the row.
Bits 6 and 7 Not used in SQL Server 7.
.gif "Cc917720.f06wh06(en-us,TechNet.10).gif")
Figure 6-6: The structure of data rows.
Within each block of fixed-length or variable-length data, the data is stored in the column order in which the table was created. For example, suppose a table is created with the following statement:
CREATE TABLE Test1 (Col1 int NOT NULL,
Col2 char(25) NOT NULL,
Col3 varchar(60) NULL,
Col4 money NOT NULL,
Col5 varchar(20) NOT NULL)
The fixed-length data portion of this row would contain the data for Col1, followed by the data for Col2, followed by the data for Col4. The variable-length data portion would contain the data for Col3, followed by the data for Col5. For rows that contain only fixed-length data, the following is true:
The first hexadecimal digit of the first byte of the data row will be 1, indicating that no variable-length columns exist. (The first hexadecimal digit is comprised of bits 4 through 7; bits 6 and 7 are always 0, and if no variable-length columns exist, bit 5 is also 0. Bit 4 is always 1, so the value of the four bits is displayed as 1.)
The data row ends after the NULL bitmap, which follows the fixed-length data. (That is, the shaded portion shown in Figure 6-6 won't exist in rows with only fixed-length data.)
The total length of every data row will be the same.
Table 6-4 Information stored in a table's data rows.
Information
Mnemonic
Size
Status Bits A
TagA
1 byte
Status Bits B (not used in SQL Server 7)
TagB
1 byte
Fixed-length size
Fsize
2 bytes
Fixed-length data
Fdata
Fsize --4
Number of columns
Ncol
2 bytes
NULL bitmap(1 byte for each column in table; 1 indicates that the corresponding column is NULL)
Nullbits
Ceiling (Ncol / 8)
Number of variable-length columns
VarCount
2 bytes
Variable column offset array
VarOffset
2 * VarCount
Variable-length data
VarData
VarOff[VarCount]
-- (fsize + 4 + Ceiling
(Ncol / 8) + 2 * VarCount)
Column Offset Array
A data row that has all fixed-length columns has no variable column count or column offset array. A data row that has variable-length columns has a column offset array in the data row with a 2-byte entry for each variable-length column, indicating the position within the row where each column ends. (The terms offset and position aren't exactly interchangeable. Offset is 0-based, and position is 1-based. A byte at an offset of 7 is in the eighth byte position in the row.)
Storage of Fixed-Length and Variable-Length Rows
Two examples follow that illustrate how fixed-length and variable-length data rows are stored. First, the simpler case of an all fixed-length row:
CREATE TABLE Fixed
(Col1 char(5) NOT NULL,
Col2 int NOT NULL,
Col3 char(3) NULL,
Col4 char(6) NOT NULL,
Col5 float NOT NULL)
When this table is created, the following row (or one very much like it) is inserted into the sysindexes system table:
id name indid first minlen ----------- ----- ------ -------------- ------ 1797581442 Fixed 0 0xC70000000100 30
And these rows are inserted into the syscolumns system table:
name colid xtype length xoffset ---- ------ ----- ------ ------- Col1 1 175 5 4 Col2 2 56 4 9 Col3 3 175 3 13 Col4 4 175 6 16 Col5 5 62 8 22
For tables containing only fixed-length columns, the minlen value in sysindexes will be equal to the sum of the column lengths (from syscolumns.length), plus 4 bytes. It won't include the 2 bytes for the number of columns, or the bytes for the null bitmap.
To look at a specific data row in this table, first insert a new row:
INSERT Fixed VALUES ('ABCDE', 123, null, 'CCCC', 4567.8)
Figure 6-7 on the following page shows this row's actual contents on the data page. To run the DBCC PAGE command, we had to take the value of first from the syindexes output above (0xC70000000100) and convert it to a file and page address. In hexadecimal notation, each set of two characters represents a byte. We first had to swap the bytes to get 00 01 00 00 00 C7. The first two groups represent the 2-byte file number, and the last four groups represent the page number. So the file is 0x0001, which is 1, and the page number is 0x000000C7, which is 199 in decimal.
.gif "Cc917720.f06wh07(en-us,TechNet.10).gif")
Figure 6-7: A data row containing all fixed-length columns (header not shown).
Note: The sysindexes table contains three columns that represent page numbers within a database: first, firstIAM, and root. Each is stored in a byte-swapped format. To convert to a decimal file number and page number, you must first swap the bytes and then convert the values from hexadecimal to decimal. You could use the Windows calculator to do the conversion. However, a script has been provided on the companion CD to create a stored procedure called ConvertPageNums, which will convert all these columns in the sysindexes table for you.
Warning: Version 7 of SQL Server does not guarantee that the sysindexes.first column will also indicate the first page of a table. We have found that first is reliable until you begin to perform deletes and updates on the data in the table.
Reading the output takes a bit of practice. DBCC PAGE displays the data rows in groups of 4 bytes at a time. Within each group of four, the bytes are listed in reverse order. So the first group of four bytes is byte 3, byte 2, byte 1, and byte 0. The shaded area in the figure has been expanded to show the bytes in the actual byte-number sequence.
The first byte is Status Bits A, and its value (0x10) indicates that only bit 4 is on, so the row has no variable-length columns. The second byte in the row remains unused. The third and fourth bytes (1e00) indicate the length of the fixed-length fields, which is also the column offset in which the Ncol value can be found. (The byte-swapped value is 0x001e, which translates to 30.) You can identify the data in the row for each column simply by using the offset value in the syscolumns table: the data for column Col1 begins at offset 4, the data for column Col2 begins at offset 9, and so on. As an int, the data in Col2 (7b000000) must be byte-swapped to give us the value 0x0000007b, which is equivalent to 123 in decimal.
Note that the 3 bytes of data for Col3 are all zeros, representing an actual NULL in the column. Because the row has no variable-length columns, the row ends 3 bytes after the data for column Col5. The 2 bytes starting right after the fixed-length data at offset 30 (0500, which is byte-swapped to yield 0x0005) indicate that five columns are in the row. The last byte is the NULL bitmap. The value of 4 means that only the third bit is on, because in our row, the third column was indeed a NULL.
Warning: Fixed-length columns always use the full number of bytes defined for the table, even if the column holds a NULL value. If you've used previous versions of SQL Server, this might take you by surprise. We know of at least one large database at a manufacturing company that expanded to three or four times its size when upgraded to version 7. This is because they had fields in many tables defined as char(255) and allowing NULLs. Some tables had 10 or more such columns, and the vast majority of the data was actually NULL. However, in SQL Server 7, every one of these columns needed a full 255 bytes of storage and the database ballooned in size!
Here's the somewhat more complex case of a table with variable-length data. Each row has three varchar columns:
CREATE TABLE Variable
(Col1 char(3) NOT NULL,
Col2 varchar(250) NOT NULL,
Col3 varchar(5) NULL,
Col4 varchar(20) NOT NULL,
Col5 smallint NULL)
When you create this table, the following row is inserted into the sysindexes system table:
id name indid first minlen ----------- -------- ------ -------------- ------ 1333579789 Variable 0 0xC90000000100 9
And these rows are inserted into the syscolumns system table:
name colid xtype length xoffset ---- ------ ----- ------ ------- Col1 1 175 3 4 Col2 2 167 250 -1 Col3 3 167 5 -2 Col4 4 167 20 -3 Col5 5 52 2 7
Now insert a row into the table:
INSERT Variable VALUES
('AAA', REPLICATE('X',250), NULL, 'ABC', 123)
The REPLICATE function is used here to simplify populating a column; this function builds a string of 250 Xs to be inserted into Col2.
As shown in Figure 6-8, the data for the fixed-length columns is located using the offset value in syscolumns. In this case, Col1 begins at offset 4, and Col5 begins at offset 7.
.gif "Cc917720.f06wh08(en-us,TechNet.10).gif")
Figure 6-8: A data row with variable-length columns (header not shown).
To find the variable-length columns, first locate the column offset tables in the row. Right after the 2-byte field indicating the total number of columns (0500), and the NULL bitmap with the value 0x04, a 2-byte field exists with the value 0x0300 (or 3, decimal) indicating that three variable-length fields exist. Next comes the column offset array. Three 2-byte values indicate the ending position of each of the three variable-length columns: 0e01 is byte-swapped to 0x010e, so the first variable byte column ends at position 270. The next 2-byte offset is also 0e01, so that column has no length and has nothing stored in the variable data area. (Unlike fixed-length fields, if a variable-length field has a NULL value, it takes no room in the data row. SQL Server distinguishes between a varchar containing NULL and an empty string by determining whether the bit for the field is 0 or 1 in the NULL bitmap.) The third 2-byte offset is 1101, which, when byte-swapped, gives us 0x0111. This means the row ends at position 273 (and is a total of 273 bytes in length).
The total storage space needed for a row depends on a number of factors. Variable-length fields add additional overhead to a row, and their actual size is probably unpredictable. Even for fixed-length fields, the number of bytes of overhead can change depending on the number of columns in the table. In the earlier example pertaining to Figure 6-1, we mentioned that 10 bytes of overhead existed if a row contained all fixed-length columns. For that row, 10 is the correct number. The size of the NULL bitmap needs to be long enough to store a bit for every column in the row. In the Figure 6-1 example, the table had 11 columns, so the NULL bitmap needed to be 2 bytes. In the examples illustrated by Figures 6-7 and 6-8, the table had only 5 columns, so the NULL bitmaps needs only a single byte. Don't forget that the total row overhead also needs to include the 2 bytes for each row in the offset table at the bottom of the page.
Page Linkage
Unlike earlier versions of SQL Server, SQL Server 7 doesn't connect the individual data pages of a table in a doubly linked list unless the table has a clustered index. All levels of indexes are linked together, and since the data is considered the leaf level of a clustered index, SQL Server does maintain the linkage. However, for a heap, there is no such linked list connecting the pages to each other. The only way that SQL Server determines which pages belong to a table is by inspecting the IAMs for the table.
If the table has a clustered index, you can use the M_nextPage and M_prevPage values in the page header information to determine the ordering of pages in the list. Alternatively, you can use the DBCC EXTENTINFO command to get a list of all the extents that belong to an object. This example uses the Orders table in the Northwind database:
dbcc extentinfo ('Northwind', 'Orders', 1 )
The last argument indicates only extents for index 1, which is the clustered index (and includes the data). Here is the output:
file_id page_id pg_alloc ext_size obj_id index_id avg_used ----------- ----------- ----------- ----------- ----------- -------- -------- 1 143 1 1 357576312 1 25 1 145 1 1 357576312 1 25 1 291 1 1 357576312 1 25 1 292 1 1 357576312 1 25 1 293 1 1 357576312 1 25 1 294 1 1 357576312 1 25 1 295 1 1 357576312 1 25 1 296 1 1 357576312 1 25 1 304 8 8 357576312 1 25 1 328 5 8 357576312 1 25
Notice that the first eight rows indicate an extent size (ext_size) of 1. As discussed in Chapter 5, the first eight pages of a table are allocated from mixed extents. Only after the table has reached eight pages does SQL Server allocate uniform extents of eight pages each. The last two rows in the table show this situation, and the page number (page_id) column gives the page number of the first page of the extent. Note that the last extent (starting on page 328) has used only five of its pages at this time.
Text and Image Data
As mentioned earlier, if a table contains text or image data, the actual data isn't stored on the data pages with the rest of the data for a row. Instead, SQL Server stores a 16-byte pointer in the data row that indicates where the actual data can be found. In SQL Server 7, individual text, ntext, and image pages aren't limited to holding data for only one occurrence of a text, ntext, or image column. A text, ntext, or image page can hold data from multiple columns and from multiple rows; the page can even have a mix of text, ntext, and image data. One text or image page can hold only text or image data from a single table.
Text or image data is stored in a collection of 8-KB pages that aren't necessarily located next to each other. In SQL Server 7, the pages are logically organized in a B-tree structure, while in earlier versions of SQL Server, pages were linked together in a page chain. The advantage of the SQL Server 7 method is that operations starting in the middle of the string are more efficient. SQL Server 7 can quickly navigate the tree, while earlier versions of SQL Server had to scan through the page chain. The structure of the B-tree differs slightly depending on whether the amount of data is less than or more than 32 KB. (See Figure 6-9 for the general structure.) We'll discuss B-trees in more detail in the next section of the chapter.
.gif "Cc917720.f06wh09(en-us,TechNet.10).gif")
Figure 6-9: A text column pointing to a B-tree that contains the blocks of data.
If the amount of data is less than 32 KB, the text pointer in the data row points to an 84-byte text root structure. This forms the root node of the B-tree structure. The root node points to the blocks of text or image data. While the data for text, ntext, and image columns is arranged logically in a B-tree, physically both the root node and the individual blocks of data are spread throughout the text, ntext, and image pages for the table. They're placed wherever space is available. The size of each block of data is determined by the size written by an application. Small blocks of data will be combined to fill a page. If the amount of data is less than 64 bytes, it's all stored in the root structure.
If the amount of data for one occurrence of a text or image column exceeds 32 KB, SQL Server starts building intermediate nodes between the data blocks and the root node. The root structure and the data blocks are interleaved throughout the text and image pages in the same manner as described earlier. The intermediate nodes, however, are stored in pages that aren't shared between occurrences of text or image columns. Each page storing intermediate nodes contains only intermediate nodes for one text or image column in one data row.
Indexes
Indexes are the other significant user-defined, on-disk data structure (in addition to tables). An index provides fast access to data when the data can be searched by the value that is the index key. We discussed indexes in Chapter 3, but some of that information bears repeating and elaboration here in our discussion of tables. (If indexing is a topic of interest to you, make sure you read "The Index Manager" section of Chapter 3. Indexing is also discussed in the context of query tuning in Chapter 14.) Think of indexes in your everyday life. You're reading a SQL Server book, and you want to find entries for the word SELECT. You have two basic choices for doing this: you can open the book and scan through it page by page, or you can look in the index in the back, find the word SELECT, and then turn to the page numbers listed. That is exactly how an index works in SQL Server. SQL Server supports clustered and nonclustered indexes (discussed further in the next two sections). Both types use standard B-trees, as shown in Figure 6-10.
A B-tree provides fast access to data by searching on a key value of the index. B-trees cluster records with similar keys. The B stands for balanced, and balancing the tree is a core feature of a B-tree's usefulness. The trees are managed, and branches are grafted as necessary so that navigating down the tree to find a value and locate a specific record always takes only a few page accesses. Because the trees are balanced, finding any record requires about the same amount of resources, and retrieval speed will be consistent because the index has the same depth throughout.
.gif "Cc917720.f06wh10(en-us,TechNet.10).gif")
Figure 6-10: A standard B-tree for a SQL Server index.
An index consists of a tree with a root from which the navigation begins, possible intermediate index levels, and bottom-level leaf pages. The index is used to find the correct leaf page. The number of levels in an index will vary depending on the number of rows in the table and the size of the key column or columns for the index. If you create an index using a large key, fewer entries will fit on a page, so more pages (and possibly more levels) will be needed for the index. On a qualified retrieval or delete, the correct leaf page will be the lowest page of the tree in which one or more rows with the specified key or keys reside. In any index, the leaf level contains every key value, in key sequence.
Clustered Indexes
The leaf level of a clustered index contains the data pages, not just the index keys. A clustered index keeps the data in a table physically ordered around the key. Deciding which key to cluster on is an important performance consideration. When the index is traversed to the leaf level, the data itself has been retrieved, not simply pointed to.
Because data can be physically ordered in only one way, a table can have only one clustered index. The query optimizer strongly favors a clustered index because it allows the data to be found directly at the leaf level. Because it defines the actual order of the data, a clustered index allows especially fast access for queries looking for a range of values. The query optimizer detects that only a certain range of data pages must be scanned. Most tables should have a clustered index. If your table will have only one index, it generally should be clustered.
In SQL Server 7, all clustered indexes are unique. If a clustered index is built without specifying the unique keyword, SQL Server will force uniqueness by adding a uniqueifier to the rows when necessary. This uniqueifier is a 4-byte value added as a secondary sort key to only the rows that have duplicates of their primary sort key.
Nonclustered Indexes
In a nonclustered index, the lowest level of the tree (the leaf level) contains a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. As we saw in Chapter 3, a bookmark can have one of two forms. If the table has a clustered index, the bookmark is the clustered index key. If the table is a heap (in other words, it has no clustered index), the bookmark will be a RID, which is an actual row locator in the form File#:Page#:Slot#. (In contrast, in a clustered index, the leaf page is the data page.)
The presence or absence of a nonclustered index doesn't affect how the data pages are organized, so you're not restricted to having only one nonclustered index per table, as is the case with clustered indexes. Each table can include as many as 249 nonclustered indexes, but you'll usually want to have far less than this number.
Note: If you're using an index to enforce uniqueness, there's a better way. PRIMARY KEY and UNIQUE constraints make use of indexing for enforcement. We'll discuss these constraints shortly.
Searching for data using a nonclustered index requires first that the index is traversed and then that the record pointed to is retrieved. For example, to get to a data page using an index with a depth of three—a root page, one intermediate page, and the leaf page—all three index pages must be traversed. If the leaf level contains a clustered index key, all the levels of the clustered index will be traversed to locate the specific row. The clustered index will probably have two levels because in most cases, the clustered index is one level shallower than a nonclustered index. The data page still must be retrieved, although it has been exactly identified, so there's no need to scan the entire table. Still, it takes six logical I/O operations to get one data page. You can see that a nonclustered index is a win only if it's highly selective.
Structure of Index Pages
Index pages are structured much like data pages. As with all other types of pages in SQL Server, index pages have a fixed size of 8 KB, or 8192 bytes. Index pages also have a 96-byte header but, unlike data pages, no offset array appears at the end of the page. Each index has a row in the sysindexes table, with an indid value of either 1, for a clustered index, or a number between 2 and 250, indicating a nonclustered index. (An indid value of 255 indicates text or image information.) The root column value contains a file number and page number where the root of the index can be found. You can then use DBCC PAGE to examine index pages, just as you do for data pages.
Creating Indexes
The typical syntax for creating an index is straightforward:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column_name [,
column_name]...)
CREATE INDEX has some additional options available for specialized purposes:
[WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE] ] [ON filegroup]
FILLFACTOR is probably the most commonly used of these options. FILLFACTOR lets you reserve some space on each leaf page of an index. (In a clustered index, this equals the data page.) By reserving some free space with FILLFACTOR, you can later avoid the need to split pages to make room for an entry. (Refer to the discussion of index management and page splitting in Chapter 3.) But remember that FILLFACTOR is not maintained; it indicates only how much space is reserved with the existing data. If you need to, you can use the DBCC DBREINDEX command to rebuild the index and to reestablish the original FILLFACTOR specified.
Tip If you'll be rebuilding all of a table's indexes, simply specify the clustered index with DBCC DBREINDEX. Doing so internally rebuilds the entire table and all nonclustered indexes.
FILLFACTOR isn't usually specified on an index-by-index basis, but you can specify it this way for fine-tuning. If FILLFACTOR isn't specified, the serverwide default is used. The value is set for the server via sp_configure, fillfactor. This value is 0 by default, which means that leaf pages of indexes are made as full as possible. FILLFACTOR generally applies only to the index's leaf page (the data page for a clustered index). In specialized and high-use situations, you might want to reserve space in the intermediate index pages to avoid page splits there, too. You can do this by using the PAD_INDEX option, which uses the same value as FILLFACTOR.
The DROP_EXISTING option is valid only when creating a clustered index and specifies that the given table's clustered index should be dropped and rebuilt. Then all existing nonclustered indexes are updated. Because nonclustered indexes must go through the clustered index to gain access to rows in a table, the DROP_EXISTING option prevents the nonclustered indexes from having to be rebuilt twice. Normally, when a clustered index is dropped, every nonclustered index has to be rebuilt to change its bookmarks to RIDs instead of the clustering keys. Then, if a clustered index is built (or rebuilt), all the nonclustered indexes must be rebuilt again to update the bookmarks. The DROP_EXISTING option to the CREATE INDEX command allows a clustered index to be rebuilt without affecting any nonclustered indexes on the table.
You can ensure the uniqueness of a key by using the PRIMARY KEY and UNIQUE constraints, which we'll discuss beginning on page 255. These constraints work by making a unique index on the key value or values. If an UPDATE or INSERT statement would affect multiple rows, and if even one row is found that would cause duplicate keys in the table, the entire statement is aborted and no rows are affected. With a unique index, you can use the IGNORE_DUP_KEY option so that a nonunique error on a multiple-row UPDATE or INSERT won't cause the entire statement to be rolled back. The nonunique row will be discarded, and all other rows will be inserted or updated. IGNORE_DUP_KEY doesn't allow the uniqueness of the index to be violated; instead, it makes a violation in a multiple-row data modification nonfatal to all the nonviolating rows.
The STATISTICS_NORECOMPUTE option will be discussed in Chapter 14 of the book, when we discuss statistics maintenance.
User-Defined Datatypes
A user-defined datatype (UDDT) provides a convenient way for you to guarantee consistent use of underlying native datatypes for columns known to have the same domain of possible values. For example, perhaps your database will store various phone numbers in many tables. Although no single, definitive way exists to store phone numbers, in this database consistency is important. You can create a phone_number UDDT and use it consistently for any column in any table that keeps track of phone numbers to ensure that they all use the same datatype. Here's how to create this UDDT:
EXEC sp_addtype phone_number, 'varchar(20)', 'not null'
And here's how to use the new UDDT when creating a table:
CREATE TABLE customer ( cust_id smallint NOT NULL, cust_name varchar(50) NOT NULL, cust_addr1 varchar(50) NOT NULL, cust_addr2 varchar(50) NOT NULL, cust_city varchar(50) NOT NULL, cust_state char(2) NOT NULL, cust_zip varchar(10) NOT NULL, cust_phone phone_number, cust_fax varchar(20) NOT NULL, cust_email varchar(30) NOT NULL, cust_web_url varchar(20) NOT NULL)
When the table is created, internally the datatype of cust_phone is known to be varchar(20). Notice that both cust_phone and cust_fax are varchar(20), although cust_phone has that declaration through its definition as a UDDT.
Here's how the customer table appears in the entries in the syscolumns table for this table:
SELECT colid, name, xtype, length, xusertype, offset
FROM syscolumns WHERE id=object_id('customer')
colid name xtype length xusertype offset
------ ---------- ----- ------ --------- ------
1 cust_id 52 2 52 2
2 cust_name 167 50 167 -1
3 cust_addr1 167 50 167 -2
4 cust_addr2 167 50 167 -3
5 cust_city 167 50 167 -4
6 cust_state 175 2 175 4
7 cust_zip 167 10 167 -5
8 cust_phone 167 20 261 -6
9 cust_fax 167 20 167 -7
10 cust_email 167 30 167 -8
11 cust_web_u 167 20 167 -9
You can see that both the cust_phone and cust_fax columns have the same xtype (datatype), although the cust_phone column shows that the datatype is a UDDT (xusertype = 261). The type is resolved when the table is created, and the UDDT can't be dropped or changed as long as one or more tables are currently using it. Once declared, a UDDT is static and immutable, so no inherent performance penalty occurs in using a UDDT instead of the native datatype.
The use of UDDTs can make your database more consistent and clear. SQL Server implicitly converts between compatible columns of different types (either native types or UDDTs of different types).
Currently, UDDTs don't support the notion of subtyping or inheritance, nor do they allow a DEFAULT value or CHECK constraint to be declared as part of the UDDT itself. These powerful object-oriented concepts will likely make their way into future versions of SQL Server. These limitations not withstanding, UDDT functionality is a dynamic and often underused feature of SQL Server.
Identity Property
It is common to provide simple counter-type values for tables that don't have a natural or efficient primary key. Columns such as customer_number are usually simple counter fields. SQL Server provides the Identity property that makes generating unique numeric values easy. Identity isn't a datatype; it's a column property that you can declare on a whole-number datatype such as tinyint, smallint, int, and numeric/decimal (having a scale of zero). Each table can have only one column with the Identity property. The table's creator can specify the starting number (seed) and the amount that value increments or decrements. If not otherwise specified, the seed value starts at 1 and increments by 1, as shown in this example:
CREATE TABLE customer ( cust_id smallint IDENTITY NOT NULL, cust_name varchar(50) NOT NULL, )
To find out which seed and increment values were defined for a table,
you can use the IDENT_SEED(tablename) and IDENT_INCR(tablename) functions. The statement
SELECT IDENT_SEED('CUSTOMER'), IDENT_INCR('CUSTOMER')
produces
1 1
for the customer table because values weren't explicitly declared and the default values were used.
This next example explicitly starts the numbering at 100 (seed) and increments the value by 20:
CREATE TABLE customer ( cust_id smallint IDENTITY(100,20) NULL, cust_name varchar(50) NOT NULL, )
The value automatically produced with the Identity property will normally be unique, but it isn't guaranteed by the Identity property itself, nor is it guaranteed to be consecutive. For efficiency, a value is considered used as soon as it is presented to a client doing an INSERT operation. If that client doesn't ultimately commit the INSERT, the value will never appear, so a break will occur in the consecutive numbers. An unacceptable level of serialization would exist if the next number couldn't be parceled out until the previous one was actually committed or rolled back. (And even then, as soon as a row was deleted, the values would no longer be consecutive. Gaps are inevitable.)
Note: If you need exact sequential values without gaps, Identity isn't the appropriate feature to use. Instead, you should implement a next_number-type table in which you can make the operation of bumping the number contained there part of the larger transaction (and incur the serialization of queuing for this value).
To temporarily disable the automatic generation of values in an identity column, use the SET IDENTITY_INSERT tablename ON option. In addition to filling in gaps in the identity sequence, this option is useful for tasks such as bulk loading data in which the previous values already exist. For example, perhaps you're loading a new database with customer data from your previous system. You might want to preserve the previous customer numbers but have new ones automatically assigned using Identity. The SET option was created exactly for cases like this.
Because of the SET option's ability to override values, the Identity property alone doesn't enforce uniqueness of a value within the table. Although Identity will generate a unique number, it can be overridden with the SET option. To enforce uniqueness (which you'll almost always want to do when using Identity), you should also declare a UNIQUE or PRIMARY KEY constraint on the column. If you insert your own values for an identity column (using SET IDENTITY_INSERT), when automatic generation resumes, the next value will be the next incremented value (or decremented value) of the highest value that exists in the table, whether it was generated previously or explicitly inserted.
Tip If you're using the bcp utility for bulk loading data, be aware of the /E (uppercase) parameter if your data already has assigned values that you want to keep for a column having the Identity property. You can also use the Transact-SQL BULK INSERT command with the KEEPIDENTITY option. For more information, see the SQL Server documentation for bcp and BULK INSERT.
The keyword IDENTITYCOL automatically refers to the specific column in a table, whatever its name, that has the Identity property. If cust_id is that column, you can refer to the column as IDENTITYCOL without knowing or using the column name, or you can refer to it explicitly as cust_id. For example, the following two statements work identically and return the same data:
SELECT IDENTITYCOL FROM customer SELECT cust_id FROM customer
The column name returned to the caller is cust_id, not IDENTITYCOL, in both of these cases.
When inserting rows, you must omit an identity column from the column list and VALUES section. (The only exception is when the IDENTITY_INSERT option is on.) If you do supply a column list, you must omit the column for which the value will be automatically supplied. Here are two valid INSERT statements for the customer table shown earlier:
INSERT customer VALUES ('ACME Widgets')
INSERT customer (cust_name) VALUES ('AAA Gadgets')
Selecting these two rows produces this output:
cust_id cust_name ------- --------- 1 ACME Widgets 2 AAA Gadgets (2 row(s) affected)
Sometimes in applications, it's desirable to immediately know the value produced by Identity for subsequent use. For example, a transaction might first add a new customer and then add an order for that customer. To add the order, you probably need to use the cust_id. Rather than select the value from the customer table, you can simply select the special system function @@IDENTITY, which contains the last identity value used by that connection. It doesn't necessarily provide the last value inserted into the table, however, because another user might have subsequently inserted data. If multiple INSERT statements are carried out in a batch to the same or different tables, the variable has the value for the last statement only.
You can't define the Identity property as part of a UDDT, but you can declare the Identity property on a column that uses a UDDT. A column having the Identity property must always be declared NOT NULL (either explicitly or implicitly), or error message number 8147 will result from the CREATE TABLE statement, and CREATE won't succeed. Likewise, you can't declare the Identity property and a DEFAULT on the same column. To check that the current identity value is valid based on the current maximum values in the table, and to reset it if an invalid value is found (which should never be the case), use the DBCC CHECKIDENT (tablename) statement.
Identity values are fully recoverable. If a system outage occurs while insert activity is taking place with tables that have identity columns, the correct value will be recovered when SQL Server is restarted. This is accomplished during the SQL Server checkpoint processing by flushing the current identity value for all tables. For activity beyond the last checkpoint, subsequent values are reconstructed from the transaction log during the standard database recovery process. Any inserts into a table having the Identity property are known to have changed the value, and the current value is retrieved from the last INSERT statement (post-checkpoint) for each table in the transaction log. The net result is that when the database is recovered, the correct current identity value is also recovered.
SQL Server, unlike some other products, doesn't require that you maintain a large safety buffer or burning set. After a system failure, products that don't recover their autosequencing values sometimes add a large number to the last known value on recovery to ensure that a number isn't reused. This can result in odd and probably undesirable values. For example, values might be progressing nicely as 101, 102, 103, 104, 105, and so on. Then a system outage occurs. Because the next value isn't recovered, these products don't detect exactly where to resume (104? 105? 106? 107?). To avoid reusing a number, these products simply add a safety buffer; the number after 105 might be 1106, with a safety buffer of 1000. This can result in some odd patterns for what are loosely thought of as sequential numbers (for example, 102, 103, 104, 105, 1106, 1107, 2108, 2109, 3110). Because SQL Server recovers the exact value, large gaps like this never occur.
In rare cases, the identity value can get out of sync. If this happens, you can use the DBCC CHECKINDENT command to reset the identity value to the appropriate number. In addition, the RESEED option to this command allows you to set a new starting value for the identity sequence. Take a look in the online documentation for complete details.
Constraints
Constraints provide a powerful yet easy way for you to enforce relationships between tables (referential integrity) by declaring primary, foreign, and alternate keys. CHECK constraints enforce domain integrity. Domain integrity enforces valid entries for a given column by restricting the type (through datatypes), the format (through CHECK constraints and rules), or the range of possible values (through REFERENCES to foreign keys, CHECK constraints, and rules). The declaration of a column as either NULL or NOT NULL can be thought of as a type of constraint. And you can declare default values for use when a value isn't known at insert or update time.
PRIMARY KEY and UNIQUE Constraints
A central tenet of the relational model is that every tuple (row) in a relation (table) is in some way unique and can be distinguished in some way from every other row in the table. The combination of all columns in a table could be used as this unique identifier, but in practice, the identifier is usually at most the combination of a handful of columns, and often it's just one column: the primary key. Although some tables might have multiple unique identifiers, each table can have only one primary key. For example, perhaps the employee table maintains both an Emp_ID column and an SSN (Social Security number) column, both of which can be considered unique. Such column pairs are often referred to as alternate keys or candidate keys, although both terms are design terms and aren't used by the ANSI SQL standard or by SQL Server. In practice, one of the two columns is logically promoted to primary key with the PRIMARY KEY constraint, and the other will usually be declared by a UNIQUE constraint. Although neither the ANSI SQL standard nor SQL Server require it, it's good practice to always declare a PRIMARY KEY constraint on every table. Furthermore, you must designate a primary key for a table that will be published for transaction-based replication.
Internally, PRIMARY KEY and UNIQUE constraints are handled almost identically, so we'll discuss them together here. Declaring a PRIMARY KEY or UNIQUE constraint simply results in a unique index being created on the specified column or columns, and this index enforces the column's uniqueness, in the same way that a unique index created manually on a column would. The query optimizer makes decisions based on the presence of the unique index rather than on the fact that a column was declared as a primary key. How the index got there in the first place is irrelevant to the optimizer.
Nullability
All columns that are part of a primary key must be declared (either explicitly or implicitly) as NOT NULL. Columns that are part of a UNIQUE constraint can be declared to allow NULL. However, for the purposes of unique indexes, all NULLs are considered equal. So if the unique index is on a single column, only one NULL value can be stored (another good reason to try to avoid NULL whenever possible). If the unique index is on a composite key, one of the columns can have many NULLs, as long as the value in the other column is unique.
Index Attributes
The index attributes of CLUSTERED or NONCLUSTERED can be explicitly specified when declaring the constraint. If not specified, the index for a UNIQUE constraint will be nonclustered, and the index for a PRIMARY KEY constraint will be clustered (unless CLUSTERED has already been explicitly stated for a unique index, because only one clustered index can exist per table). The index FILLFACTOR attribute can be specified if a PRIMARY KEY or UNIQUE constraint is added to an existing table using the ALTER TABLE command. FILLFACTOR doesn't make sense in a CREATE TABLE statement because the table has no existing data, and FILLFACTOR on an index affects how full pages are only when the index is initially created. FILLFACTOR isn't maintained when data is added.
Choosing Keys
Try to keep the key lengths as compact as possible. Columns that are the primary key or that are unique are most likely to be joined and frequently queried. Compact key lengths allow more index entries to fit on a given 8-KB page, reducing I/O, increasing cache hits, and speeding character matching. When no naturally efficient compact key exists, it's often useful to manufacture a surrogate key using the Identity property on an int column. (If int doesn't provide enough range, a good second choice is a numeric column with the required precision and with scale 0.) You might use this surrogate as the primary key, use it for most join and retrieval operations, and declare a UNIQUE constraint on the natural but inefficient columns that provide the logical unique identifier in your data. (Or you might dispense with creating the UNIQUE constraint altogether if you don't need to have SQL Server enforce the uniqueness. Indexes slow performance of data modification statements because the index, as well as the data, must be maintained.)
Another reason for keeping your primary key short is relevant when the primary key has a clustered index on it (the default). Because clustered index keys are used as the locators for all nonclustered indexes, the clustered key will occur over and over again. A large key for your clustered index will mean your nonclustered indexes will end up large.
Although it's permissible to do so, don't create a PRIMARY KEY constraint on a column of type float or real. Because these are approximate datatypes, the uniqueness of such columns is also approximate, and the results can sometimes be unexpected.
Removing Constraints
You can't directly drop a unique index created as a result of a PRIMARY KEY or UNIQUE constraint by using the DROP INDEX statement. Instead, you must drop the constraint by using ALTER TABLE DROP CONSTRAINT (or you have to drop the table itself). This feature was designed so that a constraint can't be compromised accidentally by someone who doesn't realize that the index is being used to enforce the constraint. There is no way to temporarily disable a PRIMARY KEY or UNIQUE constraint. If disabling is required, use ALTER TABLE DROP CONSTRAINT, and then later restore the constraint by using ALTER TABLE ADD CONSTRAINT. If the index you use to enforce uniqueness is clustered, when you add the constraint to an existing table, the entire table and all nonclustered indexes are internally rebuilt to establish the cluster order. This can be a time-consuming task, and it requires about 1.2 times the existing table space as a temporary work area in the database would require (2.2 times in total, counting the permanent space needed) so that the operation can be rolled back if necessary.
Creating Constraints
Typically, you declare PRIMARY KEY and UNIQUE constraints when you create the table (CREATE TABLE). However, you can add or drop both by subsequently using the ALTER TABLE command. To simplify matters, you can declare a PRIMARY KEY or UNIQUE constraint that includes only a single column on the same line where you define that column in the CREATE TABLE statement. Such a constraint is known as a column-level constraint. Or you can declare the constraint after all columns have been defined; this constraint is known as a table-level constraint. Which approach you use is largely a matter of personal preference. We find the column-level syntax more readable and clear. You can use abbreviated syntax with a column-level constraint, in which case SQL Server will generate the name for the constraint, or you can use a slightly more verbose syntax that uses the clause CONSTRAINT name. A table-level constraint must always be named by its creator. If you'll be creating the same database structure across multiple servers, it's probably wise to explicitly name column-level constraints so that the same name will be used on all servers.
Following are examples of three different ways to declare a PRIMARY KEY constraint on a single column. All methods cause a unique, clustered index to be created. Note the (abridged) output of the sp_helpconstraint procedure for each—especially the constraint name.
EXAMPLE 1
CREATE TABLE customer ( cust_id int IDENTITY NOT NULL PRIMARY KEY, cust_name varchar(30) NOT NULL ) GO EXEC sp_helpconstraint customer GO >>>> Object Name ----------- customer constraint_type constraint_name ----------------------- ------------------------------ PRIMARY KEY (clustered) PK__customer__68E79C55
EXAMPLE 2
CREATE TABLE customer
(
cust_id int IDENTITY NOT NULL
CONSTRAINT cust_pk PRIMARY KEY,
cust_name varchar(30) NOT NULL
)
GO
EXEC sp_helpconstraint customer
GO
>>>>
Object Name
-----------
customer
constraint_type constraint_name
----------------------- ---------------
PRIMARY KEY (clustered) cust_pk
No foreign keys reference this table.
EXAMPLE 3
CREATE TABLE customer ( cust_id int IDENTITY NOT NULL, cust_name varchar(30) NOT NULL, CONSTRAINT customer_PK PRIMARY KEY (cust_id) ) GO EXEC sp_helpconstraint customer GO >>>> Object Name ----------- customer constraint_type constraint_name --------------- --------------- PRIMARY KEY (clustered) customer_PK No foreign keys reference this table.
In Example 1, the constraint name bears the seemingly cryptic name of PK__customer__68E79C55. There is some method to this apparent madness—all types of column-level constraints use this naming scheme (which we'll discuss later in this chapter). Whether you choose a more intuitive name of your own, such as customer_PK in Example 3, or the less intuitive (but information-packed), system-generated name produced with the abbreviated column-level syntax is up to you. However, when a constraint involves multiple columns, as is often the case for PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints, the only way to syntactically declare them is with a table-level declaration. The syntax for creating constraints is quite broad and has many variations. A given column could have the Identity property, be part of a primary key, be a foreign key, and be declared NOT NULL. The order of these specifications isn't mandated and can be interchanged. Here's an example of creating a table-level, UNIQUE constraint on the combination of multiple columns. (The primary key case is essentially identical.)
CREATE TABLE customer_location
(
cust_id int NOT NULL,
cust_location_number int NOT NULL,
CONSTRAINT customer_location_unique UNIQUE
(cust_id, cust_location_number)
)
GO
EXEC sp_helpconstraint customer_location
GO
>>>
Object Name
-----------------
customer_location
constraint_type constraint_name constraint_keys
--------------- ------------------------ ---------------
UNIQUE customer_location_unique cust_id,
(non-clustered) cust_location_number
No foreign keys reference this table.
As noted earlier, a unique index is created to enforce either a PRIMARY KEY or a UNIQUE constraint. The name of the index is based on the constraint name, whether it was explicitly named or system generated. The index used to enforce the CUSTOMER_LOCATION_UNIQUE constraint in the above example is also named customer_location_unique. The index used to enforce the column-level, PRIMARY KEY constraint of the customer table in Example 1 is named PK__customer__68E79C55, which is the system-generated name of the constraint. You can use the sp_helpindex stored procedure to see information for all indexes of a given table. For example:
EXEC sp_helpindex customer
>>>
index_name index_description index_keys
------------------------------ ------------------ ----------
PK__customer__68E79C55 clustered, unique, cust_id
primary key
located on default
You can't directly drop an index created to enforce a PRIMARY KEY or UNIQUE constraint. However, you can rebuild the index by using DBCC DBREINDEX, which is useful when you want to reestablish a given FILLFACTOR for the index or to reorganize the table, in the case of a clustered index.
FOREIGN KEY Constraints
As the term implies, logical relationships between tables is a fundamental concept of the relational model. In most databases, certain relationships must exist (that is, the data must have referential integrity), or the data will be logically corrupt.
SQL Server automatically enforces referential integrity through the use of FOREIGN KEY constraints. (This feature is sometimes referred to as declarative referential integrity, or DRI, to distinguish it from other features, such as triggers, that you can also use to enforce the existence of the relationships.)
A foreign key is one or more columns of a table whose values must be equal to a value in another column having a PRIMARY KEY or UNIQUE constraint in another table (or the same table when it references itself). After the foreign key is declared in a CREATE TABLE or ALTER TABLE statement, SQL Server restricts a row from being inserted or updated in a table that references another table if the relationship wouldn't be established. SQL Server also restricts the rows in the table being referenced from being deleted or changed in a way that would destroy the relationship.
Here's a simple way to declare a primary key/foreign key relationship:
CREATE TABLE customer ( cust_id int NOT NULL IDENTITY PRIMARY KEY, cust_name varchar(50) NOT NULL ) (continued) CREATE TABLE orders ( order_id int NOT NULL IDENTITY PRIMARY KEY, cust_id int NOT NULL REFERENCES customer(cust_id) )
The orders table contains the column cust_id, which references the primary key of the customer table. An order (order_id) must not exist unless it relates to an existing customer (cust_id). You can't delete a row from the customer table if a row that references it currently exists in the orders table, and you can't modify the cust_id column in a way that would destroy the relationship.
The previous example shows the syntax for a column-level constraint, which you can declare only if the foreign key is a single column. This syntax uses the keyword REFERENCES, and the term "foreign key" is implied but not explicitly stated. The name of the FOREIGN KEY constraint is generated internally, following the same general form described earlier for PRIMARY KEY and UNIQUE constraints. Here's a portion of the output of sp_helpconstraint for both the customer and orders tables. (The tables were created in the pubs sample database.)
EXEC sp_helpconstraint customer
>>>
Object Name
------------
customer
constraint_type constraint_name constraint_keys
---------------- ---------------------- ---------------
PRIMARY KEY PK__customer__07F6335A cust_id
(clustered)
Table is referenced by
----------------------------------------------
pubs.dbo.orders: FK__orders__cust_id__0AD2A005
EXEC sp_helpconstraint orders
>>>
Object Name
-----------
orders
constraint_type constraint_name constraint_keys
----------------- ----------------------------- ---------------
FOREIGN KEY FK__orders__cust_id__0AD2A005 cust_id
REFERENCES
pubs.dbo.
customer(cust_id)
PRIMARY KEY PK__orders__09DE7BCC order_id
(clustered)
No foreign keys reference this table.
As with PRIMARY KEY and UNIQUE constraints, you can declare FOREIGN KEY constraints at the column and table levels. If the foreign key is a combination of multiple columns, you must declare FOREIGN KEY constraints at the table level. The following example shows a table-level, multiple-column FOREIGN KEY constraint:
CREATE TABLE customer
(
cust_id int NOT NULL,
location_num smallint NULL,
cust_name varchar(50) NOT NULL,
CONSTRAINT CUSTOMER_UNQ UNIQUE CLUSTERED (location_num, cust_id)
)
CREATE TABLE orders
(
order_id int NOT NULL IDENTITY CONSTRAINT ORDER_PK
PRIMARY KEY NONCLUSTERED,
cust_num int NOT NULL,
cust_loc smallint NULL,
CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (cust_loc, cust_num)
REFERENCES customer (location_num, cust_id)
)
GO
EXEC sp_helpconstraint customer
EXEC sp_helpconstraint orders
GO
>>>
Object Name
-----------
customer
(continued) constraint_type constraint_name constraint_keys
------------------ ---------------- --------------------
UNIQUE (clustered) CUSTOMER_UNQ location_num, cust_id
Table is referenced by
---------------------------
pubs.dbo.orders: FK_ORDER_CUSTOMER
Object Name
------------
orders
constraint_type constraint_name constraint_keys
---------------- ---------------- ------------------
FOREIGN KEY FK_ORDER_CUSTOMER cust_loc, cust_num
REFERENCES pubs.dbo.customer
(location_num, cust_id)
PRIMARY KEY ORDER_PK order_id
(non-clustered)
No foreign keys reference this table.
The previous example also shows the following variations of how you can create constraints:
FOREIGN KEY constraints You can use a FOREIGN KEY constraint to reference a UNIQUE constraint (an alternate key) instead of a PRIMARY KEY constraint. (Note, however, that referencing a PRIMARY KEY is much more typical and is generally better practice.)
Matching column names and datatypes You don't have to use identical column names in tables involved in a foreign key reference, but it's often good practice. The cust_id and location_num column names are defined in the customer table. The orders table, which references the customer table, uses the names cust_num and cust_loc. Although the column names of related columns can differ, the datatypes of the related columns must be identical, except for nullability and variable-length attributes. (For example, a column of char(10) NOT NULL can reference one of varchar(10) NULL, but it can't reference a column of char(12) NOT NULL. A column of type smallint can't reference a column of type int.) Notice in the preceding example that cust_id and cust_num are both int NOT NULL and that location_num and cust_loc are both smallint NULL.
UNIQUE columns and NULL values You can declare a UNIQUE constraint on a column that allows NULL, but only one entirely NULL UNIQUE column is allowed when multiple columns make up the constraint. More precisely, the UNIQUE constraint would allow one entry for each combination of values that includes a NULL, as though NULL were a value in itself. For example, if the constraint contained two int columns, exactly one row of each of these combinations (and so on) would be allowed:
NULL |
NULL |
0 |
NULL |
NULL |
0 |
1 |
NULL |
NULL |
1 |
This case is questionable: NULL represents an unknown, yet using it this way clearly implies that NULL is equal to NULL. (As you'll recall, we recommend that you avoid using NULLs, especially in key columns.)
Index attributes You can specify the CLUSTERED and NONCLUSTERED index attributes for a PRIMARY KEY or UNIQUE constraint. (You can also specify FILLFACTOR when using ALTER TABLE to add a constraint.) The index keys will be created in the order in which you declare columns. In the preceding example, we specified location_num as the first column of the UNIQUE constraint, even though it follows cust_id in the table declaration, because we want the clustered index to be created with location_num as the lead column of the B-tree and, consequently, to keep the data clustered around the location values.
CONSTRAINT syntax You can explicitly name a column-level constraint by declaring it with the more verbose CONSTRAINT syntax on the same line or section as a column definition. You can see this syntax for the PRIMARY KEY constraint in the orders table. There's no difference in the semantics or run-time performance of a column-level or table-level constraint, and it doesn't matter to the system whether the constraint name is user specified or system generated. (A slight added efficiency occurs when you initially create a column-level constraint rather than a table-level constraint on only one column, but typically, only run-time performance matters.) However, the real advantage of explicitly naming your constraint rather than using the system-generated name is improved understandability. The constraint name is used in the error message for any constraint violation, so creating a name such as CUSTOMER_PK will probably make more sense to users than a name such as PK__customer__cust_i__0677FF3C. You should choose your own constraint names if such error messages are visible to your users.
Unlike a PRIMARY KEY or UNIQUE constraint, an index isn't automatically built for the column or columns declared as FOREIGN KEY. However, in many cases, you'll want to build indexes on these columns because they're often used for joining to other tables. To enforce foreign key relationships, SQL Server must add additional steps to the execution plan of every insert, delete, and update (if the update affects columns that are part of the relationship) that affects either the table referencing another table or the table being referenced itself. The execution plan, determined by the SQL Server optimizer, is simply the collection of steps that carries out the operation. (In Chapter 14, you'll see the actual execution plan by using the SET SHOWPLAN ON statement.)
If no FOREIGN KEY constraints exist, a statement specifying the update of a single row of the orders table might have an execution plan like the following:
Find a qualifying order record using a clustered index.
Update the order record.
When a FOREIGN KEY constraint exists on the orders table, the same operation would have additional steps in the execution plan:
Check for the existence of a related record in the customer table (based on the updated order record) using a clustered index.
If no related record is found, raise an exception and terminate the operation.
Find a qualifying order record using a clustered index.
Update the order record.
The execution plan is more complex if the orders table has many FOREIGN KEY constraints declared. Internally, a simple update or insert operation might no longer be possible. Any such operation requires checking many other tables for matching entries. Because a seemingly simple operation could require checking as many as 253 other tables (see the next paragraph) and possibly creating multiple worktables, the operation might be much more complicated than it looks and much slower than expected.
A table can have a maximum of 253 FOREIGN KEY references. This limit is derived from the internal limit of 256 tables in a single query. In practice, an operation on a table with 253 or fewer FOREIGN KEY constraints might still fail with an error because of the 256-table query limit if worktables are required for the operation.
A database designed for excellent performance doesn't reach anything close to this limit of 253 FOREIGN KEY references. For best performance results, use FOREIGN KEY constraints judiciously. Some sites use many FOREIGN KEY constraints because the constraints they declare are logically redundant. Take the case in the following example. The orders table declares a FOREIGN KEY constraint to both the master_customer and customer_location tables:
CREATE TABLE master_customer
(
cust_id int NOT NULL IDENTITY PRIMARY KEY,
cust_name varchar(50) NOT NULL
)
CREATE TABLE customer_location
(
cust_id int NOT NULL,
cust_loc smallint NOT NULL,
CONSTRAINT PK_CUSTOMER_LOCATION PRIMARY KEY (cust_id,cust_loc),
CONSTRAINT FK_CUSTOMER_LOCATION FOREIGN KEY (cust_id)
REFERENCES master_customer (cust_id)
)
CREATE TABLE orders
(
order_id int NOT NULL IDENTITY PRIMARY KEY,
cust_id int NOT NULL,
cust_loc smallint NOT NULL,
CONSTRAINT FK_ORDER_MASTER_CUST FOREIGN KEY (cust_id)
REFERENCES master_customer (cust_id),
CONSTRAINT FK_ORDER_CUST_LOC FOREIGN KEY (cust_id, cust_loc)
REFERENCES customer_location (cust_id, cust_loc)
)
Although logically, the relationship between the orders and master_customer tables exists, the relationship is redundant to and subsumed by the fact that orders is related to customer_location, which has its own FOREIGN KEY constraint to master_customer. Declaring a foreign key for master_customer adds unnecessary overhead without adding any further integrity protection.
Note: In the case just described, perhaps declaring a foreign key adds readability to the table definition, but you can achieve this readability by simply adding comments to the CREATE TABLE command. It's perfectly legal to add a comment practically anywhere—even in the middle of a CREATE TABLE statement. A more subtle way to achieve this readability is to declare the constraint so that it appears in sp_helpconstraint and in the system catalogs, but to then disable the constraint by using the ALTER TABLE NOCHECK option. Because the constraint would then be unenforced, an additional table wouldn't be added to the execution plan.
The CREATE TABLE statement that is shown in the following example for the orders table omits the redundant foreign key and, for illustrative purposes, includes a comment. Despite the lack of a FOREIGN KEY constraint in the master_customer table, you still couldn't insert a cust_id that didn't exist in the master_customer table, because the reference to the customer_location table would prevent it.
CREATE TABLE orders
(
order_id int NOT NULL IDENTITY PRIMARY KEY,
cust_id int NOT NULL,
cust_loc smallint NOT NULL,
—- Implied Foreign Key Reference of:
—- (cust_id) REFERENCES master_customer (cust_id)
CONSTRAINT FK_ORDER_CUST_LOC FOREIGN KEY (cust_id, cust_loc)
REFERENCES customer_location (cust_id, cust_loc)
)
Note that the table on which the foreign key is declared (the referencing table, which in this example is orders) isn't the only table that requires additional execution steps. When being updated, the table being referenced (in this case customer_location) also must have additional steps in its execution plan to ensure that an update of the columns being referenced won't break a relationship and create an orphan entry in the orders table. Without making any changes directly to the customer_location table, you can see a significant decrease in update or delete performance because of foreign key references added to other tables.
Practical Considerations for FOREIGN KEY Constraints
When using constraints, you should consider triggers, performance, and indexing. Let's take a look at the ramifications of each.
Constraints and triggers Triggers won't be discussed in detail until Chapter 10, but for now, you should simply note that constraints are enforced before a triggered action is performed. If the constraint is violated, the statement will abort before the trigger fires.
Note: The owner of a table isn't allowed to declare a foreign key reference to another table unless the owner of the other table has granted REFERENCES permission to the first table owner. Even if the owner of the first table is allowed to select from the table to be referenced, that owner must have REFERENCES permission. This prevents another user from changing the performance of operations on your table without your knowledge or consent. You can grant any user REFERENCES permission even if you don't also grant SELECT permission, and vice versa. The only exception is that the DBO, or any user who is a member of the db_owner role, has full default permissions on all objects in the database.
Performance considerations In deciding on the use of foreign key relationships, you must balance the protection provided with the corresponding performance overhead. Be careful not to add constraints that form logically redundant relationships. Excessive use of FOREIGN KEY constraints can severely degrade the performance of seemingly simple operations.
Constraints and indexing The columns specified in FOREIGN KEY constraints are often strong candidates for index creation. You should build the index with the same key order used in the PRIMARY KEY or UNIQUE constraint of the table it references so that joins can be performed efficiently. Also be aware that a foreign key is often a subset of the table's primary key. In the customer_location table used in the preceding two examples, cust_id is part of the primary key as well as a foreign key in its own right. Given that cust_id is part of a primary key, it's already part of an index. In this example, cust_id is the lead column of the index, and building a separate index on it alone probably isn't warranted. However, if cust_id were not the lead column of the index B-tree, it might make sense to build an index on it.
Constraint Checking Solutions
Sometimes two tables reference one another, which creates a bootstrap problem. Suppose Table1 has a foreign key reference to Table2, but Table2 has a foreign key reference to Table1. Even before either table contains any data, you'll be prevented from inserting a row into Table1 because the reference to Table2 will fail. Similarly, you can't insert a row into Table2 because the reference to Table1 will fail.
ANSI SQL has a solution: deferred constraints, in which you can instruct the system to postpone constraint checking until the entire transaction is committed. Using this elegant remedy puts both INSERT statements into a single transaction that results in the two tables having correct references by the time COMMIT occurs. Unfortunately, no mainstream product currently provides the deferred option for constraints. The deferred option is part of the complete SQL-92 specification, which no product has yet fully implemented. It's not required for NIST certification as ANSI SQL-92 compliant, a certification that Microsoft SQL Server has achieved.
SQL Server 7 provides immediate constraint checking; it has no deferred option. SQL Server offers three options for dealing with constraint checking: it allows you to add constraints after adding data, it lets you temporarily disable checking of foreign key references, and it allows you to use the bcp (bulk copy) program or BULK INSERT command to initially load data and avoid checking FOREIGN KEY constraints. (You can override this default option with bcp or the BULK INSERT command and force FOREIGN KEY constraints to be validated.) To add constraints after adding data, don't create constraints via the CREATE TABLE command. After adding the initial data, you can add constraints by using the ALTER TABLE command.
With the second option, the table owner can temporarily disable checking of foreign key references by using the ALTER TABLE table NOCHECK CONSTRAINT statement. Once data exists, you can reestablish the FOREIGN KEY constraint by using ALTER TABLE table CHECK CONSTRAINT. Note that when an existing constraint is reenabled using this method, SQL Server doesn't automatically check to see that all rows still satisfy the constraint. To do this, you can simply issue a dummy update by setting a column to itself for all rows, determining whether any constraint violations are raised, and then fixing them. (For example, you could issue UPDATE ORDERS SET cust_id = cust_id.)
Finally you can use the bcp program or the BULK INSERT command to initially load data. The BULK INSERT command and the bcp program don't check any FOREIGN KEY constraints, by default. You can use the CHECK_CONSTRAINTS option to override this behavior. BULK INSERT and bcp are faster than regular INSERT commands because they usually bypass normal integrity checks and most logging.
When using ALTER TABLE to add (instead of reenable) a new FOREIGN KEY constraint for a table in which data already exists, the existing data is checked by default. If constraint violations occur, the constraint won't be added. With large tables, such a check can be quite time-consuming. You do have an alternative—you can add a FOREIGN KEY constraint and omit the check. To do this, specify the WITH NOCHECK option with ALTER TABLE. All subsequent operations will be checked, but existing data won't be checked. As in the case for reenabling a constraint, you could then perform a dummy update to flag any violations in the existing data. If you use this option, you should do the dummy update as soon as possible to ensure that all the data is clean. Otherwise, your users might see constraint error messages when they perform update operations on the preexisting data, even if they haven't changed any values.
Restrictions on Dropping Tables
If you're dropping tables, you must drop all the referencing tables, or drop the referencing FOREIGN KEY constraints before dropping the referenced table. For example, in the preceding example's orders, customer_location, and master_customer tables, the following sequence of DROP statements fails because a table being dropped is referenced by a table that still exists—that is, customer_location can't be dropped because the orders table references it, and orders isn't dropped until later:
DROP TABLE customer_location DROP TABLE master_customer DROP TABLE orders
Changing the sequence to the following works fine because orders is dropped first:
DROP TABLE orders DROP TABLE customer_location DROP TABLE master_customer
When two tables reference each other, you must first drop the constraints or set them to NOCHECK (both operations use ALTER TABLE) before the tables can be dropped. Similarly, a table that's being referenced can't be part of a TRUNCATE TABLE command. You must drop or disable the constraint, or you must simply drop and rebuild the table.
Self-Referencing Tables
A table can be self-referencing—that is, the foreign key can reference one or more columns in the same table. The following example shows an employee table, in which a column for manager references another employee entry:
CREATE TABLE employee ( emp_id int NOT NULL PRIMARY KEY, emp_name varchar(30) NOT NULL, mgr_id int NOT NULL REFERENCES employee(emp_id) )
The employee table is a perfectly reasonable table. It illustrates most of the issues we've discussed. However, in this case, a single INSERT command that satisfies the reference is legal. For example, if the CEO of the company has an emp_id of 1 and is also his own manager, the following INSERT will be allowed and can be a useful way to insert the first row in a self-referencing table:
INSERT employee VALUES (1,'Chris Smith',1)
Although SQL Server doesn't currently provide a deferred option for constraints, self-referencing tables add a twist that sometimes makes SQL Server use deferred operations internally. Consider the case of a nonqualified DELETE statement that deletes many rows in the table. After all rows are ultimately deleted, you can assume that no constraint violation would occur. However, while some rows are deleted internally and others remain during the delete operation, violations would occur because some of the referencing rows would be orphaned before they were actually deleted. SQL Server handles such interim violations automatically and without any user intervention. As long as the self-referencing constraints are valid at the end of the data modification statement, no errors are raised during processing.
To gracefully handle these interim violations, however, additional processing and worktables are required to hold the work-in-progress. This adds substantial overhead and can also limit the actual number of foreign keys that can be used. An UPDATE statement can also cause an interim violation. For example, if all employee numbers are to be changed by multiplying each by 1000, the following UPDATE statement would require worktables to avoid the possibility of raising an error on an interim violation:
UPDATE employee SET emp_id=emp_id * 1000, mgr_id=mgr_id * 1000
The additional worktables and the processing needed to handle the worktables are made part of the execution plan. Therefore, if the optimizer sees that a data modification statement could cause an interim violation, the additional temporary worktables will be created, even if no such interim violations ever actually occur. These extra steps are needed only in the following situations:
A table is self-referencing (it has a FOREIGN KEY constraint that refers back to itself).
A single data modification statement (UPDATE, DELETE, or INSERT based on a SELECT) is performed and can affect more than one row. (The optimizer can't determine a priori, based on the WHERE clause and unique indexes, whether more than one row could be affected.) Multiple data modification statements within the transaction don't apply—this condition must be a single statement that affects multiple rows.
Both the referencing and referenced columns are affected (which is always the case for DELETE and INSERT operations, but might or might not be the case for UPDATE).
If a data modification statement in your application meets the above criteria, you can be sure that SQL Server is automatically using a limited and special-purpose form of deferred constraints to protect against interim violations.
Referential Actions
The full ANSI SQL-92 standard contains the notion of the referential action, sometimes (incompletely) referred to as a cascading delete. SQL Server 7 doesn't provide this feature as part of FOREIGN KEY constraints, but this notion warrants some discussion here because the capability exists via triggers.
The idea behind referential actions is this: sometimes, instead of just preventing an update of data that would violate a foreign key reference, you might be able to perform an additional, compensating action that would still enable the constraint to be honored. For example, if you were to delete a customer table, which had references to orders, it would be possible to have SQL Server automatically delete all those related order records (that is, cascade the delete to orders), in which case the constraint wouldn't be violated and the customer table could be deleted. This feature is intended for both UPDATE and DELETE statements, and four possible actions are defined: NO ACTION, CASCADE, SET DEFAULT, and SET NULL.
NO ACTION The delete is prevented. This default mode, per the ANSI standard, occurs if no other action is specified. SQL Server constraints provide this action (without the NO ACTION syntax). NO ACTION is often referred to as RESTRICT, but this usage is slightly incorrect in terms of how ANSI defines RESTRICT and NO ACTION. ANSI uses RESTRICT in DDL statements such as DROP TABLE, and it uses NO ACTION for FOREIGN KEY constraints. (The difference is subtle and unimportant. It's common to refer to the FOREIGN KEY constraint as having an action of RESTRICT.)
CASCADE A delete of all matching rows in the referenced table occurs.
SET DEFAULT The delete is performed, and all foreign key values in the referencing table are set to a default value.
SET NULL The delete is performed, and all foreign key values in the referencing table are set to NULL.
Implementation of referential actions isn't required for NIST certification as ANSI SQL-92 conformant. SQL Server will provide referential actions in a future release. Until then, the program performs these actions via triggers, as discussed in Chapter 10. Creating triggers for such actions or for constraint enforcement is easy. Practically speaking, performance is usually equivalent to that of a FOREIGN KEY constraint, because both need to do the same type of operations to check the constraint.
Note: Because a constraint is checked before a trigger fires, you can't have both a constraint to enforce the relationship when a new key is inserted into the referencing table and a trigger that performs an operation such as a cascade delete from the referenced table. Triggers need to perform both the enforcement (an INSERT trigger on the referencing table) and the referential action (a DELETE trigger on the referenced table). If you do have both a constraint and a trigger, the constraint will fail and the statement will be aborted before the trigger to cascade the delete fires.
You might still want to declare the foreign key relationship largely for readability so that the relationship between the tables is clear. Simply use the NOCHECK option of ALTER TABLE to ensure that the constraint won't be enforced, and then the trigger will fire. (The trigger must also take on the enforcement of the constraint.)
SQL Server users often request support for referential actions, and it will surely be implemented in a future version. However, using application logic for such actions and SQL Server constraints (without referential actions other than NO ACTION) to safeguard the relationship is more often applicable. Although referential actions are intuitive, how many applications could really avail themselves of this feature? How many real-world examples exist in which the application is so simplistic that you would go ahead and unconditionally delete (or set to the default or to NULL) all matching rows in a related table? Probably not many. Most applications would perform some additional processing, such as asking a user if she really intends to delete a customer who has open orders. The declarative nature of referential actions doesn't provide a way to hook in application logic to handle cases like these. Probably the most useful course, then, is to use SQL Server's constraints to restrict breaking relationships and have the application deal with updates that would produce constraint violations. This method will probably continue to be the most useful even after referential actions are added.
CHECK Constraints
Enforcing domain integrity (that is, ensuring that only entries of expected types, values, or ranges can exist for a given column) is also important. SQL Server provides two ways to enforce domain integrity: CHECK constraints and rules. CHECK constraints allow you to define an expression for a table that must not evaluate to FALSE for a data modification statement to succeed. (Note that we didn't say that the constraint must evaluate to TRUE. The constraint will allow the row if it evaluates to TRUE or to unknown. The constraint evaluates to unknown when NULL values are present, and this introduces three-value logic. The issues of NULLs and three-value logic are discussed in depth in Chapter 7.) Rules perform almost the same function as CHECK constraints, but they use different syntax and have fewer capabilities. Rules have existed in SQL Server since its initial release in 1989, well before CHECK constraints, which are part of the ANSI SQL-92 standard and were added in version 6 in 1995.
CHECK constraints make a table's definition more readable by including the domain checks in the DDL. Rules have a potential advantage in that they can be defined once and then bound to multiple columns and tables (using sp_bindrule each time), while you must respecify a CHECK constraint for each column and table. But the extra binding step can also be a hassle, so this capability for rules is beneficial only if a rule will be used in many places. Although performance between the two approaches is identical, CHECK constraints are generally preferred over rules because they're a direct part of the table's DDL, they're ANSI-standard SQL, they provide a few more capabilities than rules (such as the ability to reference other columns in the same row or to call a system function), and perhaps most importantly, they're more likely than rules to be further enhanced in future releases of SQL Server. For these reasons, we're going to concentrate on CHECK constraints.
CHECK constraints (and rules) add additional steps to the execution plan to ensure that the expression doesn't evaluate to FALSE (which would result in the operation being aborted). Although steps are added to the execution plan for data modifications, these are typically much less expensive than the extra steps discussed earlier for FOREIGN KEY constraints. For foreign key checking, another table must be searched, requiring additional I/O. CHECK constraints deal only with some logical expression for the specific row already being operated on, so no additional I/O is required. Because additional processing cycles are used to evaluate the expressions, the system requires more CPU use. But if there's plenty of CPU power to spare, the effect might well be negligible. (You can watch this by using Performance Monitor.)
Like other constraint types, you can declare CHECK constraints at the column or table level. For a constraint on one column, the run-time performance is the same for the two methods. You must declare a CHECK constraint that refers to more than one column as a table-level constraint. Only a single column-level CHECK constraint is allowed for a specific column, although the constraint can have multiple logical expressions that can be AND'ed or OR'ed together. And a specific column can have or be part of many table-level expressions.
Some CHECK constraint features have often been underutilized by SQL Server database designers, including the ability to reference other columns in the same row, use system and niladic functions (which are evaluated at runtime), and use AND/OR expressions. The following example shows a table with multiple CHECK constraints (as well as a PRIMARY KEY constraint and a FOREIGN KEY constraint) and showcases some of these features:
CREATE TABLE employee
(
emp_id int NOT NULL PRIMARY KEY
CHECK (emp_id BETWEEN 0 AND 1000),
emp_name varchar(30) NOT NULL CONSTRAINT no_nums
CHECK (emp_name NOT LIKE '%[0-9]%'),
mgr_id int NOT NULL REFERENCES employee(emp_id),
entered_date datetime NULL CHECK (entered_date >=
CURRENT_TIMESTAMP),
entered_by int CHECK (entered_by IS NOT NULL),
CONSTRAINT valid_entered_by CHECK
(entered_by = SUSER_ID(NULL) AND
entered_by <> emp_id),
CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
)
GO
EXEC sp_helpconstraint employee
GO
>>>>
Object Name
---------------
employee
constraint_type constraint_name constraint_keys
--------------- -------------------------------- ------------------
CHECK on column CK__employee__emp_id__2C3393D0 ([emp_id] >= 0
emp_id [emp_id] <= 1000)
CHECK on column CK__employee__entered_by__300424B4 (((not([entered_by]
entered_by is null))))
CHECK on column CK__employee__entered_date__2F10007B ([entered_date] >=
entered_date getdate())
CHECK Table Level end_of_month (datepart(day,
getdate()) < 28)
FOREIGN KEY FK__employee__mgr_id__2E1BDC42 mgr_id
CHECK on column no_nums ([emp_name] not
emp_name like '%[0-9]%')
PRIMARY KEY PK__employee__2B3F6F97 emp_id
(clustered)
CHECK Table Level valid_entered_by ([entered_by] =
suser_id(null)
and [entered_by]
<> [emp_id])
CHECK Table Level valid_mgr ([mgr_id] <>
[emp_id] or
[emp_id] = 1)
Table is referenced by
--------------------------------
pubs.dbo.employee: FK__employee__mgr_id__2E1BDC42
This example illustrates the following points:
Constraint syntax CHECK constraints can be expressed at the column level with abbreviated syntax (leaving naming to SQL Server), such as the check on entered_date; at the column level with an explicit name, such as the NO_NUMS constraint on emp_name; or as a table-level constraint, such as the VALID_MGR constraint.
Regular expressions CHECK constraints can use regular expressions—for example, NO_NUMS ensures that a digit can never be entered as a character in a person's name.
AND/OR Expressions can be AND'ed and OR'ed together to represent more complex situations—for example, VALID_MGR. However, SQL Server won't check for logical correctness at the time a constraint is added to the table. Suppose you wanted to restrict the values in a department_id column to either negative numbers or values greater than 100. (Perhaps numbers between 0 and 100 are reserved.) You could add this column and constraint to the table using ALTER TABLE.
ALTER TABLE employee add department_no int CHECK(department_no < 100 AND department_no > 100)
However, once the above command has been executed successfully and the new column and constraint have been added to the table, we'll never be able to put another row into the table. We accidentally typed AND in the ALTER TABLE statement instead of OR, but that isn't considered an illegal CHECK constraint. Each time we tried to insert a row, SQL Server tried to validate that the value in the department_no column was both less than 0 and more than 100! Any value for department_no that doesn't meet that requirement will cause the insert to fail. It could take a long time to come up with a value to satisfy this constraint. In this case, we'd have to drop the constraint and add a new one with the correct definition before we could insert any rows.
Constraint reference Table-level CHECK constraints can refer to more than one column in the same row. For example, VALID_MGR insists that no employee can be his own boss, except employee number 1, who is assumed to be the CEO. SQL Server currently has no provision that allows you to check a value from another row or from a different table.
NULL prevention You can make a CHECK constraint prevent NULL values—for example, CHECK (entered_by IS NOT NULL). Generally, you would simply declare the column NOT NULL.
Unknown expressions A NULL column might make the expression logically unknown. For example, a NULL value for entered_date gives CHECK entered_date >= CURRENT_TIMESTAMP an unknown value. This doesn't reject the row, however. The constraint rejects the row only when the expression is clearly false, even if it isn't necessarily true.
System functions System functions, such as GETDATE(), APP_NAME(), DATALENGTH(), and SUSER_ID(), as well as niladic functions, such as SYSTEM_USER, CURRENT_TIMESTAMP, and USER, can be used in CHECK constraints. This subtle feature is powerful and can be useful, for example, for assuring that a user can change only records that she has entered by comparing entered_by to the user's system ID, as generated by SUSER_ID() (or by comparing emp_name to SYSTEM_USER). Note that niladic functions such as CURRENT_TIMESTAMP are provided for ANSI SQL conformance and simply map to an underlying SQL Server function, in this case GETDATE(). So while the DDL to create the constraint on entered_date uses CURRENT_TIMESTAMP, sp_helpconstraint shows it as GETDATE(), which is the underlying function. Either expression is valid and equivalent in the CHECK constraint. The VALID_ENTERED_BY constraint ensures that the entered_by column can be set only to the currently connected user's ID, and it ensures that users can't update their own records.
System functions and column references A table-level constraint can call a system function without referencing a column in the table. In the example preceding this list, the END_OF_MONTH CHECK constraint calls two date functions, DATEPART() and GETDATE(), to ensure that updates can't be made after day 28 of the month (when the business's payroll is assumed to be processed). The constraint never references a column in the table. Similarly, a CHECK constraint might call the APP_NAME() function to ensure that updates can be made only from an application of a certain name, instead of from an ad hoc tool such as the Query Analyzer.
As with FOREIGN KEY constraints, you can add or drop CHECK constraints by using ALTER TABLE. When adding a constraint, by default the existing data is checked for compliance; you can override this default by using the NOCHECK syntax. You can later do a dummy update to check for any violations. The table or database owner can also temporarily disable CHECK constraints by using WITH NOCHECK in the ALTER TABLE statement.
Default Constraints
A default allows you to specify a constant value, NULL, or the run-time value of a system function if no known value exists or if the column is missing in an INSERT statement. Although you could argue that a default isn't truly a constraint (because a default doesn't enforce anything), you can create defaults in a CREATE TABLE statement using the CONSTRAINT keyword; therefore, defaults will be referred to as constraints. Defaults add little overhead, and you can use them liberally without too much concern about performance degradation.
SQL Server provides two ways of creating defaults. Since the original SQL Server release in 1989, you can create a default (CREATE DEFAULT) and then bind the default to a column (sp_bindefault). Default constraints were introduced in 1995 with SQL Server 6.0 as part of the CREATE TABLE and ALTER TABLE statements, and they're based on the ANSI SQL standard (which includes such niceties as being able to use system functions). Using defaults is pretty intuitive. The type of default you use is a matter of preference; both perform the same function internally. Future enhancements are likely to be made to the ANSI-style implementation. Having the default within the table DDL seems a cleaner approach. We recommend that you use defaults within CREATE TABLE and ALTER TABLE rather than within CREATE DEFAULT, so we'll focus on that style here.
Here's the example from the previous CHECK constraint discussion, now modified to include several defaults:
CREATE TABLE employee
(
emp_id int NOT NULL PRIMARY KEY DEFAULT 1000
CHECK (emp_id BETWEEN 0 AND 1000),
emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums
CHECK (emp_name NOT LIKE '%[0-9]%'),
mgr_id int NOT NULL DEFAULT (1) REFERENCES
employee(emp_id),
entered_date datetime NOT NULL CHECK (entered_date >=
CONVERT(char(10), CURRENT_TIMESTAMP, 102))
CONSTRAINT def_today DEFAULT
(CONVERT(char(10), GETDATE(), 102)),
entered_by int NOT NULL DEFAULT SUSER_ID()
CHECK (entered_by IS NOT NULL),
CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND
entered_by <> emp_id),
CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
)
GO
EXEC sp_helpconstraint employee
GO
>>>
Object Name
---------------
employee
constraint_type constraint_name constraint_keys
--------------- -------------------------------- ------------------
CHECK on column CK__employee__emp_id__2C3393D0 ([emp_id] >= 0
emp_id [emp_id] <= 1000)
CHECK on column CK__employee__entered_by__300424B4 (((not([entered_by]
entered_by is null))))
DEFAULT on column def_today convert(char(10),
entered_date getdate(),102))
DEFAULT on column DF__employee__emp_id__35BCFE0A (1000)
emp_id
DEFAULT on column DF__employee__emp_name__37A5467C (null)
emp_name
DEFAULT on column DF__employee__entered_by__3D5E1FD2 (suser_id())
entered_by
DEFAULT on column DF__employee__mgr_id__398D8EEE (1)
mgr_id
CHECK on column CK__employee__entered_date__2F10007B ([entered_date] >=
entered_date getdate())
CHECK Table Level end_of_month (datepart(day,
getdate()) < 28)
FOREIGN KEY FK__employee__mgr_id__2E1BDC42 mgr_id
CHECK on column no_nums ([emp_name] not
emp_name like '%[0-9]%')
(continued) PRIMARY KEY PK__employee__2B3F6F97 emp_id
(clustered)
CHECK Table Level valid_entered_by ([entered_by] =
suser_id(null)
and [entered_by]
<> [emp_id])
CHECK Table Level valid_mgr ([mgr_id] <>
[emp_id] or
[emp_id] = 1)
Table is referenced by
--------------------------------
pubs.dbo.employee: FK__employee__mgr_id__2E1BDC42
The preceding code demonstrates the following about defaults:
Column-level constraint A default constraint is always a column-level constraint because it pertains to only one column. You can use the abbreviated syntax that omits the keyword CONSTRAINT and the specified name, letting SQL Server generate the name, or you can specify the name by using the more verbose CONSTRAINT name DEFAULT syntax.
Clashes with CHECK constraint A default value can clash with a CHECK constraint. This problem appears only at runtime, not when you create the table or when you add the default via ALTER TABLE. For example, a column with a default of 0 and a CHECK constraint that states that the value must be greater than 0 would never be able to insert or update the default value.
PRIMARY KEY or UNIQUE constraint You can assign a default to a column having a PRIMARY KEY or a UNIQUE constraint. Such columns must have unique values, so only one row could exist with the default value in that column. The preceding example sets a DEFAULT on a primary key column for illustration, but in general, this practice is unwise.
Parentheses and quotation marks You can write a constant value within parentheses, as in DEFAULT (1), or without them, as in DEFAULT 1. A character or date constant must be enclosed in either single or double quotation marks.
NULL vs. default value A tricky concept is knowing when a NULL is inserted into a column vs. when a default value is entered. A column declared NOT NULL with a default defined uses the default only under one of the following conditions:
The INSERT statement specifies its column list and omits the column with the default.
The INSERT statement specifies the keyword DEFAULT in the values list (whether the column is explicitly specified as part of the column list or implicitly specified in the values list and the column list is omitted, meaning "All columns in the order in which they were created"). If the values list explicitly specifies NULL, an error is raised and the statement fails; the default value isn't used. If the INSERT statement omits the column entirely, the default is used and no error occurs. (This behavior is in accordance with ANSI SQL.) The keyword DEFAULT can be used in the values list, and this is the only way the default value will be used if a NOT NULL column is specified in the column list of an INSERT statement (either, as in the following example, by omitting the column list—which means all columns—or by explicitly including the NOT NULL column in the columns list).
INSERT EMPLOYEE VALUES (1, 'The Big Guy', 1, DEFAULT, DEFAULT)
Table 6-5 summarizes the behavior of INSERT statements based on whether a column is declared NULL or NOT NULL and whether it has a default specified. It shows the result for the column for three cases:
Omitting the column entirely (no entry).
Having the INSERT statement use NULL in the values list.
Specifying the column and using DEFAULT in the values list.
Table 6-5 INSERT behavior with defaults.
No Entry |
|
Enter NULL |
|
Enter DEFAULT |
|
|
|---|---|---|---|---|---|---|
|
No Default |
Default |
No Default |
Default |
No Default |
Default |
NULL |
NULL |
default |
NULL |
NULL |
NULL |
default |
NOT NULL |
error |
default |
error |
error |
error |
default |
Note: Although you can declare DEFAULT NULL on a column allowing NULL, SQL Server does this without declaring a default at all, even when using the DEFAULT keyword in an INSERT or UPDATE statement.
Declaring a default on a column that has the Identity property wouldn't make sense, and SQL Server will raise an error if you try it. The Identity property acts as a default for the column. But the DEFAULT keyword cannot be used as a placeholder for an identity column in the values list of an INSERT statement. You can use a special form of INSERT statement if a table has a default value for every column (an identity column does meet this criteria) or allows NULL. The following statement uses the DEFAULT VALUES clause instead of a column list and values list:
INSERT EMPLOYEE DEFAULT VALUES
Tip You can generate some test data by putting the Identity property on a primary key column and declaring default values for all other columns and then repeatedly issuing an INSERT statement of this form within a Transact-SQL loop.
More About Constraints
This section covers some tips and considerations that you should know about when working with constraints. We'll look at constraint names and system catalog entries, the status field, constraint failures, multiple-row data modifications, and integrity checks.
Constraint Names and System Catalog Entries
Earlier in this chapter, you learned about the cryptic-looking constraint names that SQL Server generates. Now we'll explain the naming. Consider again the following simple CREATE TABLE statement:
CREATE TABLE customer ( cust_id int IDENTITY NOT NULL PRIMARY KEY, cust_name varchar(30) NOT NULL )
The constraint produced from this simple statement bears the nonintuitive name PK__customer__59FA5E80. All types of column-level constraints use this naming scheme. (Note that although the NULL/NOT NULL designation is often thought of as a constraint, it's not quite the same. Instead, this designation is treated as a column attribute and has no name or representation in the sysobjects system table.)
The first two characters (PK) show the constraint type—PK for PRIMARY KEY, UN for UNIQUE, FK for FOREIGN KEY, and DF for DEFAULT. Next are two underscore characters (__) used as a separator. (You might be tempted to use one underscore as a separator, to conserve characters and to avoid having to truncate as much. However, it's common to use an underscore in a table name or a column name, both of which appear in the constraint name. Using two underscore characters distinguishes which kind of a name this is and where the separation occurs.)
Next comes the table name (customer), which is limited to 116 characters for a PRIMARY KEY constraint and slightly fewer characters for all other constraint names. For all constraints other than PRIMARY KEY, there are then two more underscore characters for separation followed by the next sequence of characters, which is the column name. The column name is truncated to five characters, if necessary. If the column name has fewer than five characters in it, the length of the table name portion can be slightly longer.
And finally, the hexadecimal representation of the object ID for the constraint (59FA5E80) comes after another separator. (This value is used as the id column of the sysobjects system table and the constid column of the sysconstraints system table.) Object names are limited to 128 characters in SQL Server 7, so the total length of all portions of the constraint name must also be less than or equal to 128.
Note the queries and output using this value that are shown in Figure 6-11.
.gif "Cc917720.f06wh11(en-us,TechNet.10).gif")
Figure 6-11: Querying the system tables using the hexadecimal ID value.
Tip The hexadecimal value 0x59FA5E80 is equal to the decimal value 1509580416, which is the value of constid in sysconstraints and of id in sysobjects.
These sample queries of system tables show the following:
A constraint is an object A constraint has an entry in the sysobjects table in the xtype column of C, D, F, PK, or UQ for CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, and UNIQUE, respectively.
Sysconstraints relates to sysobjects The sysconstraints table is really just a view of the sysobjects system table. The constid column in the view is the object ID of the constraint, and the id column of sysconstraints is the object ID of the base table on which the constraint is declared.
Colid values If the constraint is a column-level CHECK, FOREIGN KEY, or DEFAULT, the colid has the colid of the column. This colid is related to the colid of syscolumns for the base table represented by id. A table-level constraint, or any PRIMARY KEY/UNIQUE constraint (even if column level), always has 0 in this column.
Tip To see the names and order of the columns in a PRIMARY KEY or UNIQUE constraint, you can query the sysindexes and syscolumns tables for the index being used to enforce the constraint. The name of the constraint and that of the index enforcing the constraint are the same, whether the name was user specified or system generated. The columns in the index key are somewhat cryptically encoded in the keys1 and keys2 fields of sysindexes. The easiest way to decode these values is to simply use the sp_helpindex system stored procedure, or you can use the code of that procedure as a template if you need to decode them in your own procedure.
Decoding the status Field
The status field of the sysconstraints view is a pseudo–bit-mask field packed with information. If you know how to crack this column, you can essentially write your own sp_helpconstraint-like procedure. Note that the documentation is incomplete regarding the values of this column. One way to start decoding this column is to look at the definition of the sysconstraints view using the sp_helptext system procedure.
The lowest four bits, obtained by AND'ing status with 0xF (status & 0xF), contain the constraint type. A value of 1 is PRIMARY KEY, 2 is UNIQUE, 3 is FOREIGN KEY, 4 is CHECK, and 5 is Default. The fifth bit is on (status & 0x10 <> 0) when the constraint was created at the column level. The sixth bit is on (status & 0x20 <> 0) when the constraint was created at the table level.
Some of the higher bits are used for internal status purposes, such as noting whether a nonclustered index is being rebuilt and for other internal states. Table 6-6 shows some of the other bit-mask values you might be interested in:
Table 6-6 Bit-mask values.
Bit-Mask Value |
Description |
|---|---|
512 |
The constraint creates a clustered index. |
1024 |
The constraint creates a nonclustered index. |
16384 |
The constraint has been disabled. |
32767 |
The constraint has been enabled. |
131072 |
SQL Server generates a name for the constraint. |
Using this information, and not worrying about the higher bits used for internal status, you could use the following query to show constraint information for the employee table:
SELECT
OBJECT_NAME(constid) 'Constraint Name',
constid 'Constraint ID',
CASE (status & 0xF)
WHEN 1 THEN 'Primary Key'
WHEN 2 THEN 'Unique'
WHEN 3 THEN 'Foreign Key'
WHEN 4 THEN 'Check'
WHEN 5 THEN 'Default'
ELSE 'Undefined'
END 'Constraint Type',
CASE (status & 0x30)
WHEN 0x10 THEN 'Column'
WHEN 0x20 THEN 'Table'
ELSE 'NA'
END 'Level'
FROM sysconstraints
WHERE id=OBJECT_ID('employee')
>>>
Level
ID Type
-------------------------------------- ----------- ------------ ------
PK__employee__49C3F6B7 1237579447 Primary Key Table
DF__employee__emp_id__4AB81AF0 1253579504 Default Column
CK__employee__emp_id__4BAC3F29 1269579561 Check Column
DF__employee__emp_name__4CA06362 1285579618 Default Column
no_nums 1301579675 Check Column
DF__employee__mgr_id__4E88ABD4 1317579732 Default Column
FK__employee__mgr_id__4F7CD00D 1333579789 Foreign Key Column
CK__employee__entered_date__5070F446 1349579846 Check Column
def_today 1365579903 Default Column
DF__employee__entered_by__52593CB8 1381579960 Default Column
CK__employee__entered_by__534D60F1 1397580017 Check Column
valid_entered_by 1413580074 Check Table
valid_mgr 1429580131 Check Table
end_of_month 1445580188 Check Table
Constraint Failures in Transactions and Multiple-Row Data Modifications
Many bugs occur in application code because the developers don't understand how failure of a constraint affects a multiple-statement transaction declared by the user. The biggest misconception is that any error, such as a constraint failure, automatically aborts and rolls back the entire transaction. Rather, after an error is raised, it's up to the transaction to either proceed and ultimately commit, or to roll back. This feature provides the developer with the flexibility to decide how to handle errors. (The semantics are also in accordance with the ANSI SQL-92 standard for COMMIT behavior.)
Following is an example of a simple transaction that tries to insert three rows of data. The second row contains a duplicate key and violates the PRIMARY KEY constraint. Some developers believe that this example wouldn't insert any rows because of the error that occurred in one of the statements; they think that this error causes the entire transaction to be aborted. However, this isn't what happens—instead, the statement inserts two rows and then commits that change. Although the second INSERT fails, the third INSERT is processed because no error checking occurred between the statements, and then the transaction does a COMMIT. Because no instructions were provided to take some other action after the error other than to proceed, proceed is exactly what SQL Server does. It adds the first and third INSERT statements to the table and ignores the second statement.
IF EXISTS (SELECT * FROM sysobjects WHERE name='show_error' AND
type='U')
DROP TABLE show_error
GO
CREATE TABLE show_error
(
col1 smallint NOT NULL PRIMARY KEY,
col2 smallint NOT NULL
)
GO
BEGIN TRANSACTION
INSERT show_error VALUES (1, 1)
INSERT show_error VALUES (1, 2)
INSERT show_error VALUES (2, 2)
COMMIT TRANSACTION
GO
SELECT * FROM show_error
GO
Server: Msg 2627, Level 14, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK__show_error__6754599E'. Cannot insert duplicate key in
object 'show_error'.
The statement has been aborted.
col1 col2
---- ----
1 1
2 2
Here's a modified version of the transaction. This example does some simple error checking using the system function @@ERROR and rolls back the transaction if any statement results in an error. In this example, no rows are inserted because the transaction is rolled back:
IF EXISTS (SELECT * FROM sysobjects WHERE name='show_error'
AND type='U')
DROP TABLE show_error
GO
CREATE TABLE show_error
(
col2 smallint NOT NULL
)
GO
BEGIN TRANSACTION
INSERT show_error VALUES (1, 1)
IF @@ERROR <> 0 GOTO TRAN_ABORT
INSERT show_error VALUES (1, 2)
if @@ERROR <> 0 GOTO TRAN_ABORT
INSERT show_error VALUES (2, 2)
if @@ERROR <> 0 GOTO TRAN_ABORT
COMMIT TRANSACTION
GOTO FINISH
TRAN_ABORT:
ROLLBACK TRANSACTION
FINISH:
GO
SELECT * FROM show_error
GO
Server: Msg 2627, Level 14, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK__show_error__6B24EA82'. Cannot insert duplicate key in
object 'show_error'.
The statement has been aborted.
col1 col2
---- ----
Because many developers have handled transaction errors incorrectly, and because it can be tedious to add an error check after every command, SQL Server includes a SET statement that aborts a transaction if it encounters any error during the transaction. (Transact-SQL has no WHENEVER statement, although such a feature would be useful for situations like this.) Using SET XACT_ABORT ON causes the entire transaction to be aborted and rolled back if any error is encountered. The default setting is OFF, which is consistent with semantics prior to version 6.5 (when this SET option was introduced) as well as with ANSI-standard behavior. By setting the option XACT_ABORT ON, we can now rerun the example that does no error checking, and no rows will be inserted:
IF EXISTS (SELECT * FROM sysobjects WHERE name='show_error'
AND type='U')
DROP TABLE show_error
GO
CREATE TABLE show_error
(
col1 smallint NOT NULL PRIMARY KEY,
col2 smallint NOT NULL
)
GO
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT show_error VALUES (1, 1)
INSERT show_error VALUES (1, 2)
INSERT show_error VALUES (2, 2)
COMMIT TRANSACTION
GO
SELECT * FROM show_error
GO
Server: Msg 2627, Level 14, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK__show_error__6D0D32F4'. Cannot insert duplicate key in
object 'show_error'.
col1 col2
---- ----
A final comment about constraint errors and transactions: A single data modification statement (such as an UPDATE statement) that affects multiple rows is automatically an atomic operation, even if it's not part of an explicit transaction. If such an UPDATE statement finds 100 rows that meet the criteria of the WHERE clause but one row fails because of a constraint violation, no rows will be updated.
The Order of Integrity Checks
The modification of a given row will fail if any constraint is violated or if a trigger aborts the operation. As soon as a failure in a constraint occurs, the operation is aborted, subsequent checks for that row aren't performed, and no trigger fires for the row. Hence, the order of these checks can be important, as the list on the following page shows.
Defaults are applied as appropriate.
NOT NULL violations are raised.
CHECK constraints are evaluated.
FOREIGN KEY checks of referencing tables are applied.
FOREIGN KEY checks of referenced tables are applied.
UNIQUE/PRIMARY KEY is checked for correctness.
Triggers fire.
Altering a Table
SQL Server 7 allows existing tables to be modified in several ways, and we've seen some of these methods already. You can use ALTER TABLE to add or drop constraints from a table. We've seen that some types of constraints have the option of not being applied to existing data by using the WITH NOCHECK option. Using the ALTER table command, you can make the following types of changes to an existing table:
Change the datatype or NULL property of a single column.
Add one or more new columns, with or without defining constraints for those columns.
Add one or more constraints.
Drop one or more constraints.
Drop one or more columns.
Enable or disable one or more constraints (only applies to CHECK and FOREIGN KEY constraints).
Enable or disable one or more triggers.
Changing a Datatype
By using the ALTER COLUMN clause of ALTER TABLE, you can modify the datatype or NULL property of an existing column. But be aware of the following restrictions:
The modified column can't be a text, image, ntext, or timestamp column.
If the modified column is the ROWGUIDCOL for the table, only DROP ROWGUIDCOL is allowed; no datatype changes are allowed.
The modified column can't be a computed or replicated column.
The modified column can't have a CHECK or FOREIGN KEY constraint defined on it.
The modified column can't be referenced in a computed column.
The modified column can't have the type changed to timestamp.
If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type, changing nullability of the column (for example, VARCHAR(10) to VARCHAR(20)), or both.
If the modified column has a default defined on it, the only changes that are allowed are increasing or decreasing the length of a variable-length type, changing nullability, or both.
The old type of the column should have an allowed implicit conversion to the new type.
The new type always has ANSI_PADDING semantics if applicable, regardless of the current setting.
If conversion of old type to new type causes an overflow (arithmetic or size), the ALTER TABLE statement is aborted.
Here's the syntax and an example of using the ALTER COLUMN clause of the ALTER TABLE statement:
SYNTAX
ALTER TABLE table-name ALTER COLUMN column-name
{ type_name [ ( prec [, scale] ) ] [ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL }
EXAMPLE
/* Change the length of the emp_name column in the employee table from varchar(30) to varchar(50) */ ALTER TABLE employee ALTER COLUMN emp_name (varchar(50)
Adding a New Column
You can add a new column, with or without specifying column-level constraints. You can add only one column for each ALTER TABLE statement. If the new column doesn't allow NULLs and isn't an identity column, the new column must have a default constraint defined. SQL Server populates the new column in every row with a NULL, the appropriate identity value, or the specified default. If the newly added column is nullable and has a default constraint, the existing rows of the table are not filled with the default value, but rather with NULL values. You can override this restriction by using the WITH VALUES clause so that the existing rows of the table are filled with the specified default value.
Adding, Dropping, Disabling, or Enabling a Constraint
Constraint modifications were covered earlier in the discussion about constraints. The trickiest part of using ALTER TABLE to manipulate constraints is that the word CHECK can be used in three different ways:
To specify a CHECK constraint.
To defer checking of a newly added constraint. In the following example, we're adding a constraint to validate that cust_id in orders matches a cust_id in customer, but we don't want the constraint applied to existing data:
ALTER TABLE orders WITH NOCHECK ADD FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
**Note**: We could also use WITH CHECK to force the constraint to be applied to existing data, but that's unnecessary because it's the default behavior.
- To enable or disable a constraint. In the next example, we're enabling all the constraints on the employee table:
ALTER TABLE EMPLOYEE CHECK ALL
One more commonly misunderstood aspect of using ALTER TABLE to drop constraints is that dropping a PRIMARY KEY or UNIQUE constraint will automatically drop the associated index. In fact, the only way to drop those indexes is by altering the table to remove the constraint.
Dropping a Column
You can use ALTER TABLE to remove one or more columns from a table. However, you can't drop the following columns:
A replicated column.
A column used in an index.
A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
A column associated with a default defined with the DEFAULT keyword or bound to a default object.
A column bound to a rule, accomplished via the following syntax:
ALTER TABLE table-name DROP COLUMN column-name [,next-column-name]...
**Note**: Notice the syntax difference between adding a new column and dropping a column: when adding a new column to a table, the word COLUMN isn't used, but when dropping a column, the word COLUMN is required.
Enabling or Disabling a Trigger
You can enable or disable one or more (or all) triggers on a table using the ALTER TABLE command. We'll look at this topic in more detail when we discuss triggers in Chapter 10.
Note that not all the ALTER TABLE variations require SQL Server to change every row when the ALTER TABLE is issued. In many cases, SQL Server can just change the metadata (in syscolumns) to reflect the new structure. In particular, the data isn't touched when a column is dropped, when a new column is added and NULL is assumed as the new value for all rows, when the length of a variable-length column is changed, or when a non-nullable column is changed to allow NULLs. All other changes to a table's structure require SQL Server to physically update every row and to write the appropriate records to the transaction log.
Temporary Tables
Temporary tables are useful workspaces, like scratch pads, that you can use to try out intermediate data processing or to share work-in-progress with other connections. You can create temporary tables from within any database, but they exist in only the tempdb database, which is created every time the server is restarted. Don't assume that temporary tables aren't logged: temporary tables, and actions on those tables, are logged in tempdb so that transactions can be rolled back as necessary. However, the log isn't used for recovery of the database at system restart because the database is entirely re-created. Likewise, tempdb is never restored from a backup, so the log in tempdb is never needed for restoring the database. Unlike earlier versions of SQL Server, SQL Server 7 can log just enough information to allow rollback of transactions, without logging the additional information that would be necessary to recover those transactions, either at system startup or when recovering from a backup. This reduced logging means that data modification operations on tables in tempdb can be up to four times faster than the same operations in other databases. You can use temporary tables in three ways in SQL Server: privately, globally, and directly.
Private Temporary Tables (#)
By prefixing a table name with a single pound sign (#)—for example, CREATE TABLE #my_table—the table can be created from within any database as a private temporary table. Only the connection that created the table can access the table, making it truly private. Privileges can't be granted to another connection. As a temporary table, it exists for the life of that connection only; that connection can drop the table via DROP TABLE. Because the scoping of a private temporary table is specific to the connection that created it, you won't encounter a name collision should you choose a table name that's used in another connection. Private temporary tables are analogous to local variables—each connection has its own private version, and private temporary tables that are held by other connections are irrelevant. (However, temporary tables do differ from local variables in one crucial way: temporary tables exist for the life of the session, while local variables exist only for a single batch.)
Global Temporary Tables (##)
By prefixing the table name with double pound signs (##)—for example, CREATE TABLE ##our_table—a global temporary table can be created from within any database and any connection. Any connection can subsequently access the table for retrieval or data modification, even without specific permission. Unlike private temporary tables, all connections can use the single copy of a global temporary table. Therefore, you can encounter a name collision if another connection has created a global temporary table of the same name, and the CREATE TABLE statement will fail.
A global temporary table exists until the creating connection terminates and all current use of the table completes. After the creating connection terminates, however, only those connections already accessing it are allowed to finish, and no further use of the table is allowed. If you want a global temporary table to exist permanently, you can create the table in a stored procedure that's marked to autostart whenever SQL Server is started. That procedure can be put to sleep using WAITFOR and it will never terminate, so the table will never be dropped. Or you can choose to use tempdb directly, which is discussed next.
Direct Use of tempdb
Realizing that tempdb is re-created every time SQL Server is started, you can use tempdb to create a table or you can fully qualify the table name to include the database name tempdb in the CREATE TABLE statement issued from another database. To do this, you need to establish create table privileges in tempdb. You can set up privileges in tempdb in one of two ways every time SQL Server starts: you can set the privileges in model (the template database) so that they are copied to tempdb when it's created at system restart, or you can have an autostart procedure set the tempdb privileges every time SQL Server is started. One reason to consider not setting the privileges for tempdb in the model database is because tempdb isn't the only database that will be affected. Any new database you create will inherit those permissions too.
Tables directly created in tempdb can exist even after the creating connection is terminated, and the creator can specifically grant and revoke access permissions to specific users:
— Creating a table in tempdb from pubs. Another method would be — to first do a 'use tempdb' instead of fully qualifying — the name. CREATE TABLE tempdb.dbo.testtemp (col1 int)
Constraints on Temporary Tables
A few articles about SQL Server erroneously state that constraints don't work on temporary tables. However, all constraints work on temporary tables explicitly built in tempdb (not using the # or ## prefixes). All constraints except FOREIGN KEY constraints work with tables using the # (private) and ## (global) prefixes. FOREIGN KEY references on private and global temporary tables are designed not to be enforced, because such a reference could prevent the temporary table from being dropped at close-connection time (for private temporary tables) or when a table goes out of scope (for global temporary tables) if the referencing table wasn't dropped first.
Summary
Tables are the heart of relational databases in general and of SQL Server in particular. In this chapter, we've looked at datatype issues, including size and performance tradeoffs. SQL Server provides a datatype for almost every use. We've looked at variable-length datatype issues and seen that it's simplistic to think that variable-length datatypes are either always good or always bad to use. We've also seen how data is physically stored in data pages and discussed some of the system table entries that are made to support tables.
SQL Server provides user-defined datatypes for support of domains, and it provides the Identity property to make a column produce autosequenced numeric values. SQL Server also provides constraints offering a powerful way to ensure your data's logical integrity. In addition to the NULL/NOT NULL designation, SQL Server provides PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and DEFAULT constraints. The discussion of these features also touched on a number of pragmatic issues, such as performance implications, conflicts between different constraints and triggers, and transaction semantics when a constraint fails.
To get the most out of SQL Server, you must understand tables, datatypes, and constraints. This chapter has provided some insight into subtleties that you might not have gleaned from the reference documentation of these features.
The above article is courtesy of Microsoft Press https://www.microsoft.com/mspress/. Copyright 1999, Microsoft Corporation.
We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages. All prices for products mentioned in this document are subject to change without notice.
International rights = English only.
.gif "Link")