Modifying a Table Using T-SQL

3 4

In this section, you'll learn how to use T-SQL commands to alter, add, drop, and rename columns in an existing table. The T-SQL command used to perform all table modifications is ALTER TABLE.

Altering Columns

Once you have created a table, you can alter a column's data type, precision (for numeric types), and nullability, and you can add the ROWGUIDCOL property to a column or drop it from a column, all by using the ALTER TABLE command. You can make other column modifications, such as adding a default value, by using other T-SQL commands; these commands will be described in Chapter 16.

Not all columns can be altered. In general, you cannot alter the following types of columns:

• A column that is part of a primary key or foreign key constraint
• A column that is used in replication (Replication is explained in Chapter 26.)
• A column that has a text, an ntext, an image, or a timestamp data type
• A computed column
• A ROWGUIDCOL column (You can, however, add or drop the ROWGUIDCOL property for a column.)
• A column used in an index
• A column used in a check or unique constraint (Constraints are explained in Chapter 16.)
• A column used in statistics generation by explicitly executing the CREATE STATISTICS statement (SQL Server_generated statistics are dropped by using ALTER TABLE.)
• A column associated with a default value

All other types of columns can be altered by using the ALTER TABLE statement. In some of the preceding cases, you can remove the restriction on altering the column. For example, you could remove a foreign key or other constraint or delete an index on a column, and if no other restrictions apply, you could then alter that column.

Altering the Data Type

To alter the data type of a column, the original data type must be implicitly convertible to the new data type. A table of allowable conversions can be found under the topic "CAST and CONVERT (T-SQL)" in Books Online. To access this topic, look up "CAST" in the Books Online index, and then select "CAST and CONVERT (TSQL)" in the Topics Found dialog box.

The command for altering the data type of a column has the following syntax:

 ALTER TABLE <table_name> ALTER COLUMN <column_name> <new_data_type> 

You will use less space per row of data if you change the data type for the sale_date column of our Bicycle_Sales table from the datetime type to the smalldatetime type. This is because datetime takes up 8 bytes, whereas smalldatetime takes only 4 bytes. To make this change, use the following command:

 ALTER TABLE Bicycle_Sales ALTER COLUMN sale_date smalldatetime NOT NULL GO 

Any existing table data will be implicitly converted to the new data type, smalldatetime. The nullability on this column, NOT NULL, has not been changed.

To change the description column from the data type char(30) to varchar(20) (which has a shorter length), use the following command:

 ALTER TABLE Bicycle_Sales ALTER COLUMN description varchar(20) NULL GO 

Note that char(30), the original data type, is implicitly convertible to varchar(20), the new data type, but that varchar(20) is shorter. Therefore, for all existing rows, values in the description column that are longer than 20 characters will be truncated without warning and will be converted to varchar(20).

NOTE

---

If you alter a column to a new data type that is shorter than the original data type, the existing rows in the table whose values in that column exceed the length of the new data type will have those values automatically truncated.

Altering Nullability

To alter nullability, you can change a NOT NULL column to NULL, except for columns in primary key constraints. (By definition, such columns can never allow null values.) You can change a NULL column to NOT NULL only if no null values exist in the column. If the column contains null values, you could execute an UPDATE statement to change all null values to some value and then make the change from NULL to NOT NULL for that column. If nullability is not specified for an altered column, the column allows null values by default. Let's look at some examples.

To change the nullability of the quantity column to allow null values, execute the following statement:

 ALTER TABLE Bicycle_Sales ALTER COLUMN quantity tinyint NULL GO 

The data type of the column, tinyint, remains the same; only the nullability of the column was changed. Now the quantity column will allow null values to be inserted. If no value is entered, NULL will automatically be inserted. This change will not affect the quantity column values in existing rows, but it will allow NULLs to be inserted for new rows added to the table.

Now let's change the description column to NOT NULL. We'll assume that some null values already exist in the column. Therefore, we must first set all null values to some value—in this case, None, which is compatible with the column's data type. To test for a value of NULL, it is safest not to use the equality operator (=), but rather to use the TSQL keywords IS NULL or IS NOT NULL. This is because NULL is an unknown value, and an equality operation might not be able to match null values, depending on whether the ANSI nulls database option is set to ON or OFF. If this option is set to OFF, the equality operator will return TRUE for expression = NULL if expression holds a null value. The operator will return FALSE if expression does not hold a null value. If this option is set to ON, expression = NULL will return UNKNOWN for all comparisons, and no results will be returned. SQL Server will not return the values that are NULL, as you might expect when the option is set to ON. IS NULL and IS NOT NULL will behave the same no matter what you have the ANSI_NULLS option set to. To change null values in the description column to the value "None", use the UPDATE SET statement, as shown here:

 UPDATE Bicycle_Sales SET description = "None" WHERE description IS NULL GO 

