Manipulating Tables, Indexes, and Views

Updated : July 19, 2001

In Microsoft SQL Server 2000, the structures of tables and indexes are just as important as the database itself, especially when it comes to performance. Tables are collections of data about a specific entity, such as a customer or an order. To describe the attributes of these entities, you use named columns. For example, to describe the attributes of a customer, you could use these columns: cust_name, cust_address, and cust_phone.

Each instance of data in a table is represented as a single data entry or row. Typically, rows are unique and have unique identifiers called primary keys associated with them. However, a primary key isn't mandatory in ANSI SQL, and it isn't required in SQL Server. The job of the primary key is to set a unique identifier for each row in the table and to allow SQL Server to create a unique index on this key. Indexes are user-defined data structures that provide fast access to data when you search on an indexed column. Indexes are separate from tables, and you can configure them automatically with the Index Tuning Wizard.

Most tables are related to other tables. For example, a Customers table may have a cust_account column that contains a customer's account number. The cust_account column may also appear in tables named Orders and Receivables. If the cust_account column is the primary key of the Customers table, a foreign key relationship can be established between Customers and Orders as well as between Customers and Receivables. The foreign key creates a link between the tables that you can use to preserve referential integrity in the database.

Once you've established the link, you won't be able to delete a row in the Customers table if the cust_account identifier is referenced in the Orders or Receivables tables. This feature prevents you from invalidating references to information used in other tables. You would first need to delete or change the related references in the Orders or Receivables tables, or both, before deleting a primary key row in the Customers table. Foreign key relationships allow you to combine data from related tables in queries by matching the foreign key constraint of one table with the primary or unique key in another table. Combining tables in this manner is called a table join, and the keys allow SQL Server to optimize the query and quickly find related data.

On This Page

Table Essentials
Working with Tables
Managing Table Values
Using Views
Creating and Managing Indexes
Column Constraints and Rules

Table Essentials

Tables are defined as objects in SQL Server databases. Tables consist of columns and rows of data, with each column having a native or user-defined data type. Tables have two units of data storage: data pages and extents. Data pages are the fundamental units of data storage. Extents are the basic units in which space is allocated to tables and indexes.

Understanding Data Pages

For all data types except text, ntext, and image, table data is stored in data pages that have a fixed size of 8 KB (8192 bytes). Each data page has a page header, data rows, and free space that can contain row offsets. The page header uses the first 96 bytes of each page, leaving 8096 bytes for data and row offsets. Row offsets indicate the logical order of rows on a page, which means that offset 0 refers to the first row in the index, offset 1 refers to the second row, and so on. If a table contains text and image data, the text or image may not be stored with the rest of the data for a row. Instead, SQL Server can store a 16-byte pointer to the actual data, which is stored in a collection of 8 KB pages that aren't necessarily written contiguously.

SQL Server 2000 supports six types of data pages:

• Data Contain data rows with all data (except for text, ntext, and image data)

• Index Contain index entries

• Global Allocation Map Contain information about extents that have been allocated by SQL Server

• Index Allocation Map Contain information about extents used by a table or index

• Page Free Space Contain information about free space available in data pages

• Text/Image (Binary Large Object BLOB) Contain text, ntext, and image data

Within data pages, SQL Server stores data in rows. Data rows don't normally span more than one page. The maximum size of a single data row is 8096 bytes (including any necessary overhead). Effectively, this means the maximum size of character columns is 8000 bytes and that columns can store up to 8000 ASCII characters or up to 4000 Unicode characters. Individual text, ntext, and image data values can be up to 2 GB in size, which is too large to be stored in a single data row. With text, ntext, and image data, data is stored in a collection of 8 KB pages, which may or may not be contiguously stored.

While collections of pages are ideal for large text, ntext, and image data, this storage mechanism isn't ideal when the total data size is 8096 bytes or less. Here, you'll want to store the data in a single row and to do this, you must set the text in row table option. The text in row option allows you to place small text, ntext, and image values directly in a data row instead of in separate pages. This can reduce the amount of space used to store small text, ntext, and image data and can also reduce the amount of disk input/output (I/O) needed to retrieve the values.

Note: A table that has fixed-length rows always stores the same amount of rows on each page. On the other hand, a table with variable-length rows stores as many rows as possible, based on the length of the data entered. As you might expect, there's a definite performance advantage to keeping rows compact and allowing more rows to fit on a page. With more rows per page, you'll have an improved cache-hit ratio and reduce I/O.

Understanding Extents

An extent is a set of eight contiguous data pages, which means extents are allocated in 64 KB blocks and there are 16 extents per megabyte. SQL Server 2000 has two types of extents:

• Mixed extents With mixed extents, different objects can own pages in the extent. This means that up to eight objects can own a page in the extent.

• Uniform extents With uniform extents, a single object owns all the pages in the extent. This means that only the owning object can use all eight pages in the extent.

When you create a new table or index, SQL Server allocates pages from a mixed extent to the new table or index. The table or index continues to use pages in the mixed extent until it grows to the point where it uses eight data pages. When this happens, SQL Server changes the table or index to uniform extents. As long as the table or index continues to use at least eight data pages, it will use uniform extents.

Working with Tables

SQL Server provides many ways to work with tables. You can create new tables using the New Table window in Enterprise Manager or the CREATE TABLE command. You can modify existing tables by using the Design Table window in Enterprise Manager or the ALTER TABLE command. You can also perform other table management functions, including copy, rename, and delete.

Creating Tables

Before you create a table, you should carefully consider the table name. Table names can be up to 128 characters long. Table names must begin with an alphabetic character but can contain underscores (_), at symbols (@), and pound signs (#). The exceptions to this rule are temporary tables. Private temporary tables begin with # and are accessible to you only during the current user session. Global temporary tables begin with ## and are accessible to anyone as long as your user session remains connected. Temporary tables are created in tempdb and are automatically deleted when your user session ends.

In Enterprise Manager you create a new table by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with. Click the plus sign (+) next to the database name to display a list of data objects and resources.

2. To create a new table, right-click the Tables node and from the shortcut menu, choose New Table. You'll then access the New Table view in Enterprise Manager, which is similar to what you see in Figure 6-1.

   You can now create a new column by entering a column name and specifying its properties. Rows and columns displayed in the Enterprise Manager window are used as follows:

   • Rows in the New Table view correspond to columns in the table you're working with. In Figure 6-1, columns listed include CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, and Fax.

   • Columns in the New Table view correspond to column properties in the table you're working with. In Figure 6-1, column properties listed include Column Name, Data Type, Length, and Allow Nulls.

     

     Figure 6-1: Enterprise Manager makes it easy to create and modify tables. Work your way through the column properties from left to right.

   Use the Columns panel in the lower left corner to specify additional characteristics for the column you're creating. Additional characteristics are:

   • Description Shows a description of the column.

   • Default Value Shows or determines the default value for the column, which is used whenever a row with a null value for this column is inserted into the database.

   • Precision Shows or determines the maximum number of digits for values in the column. Only applies when the column contains numeric values.

   • Scale Shows or determines the maximum number of digits that can appear to the right of the decimal point for values in the column. Only applies when the column contains numeric values.

   • Identity Shows or determines whether the column is used as an identifier column.

   • Identity Seed Shows or sets the base value for generating unique identifiers. Only applies to columns whose Identity option is set to Yes or Yes (Not For Replication).

   • Identity Increment Shows or sets the increment for generating unique identifiers. Only applies to columns whose Identity option is set to Yes or Yes (Not For Replication).

   • Is RowGuid Shows or determines whether the column contains globally unique identifiers. Only applies to columns whose Identity option is set to Yes or Yes (Not For Replication).

   • Formula Shows or sets the formula for a computed column.

   • Collation Shows or sets the default collating sequence that SQL Server applies to the column whenever the column values are used to sort rows of a query result.

3. You can now create a new column by entering a column name and specifying its properties or by modifying an existing column by changing its name or its properties, or both.

4. When you're finished creating the table, click Save. Afterward type the table name when prompted and then click OK.

You can create tables with Transact-SQL using the CREATE TABLE command. The syntax and usage for this command is shown as Sample 6-1.

Sample 6-1 CREATE TABLE Syntax and Usage

Syntax

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
      | column_name AS computed_column_expression
      | < table_constraint > } [ ,...n ]
   )
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
   [ [ DEFAULT constant_expression ]
      | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
   ]
   [ ROWGUIDCOL ]
   [ COLLATE < collation_name > ]
   [ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT constraint_name ]
   { [ NULL | NOT NULL ]
      | [ { PRIMARY KEY | UNIQUE }
         [ CLUSTERED | NON-CLUSTERED ]
         [ WITH FILLFACTOR = fillfactor ]
         [ON {filegroup | DEFAULT} ] ]
      ]
      | [ [ FOREIGN KEY ]
         REFERENCES ref_table [ ( ref_column ) ]
         [ ON DELETE { CASCADE | NO ACTION } ]
         [ ON UPDATE { CASCADE | NO ACTION } ]
         [ NOT FOR REPLICATION ]
      ]
      | CHECK [ NOT FOR REPLICATION ]
      ( logical_expression )
   }
