Adding Columns To A Table

Use it to add a new field (with the default properties) to a table, and it will generate the following script.

ALTER TABLE dbo.MyTable ADD
MyNewField char(10) NULL

Actually, the generated script is a little longer than that, but that is the important part.

In this example, the field I added allows NULL values. If instead, I add a field which does not allow NULL values (without specifying a default value), the generated script is a little longer:

CREATE TABLE dbo.Tmp_MyTable
(
     Name varchar(50) NULL,
     ID int NOT NULL IDENTITY (1, 1),
     ActualNumber int NULL,
     Number varchar(50) NULL,
     MyNewField char(10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_MyTable ON
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
     EXEC('INSERT INTO dbo.Tmp_MyTable(Name, ID, ActualNumber, Number)
          SELECT Name, ID, ActualNumber, Number FROM dbo.MyTable TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_MyTable OFF
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename 'dbo.Tmp_MyTable', 'MyTable'
GO
GRANT SELECT ON dbo.MyTable TO public AS dbo
GRANT INSERT ON dbo.MyTable TO public AS dbo
GRANT UPDATE ON dbo.MyTable TO public AS dbo

What is happening here is
1. A new table called Tmp_MyTable is created with the new structure.
2. All of the records in MyTable are inserted into the new table.
3. MyTable is dropped.
4. Tmp_MyTable is renamed to MyTable.
5. The same permissions are created on MyTable as were there previously.

This process overcomes the limitations of ALTER TABLE, but has one significant drawback.

What if MyTable contains millions of records?

Step 2 of the process described above is likely to take a very long time.

And that process is initiated whenever I press the "Save" button in Enterprise Manger, without any warning as to the length of time the process is likely to take.

There is another problem. I have purposely chosen an example which won't work.

The following part of the script will fail:

INSERT INTO dbo.Tmp_MyTable(Name, ID, ActualNumber, Number)
SELECT Name, ID, ActualNumber, Number FROM dbo.MyTable TABLOCKX

This will fail, because Tmp_MyTable has the new field "MyNewField" and the INSERT statement does not.

If the new field is to have a default value, then this process of transferring all of the records into a new table can be avoided, and a single ALTER TABLE command can be used instead:

ALTER TABLE dbo.MyTable ADD
MyNewField char(10) NOT NULL CONSTRAINT DF_MyTable_MyNewField
DEFAULT
('MyDefault')

A great idea, but that's not the script that gets automatically generated by Enterprise Manager.

In this situation, Enterprise Manager will use the 5 step process described above.

Using ALTER TABLE won't always be significantly faster than the 5 step process, because each record in the table needs to be updated with the default value. The 5 step process will be significantly slower, if there are several fields already in the table which will need to be copied.

The exact limitations of ALTER TABLE depend on the RDBMS, but generally speaking it can be used to add a column when
(i) The new column will be the last column.
(ii) The new column either allows NULL values, or has a default value.

In relation to (i), it is worth mentioning that the order in which the fields of a table are stored is not important.

When I am creating a new table, I like the identity field to be the first field in the table. If I am adding an identity field to a pre-existing table, I forego this preference in favour of adding the identity field as the last column.

On a large table, it may take a lot longer to add the column at the beginning than at the end, because adding a column to the end only requires an ALTER TABLE, whereas adding a column to the beginning requires the longer process described above.

Adelle.