Creating Tables Using T-SQL

This section examines how to use T-SQL to create tables. You will see how to define table columns and set properties for the columns . Also covered is defining a location for the table, adding constraints, and making modifications to existing tables.

Defining Columns

In defining a column, you assign a name and a datatype to the column. Depending on the datatype you choose, you might also have to assign parameters to the datatype, such as a length for a char() column. Listing 12.3 shows a simple CREATE TABLE statement defining six columns.

Listing 12.3 Defining Columns with CREATE TABLE

 CREATE TABLE yourdb.dbo.employee (   Emp_no int,   Lname varchar(20),   Fname varchar(20),   Phone char(13),   Dept smallint,   Salary money ) 

Column Properties

Columns also can have properties assigned to them. These properties can address whether a value must be provided for a column, using NULL or NOT NULL , or whether SQL Server provides a value for the column, as is the case with the identity property.

NULL or NOT NULL

When writing your create table scripts, it is always good form to explicitly state whether a column should or should not contain nulls. The SQL Server default is not to allow nulls. The ANSI-92 standard is to allow nulls. To further confuse matters, the database option 'ANSI_NULL_DEFAULT' can be set so that SQL Server matches the ANSI-92 standard. It can also be set at the session level. As a matter of fact, if you run your script from Query Analyzer, it overrides the SQL Server default and allows nulls if not specified. I hope I've made my point that it is best to explicitly specify the NULL property so you know for sure what it's going to be. Listing 12.4 expands on the previous example and properly specifies NULL or NOT NULL .

Listing 12.4 Defining Column NULL Properties with CREATE TABLE

 CREATE TABLE yourdb.dbo.employee (   Emp_no int NOT NULL,   Lname char(20) NOT NULL,   Fname char(20) NOT NULL,   Phone char(13) NULL,   Dept smallint NOT NULL,   Salary int NULL ) 

It is beyond the scope of this section to enter the debate on whether columns should ever allow nulls. That being said, I'll go ahead and put in my advice. If a column is defined as NULL , no value needs to be entered in that column when inserting or updating data. By defining columns as NOT NULL and providing a default value where possible, your data will be more consistent and easier to work with. If you allow nulls, you and the development team must always be aware of the effect nulls can have on querying the database.

Identity Columns

Another common property specified when creating tables is the IDENTITY property. This property, used in conjunction with the integer datatypes (although decimal can be used with a scale of 0), automatically generates a unique value for a column. This is extremely useful for generating what is referred to as a surrogate primary key. Purists will say that the primary key, or unique row identifier, should be derived from a column or combination of columns that are valid attributes of the entity. In the employee table I have been using in the examples, without an employee key being generated, I would have to combine last name, first name, and phone number as the primary key. Even then, if John Smith Jr. and John Smith Sr. had the same phone number, this combination would fail to guarantee uniqueness. This is where IDENTITY comes in. By generating a unique value for each row entered, I have satisfied the need for a unique key on the row.

When implementing an IDENTITY property, you supply a seed and an increment. The seed is the start value for the numeric count, and the increment is the amount by which it grows. A seed of 10 and an increment of 10 would produce 10, 20, 30, 40, and so on. If not specified, the default seed value is 1 and the increment is 1. Listing 12.5 adds to the script by setting an IDENTITY value that starts at 100 and increments by 10.

Listing 12.5 Defining an Identity Column with CREATE TABLE

 CREATE TABLE yourdb.dbo.employee (   Emp_no int IDENTITY (100, 10) NOT NULL,   Lname char(20) NOT NULL,   Fname char(20) NOT NULL,   Phone char(13) NULL,   Dept smallint NOT NULL,   Salary int NULL ) 

Defining Table Location

As databases scale in size, the physical location of database objects, particularly tables and indexes, becomes crucial. Consider two tables, Employee and Dept, which are always queried together. If they are located on the same physical disk, contention for hardware resources slows performance. SQL Server enables you to specify where a table (or index) is stored. This not only affects performance, but planning for backups as well. By dedicating a read-only table to a filegroup, you only need to back up the filegroup once. If your table contains text or image data, you can also specify where it should be stored.

The location of the table is specified with the ON clause, and TEXTIMAGE ON indicates where the text and image locaters should point. In Listing 12.6, you create the employee and dept tables, place them on two different filegroups, and store the image for the employee security photo on yet another filegroup. Note that the filegroups must exist before the tables are created. For information on filegroups, see Chapter 11, "Creating and Managing Databases."

Listing 12.6 Syntax for Creating Tables on Specific Filegroups

 CREATE TABLE yourdb.dbo.employee (   Emp_no int IDENTITY (100, 10) NOT NULL,   Lname char(20) NOT NULL,   Fname char(20) NOT NULL,   Phone char(13) NULL,   Dept smallint NOT NULL,   Photo image NULL,   Salary int NULL ) ON FGDISK1 TEXTIMAGE_ON FGDISK3 GO CREATE TABLE yourdb.dbo.dept (   Dept_no smallint IDENTITY (10, 10) NOT NULL,   Name varchar(20) NOT NULL,   Description varchar(80) NOT NULL,   Loc_code char(2) NULL ) ON FGDISK2 

Defining Table Constraints

Constraints provide us with the means to enforce data integrity. In addition to NULL / NOT NULL , which was covered in a previous section, SQL Server provides five constraint types: PRIMARY KEY , FOREIGN KEY , UNIQUE , CHECK , and DEFAULT .

Constraints are covered in detail in Chapter 14, so in the context of creating tables, this chapter will concentrate on the syntax for adding constraints.

Listing 12.7 expands on the CREATE TABLE script by adding primary keys to both tables and creating a foreign key on the employee table that references the dept table.