Next change the nullability of the description column to NOT NULL:

 ALTER TABLE Bicycle_Sales ALTER COLUMN description char(30) NOT NULL GO 

Again, we did not change the original char(30) data type of this column, only the nullability. You can change both data type and nullability in a single ALTER TABLE command, as shown here:

 ALTER TABLE Bicycle_Sales ALTER COLUMN description varchar(20) NOT NULL GO 

This statement changes the data type and the nullability of the description column.

Adding or Dropping the ROWGUIDCOL Property

To add the ROWGUIDCOL property to a column or drop it from a column, use the following syntax:

 ALTER TABLE <table_name> ALTER COLUMN <column_name> ADD | DROP ROWGUIDCOL 

You can add the ROWGUIDCOL property only to a column of the type uniqueidentifier. Assuming that we have a uniqueidentifier column named unique_id in our Bicycle_Sales table, you would add the ROWGUIDCOL property using the following command:

 ALTER TABLE Bicycle_Sales ALTER COLUMN unique_id ADD ROWGUIDCOL GO 

And you would drop the property using this command:

 ALTER TABLE Bicycle_Sales ALTER COLUMN unique_id DROP ROWGUIDCOL GO 

Adding Columns

You can also add columns to a table using the ALTER TABLE command. A column is defined in much the same way, whether you are creating or altering a table. You must assign the column a name and a data type, and you can optionally assign other attributes, properties, and constraints.

When you add a column that is NOT NULL, you must also declare a default value so that the existing rows will be given the default value in the new column. You specify this default value with the keyword DEFAULT. To add a column, use the following syntax:

 ALTER TABLE <table_name> ADD <column_name> <data_type> <nullability > DEFAULT default_value 

For example, to add a column named salesperson_id to the Bicycle_Sales table, use the following command. (The new column does not allow null values and has a default value of 0.)

 ALTER TABLE Bicycle_Sales ADD salesperson_id tinyint NOT NULL DEFAULT 0 GO 

Because the column is declared as NOT NULL, all existing rows in the table will be assigned the default value of 0 in the new column.

If we instead add the salesperson_id column to the table as NULL, as shown here, a default value is optional:

 ALTER TABLE Bicycle_Sales ADD salesperson_id tinyint NULL DEFAULT 0 --Optional default value GO 

Even if we specify a default value, the existing rows will be assigned NULL for the salesperson_id column—the default value will apply only to newly inserted rows.

To force the existing rows to be assigned a default of 0 instead of NULL, use the WITH VALUES option of DEFAULT, as follows:

 ALTER TABLE Bicycle_Sales ADD salesperson_id tinyint NULL DEFAULT 0 WITH VALUES GO 

WITH VALUES specifies that all existing rows will be assigned the new default value instead of NULL for the new column.

Dropping Columns

You can also use the ALTER TABLE command to drop columns from a table. All data for a dropped column will be deleted from the table. When you are using TSQL to drop columns, you cannot drop the following types of columns:

• A column used in a primary key, foreign key, unique, or check constraint
• A column used for replication
• A column used in an index (unless the index is dropped first)
• A column bound to a rule
• A column associated with a default value

NOTE

---

These restrictions still apply, but are handled differently, when you are using Enterprise Manager to drop a column. See the section "Modifying a Table Using Enterprise Manager" later in this chapter for details.

To drop a column from a table, use the following syntax:

 ALTER TABLE <table_name> DROP COLUMN <column_name> 

The following command drops the description column from the Bicycle_Sales table:

 ALTER TABLE Bicycle_Sales DROP COLUMN description GO 

The description column and its values are deleted from all rows in the table.

CAUTION

---

Be careful when you are dropping columns. You cannot retrieve a dropped column's data without restoring the database from a backup. If transactions had been issued subsequent to your backup, you would also need to apply the transaction log to recover these transactions. You could also recreate the column and supply new values for it.

Renaming Columns

To rename a column using T-SQL commands, you must run the sp_rename system stored procedure using the following syntax:

 sp_rename 'table.original_column_name', 'new_column_name', 'COLUMN' 

For example, to change the column name description to Bicycle_desc, you would use the following statement:

 sp_rename 'Bicycle_Sales.description', 'bicycle_desc', 'COLUMN' GO 

The original column name must be specified using the format table.column, but the table name should not be included with the new column name.