< table_constraint > ::= [ CONSTRAINT constraint_name ]
   { [ { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NON-CLUSTERED ]
      { ( column [ ASC | DESC ] [ ,...n ] ) }
      [ WITH FILLFACTOR = fillfactor ]
      [ ON { filegroup | DEFAULT } ]
   ]
   | FOREIGN KEY
      [ ( column [ ,...n ] ) ]
      REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
      [ ON DELETE { CASCADE | NO ACTION } ]
      [ ON UPDATE { CASCADE | NO ACTION } ]
      [ NOT FOR REPLICATION ]
   | CHECK [ NOT FOR REPLICATION ]
      ( search_conditions )
   }

Usage

CREATE TABLE Customers
(
   cust_lname varchar(40) NOT NULL,
   cust_fname varchar(20) NOT NULL,
   phone char(12) NOT NULL,
   uid uniqueidentifier NOT NULL
   DEFAULT newid()
)

Modifying Existing Tables

In Enterprise Manager you modify an existing table by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with. Click the plus sign (+) next to the database name to display a list of data objects and resources.

2. To edit an existing table, right-click the table name in the right pane and from the shortcut menu, choose Design Table. This opens the Design Table view shown previously in Figure 6-1.

3. Make any necessary changes to the table and then click Save. If the changes you make affect multiple tables, you'll see a prompt showing which tables will be updated and saved in the database. Click Yes to continue and complete the operation.

The Transact-SQL command for modifying tables is ALTER TABLE. The syntax and usage for this command is shown as Sample 6-2.

Sample 6-2 ALTER TABLE Syntax and Usage

Syntax

ALTER TABLE table
{ [ ALTER COLUMN column_name
   { new_data_type [ ( precision [ , scale ] ) ]
      [ COLLATE < collation_name > ]
      [ NULL | NOT NULL ]
      | {ADD | DROP } ROWGUIDCOL }
   ]
   | ADD
      { [ < column_definition > ]
      | column_name AS computed_column_expression
      } [ ,...n ]
   | [ WITH CHECK | WITH NOCHECK ] ADD
      { < table_constraint > } [ ,...n ]
   | DROP
      { [ CONSTRAINT ] constraint_name
         | COLUMN column } [ ,...n ]
   | { CHECK | NOCHECK } CONSTRAINT
      { ALL | constraint_name [ ,...n ] }
   | { ENABLE | DISABLE } TRIGGER
      { ALL | trigger_name [ ,...n ] }
}
< column_definition > ::=
   { column_name data_type }
   [ [ DEFAULT constant_expression ]
   | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]
      ]
   [ ROWGUIDCOL ]
   [ COLLATE < collation_name > ]
   [ < column_constraint > ] [ ...n ]
< column_constraint > ::=
   [ CONSTRAINT constraint_name ]
   { [ NULL | NOT NULL ]
      | [ { PRIMARY KEY | UNIQUE }
         [ CLUSTERED | NON-CLUSTERED ]
         [ WITH FILLFACTOR = fillfactor ]
         [ ON { filegroup | DEFAULT } ]
         ]
      | [ [ FOREIGN KEY ]
         REFERENCES ref_table [ ( ref_column ) ]
         [ ON DELETE { CASCADE | NO ACTION } ]
         [ ON UPDATE { CASCADE | NO ACTION } ]
         [ NOT FOR REPLICATION ]
         ]
   | CHECK [ NOT FOR REPLICATION ]
         ( logical_expression )
   }
< table_constraint > ::=
   [ CONSTRAINT constraint_name ]
   { [ { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NON-CLUSTERED ]
      { ( column [ ,...n ] ) }
      [ WITH FILLFACTOR = fillfactor ]
      [ ON {filegroup | DEFAULT } ]
      ]
      | FOREIGN KEY
         [ ( column [ ,...n ] ) ]
         REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
         [ ON DELETE { CASCADE | NO ACTION } ]
         [ ON UPDATE { CASCADE | NO ACTION } ]
         [ NOT FOR REPLICATION ]
      | DEFAULT constant_expression
         [ FOR column ]
      | CHECK [ NOT FOR REPLICATION ]
         ( search_conditions )
}

Usage

ALTER TABLE Customers
ADD uid uniqueidentifier NOT NULL DEFAULT newid()
ALTER TABLE Customers
ALTER COLUMN cust_fname CHAR(10) NOT NULL
ALTER TABLE Customers
DROP Address2

Viewing Table Row and Size Information

In Enterprise Manager you can view table row and size information by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. With the Taskpad enabled, select the database entry in the left pane and then in the right pane, click the Table Info tab, as shown in Figure 6-2.

You can also view row, size, and space statistics for individual tables using the sp_spaceused stored procedure. The following accesses the Customer database and then checks the statistics for the Customers table:

USE CUSTOMER
EXEC sp_spaceused Customers

Figure 6-2: The Table Info view provides a summary of tables and indexes used in the database, which includes the number of rows used and the table size.

Displaying Table Properties and Permissions

In Enterprise Manager you can display table properties and permissions by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Select the Tables node to display a table list in the right pane.

3. Double-click the table whose properties you want to examine.

4. Once the Properties dialog box is open, you can click Permissions to display and change table permissions.

Displaying Current Values in Tables

In Enterprise Manager, you modify an existing table by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with. Click the plus sign (+) next to the database name to display a list of data objects and resources.

   To display the current values in a table, right-click the table name in the right pane and from the shortcut menu, point to Open Table and then select one of the following options:

   • Return All Rows Returns all rows in the selected table.

   • Return Top Returns N number of rows in the selected table. When prompted, type the maximum number of rows to fetch and then click OK.

   • Query Displays a Data In Table view with a basic query that you can extend to display table data.

