Altering Tables

One of the reasons for Microsoft Access' tremendous success is the user interface. You can create tables, determine data types by example, and make changes to the table design even after it has been implemented. Actually, in many cases Microsoft Access doesn't really modify existing tables. Instead, it creates new tables according to your specifications, then moves data into it.

SQL Server makes changes to tables in the same way, but you must guide it through the steps. At first, you may regard this as a loss of functionality, but there is a reason for the difference in approach. Microsoft Access tables are typically smaller than SQL Server tables; data in a Microsoft Access table is usually measured in the tens of thousands or hundreds of thousands of records. In contrast, SQL Server tables often have millions of records. Many SQL Server databases are over 50 GB and some exceed 100 GB. Think twice before changing the structure of a table with 5,000,000 records. SQL Server will rewrite the table schema, copy the records to the new table, and rebuild all of the indexes for the table.

SQL Browser and Visual Studio DataTools

The SQL Server Browser Add-In included in the Upsizing Tools permits you to make some changes to your SQL Server data structure from within Microsoft Access, using a format similar to Microsoft Access design view. This tool has tabs for Tables, Views, Defaults, Rules, and Procs. This tool allows you to make changes equivalent to those that can be made with the ALTER TABLE syntax. More complex changes such as changing the data type of a field cannot be made here.

Microsoft has now released a new product Add-In called Visual Database Tools. This ships with the Enterprise Editions of Visual Studio™ 97 development software, Visual Basic 5, and Visual C++® 5. This Add-In is a more sophisticated interface than the SQL Server Browser. It looks much like Microsoft Access and writes the SQL statements necessary for more complicated changes such as changes to data types.

Adding Fields

Adding fields is the easiest change to make. Using SQL Enterprise Manager, select your server in the Server Manager window. Click on the plus sign (+) to drill down through databases and select your database. On the Manage menu, select Tables. Select the desired table from the list. In this window you can view the design of the table, add new fields to the table, and change the names of the columns. You can't delete columns or change a column's data type.

You can also add a column using Transact-SQL language from SQL Enterprise Manager or from Microsoft Access with a SQL Pass-Through Query.

Type in the Transact-SQL statement in the following format:

ALTER TABLE SomeTable 
ADD 
Field1 INT NULL,
Field2 varchar(10) NULL

Because SQL Server rewrites tables to add the field(s), expect large table changes to take some time. SQL Server moves all of the data to new pages and rebuilds the indexes.

Changing the Data Type of a Field

You can change the data type of a field in two ways:

Using SELECT INTO

In the Server Manager window double-click on the database name to open the Manage Database window. Click Options and place a check in the Select into/Bulk Copy box. Now you can make a copy of a table, for example the jobs table in the pubs database.

SELECT * INTO newjobs FROM jobs

You can modify this query slightly with the CONVERT function. Here is the query to make a copy of the jobs table, changing the max_lvl field from a data type of tinyint to a float.

SELECT job_id, job_desc, min_lvl, 
CONVERT(float,max_lvl) as max_lvl
 INTO changedjobs FROM JOBS

Open each table in the Manage Tables window to see that although the table definition has changed, no defaults or constraints have been applied to the new table. Read the descriptions in the old table and recreate them in the new table. More dangerously, the converted column will have been created with the default NULL behavior (NULL on SQL Server 6.5, NOT NULL on SQL Server 6.0). If this is the desired result, verify the data in the new version, drop the old version of the table, and rename the new version with the original name. If you have any foreign key constraints, drop them before dropping the old table. Then recreate them with an ALTER TABLE statement.

Using Scripts

Changing a table by creating a new version from a SQL Script takes longer than using SELECT INTO, but it gives you more control over the process. You must make a new version of the table with the changed data type, move the data to the new table, rename (or delete) the old table, and rename the new table with the original table's name. Since you can't delete tables with relationships, you must first drop the relationships and recreate them. Recompile stored procedures that refer to the table to get a new object number for the new table. If the table you are changing is part of a complex structure of tables, you may find that it is quite complex to get all the details reconstructed properly. Document the structure well before you start making changes. Make a backup of databases before modifying them.

