BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Modifying 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.

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
9 August 2008