Copying Tables

The easiest way to create a copy of a table is to use Transact-SQL. To do that, complete the following steps:

1. Make sure that the Select Into/Bulk Copy database option is selected as specified in the section of Chapter 4 entitled "Database Options for Enterprise Manager and Transact-SQL."

2. Use SELECT INTO to extract all the rows from an existing table into the new table. The new table must not exist already. For example, if you wanted to copy the Customers table to a new table called Customers2, you could use

SELECT * INTO Customers2 FROM Customers

Renaming and Deleting Tables

In Enterprise Manager, the easiest way to rename or delete a table is to complete the following steps:

1. Start Enterprise Manager, and then work your way down to the database you want to work with.

2. Select the Tables node to display a table list in the right pane.

3. To rename a table, right-click the table and then from the shortcut menu, choose Rename. You can now type a new name for the table.

4. To delete a table, right-click the table and then from the shortcut menu, choose Delete. When prompted to confirm the action, choose Yes.

You can also rename tables using sp_rename, such as:

USE CUSTOMER
EXEC sp_rename Customers, Customers2

You can remove a table from the database using the DROP TABLE command, such as:

USE CUSTOMER
DROP TABLE Customers2

If you'd rather delete the rows in a table but leave its structure intact, you can use DELETE. The following DELETE command deletes all the rows in a table but doesn't remove the table structure:

USE CUSTOMER
DELETE Customers

Adding and Removing Columns in a Table

In Enterprise Manager you add or remove columns in a table as described in the section of this chapter entitled "Working with Tables." In Transact-SQL you modify table columns using the ALTER TABLE command, which was listed previously in Sample 6-2.

Adding Columns

The following example adds a unique identifier column to the Customers table:

USE CUSTOMER
ALTER TABLE Customers
ADD uid uniqueidentifier NOT NULL DEFAULT newid()

Modifying Columns

To change the characteristics of an existing column, use the ALTER COLUMN command, such as:

USE CUSTOMER
ALTER TABLE Customers
ALTER COLUMN cust_fname CHAR(10) NOT NULL

Removing Columns

The following example removes the Address2 column from the Customers table:

USE CUSTOMER
ALTER TABLE Customers
DROP Address2

Scripting Tables

You can recreate and store all the SQL commands that go into creating tables in a database in a .sql file for later use. To do this, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Select the Tables node to display a table list in the right pane.

3. Right-click the table you want to work with and then select All Tasks. Now choose Generate SQL Script to open the dialog box shown in Figure 6-3.

4. By default, the table you clicked is listed in the Objects To Be Scripted list box. You can add other objects by clicking Show All, selecting the objects to add, and then using the Add button to add the objects to the script.

5. Use the Formatting tab fields to add formatting information that details how the objects are to be scripted.

6. Use the Options tab fields to script additional information, such as roles, logins, permissions, indexes, triggers, keys, and file options.

   

   Figure 6-3: Generate SQL commands needed to recreate tables using the Generate SQL Scripts dialog box.

7. Click OK when you're ready to save the .sql script. When prompted, set a folder and file path for the script.

Managing Table Values

The sections that follow examine key techniques and concepts for working with table values. Whether you want to create a new table or modify an existing one, the techniques you'll use and the concepts you'll need to understand are similar.

Using Native Data Types

Native data types are those built into SQL Server and supported directly. All data types have a length value, which is either fixed or variable. Length for a numeric or binary data type is the number of bytes used to store the number. Length for a character data type is the number of characters. Most numeric data types also have precision and scale. Precision is the total number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 8714.235 has a precision of seven and a scale of three.

Table 6-1 summarizes native data types that work with numbers and money. The first column shows the general data type or data type synonym for SQL-92 compatibility. The second column shows the SQL Server data type.

Table 6-1 Native Data Types for Numbers and Money

SQL-92 Name–Type	SQL Server Name	Range–Description
Integers
Bit	bit	0 or 1
Big integer	bigint	-2^63 through 2^63 =1.
Integer	int	-2^31 (-2,147,483,648) through 2^31 =1 (2,147,483,647)
small integer	smallint	2^15 (-32,768) through 2^15 - 1 (32,767)
tiny integer	tinyint	0 through 255
Money
Money	money	-922,337,203,685,477.5808 through +922,337,203,685,477.5807
small money	smallmoney	-214,748.3648 through +214,748.3647
Exact Numeric
dec, decimal	decimal	-10^38 through 10^38 -1
Numeric	decimal	-10^38 through 10^38 -1
Approximate Numeric
double precision	float	-1.79E + 308 through 1.79E + 308
Float	float	-1.79E + 308 through 1.79E + 308. float[(n)] for n = 1-53
Float	real	-3.40E + 38 through 3.40E + 38. float[(n)] for n = 1-24
Numerics
cursor	cursor	A reference to a cursor.
Rowversion	Rowversion	A database-wide unique number that indicates the sequence in which modifications took place in the database. Formerly called a timestamp.
SQL Variant	Sql_variant	A special data type that allows a single column to store multiple data types (except text, ntext, rowversion, and sql_variant).
Table	Table	A special data type that's used to store a result set temporarily for processing. Can be used only to define local variables and as the return type for user-defined functions.
Uniqueidentifier	uniqueidentifier	A globally unique identifier (GUID).

Table 6-2 summarizes native data types for dates, characters, and binary data. Again, the first column shows the general data type or data type synonym for SQL-92 compatibility. The second column shows the SQL Server data type.

Table 6-2 Native Data Types for Dates, Characters, and Binary Values

SQL-92 Name–Type	SQL Server Name	Range–Description
Date
datetime	datetime	January 1, 1753, to December 31, 9999; accuracy of three-hundredths of a second
small datetime	smalldatetime	January 1, 1900, through June 6, 2079; accuracy of one minute
Character
character	char	Fixed-length, non-Unicode character data with a maximum length of 8000 characters
character varying	varchar	Variable-length, non-Unicode data with a maximum of 8000 characters
text	text	Variable-length, non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters
national character	nchar	Fixed-length, Unicode data with a maximum length of 4000 characters
national char varying	nvarchar	Variable-length, Unicode data with a maximum length of 4000 characters
national text	ntext	Variable-length, Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters
Binary
binary	binary	Fixed-length, binary data with a maximum length of 8000 bytes
binary varying	varbinary	Variable-length, binary data with a maximum length of 8000 bytes
image	image	Variable-length, binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes

When you create or modify a table in Enterprise Manager, you assign a native data type by clicking in the Data Type column and using the selection list to choose a data type. In Transact-SQL you set the data type when you create the table and populate its columns or when you alter a table and add or change columns. Sample 6-3 shows how you could create the table shown previously in Figure 6-1.

Sample 6-3 Creating a Table and Its Columns

USE CUSTOMER
CREATE TABLE Customers
   (CustomerID nchar(5) NOT NULL,
   CompanyName nvarchar(40) NOT NULL,
   ContactName nvarchar(30) NOT NULL,
   ContactTitle nvarchar(30) NOT NULL,
   Address nvarchar(60) NOT NULL,
   City nvarchar(15) NULL,
   Region nvarchar(15) NULL,
   PostalCode nvarchar(5) NULL,
   Country nvarchar(15) NULL,
   Phone nvarchar(24) NULL,
   Fax nvarchar(24) NULL)

