Lesson 3:Creating and Managing Tables | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

After you have created the database and identified the data types, you are ready to create the table objects that store the data within the database. When you are creating a table, the table definition should include, at the very least, the table name, the column names, the data types (and lengths, if required), and whether a column accepts NULL values. You can configure other properties at a later time, although the more properties that you configure when you create the table, the more efficient the overall process becomes. In this lesson, you will learn how to create tables, including how to specify nullability, generate column values, and define default column values. You will also learn how to view information about the table object, modify table characteristics, and delete table objects from the database.

After this lesson, you will be able to:

Create table objects in a SQL Server database and define their attributes.
View information about table objects and modify table characteristics.
Delete table objects from a SQL Server database.

Estimated lesson time: 35 minutes

Creating Tables in a SQL Server Database

A table is a collection of data about a specific entity, such as customers, orders, or inventory. A table contains a set of columns. Each column represents an attribute of the table's data. For example, the order date might be an attribute of the orders entity. Each instance of data in a table is represented as a single record or row (sometimes referred to as a tuple).

At this point in the database development process, you should have all of the information you need to create the table in your database. Ideally, you would define everything you need in your table at one time, including PRIMARY KEY constraints as well as other constraints. For the purposes of this training kit, however, you will first learn how to create only the basic table (table name, columns, data types, nullability, and column values, where appropriate).

Determining Column Nullability

The nullability of a column determines whether the rows in the table can contain a null value for that column. A null value is not the same as zero, blank, or a zero-length character string (such as " "). Null means that no entry has been made. The presence of a null value usually implies that the value is either unknown or undefined. For example, a null value in the Price column of the Titles table of the Pubs database does not mean that the book has no price; instead, null means that the price is unknown or has not been set.

In general, avoid permitting null values because they incur more complexity in queries and updates and because they cannot be used with some column options, such as PRIMARY KEY constraints. Comparisons between two null values, or between a null value and any other value, return an unknown value because the value of each NULL is unknown. Null values cannot be used for information required to distinguish one row from another row in a table. In addition, eliminating null values when performing calculations can be important because certain calculations (such as average) can be inaccurate if NULL columns are included. If you need to create a column in which values are unknown, you can often define a default value for that column. For example, the Phone column in the Authors table of the Pubs database does not permit null values. The column does include a default value of UNKNOWN, however. If the phone number for the author is not added to the row for that author, the value in the Phone column will be UNKNOWN. Defining default values will be discussed in more detail later in this lesson.

