SQL 2005Modifying SQL Server Tables
The ninth part of the SQL Server Programming Fundamentals tutorial describes how database tables can be changed using T-SQL. In this article we will add, remove and modify columns, whilst considering the impact these operations can have on existing data.
Modifying Tables Using T-SQL
In previous articles in the SQL Server Programming Fundamentals tutorial we have seen how to create tables, and their constituent columns, and make modifications to their structure using the SQL Server Management Studio (SSMS) graphical user interface. During development, and for support purposes, SSMS is an ideal tool for such operations.
In this article we will consider the modification of existing tables using Transact-SQL statements, such as may be executed from a software application. This article describes simple changes, such as renaming tables and columns and changing column data types. The commands and techniques investigated here will also form the basis for future articles that explain how to make more detailed changes to tables, including the addition of primary keys, default values and constraints.
This article uses the JoBS database that has been created in earlier tutorial articles. If you have not followed the tutorial so far, download the script using the link at the top of this page and use it to create the database and sample data. You may also want to use this script to recreate the database if you have made modifications to your own installation. In this article we will make further changes to the JoBS schema, including some that will be incorporated into a new script to be used in the next article in the series.
Adding Columns to a Table
Many of the schema modifications that can be made to tables use the ALTER TABLE command. This command allows you to specify the name of the table to be changed and the action that you wish to perform. The first type of change that will be demonstrated is adding a new column to a table. In this case, the ALTER TABLE statement includes an ADD clause, followed by the new column specification.
NB: Column specifications are described in the earlier article, "Creating SQL Server Tables Part 2".
ALTER TABLE table-name ADD column-specification
A new column can be added to an existing table that is empty or that contains information. When a table already holds data, the information will be updated so that the new column has a null value in each row. This means that the column specification must not specify that the column is not nullable. You can change the nullable status later, once the rows have all been updated to ensure that there are no nulls in the new column.
NB: Later in the tutorial we will create default values for columns. If a default value is specified for a new column, this value is added to every existing row and the NOT NULL clause for the new column is then valid.
Let's try the new statement by adding a new column to the StandardJobs table. This column will hold a description of the standard work that may be very long, so a VarChar(Max) column has been chosen. Execute the following statement, then review the table and its contents to see the changes.
ALTER TABLE StandardJobs ADD JobDescription VARCHAR(MAX)
Another missing piece of information in the JoBS database is the time that was required to complete a job. We can add a column to the Jobs table to hold this.
ALTER TABLE Jobs ADD Duration DECIMAL(4,2)
Modifying Existing Columns
Columns can be modified using a similar syntax to that used when adding new ones. Using the ALTER COLUMN clause allows the size or data type for a column to be modified and the status of the column to be changed between nullable and required. To alter the size of a column, the new column specification is provided, including the name and nullable status. For example, the following statement reduces the maximum size of the EngineerName column in the Engineers table from fifty to thirty characters.
ALTER TABLE Engineers ALTER COLUMN EngineerName VARCHAR(30) NOT NULL
If you attempt to make a column too small for the data that is already held in the table, the change is refused and an error is displayed. This prevents information from being truncated or accuracy being lost. For example, the following statement attempts to shorten the EngineerName column to a size that is too small:
ALTER TABLE Engineers ALTER COLUMN EngineerName VARCHAR(5) NOT NULL
The same syntax can be used to change the data type for a column. In the downloaded script, the Engineers table contains a column that holds the engineer's hourly charging rate. This column is defined as a Money type. To change this to a numeric column, the following statement can be used:
ALTER TABLE Engineers ALTER COLUMN HourlyRate NUMERIC(5,2) NOT NULL
When changing a column's data type, the new type must be compatible with the data within the table so that information is not lost. The next sample statement breaks this rule by trying to change a numeric value to a globally unique identifier.
ALTER TABLE Engineers ALTER COLUMN EngineerName UNIQUEIDENTIFIER NOT NULL
Finally, the nullable option for a column may be changed. To make a column nullable, the column definition should not include the NOT NULL clause. Optionally, you may include the NULL keyword, as in the next example, to improve the readability of the statement and ensure that the purpose is obvious.
ALTER TABLE Engineers ALTER COLUMN HourlyRate MONEY NULL
To change a nullable column so that it requires a value, add the NOT NULL clause. NB: This command will fail if the table contains rows where the column value is null.
ALTER TABLE Engineers ALTER COLUMN HourlyRate MONEY NOT NULL
Limitations
There are some limitations on the changes that may be made to columns. These are generally related to more advanced features than have been used so far in the tutorial, so do not affect the JoBS database in its current form. However, some of these limitations are worthy of consideration here.
- A column may not be modified if it uses the Timestamp data type. Text and NText columns may only be changed to VarChar(Max), NVarChar(Max) or XML types. Image columns may only be altered to VarBinary(Max).
- It is possible to define a UniqueIdentifier column as the ROWGUIDCOL for a table. This marks the column as being a special unique value for each row within the table. Such a column may not be modified.
- If a column is used within an index, the data type may not be changed. The size of such a column may be increased if the data type is VarChar, NVarChar or VarBinary.
- A column that is included within a primary key, foreign key relationship or in a constraint that enforces uniqueness or other conditions, may not be altered.
- If a column's data type is changed from NChar or NVarChar to Char or VarChar, any extended characters in its data may be converted to standard English characters.
- If a column's value is calculated or is used in the calculation of another column, the column type may not be changed.
Deleting Columns
The last use of the ALTER TABLE command that we will consider in this article is for the deletion of columns. By using the DROP COLUMN clause, the column to be removed can be specified. This permanently deletes the column and all of its contents. For example, the following command drops the Duration column from the Jobs table.
ALTER TABLE Jobs DROP COLUMN Duration
Renaming Columns
The names of columns and other database objects can be modified using the standard stored procedure, "sp_rename". To rename a column, three parameters are required. The first parameter provides the name of the column to be renamed, prefixed with the name of the containing table and a full stop, or period. The second parameter is the new name for the column and the final parameter tells the stored procedure that it is a column that is being renamed.
To execute a stored procedure you must provide the name of the procedure and a comma-separated list of parameters. If you wish to run more than one procedure at a time you must also prefix the command with EXEC. This is optional for single statements.
The Parts table in the JoBS database contains a column named Cost that holds a unit cost for the associated part. We can change this name to something more appropriate using the statement below:
EXEC sp_rename 'Parts.Cost', 'UnitCost', 'COLUMN'
When an item is renamed, it is possible that other procedures or software that refer to it by name will be broken by the change. SQL Server warns of this possibility by showing the following message:
Caution: Changing any part of an object name could break scripts and stored procedures.
Renaming Tables
The "sp_rename" procedure can be used to change the name of an entire table. In this case, the existing name and new name must be provided, as in the following example. As with renaming columns, changing the name of a table can break functionality of scripts, procedures and software that refer to it by name.
EXEC sp_rename 'Complaints', 'CustomerComplaints'
Deleting Tables
Sometimes you will need to completely remove a table from a database schema. This process, achieved using the DROP TABLE statement, is permanent and results in the deletion of the table and all of the data that it contains. The command requires only the name of the table to be removed. For example, executing the following command deletes the CustomerComplaints table.
DROP TABLE CustomerComplaints
|