Using Fixed-Length and Variable-Length Fields

You can create binary and character data types as fixed-length or variable-length fields. When you use fixed-length data types, the column size you specify is reserved in the database and can be written to without your having to manipulate the data around the column. This makes updates to the database quicker than with variable-length fields. When you use variable-length data types, you allow SQL Server to squeeze more rows into data pages, if possible. Generally, more rows per data page allows for more efficient reading of data, which can translate into improved performance for read operations.

To gain a better understanding of the performance implications, consider the following scenario. With fixed-length columns of 80, 120, 40, and 500 bytes each, rows would always be written using 750 bytes of storage (740 bytes for data plus 10 bytes of overhead for each row). Here, you could fit 10 rows per data page (8096/750, without the remainder). If you used variable-length columns, however, the number of bytes used per row and the amount of rows stored per page would vary. As an example, let's say that on average the variable-length rows use 400 bytes. This includes 380 bytes of data and 20 bytes of overhead (12 bytes of overhead for rows that use variable length data plus 2 bytes of overhead per variable-length column). Here, you could fit 20 rows per data page (8096/400, without the remainder), which would make data reads more efficient than the fixed-length example.

Using User-Defined Data Types

User-defined data types are special data types that are based on a native data type. You'll want to use user-defined data types when two or more tables store the same type of data in a column and these columns must have exactly the same data type, length, and nullability. Either you or SQL Server can create user-defined data types. For example, sysname is a user-defined data type that's used to reference database object names. The data type is defined as a variable Unicode character type of 128 characters, which is why object names are limited to 128 characters throughout SQL Server. You can apply this same concept to ensure that a particular tidbit of data is used exactly as you want it to be used.

Creating User-Defined Data Types

You create user-defined data types at the database level rather than at the table level, which is why user-defined data types are static and immutable (unchangeable). This ensures that there is no performance penalty associated with user-defined data types. User-defined data types do have some limitations, however. You can't declare a default value or CHECK constraint as part of the user-defined data type. You can't create a user-defined data type based on a user-defined data type either.

Tip When you create user-defined data types in a user-defined database, they apply only to that database. If you want user-defined data types to apply to multiple databases, define the data type in the model database. After that, the user-defined data type will exist in all new user-defined databases.

In Enterprise Manager you create a user-defined data type by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Right-click the database name and select New. From the shortcut menu, select User Defined Data Type. This opens the User-Defined Data Type Properties dialog box shown in Figure 6-4.

   

   Figure 6-4: Configure the new data type using the User-Defined Data Type Properties dialog box.

3. Enter a name for the new data type.

4. In the Data Type list, select the data type on which you want to base the user-defined data type.

5. If the data type has a variable length, set the number of bytes or characters for the data type. For fixed-length variables, such as int, you won't be able to set a length.

6. To allow the data type to accept null values, select Allow Nulls.

7. Optionally, use the Rule and Default lists to select a rule or default to bind to the user-defined data type.

8. Click OK. If you open a new table or edit an existing table, you'll see the new data type as one of the last entries in the Data Type selection list.

You can also create user-defined data types with the sp_addtype stored procedure. Sample 6-4 shows this procedure's syntax and usage.

Sample 6-4 sp_addtype Syntax and Usage

Syntax

sp_addtype [@typename =] type,
   [@phystype =] system_data_type
   [, [@nulltype =] 'null_type']
   [, [@owner =] 'owner_name']

Usage

USE master
EXEC sp_addtype USPhoneNumber, 'char(12)', 'NOT NULL'

Managing User-Defined Data Types

Once you create user-defined data types, you'll often need to manage their properties. To manage user-defined data types, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Select the User Defined Data Types node in the left pane. This displays current user-defined data types in the right pane.

   Right-click the user-defined data type you want to manage and then select

   • Properties, to view the data type's properties and set dependencies

   • Delete, to delete the data type

   • Rename, to rename the data type

3. To see where the data type is used in the database, right-click the user-defined data type and then from the shortcut menu, choose Properties. In the User-Defined Data Type Properties dialog box, click Where Used.

Allowing and Disallowing Nulls

When you create columns in a table, you can specify whether nulls are allowed or not. A null means there is no entry in the column for that row; it isn't the same as zero or an empty string. Columns defined with a primary key constraint or identity property can't allow null values.

If you add a row but don't set a value for a column that allows null values, SQL Server inserts the value NULL—unless a default value is set for the column. When a default value is set for a column and you insert a null value, SQL Server replaces NULL with the default value. Additionally, if the column allows nulls, you can explicitly set a column to null using the NULL keyword. Don't use quotation marks when setting null explicitly.

In Enterprise Manager's New Table and Design Table views, you

• Allow nulls in a column by selecting the Allow Nulls column

• Disallow nulls in a column by clearing the Allow Nulls column

For a Transact-SQL example of allowing and disallowing nulls for a new table, see Sample 6-3.

Default Values

Null values are useful when you don't know the value or the value is missing. The use of null is controversial, though, and a better alternative is to set a default value. The default value is used when no value is set for a column you're inserting into a table. For example, you may want a character-based column to have the value N/A rather than NULL. Here, you would set the default value as N/A.

As Table 6-3 summarizes, combinations of default values and nullability are handled in different ways. The key thing to remember is that if you set a default value, the default is used whenever a value isn't specified for the column entry. This is true even if you allow nulls.

Table 6-3 Default Values and Nullability

Column Definition	No Entry, No DEFAULT Definition	No Entry, DEFAULT Definition	Enter a Null Value
Allows null values	Sets NULL	Sets default value	Sets NULL
Disallows null values	Error occurs	Sets default value	Error occurs

Using Identities and Globally Unique Identifiers

When you design tables, you'll often need to think about unique identifiers that can be used as primary keys or to ensure that merged data don't conflict with existing data. Unique identifiers for primary keys could include customer account numbers or social security numbers. However, if a unique identifier isn't avail able, you may want to use the identity property to generate sequential values that are unique for each row in a table. You could also use this unique identifier to automatically generate a customer account number, an order number, or whatever other unique value you need.

While the identity property provides a local solution for a specific table, it doesn't guarantee that the value will be unique throughout the database. Other tables in the database may have identity columns with the same values. In most cases, this isn't a problem because the identity values are usually used only within the context of a single table and don't relate to other tables. However, there are times when you'll need a value that's unique throughout one or more databases, and this is where globally unique identifiers come into the picture.

Globally unique identifiers are guaranteed to be unique across all networked computers in the world, which is extremely useful in merge replication. When you're merging data from multiple databases, globally unique identifiers ensure that records aren't inadvertently associated with each other. For example, the company's New York, Chicago, and San Francisco offices may have customer account numbers that are unique at local offices but not at the national level. Here, globally unique identifiers would ensure that account XYZ from New York and account XYZ from Chicago aren't merged as the same account.

Identities and globally unique identities aren't mutually exclusive. Each table can have one identifier column and one globally unique identity property. These values are often used together. For example, all clustered indexes in SQL Server should be unique, but they don't have to be unique.

In Enterprise Manager's New Table and Design Table views, you set identity values for a table by completing the following steps:

1. Create or modify other columns in the table as appropriate, and then start a new column for the identity value.

2. Give the identity column a name and then select a data type. Identifier columns must use the data type tinyint, smallint, int,bigint, decimal, or numeric. Globally unique identifier columns must have a data type of uniqueidentifier.

   Tip When you set the data type for an identifier column, be sure to consider how many rows are in the table as well as how many rows may be added in the future. A tinyint identifier would allow for only 256 unique values (0 to 255). A smallint identifier would allow for 32,768 values (0 to 32,767).

3. Clear the Allow Nulls check box for the identity column.

4. To assign a globally unique identifier, select the Is RowGuid check box for the identity column. A default value of newid() is created automatically for you.

   Note: The newid() function is used to generate new uniqueidentifier values. These values are obtained by combining the identification number of a network card with a unique number from the CPU clock. If a server process generates the identifier, the network card used is the server's. If the identifier is returned by application API function calls, the network card used is the client's. Network card manufacturers guarantee that no other network card in the next 100 years will have the same number.

   To assign a unique identifier:

   • Set Identity to Yes or Yes (Not For Replication).

   • Type a value in the Identity Seed cell. This value is assigned to the first row in the table. If you leave this cell blank, the value 1 is assigned by default.

   • Type a value in the Identity Increment cell. This value is the increment that is added to the Identity Seed for each subsequent row. If you leave this cell blank, the value 1 is assigned by default.

Note: The identity seed and increment are used to determine the identifier for rows. If you entered a seed value of 100 and an increment of 10, the first row would have a value of 100, the second would have a value of 110, and so on.

When you create a table in Transact-SQL, globally unique identifiers aren't generated automatically. You must reference the newid() function as the default value for the identifier column, such as:

USE CUSTOMER
CREATE TABLE Customers
   (cust_lname varchar(40) NOT NULL,
   cust_fname varchar(20) NOT NULL,
   phone char(12) NOT NULL,
   uid uniqueidentifier NOT NULL DEFAULT newid())

Now when you insert a new row into the table, SQL Server adds the globally unique identifier by default, such as:

INSERT INTO Customers
Values ('Stanek', 'William', '123-555-1212')

Or when you explicitly call the newid() function, such as:

INSERT INTO Customers
Values ('Stanek', 'William', '123-555-1212', newid())

Using Views

Views represent data in existing tables in an alternate way, and you can think of views as virtual tables. To create a view, you use a SELECT statement to select the data in one or more tables and display it as a view. For example, you could create a view that gets the customer's first name, last name, and account number from the Customers table and the order information from the Orders table, which makes the information more manageable for your company's sales representatives. As with tables, you can assign permissions to views. These permissions are specific to the view and separate from the table permissions.

Creating Views

Once you understand tables, creating views is a fairly straightforward process. You can create a view in Enterprise Manager by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Select the Views node to display a view list in the right pane. Two types of views are available: system and user. System views provide summary information about key database information, such as table constraints and table privileges. User views are defined by you or by other database users.

3. To create a new view, right-click the Views node. From the shortcut menu, choose New View. This displays the New View window (see Figure 6-5).

   

   Figure 6-5: Generate SQL commands needed to recreate tables using the New View or Design View dialog box.

4. To modify an existing view, right-click the view name and from the shortcut menu, choose Design View. This displays the Design View window, which is similar to the window shown in Figure 6-5.

5. To add tables to the Diagram pane, right-click inside the Diagram pane window and select Add Table. In the Add Table dialog box, select the table(s) you want to add. This displays view panes for each selected table, which you can use to select table columns. Afterward, use the fields and options provided to manipulate the selection. Your actions create a select statement that can be used to generate the view. When you're ready, click Run on the toolbar to create the view.

   Tip In the example, I entered Select * From Customer2 to create an initial view. This loaded all the column data, which I then used to set limiting criteria, as shown in Figure 6-5.

   To set view properties, click Properties. Then use the Properties dialog box to set view properties. Key properties you'll want to set include

   • DISTINCT Values Ensures that rows returned are unique by filtering out duplicates

   • Encrypt View Encrypts the view so it's stored in an encoded format that can't be read using Enterprise Manager tools

6. After you run the view to update it for the latest changes, save the view. Click Save on the toolbar. If this is a new view, you'll be prompted for a view name. Enter the view name and then click OK.

You can also create views using the CREATE VIEW command. You can create a simple view by selecting all the values in a table, such as:

CREATE VIEW [Sales Custom View] As
SELECT *
FROM Customers2

You can then work directly with the view, such as:

SELECT * FROM [Sales Custom View]

To create the view shown in Figure 6-5, you would use the following command:

CREATE VIEW [Sales Custom View] As
SELECT cust_id AS Account, cust_lname AS [Last Name],
   cust_fname AS [First Name], state AS Region
FROM Customers2
WHERE (state = 'WA') OR
   (state = 'HI') OR
   (state = 'CA')

The full syntax for CREATE VIEW is shown as Sample 6-5.

Sample 6-5 CREATE VIEW Syntax

Syntax