If a row is inserted but no value is included for a column that allows null values, SQL Server supplies the null value (unless a default definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of null from the user, no matter what data type it is or whether it has a default associated with it. The null value should not be placed within quotation marks, because it will be interpreted as a character string rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that the column contains data in every row. If null values are not allowed, the user who is entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

NOTE

Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.

You define the nullability of a column when you define the column, either when you create a table or modify a table. Whether you define columns that allow or do not allow null values, you should always use the NULL or NOT NULL keywords in every column definition because of the complexities of how null is handled in SQL Server. The NULL keyword is used if null values are allowed in the column, and the NOT NULL keywords are used if null values are not allowed.

The following example uses the CREATE TABLE statement to create the Employee table. The Emp_ID column and the LastName column do not allow null values, but the FirstName column does allow null values:

 CREATE TABLE Employees ( Emp_ID char(4) NOT NULL, FirstName varchar(30) NULL, LastName varchar(30) NOT NULL )

Defining Default Values

Each column in a record must contain a value (even if that value is null). There are situations in which you need to load a row of data into a table but you do not know the value for a column (or the value does not yet exist). If the column allows null values, you can load the row with a null value. Because nullable columns might not be desirable, a better solution can be to define (where appropriate) a DEFAULT definition for the column. For example, it is common to specify zero as the default for numeric columns or N/A as the default for string columns when no value is specified.

NOTE

A DEFAULT definition in a CREATE TABLE statement is considered a type of constraint although it does not really enforce anything. Although constraints are discussed in detail in Chapter 5, "Implementing Data Integrity," DEFAULT definitions are discussed here to provide an alternative to defining columns that allow null values.

When you load a row into a table with a default definition for a column, you implicitly instruct SQL Server to load a default value in the column when you do not specify a value for the column.

If a column does not allow null values and does not have a default definition, you must specify a value for the column explicitly or SQL Server will return an error indicating that the column does not allow null values.

You can create a default definition in one of two ways:

By creating the default definition when you create the table (as part of the table definition)
By adding the default to an existing table (each column in a table can contain a single default definition)

The following example uses the CREATE TABLE statement to create the Employees table. None of the three columns allow null values; however, the FirstName column does provide for the possibility of an unknown first name by adding a default definition to the column definition. The CREATE TABLE statement uses the DEFAULT keyword to define the default value.

 CREATE TABLE Employees ( Emp_ID char(4) NOT NULL, FirstName varchar(30) NOT NULL DEFAULT 'unknown', LastName varchar(30) NOT NULL )

You can modify or delete an existing default definition. For example, you can modify the value inserted in a column when no value is entered.

NOTE

When using Transact-SQL to modify a default definition, you must first delete the existing DEFAULT definition and then re-create it with the new definition.

DEFAULT definitions cannot be created on columns defined with any of the fol-lowing:

A timestamp data type
An IDENTITY or ROWGUIDCOL property
An existing default definition or default object

NOTE

The default value must be compatible with the data type of the column to which the DEFAULT definition applies. For example, the default value for an int column must be an integer number, not a character string.

When a DEFAULT definition is added to an existing column in a table, SQL Server (by default) applies the new default only to new rows of data added to the table. Existing data inserted by using the previous default definition is unaffected. When adding a new column to an existing table, however, you can specify for SQL Server to insert the default value (specified by the default definition) rather than a null value into the new column for the existing rows in the table.

Autonumbering and Identifier Columns

For each table, you can create a single identifier column containing system-generated sequential values that uniquely identify each row within the table. For example, an identifier column can generate unique customer receipt numbers for an application automatically as rows are inserted into the table. Identifier columns usually contain values unique within the table in which they are defined. In other words, other tables containing identifier columns can contain the same identity values used by another table. This situation is usually not a problem, however, because the identifier values are typically used only within the context of a single table, and the identifier columns do not relate to other identifier columns in other tables.

A single, globally unique identifier column can also be created for each table containing values that are unique across all networked computers in the world. A column guaranteed to contain globally unique values is often useful when similar data from multiple database systems must be merged (for example, in a customer billing system with data located in various company subsidiaries around the world). When the data is merged into the central site for consolidation and reporting, using globally unique values prevents customers in different countries from having the same billing number or customer ID. SQL Server uses globally unique identifier columns for merge replication to ensure that rows are uniquely identified across multiple copies of the table.

Creating Identifier Columns

Only one identifier column and one globally unique identifier column can be created for each table.

IDENTITY Property

Identifier columns can be implemented by using the IDENTITY property, which enables the application developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed in order to determine successive identity numbers. When inserting values into a table with an identifier column, SQL Server automatically generates the next identity value by adding the increment to the seed.

When you use the IDENTITY property to define an identifier column, consider the following:

A table can have only one column defined with the IDENTITY property, and that column must be defined by using the decimal, int, numeric, smallint, bigint, or tinyint data type.
The seed and increment can be specified. The default value for both is 1.
The identifier column must not allow null values and must not contain a DEFAULT definition or object.
The column can be referenced in a select list by using the IDENTITYCOL keyword after the IDENTITY property has been set.
The OBJECTPROPERTY function can be used to determine whether a table has an IDENTITY column, and the COLUMNPROPERTY function can be used to determine the name of the IDENTITY column.

The following example uses the Transact-SQL CREATE TABLE statement to create the Employees table. Neither column allows null values. In addition, the Emp_ID column is an identity column. The seed value is 101, and the increment value is 1:

 CREATE TABLE Employees ( Emp_ID SMALLINT IDENTITY(101,1) NOT NULL, EmpName VARCHAR(50) NOT NULL )

NOTE

If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property. Instead, you can create a trigger that determines a new identifier value (based on existing values in the identifier column) as rows are inserted.

Globally Unique Identifiers

Although the IDENTITY property automates row numbering within one table, separate tables—each of which have their own identifier columns—can generate the same values. The IDENTITY property is guaranteed to be unique only for the table in which it is used. If an application must generate an identifier column that is unique across the entire database (or across every database on every networked computer in the world), use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function.

When you use the ROWGUIDCOL property to define a globally unique identifier column, consider the following:

A table can have only one ROWGUIDCOL column, and that column must be defined by using the uniqueidentifier data type.
SQL Server does not automatically generate values for the column. To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID function to generate a globally unique value.
The column can be referenced in a select list by using the ROWGUIDCOL keyword after the ROWGUIDCOL property is set. This function is similar to the way in which you can reference an IDENTITY column by using the IDENTITYCOL keyword.
The OBJECTPROPERTY function can be used to determine whether a table has a ROWGUIDCOL column, and the COLUMNPROPERTY function can be used to determine the name of the ROWGUIDCOL column.
Because the ROWGUIDCOL property does not enforce uniqueness, the UNIQUE constraint should be used to ensure that unique values are inserted into the ROWGUIDCOL column.

The following example uses the CREATE TABLE statement to create the Employees table. The Emp_ID column automatically generates a GUID for each new row added to the table:

 CREATE TABLE Employees ( Emp_ID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, EmpName VARCHAR(60) NOT NULL )

Methods for Creating a Table

SQL Server provides several methods that you can use to create a table: the Transact-SQL CREATE TABLE statement, the console tree in SQL Server Enterprise Manager, and Database Designer (which you access through SQL Server Enterprise Manager).

CREATE TABLE Statement

You can use the CREATE TABLE statement to create a table within a SQL Server database. When you use this statement, you must define, at a minimum, the table name, the columns, and the data types (and their values, if applicable). The following example illustrates how to create a basic table:

 CREATE TABLE Customers ( CustID char(4), CustName varchar(40) )

In addition to the basic table elements (table name, columns, and data types), the CREATE TABLE statement also enables you to define a number of other properties. For example, you can specify the filegroup on which the table is stored, or you can define constraints that apply to individual columns or to the table as a whole. For a complete description of the CREATE TABLE statement, refer to the Transact-SQL reference in SQL Server Books Online.

Enterprise Manager

You can create tables directly in SQL Server Enterprise Manager. To create a table in an existing database, expand the console tree until the database is displayed, expand the database, right-click the Tables node, and then click New Table. When the New Table window appears, fill in the necessary information to define the table, as shown in Figure 4.3.

figure 4.3-the new table window in sql server enterprise manager.

Figure 4.3 The New Table window in SQL Server Enterprise Manager.

Database Designer

You can use Database Designer in SQL Server Enterprise Manager to add a table to your database diagram, to edit its structure, or to relate it to other tables in your diagram. You can either add existing database tables to a diagram or insert a new table that has not yet been defined in the database. Alternatively, you can use Table Designer to create a table or modify an existing table. Table Designer is a visual tool that enables you to design and visualize a single table in a database to which you are connected.

Managing Tables in a SQL Server Database

Once you have created a table in a SQL Server database, you can view information about the table, modify the characteristics of the table, or delete the table from the database.

Viewing Information about Tables

After you have created the tables in a database, you might need to find information about the table properties (for example, the name or data type of a column, the nature of its indexes, and so on). You can also display the dependencies of the table to determine which objects, such as views, stored procedures, and triggers, depend on the table. If you make any changes to the table, dependent objects might be affected.

SQL Server includes several methods for viewing table characteristics and dependencies:

To view the definition of a table, use the sp_help system stored procedure or use SQL Server Enterprise Manager to view the table properties.
To view the dependencies of a table, use the sp_depends system stored procedure or use the Display Dependencies option in SQL Server Enterprise Manager.
To view column properties, use the COLUMNPROPERTY statement to return information about a column or procedure parameter.

Modifying Tables in a SQL Server Database

After a table is created, you can change many of the options that were defined for the table when it was originally created, including the following:

Columns can be added, modified, or deleted. For example, the column name, length, data type, precision, scale, and nullability can all be changed, although some restrictions exist. For more information, refer to Modifying Column Properties.

PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.
UNIQUE and CHECK constraints and DEFAULT definitions (and objects) can be added or deleted.
You can add or delete an identifier column by using the IDENTITY or ROWGUIDCOL property. The ROWGUIDCOL property can also be added to or removed from an existing column, although only one column in a table can have the ROWGUIDCOL property at any one time.
A table and selected columns within the table can be registered for full-text indexing.

The name or owner of a table can also be changed. When you perform this action, you must also change the name of the table in any triggers, stored procedures, Transact-SQL scripts, or other programming code that uses the old name or owner of the table.

The following table provides a list of several types of modifications that you can make to table properties. The table also lists the methods that you can use to complete these tasks. SQL Server Books Online provides detailed information about how to accomplish each of these tasks:


Type of Modification	Modification Methods
Renaming a table	The sp_rename system stored procedure The Rename option in SQL Server Enterprise Manager
Changing the owner of a table	The sp_changeobjectowner system stored procedure
Modifying column properties	ALTER DATABASE statement The Design Table option in SQL Server Enterprise Manager
Renaming a column	The sp_rename system stored procedure The Design Table option in SQL Server Enterprise Manager

Deleting Tables from a SQL Server Database

At times, you need to delete a table (for example, when you want to implement a new design or free up space in the database). When you delete a table, its structural definition, data, full-text indexes, constraints, and indexes are permanently deleted from the database, and the space formerly used to store the table and its indexes is made available for other tables. You can explicitly drop a temporary table if you do not want to wait until it is dropped automatically.

If you need to delete tables that are related through FOREIGN KEY and UNIQUE or PRIMARY KEY constraints, you must delete the tables with the FOREIGN KEY constraints first. If you need to delete a table that is referenced in a FOREIGN KEY constraint but you cannot delete the entire foreign key table, you must delete the FOREIGN KEY constraint.

To delete a table from a SQL Server database, use the DROP TABLE statement or use Enterprise Manager to remove the table from the Tables node.

Exercise 3: Creating and Managing Tables in a SQL Server Database

In this exercise, you will use the Transact-SQL CREATE TABLE statement to create the tables for the database that you created in Exercise 1. You will be creating each of the tables that you identified in the database design that you developed in Chapter 3, "Designing a SQL Server Database." When you create the tables, you will define the table names, columns, data types, lengths (where applicable), and nullability. You will also define IDENTITY properties and default values for the appropriate columns within the table. You should refer to the data constraints that you identified in that chapter. To perform this exercise, you should be logged in to your Windows 2000 Server computer as Administrator.

To create the Authors table in the BookShopDB

Open SQL Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE bookshopdb CREATE TABLE Authors        (        AuthorID SMALLINT IDENTITY(101,1) NOT NULL,        FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',        LastName VARCHAR(30) NOT NULL,        YearBorn CHAR(4) NOT NULL DEFAULT ' N/A ',        YearDied CHAR(4) NOT NULL DEFAULT ' N/A ',        Description VARCHAR(200) NOT NULL DEFAULT 'N/A'        )

In this statement, you are creating the Authors table, which contains six columns—all of which have been defined as NOT NULL. All NOT NULL values are being used to define the nullability, because a default value is being provided for those columns that do not require a value. In addition, the AuthorID column is being defined with the IDENTITY property. The first row added to the table will be assigned an AuthorID value of 101 (the seed value). Values in this column will be generated automatically in increments of one.

Execute the Transact-SQL code that you just created.

A message appears on the Messages tab saying that the command has completed successfully.

Open SQL Server Enterprise Manager and expand the console tree so that the objects within the BookShopDB database are displayed.
Click the Tables node.

Notice that the Author table appears in the list of tables in the right pane.

Right-click the Authors table, then click Properties.

The Table Properties—Authors dialog box appears.

View the properties for the Authors table.

Notice the column names, data types, size, and default values. The columns listed and their properties should reflect the table definition that you executed in SQL Query Analyzer.

To create the BookAuthors and BookCondition tables in the BookShopDB database

In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE bookshopdb CREATE TABLE BookAuthors        (        AuthorID SMALLINT NOT NULL,        TitleID CHAR(8) NOT NULL        ) CREATE TABLE BookCondition        (        ConditionID TINYINT NOT NULL,        ConditionName CHAR(10) NOT NULL,        Description VARCHAR(50) NOT NULL DEFAULT 'N/A'        )

In this statement, you are creating the BookAuthors and BookCondition tables. Notice that neither table includes an IDENTITY property. The BookAuthors table is a junction table, so values are based on other tables (the Authors table and the Books table). The BookCondition table has a finite number of rows, so it is easy to generate an ID manually.

Return to SQL Server Enterprise Manager.
Click the Refresh button on the toolbar.

The BookAuthors and BookCondition tables should now be listed in the right pane.

Open the properties for each table, and view their settings.

Notice the column names, data types, sizes, and default values. The columns listed and their properties should reflect the table definition that you executed in SQL Query Analyzer.

Close the properties after you have viewed them.

To create the remaining tables in the BookShopDB database

In the Editor pane of the Query window, enter and execute the Transact-SQL statements necessary to create the remaining tables in the BookShopDB database.

When creating the Transact-SQL statements, refer to the Transact-SQL reference in SQL Server Books Online as necessary. Also refer to the database design for the BookShopDB database that you created in Chapter 3 and the data constraints that you identified in that chapter.

What are the Transact-SQL statements that you created?

Save the script that you just created in case you want to use it in later exercises or to make corrections in the tables that you just created.

HINT

You can easily re-create a table by dropping the incorrect table and then executing the Transact-SQL code that applies only to the table that you want to re-create.

Return to SQL Server Enterprise Manager.
Click the Refresh button on the toolbar.

The tables that you just created should now be listed in the right pane.

Open the properties for each table, and view their settings.

Notice the column names, data types, sizes, and default values. The columns listed and their properties should reflect the table definition that you executed in SQL Query Analyzer.

Close the properties after you have viewed them.
Close SQL Server Enterprise Manager and SQL Query Analyzer.

Lesson Summary

Once you have created the database and identified the data types, you can create the table objects that store the data within the database. A table is a collection of data about a specific entity, such as customers, orders, or inventory. A table contains a set of columns. Each column represents an attribute of the table's data. When defining a column, you should also define its nullability. The nullability of a column determines whether the rows in the table can contain a null value for that column. You should try to avoid permitting null values, however, because they incur more complexity in queries and updates (and because there are other column options). Each column in a record must contain a value, however (even if that value is null). Because nullable columns might not be desirable, a better solution can be to define, where appropriate, a DEFAULT definition for the column. When you load a row into a table with a default definition for a column, you implicitly instruct SQL Server to load a default value in the column when you do not specify a value for the column. For each table, a single identifier column can be created that contains system-generated sequential values that uniquely identify each row within the table. Identifier columns usually contain values unique within the table for which they are defined. In other words, a table containing identifier columns can contain the same identity values that another table is using. A single, globally unique identifier column can also be created for each table containing values that are unique across all networked computers in the world. SQL Server provides several methods that you can use to create a table: the Transact-SQL CREATE TABLE statement, the console tree in SQL Server Enterprise Manager, and Database Designer (which you access through SQL Server Enterprise Manager). Once you have created a table in a SQL Server database, you can view information about the table, modify the characteristics of the table, or delete the table from the database.