The following is the Transact-SQL syntax for changing a data type of a field. This example is intentionally complex to illustrate the level of detailed knowledge required to accurately make such a change. A field named ZipCode is changed from an integer to a five-character field (char(5)).

  1. Drop any foreign key constraints that reference the table. None exist on this table, but if they occur in your tables, use the following syntax:
    ALTER TABLE Orders
DROP CONSTRAINT UPKCLSuppliersConstr
GO
  2. Create a script for the Suppliers table. This creates the original table named Suppliers. This script incorrectly creates the ZipCode field as an integer field.
    set quoted_identifier on
GO

if exists (select * from sysobjects where id = 
object_id('dbo.Suppliers') and sysstat & 0xf = 3)
drop table "dbo"."Suppliers"
GO

CREATE TABLE "dbo"."Suppliers" 
(
   "Name" varchar (50) NOT NULL ,
   "Address" varchar (50) NULL ,
   "City" varchar (20) NULL CONSTRAINT "CityDefault"
DEFAULT ('Seattle'),
   "State" char (2) NULL CONSTRAINT "StateDefault" 
DEFAULT ('WA'),
   "ZipCode" "int" NULL ,
   CONSTRAINT "UniqueConstraint" UNIQUE NONCLUSTERED 
   (
      "Name"
   ),
   CONSTRAINT "StateConstraint" CHECK 
(State = 'CA' or (State = 'OR' or (State = 'WA')))
)
GO
  3. On the Server Manager window, select Generate SQL Scripts from the Object menu. Uncheck all of the Scripting Objects and select the Suppliers table. Presumably you will select all of the Scripting Options. Click Script and provide a name and path for the output. When the scripting is complete, close the Generate SQL Scripts window and open a SQL Query Tool window from the Tools menu. In the query window click on the file folder for the Open SQL Script option and select your script file. This brings up a script like the one above for the Suppliers table. Make the necessary changes to the script. For this example, we are changing the ZipCode field from an Int data type to a Char(5):
    "ZipCode" int NULL ,

is changed to 

"ZipCode" Char (5) NULL ,
  1. Change the script to refer to NewSuppliers, NewUniqueConstraint, NewStateConstraint, NewCityDefault, and NewStateDefault wherever you see Suppliers, UniqueConstraint, StateConstraint, CityDefault, and StateDefault.
    set quoted_identifier on
GO

if exists (select * from sysobjects where id =
object_id('dbo.NewSuppliers') and sysstat & 0xf = 3)
drop table "dbo"."NewSuppliers"
GO

CREATE TABLE "dbo"."NewSuppliers" 
(
   "Name" varchar (50) NOT NULL ,
   "Address" varchar (50) NULL ,
   "City" varchar (20) NULL CONSTRAINT "NewCityDefault"
DEFAULT ('Seattle'),
   "State" char (2) NULL CONSTRAINT "NewStateDefault"
DEFAULT ('WA'),
   "ZipCode" char (5) NULL ,
   CONSTRAINT "NewUniqueConstraint" UNIQUE
NONCLUSTERED 
   (
      "Name"
   ),
   CONSTRAINT "NewStateConstraint" CHECK 
(State = 'CA' or (State = 'OR' or (State = 'WA')))
)
GO
  2. Save and run this script in the Query Tool. If it is successful, the standard message will appear: "This command did not return data, and it did not return any rows." Refresh the Table list to see the new table name. Inspect the NewSuppliers table and you should find it identical to the Suppliers table except for the data type of the ZipCode field and the slightly modified names of the constraints.

  3. Move the data from the Suppliers table to the NewSuppliers table, converting the ZipCodes from integer to character data:
    INSERT INTO NewSuppliers 
SELECT Name, Address, City, State, convert(char(5),ZipCode) 
FROM Suppliers
  4. Check your data to confirm that it is intact and converted, then rename the Suppliers table to OldSuppliers and rename the NewSuppliers table to Suppliers.

  5. When we started we ran an ALTER TABLE statement to drop any foreign key constraints. You should now add back any such constraints to all dependent tables. Though our new table has the same name as the old, it has a new object ID number, so you must recompile any stored procedures that use it.

Deleting Fields

You can't delete fields in a table with SQL Server, but you can use the process just discussed. Instead of specifying a change in data type, omit the field that you wish to drop from the table creation script. Omit the field in the INSERT INTO statement and transfer all of the data except the unwanted field.