CREATE [ < owner > ] VIEW view_name [ ( column [ ,...n ] ) ]
   [ WITH < view_attribute > [ ,...n ] ]
   AS
   select_statement
   [ WITH CHECK OPTION ]
   < view_attribute > ::=
      { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

To change an existing view without having to reset its permissions and other properties, you use ALTER VIEW. The following example changes the definition of the Sales Custom view used in previous examples:

ALTER VIEW [Sales Custom View] As
   SELECT cust_id AS Account, cust_lname AS [Customer Last Name],
   cust_fname AS [Customer First Name], state AS Region
   FROM Customers2
   WHERE (state = 'WA') OR
      (state = 'CA')

The full syntax for ALTER VIEW is shown as Sample 6-6.

Sample 6-6 ALTER VIEW Syntax

Syntax

ALTER VIEW view_name [ ( column [ ,...n ] ) ]
   [ WITH < view_attribute > [ ,...n ] ]
   AS
      select_statement
   [ WITH CHECK OPTION ]
   < view_attribute > ::=
      { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

Using Updateable Views

SQL Server supports updateable views as well. With an updateable view, you can change the information in the view using insert, update, and delete statements. You can create updateable views with one or more tables, provided the tables don't contain aggregate functions.

With updateable views, you'll usually want to set WITH CHECK OPTION. If you don't, changes to the view may result in rows no longer being displayed in the view. To see how, consider the case of the view created on the previous page. Here, you're selecting customer information for Washington, Hawaii, and California. If you change a state value to OR, the row would disappear from the view. The reason for this is that Oregon-based customers aren't displayed in the view.

Managing Views

As with tables, you can examine view properties, set view permissions, and perform other key management tasks. To get started, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

   Select the Views node to display a view list in the right pane. Right-click the view you want to work with and then select

   • Rename, to rename the view

   • Delete, to delete the view

   • Properties, to examine view properties

2. To set view permissions or generate a script to recreate the view, right-click the view you want to work with and then select All Tasks. Afterward, select Manage Permissions or Generate SQL Scripts, as appropriate.

Creating and Managing Indexes

Indexes provide quick access to data without your having to search through an entire database. SQL Server 2000 allows you to create indexes on tables, views, and columns. Indexes on tables allow you to create indexes on important information in a table and quickly search through the data. Indexes on views allow you to create indexes where the result set of the view is stored and indexed in the database. Indexes on computed columns allow you to evaluate expressions and index the results (provided certain criteria are met).

Indexes are separate from tables, and you can configure them automatically using the Index Tuning Wizard. The sections that follow examine techniques you'll use to work with indexes.

Understanding Indexes

Indexes, like tables, use pages. Index pages are structured much like table data pages. They are 8 KB (8192 bytes) in size and have a 96-byte header. But unlike data pages, they don't have row offsets. Each index has a corresponding row in the sysindexes table with an index ID value (indid) of 1 for clustered indexes or 2–250 for nonclustered indexes. An index ID value of 255 indicates text, ntext, or image data.

SQL Server maintains indexes using a BTree structure, which is a basic tree structure consisting of a root node, intermediate level nodes, and leaf nodes. The wonderful thing about trees is that you can search them quickly and efficiently. Without the tree structure, SQL Server would need to read each table data page in the database in turn, searching for the correct record.

To put this in perspective, let's consider the limited situation where each data page contains a single row. In this case, if SQL Server searches for data in row 800 and there isn't an index, SQL Server may have to search 799 other rows before finding the right row. With a tree structure, SQL Server navigates the nodes down the index searching for the row that matches the corresponding index key. In the best-case scenario, where the index keys have been arranged in a full tree, the number of nodes that need to be searched is proportional to the height of the tree. For example, 27,000 rows may be represented with 30 levels of nodes, and if so, SQL Server would have to navigate 15 nodes on average to find the matching row.

Note: If you were paying particular attention, you know I streamlined the example to demonstrate the power of indexing. The point is that indexing can improve performance by orders of magnitude, and a database without it can seem extremely slow. By the same token, if you index the wrong information, you can make the database equally slow, which is why it's so important to select the most referenced/used column to index.

Clustered and Nonclustered Indexes

SQL Server supports two types of indexing:

• Clustered indexes

• Nonclustered indexes

You can create indexes on just about any column. The key exceptions are for columns that contain the text, ntext, image, and bit data types. You can't create indexes on these data types. You should always select the index column carefully. Selecting the correct column to index improves response time dramatically. Selecting the wrong column to index could actually degrade response time. For pointers on which column to index, use the Index Tuning Wizard.

Using Clustered Indexes

A clustered index stores the actual table data pages at the leaf level, and the table data is physically ordered around the key. A table can have only one clustered index and when this index is created, the following happens:

• Table data is rearranged.

• New index pages are created.

• All nonclustered indexes within the database are deleted.

The result is a lot of disk I/O operations and extensive use of system and memory resources. When creating a clustered index, it's a good idea to have free space that's at least 1.5 times the amount of data in the table. The extra free space ensures that you have enough space to complete the operation and do it efficiently.

Normally, you create a clustered index on a primary key. You can, however, create a clustered index on any named column, such as cust_lname or cust_id. With clustered indexes, the values you're indexing should be unique. If the values aren't unique, SQL Server creates secondary sort keys on rows that have duplicates of their primary sort keys.

Using Nonclustered Indexes

In a nonclustered index, pages on the leaf level contain a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. If the table has a clustered index, the bookmark indicates the clustered index key. If the table doesn't have a clustered index, the bookmark is an actual row locator.

When you create a nonclustered index, SQL Server creates the required index pages but doesn't rearrange table data. SQL Server doesn't delete other indexes either. Each table can have up to 249 nonclustered indexes.

Which Columns Should Be Indexed?

Now that you know how indexes work, you can focus on which columns you should index. Ideally, you'll select columns for indexing based on the types of queries executed against the database. A real help in determining the types of queries being run is SQL Server Profiler. You use SQL Profiler to create a trace that contains a good snapshot of activities performed by users on the database.

You can then manually examine this trace to see what types of queries are executed, or you can use the trace file as a saved workload file in the Index Tuning Wizard. Regardless of which technique you use, keep in mind that the maximum length of all columns that comprise an index is 900 bytes. This means that the total byte size of all columns must be 900 or less.

Table 6-4 What to Index and Not to Index

Index	Don't Index
Tables with lots of rows	Tables with few rows
Columns that are often used in queries	Columns that are rarely used in queries
Columns with strong selectivity, that they have a wide range of values	Columns with poor selectivity, meaning that they have a wide range of values
Columns used in aggregate functions	Columns that have a large byte size
Columns used in group by queries	Tables with lots of modifications but few actual queries
Columns used in order by queries
Columns used in table joins

Table 6-5 provides suggestions for the types of columns that should use clustered or nonclustered indexes.

Table 6-5 When to Use Clustered and Nonclustered Indexes

Use Clustered Index	Use Nonclustered Index
Primary keys that are searched for extensively, such as account numbers	Primary keys that are sequential identifiers, such as identity columns
Queries that return large result sets	Queries that return small result sets
Columns used in lots of queries	Columns used in aggregate functions
Columns with strong selectivity	Foreign keys
Columns used in order by or group by queries
Columns used in table joins

Indexing Computed Columns and Views

With SQL Server 2000, you can index computed columns and views as well as tables. Indexes on computed columns and views involve storing results in the database for future reference. With computed columns, the column values are calculated and then used to build the keys stored in the index. With views, the result set is stored by creating a clustered index on the view. In both cases, the stored results are valid only if all connections referring to the results can generate an identical result set, which puts specific restrictions on how you can create indexes on computed columns and views.

You must establish connections referring to the results using specific SET options and these options must have the same settings. The options you must set are as follows:

• ANSI_NULLS must be set on

• ANSI_PADDING must be set on

• ANSI_WARNINGS must be set on

• ARITHABORT must be set on

• CONCAT_NULL_YIELDS_NULL must be set on

• QUOTED_IDENTIFIER must be set on

• NUMERIC_ROUNDABORT must be set off

Further, all operations referencing the view must use the exact same algorithm to build the view result set. This includes:

• The create index statement that builds the initial result set or is used to calculate the initial keys

• Any subsequent insert, update, or delete statements that affect the data used to build the view result set or are used to calculate keys

• All queries for which the query optimizer must determine if the indexed view is useful

Viewing Indexes

In Enterprise Manager you can view indexes by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. With the Taskpad enabled, select the database entry in the left pane and then, in the right pane, click the Table Info tab.

3. The right pane now lists the tables and indexes in the database.

You can also view indexes using the sp_helpindex and sp_statistics stored procedures, such as:

use customer
exec sp_help_index Customers
exec sp_statistics Customers

Creating Indexes

The primary ways to create indexes are to use the Create Index Wizard or the Transact-SQL CREATE INDEX command. To create indexes with the wizard, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Click Wizards on the toolbar or from the Tools menu, choose Wizards. You'll see the Select Wizard dialog box. Expand the Database option by clicking the plus sign (+).

3. Select Create Index Wizard and click OK. Or double-click Create Index Wizard. This starts the Create Index Wizard.

4. Read the welcome message and then click Next to access the Select Database And Table dialog box shown in Figure 6-6.

   

   Figure 6-6: Use the Select Database And Table dialog box to select a database and table to work with.

5. Select the database and table on which you want to add the index. Click Next.

6. Any indexes that already exist are summarized in the Current Index Information dialog box. Click Next.

7. Select the column(s) you want to include in the index, as shown in Figure 6-7. You can only select columns that have valid data types for indexing. Any columns that aren't available for indexing are shown with a red X.

8. Each column can have a separate sort order for the index. By default, the sort order is set to ascending. You can set the sort order to descending by selecting Sort Order (DESC).

   Next set the following indexing options:

   • Make This A Clustered Index Select this option to create a clustered index on the columns selected. Otherwise, a nonclustered index is created. Keep in mind that you can have only one clustered index per table, so if the table already has a clustered index, this option is shaded and you can't select it.

   • Make This A Unique Index Select this option to enforce the uniqueness of column values.

   • Optimal Fill Factor Select this option to let SQL Server use an optimized fill as described in the section of Chapter 2 entitled "Setting the Index Fill."

   • Fixed Fill Factor Select this option and then set a fill factor using the related field. For more information on index fill, see Chapter 2.

   

   Figure 6-7: Use the Select Columns dialog box to select columns for indexing.

9. Click Next when you're ready to continue.

10. Type a name for the index in the Index Name field. You can use up to 128 characters for the index name. Ideally, the index name should be short and easy to associate with its purpose, such as Index For Cust ID.

11. Click Finish when you're ready to complete the process. The wizard then creates the index for you.

With the CREATE INDEX command, you create indexes using the syntax shown in Sample 6-7.

Sample 6-7 CREATE INDEX Syntax

Syntax

CREATE [ UNIQUE ] [ CLUSTERED | NON-CLUSTERED ] INDEX index_name
   ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH
      [ PAD_INDEX ]
      [ [ , ] FILLFACTOR = fillfactor ]
      [ [ , ] IGNORE_DUP_KEY ]
      [ [ , ] DROP_EXISTING ]
      [ [ , ] STATISTICS_NORECOMPUTE ]
      [ [ , ] SORT_IN_TEMPDB ]
]
[ ON filegroup ]

Managing Indexes

After you create an index, you may need to change its properties, rename it, or delete it. You handle these tasks in Enterprise Manager by completing the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with. Click the plus sign (+) next to the database name to display a list of data objects and resources.

2. Right-click the table name whose indexes you want to manage and then, from the shortcut menu, select Design Table. This opens the Design Table view in Enterprise Manager.

3. On the toolbar, click Table And Index Properties. This opens the Properties dialog box.

4. Click the Indexes/Keys tab, shown in Figure 6-8.

   Use the Selected Index selection list to choose the index you want to manage. With the index selected, you can now

   • Rename the index by entering a new index name in the Index Name field

   • Delete the index by clicking Delete

   • Change the index properties by selecting new options

   

   Figure 6-8: Manage indexes using the Indexes/Keys tab in the Properties dialog box.

You can also manage indexes with stored procedures and Transact-SQL commands, namely sp_rename and DROP INDEX. Unfortunately, a lot of caveats accompany these commands. For example, you can't drop an index that was created by defining a primary key or unique constraints. You must instead drop the constraint with ALTER TABLE.

Using the Index Tuning Wizard

The Index Tuning Wizard is one of the best tools a database administrator has in the indexing and optimization process. But before you start this wizard, you should create a trace containing a representative snapshot of database activity. You'll use this snapshot as the workload file in the Index Tuning Wizard. For specific pointers on creating a trace file, see the section of Chapter 10 entitled "Creating and Managing Performance Monitor Logs." To use the Index Tuning Wizard, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with.

2. Click Wizards on the toolbar or from the Tools menu, choose Wizards. You'll see the Select Wizard dialog box. Expand the Management option by clicking the plus sign (+).

3. Select Index Tuning Wizard and click OK. Or double-click Index Tuning Wizard. This starts the Index Tuning Wizard.

4. Read the welcome message and then click Next to access the Select Server And Database dialog box shown in Figure 6-9.

5. Select the server and database you wish to analyze.

   

   Figure 6-9: Use the Select Server And Database dialog box to select a server, database, and indexing options.

6. To ensure that existing indexes aren't dropped, select Keep All Existing Indexes. Otherwise, clear this option.

   Tip If you've selected a strong, representative snapshot of database activity in the trace, you'll probably want to clear the Keep All Existing Indexes option and let the Index Tuning Wizard make the appropriate suggestions for you. This ensures that existing indexes don't conflict with the recommendations the wizard may make.

7. To analyze indexed views as well as indexed tables, select Add Indexed Views.

8. Set the tuning mode as either Fast, Medium or Thorough. A more thorough analysis requires more time to perform but results in better recommendations for indexing.

9. In the Specify Workload dialog box, make sure that the My Workload file option button in the Workload section is selected. You set this option because you're using an existing trace data.

10. If you saved the trace data to a file, select My Workload File and then use the Open dialog box to find the trace file.

11. If you saved the trace data to a table, select SQL Server Table and then use the Connect to SQL Server dialog box to specify which SQL Server to connect to and the source table to use.

    Click the Advanced Options button to set advanced options, as shown in Figure 6-10. The advanced options are

    • Limit Number of Workload Queries To Sample Optionally sets the maximum number of queries to sample. If you want to set this value, select the associated check box and then enter a workload query limit.

      

      Figure 6-10: Click the Advanced Options button and then set index tuning parameters.

    • Maximum Space For The Recommended Indexes (MB) Sets the maximum space that can be used by index pages. The default is 2 MB, which may not be sufficient for a large or complex database.

    • Maximum Columns Per Index Sets the maximum number of columns that can be used in a single index. The default is 16, which is a high value for an average-sized database.

12. Next, select tables whose indexes you want to optimize, as shown in Figure 6-11. By default, all tables are deselected. You can add tables by selecting the check box for the associated table you want added or click the Select All Tables button to add all tables.

13. Click Next and the Index Tuning Wizard will begin analyzing your workload file. You can click End to stop the analysis at any time.

14. When it has finished the analysis, the wizard will make recommendations and display them in the Index Recommendations dialog box (see Figure 6-12). To see detailed analysis information, click the Analysis button.

15. Once you've completed reviewing the Index Tuning Wizard recommendations and analysis, click Next. You'll see the Completing The Index Tuning Wizard dialog box. Click Finish to apply the index tuning choices you've made. If you execute now, your changes will be updated and take effect immediately.

    

    Figure 6-11: Select tables to tune using this dialog box.

    

    Figure 6-12: Apply the changes, if desired. Otherwise, clear the Apply Changes option.

Optionally, save the recommended changes as a SQL script file. You can review or edit the script using a text editor and schedule a job to implement the changes later.

Column Constraints and Rules

Column constraints and rules are important parts of database administration. You use constraints to control the way column values are used, such as whether a value must be unique or whether it must have a specific format. While you usually apply constraints directly to a specific column, you can use rules to create constraints that you can apply to multiple tables in a database.

Using Constraints

SQL Server enforces the uniqueness of column values using unique and primary key constraints. You'll often use unique constraints to create secondary keys (for nonclustered indexes) that you can use in conjunction with the primary key. Foreign key constraints identify the relationships between tables and ensure that referential integrity is maintained. Other types of constraints you may want to use are check and not null constraints. Check constraints restrict the format or range of acceptable values for columns. Not null constraints prevent null values in a column.

Constraints can apply to columns or to entire tables. A column constraint is specified as part of a column definition and applies only to that column. A table constraint is declared independently from a column definition and can apply to several columns in the table. You must use table constraints when more than one column must be included in a constraint. For example, if a table has three columns in the primary key, you must use a table constraint to include all three columns in the primary key.

Setting Uniqueness Constraints

When you set a unique constraint on a column or columns, SQL Server automatically creates a unique index and then checks for duplicate values. If duplicate key values exist, the index creation operation is cancelled and an error message is displayed. SQL Server also checks the data each time you add data to the table. If the new data contain duplicate keys, the insert or update is rolled back and an error message is generated. You can specify that duplicate keys should be ignored by using the IGNORE_DUP_KEY option.

In Enterprise Manager you set the uniqueness constraint with the Make This A Unique Index option, as described in the "Creating Indexes" section of this chapter. In Transact-SQL you can set the unique constraint when you create the index, such as:

USE Customer
CREATE UNIQUE INDEX [Cust ID Index]
ON Customers(cust_id)

A nonclustered index is created unless a clustered index is explicitly specified, such as:

USE Customer
CREATE UNIQUE CLUSTERED INDEX [Cust ID Index]
ON Customers(cust_id)

Primary Key Constraints

SQL Server also allows you to designate any column or group of columns as a primary key, but primary keys are often defined for identity columns. A table can have only one primary key, and, because unique values are required, no primary key column can accept null values. Also, when you use multiple columns, the values of all the columns are combined to determine uniqueness.

As with unique constraints, SQL Server creates a unique index for the primary key columns. With primary key constraints, however, the index is created as a clustered index—unless a clustered index already exists on the table or a nonclustered index is explicitly specified.

In Enterprise Manager you set the primary key in the New Table view or the Design Table view by completing the following steps:

1. Clear Allow Nulls for any columns that will be used in the primary key.

2. Select the column or columns that you want to use as the primary key by holding down Shift and clicking the shaded box to the left of the column name.

3. Click Set Primary Key on the toolbar.

You can also set the primary key when you create or alter tables. Examples are shown in Sample 6-8.

Sample 6-8 Creating a Table and Its Columns with a Primary Key Constraint

USE CUSTOMER
CREATE TABLE Customers
   (cust_id varchar(11) NOT NULL,
   cust_lname varchar(40) NOT NULL,
   cust_fname varchar(20) NOT NULL,
   phone char(12) NOT NULL,
   CONSTRAINT PK_Cust PRIMARY KEY (cust_id))
USE CUSTOMER
ALTER TABLE Customers
   (ADD CONSTRAINT PK_Cust PRIMARY KEY (cust_id))

Foreign Key Constraints

Foreign key constraints identify the relationships between tables and ensure that referential integrity is maintained. A foreign key in one table points to a candidate key in another table. Foreign keys prevent changes that would leave rows with foreign key values when there are no candidate keys with that value in the related table. You can't insert a row with a foreign key value if there is no candidate key with that value. The exception is when you insert a null foreign key value.

In the following example, the Order table establishes a foreign key referencing the Customer table defined earlier:

CREATE TABLE Order
   (order_nmbr int,
   order_item varchar(20),
   qty_ordered int,
   cust_id int
      FOREIGN KEY REFERENCES Customer(cust_id)
      ON DELETE NO ACTION
)

The ON DELETE clause defines that actions are taken if you try to delete a row to which existing foreign keys point. The ON DELETE clause has two options:

• NO ACTION Specifies that the deletion fails with an error

• CASCADE Specifies that all rows with foreign keys pointing to the deleted row are to be deleted as well

You can also set an ON UPDATE clause, such as:

CREATE TABLE Order
   (order_nmbr int,
   order_item varchar(20),
   qty_ordered int,
   cust_id int
      FOREIGN KEY REFERENCES Customer(cust_id)
      ON UPDATE CASCADE
)

The ON UPDATE clause defines the actions that are taken if you try to update a row to which existing foreign keys point. The clause also supports the NO ACTION and CASCADE options.

Using Check Constraints

Check constraints allow you to control the format or range of values, or both, that are associated with tables and columns. You could use this type of constraint to specify that zip codes must be entered in the format 999999999 or that phone numbers must be entered as 9999999999.

In Enterprise Manager you set check constraints in the New Table view or Design Table view by completing the following steps:

1. Click Table And Index Properties on the toolbar. Then, as shown in Figure 6-13, click the Check Constraints tab.

2. To create a new constraint, click New, enter the new constraint in the Constraint Expression text box, and then type a name in the Constraint Name field.

3. To edit an existing constraint, choose it in the Selected Constraint selection list. Then modify the existing constraint expression in the Constraint Expression text box.

4. To delete a constraint, choose it in the Selected Constraint selection list and then click Delete. When prompted to confirm the deletion, choose Yes.

You can also add and remove constraints using the CREATE TABLE or ALTER TABLE command, such as:

USE CUSTOMER
ALTER TABLE Customer3
ADD CONSTRAINT CheckZipFormat
CHECK (([zip] like '[09][09][09][09][09][09][09][09][09]'))

Figure 6-13: Set constraints in the Check Contraints tab.

Using Not Null Constraints

Not null constraints specify that the column doesn't accept null values. Normally, you set not null constraints when you create the table. You can also set not null constraints when you alter a table. In Enterprise Manager, the Allow Nulls column in the Create Table and Design Table views controls the use of this constraint. If the Allow Nulls column is cleared, the related table column doesn't accept nulls.

Using Rules

A rule is a constraint that you can apply to multiple columns or tables. However, if a rule is applied to multiple columns within a table, each rule will be independent of the others and not be aware of the other columns. Rules perform the same function as check constraints and are maintained in SQL Server 2000 for backward compatibility. Instead of using rules, Microsoft recommends that you use check constraints. Check constraints are more customizable and concise than rules. For example, while you can apply only one rule to a column, you can apply multiple check constraints to a column. Rules can be very useful in certain situations. Constraints are defined within table definitions, whereas rules are independently defined objects and therefore are not limited to being bound to a particular table. Rules are also bound to a table after the table is created and are not deleted if the table is deleted. Rules also have the advantage of being able to be bound to any user-defined data type.

With those caveats, you still can use rules if you want to. To create a rule in Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then work your way down to the database you want to work with. Click the plus sign (+) next to the database name to display a list of data objects and resources.

2. To create a new rule, right-click the Rules node and from the shortcut menu, choose New Rule. This opens the dialog box shown in Figure 6-14.

3. In the Name field type a name for the rule. Although the name can be up to 128 characters long, you should use a fairly short name so you can easily reference it.

4. In the Text field, enter the constraint to set as a database rule. Substitute the @value function in the place of a specific column reference.

Once you create a rule, it's displayed in the Rules view, and you can manage it much as you would any other database resource. The corresponding Transact-SQL commands for creating and managing rules are CREATE RULE and DROP RULE. You can use CREATE RULE as follows:

CREATE RULE CheckFormatZip
AS @value LIKE '[09][09][09][09][09][09][09][09][09]'

After you have created a rule, you must activate the rule in order to use it. You use a special stored procedure called sp_bindrule to bind the rule to a particular table column or user-defined data type. You can also use sp_unbindrule to remove a rule that's bound to a table column or user-defined data type. Use the following syntax when binding and unbinding rules:

sp_bindrule <'rule'>, <object_name'>, [<'futureonly_flag'>]
EXEC sp_unbindrule 'object name'

Figure 6-14: Type a rule name and set the text of the constraint.

Click to order