Listing 12.7 Syntax for Creating Constraints with CREATE TABLE

 CREATE TABLE yourdb.dbo.employee (   Emp_no int IDENTITY (100, 10)CONSTRAINT EMP_PK PRIMARY KEY NOT NULL,   Lname char(20) NOT NULL,   Fname char(20) NOT NULL,   Phone char(13) NULL,   Dept smallint CONSTRAINT EMP_DEPT_FK REFERENCES dept(dept_no)NOT NULL,   Photo image NULL,   Salary int NULL ) ON FGDISK1 TEXTIMAGE_ON FGDISK3 go CREATE TABLE yourdb.dbo.dept (   Dept_no smallint IDENTITY (10, 10) CONSTRAINT DEPT_PK PRIMARY KEY NOT NULL,   Name varchar(20) NOT NULL,   Description varchar(80) NOT NULL,   Loc_code char(2) NULL ) ON FGDISK2 

In the following example, CREATE TABLE is run first, and then ALTER TABLE is run to add the constraints. Listing 12.8 shows how separating constraint creation from table creation makes the script easier to read and more flexible.

Listing 12.8 Syntax for Creating Constraints with ALTER TABLE

 CREATE TABLE dbo.Product (   ProductID int IDENTITY (1, 1) NOT NULL ,   ProductName nvarchar (40) NOT NULL ,   SupplierID int NULL ,   CategoryID int NULL ,   QuantityPerUnit nvarchar (20) NULL ,   UnitPrice money NULL ,   UnitsInStock smallint NULL ,   UnitsOnOrder smallint NULL ,   ReorderLevel smallint NULL ,   Discontinued bit NOT NULL ) GO ALTER TABLE dbo.Product ADD   CONSTRAINT DF_Product_UnitPrice DEFAULT (0) FOR UnitPrice,   CONSTRAINT PK_Product PRIMARY KEY      (ProductID),   CONSTRAINT CK_Product_UnitPrice CHECK (UnitPrice >= 0) GO ALTER TABLE dbo.Product ADD   CONSTRAINT FK_Product_Categories FOREIGN KEY   (CategoryID) REFERENCES dbo.Categories (CategoryID) GO 

Adding/Removing/Modifying Table Columns Using T-SQL

The previous example touched on using ALTER TABLE to add constraints to an existing table. Although this is a common use of the ALTER TABLE command, you can actually change several properties of a table. The following lists the types of changes you can make to a table:

• Change the datatype or NULL property of a column.

• Add new columns or drop existing columns.

• Add or drop constraints.

• Enable or disable CHECK and FOREIGN KEY constraints.

• Enable or disable triggers.

Changing the Datatype

The ALTER COLUMN clause of ALTER TABLE can be used to modify the NULL property or datatype of a column. Listing 12.9 shows an example of changing the datatype of a column.

Listing 12.9 Changing the Datatype of a Column with ALTER TABLE

 ALTER TABLE product ALTER COLUMN ProductName varchar(50) 

You must be aware of several restrictions when you modify the datatype of a column. The following rules apply when altering columns:

• A text , image , ntext , or timestamp column can't be modified.

• The column can't be the ROWGUIDCOL for the table.

• The column can't be a computed column or be referenced by a computed column.

• The column can't be a replicated column.

• If the column is used in an index, the column length can only be increased in size. In addition, it must be a varchar , nvarchar , or varbinary datatype, and the datatype cannot change.

• If statistics have been generated using CREATE STATISTICS , the statistics must first be dropped.

• The column can't have a PRIMARY KEY or FOREIGN KEY constraint or be used in a CHECK or UNIQUE constraint; the exception is that a column with a CHECK or UNIQUE constraint, if defined as variable length, can have the length altered .

• A column with a default defined for it can only have the length, nullability, or precision and scale altered.

• If a column has a schema-bound view defined on it, the same rules that apply to columns with indexes apply.

NOTE

Changing some datatypes can result in changing the data. For example, changing from nchar to char could result in any extended characters being converted. Similarly, changing precision and scale could result in data truncation . Other modifications such as changing from char to int might fail if the data doesn't match the restrictions of the new datatype. When changing datatypes, always validate that the data conforms to the new datatype.

Adding and Dropping Columns

Columns are added to a table with the ADD COLUMN clause. Listing 12.10 illustrates adding a column to the product table.

Listing 12.10 Adding a Column with ALTER TABLE

 ALTER TABLE product ADD ProdDesc varchar(100) NULL 

SQL Server adds the column, and in this case allows a NULL value for all rows. If NOT NULL is specified, then the column must be an identity column or have a default specified. Note that even if a default is specified, if the column allows nulls, the column will not be populated with the default. Use the WITH VALUES clause to override this and populate the column with the default.

With some restrictions, columns can also be dropped from a table. The syntax for dropping a column is shown in Listing 12.11. Multiple columns can be specified, separated by a comma.

Listing 12.11 Dropping a Column with ALTER TABLE

 ALTER TABLE product DROP COLUMN ProdDesc 

The following columns cannot be dropped:

• An indexed column

• Replicated columns

• Columns used in CHECK , FOREIGN KEY , UNIQUE , or PRIMARY KEY constraints

• Columns associated with a default or bound to a default object

• A column that is bound to a rule

NOTE

Care should be taken when using ALTER TABLE to modify columns with existing data. When adding, dropping, or modifying columns, SQL Server places a schema lock on the table, preventing any other access until the operation completes. Changes to columns in tables with many rows can take a long time to complete and generate a large amount of log activity.

You should be aware that if you want to change a column's name, it is not necessary to drop it and then add a new column. Column names can be changed using sp_rename . As with any database object, consider the effects the rename might have on other objects or queries that reference the column. The syntax for changing a column name is EXEC sp_rename 'northwind.[order details]', 'details', 'COLUMN' .