Alter Table in Microsoft SQL Server Express

How to Alter a Table in Microsoft SQL Server Express

SQL Server Express is the free edition of Microsoft’s excellent database server. Understandably, Microsoft chose to cripple the free version to create some differentiation between product tiers.

There’s one cripple, however, that makes no sense to me. Microsoft chose to disallow the use of ALTER TABLE statements. But why block these statements when the workaround is so simple…

Alter Table, the Long Way

The pseudocode looks like this:

-- begin a transaction
-- copy the current table into a temporary table
-- drop the current table
-- recreate the table with modifications
-- copy data from the temp table into the new table
-- commit the transaction

And here are some concrete examples:

Suppose you have a table of contacts as follows

CREATE TABLE Contact(
    ID INT PRIMARY KEY NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Address VARCHAR(50)
)
INSERT INTO Contact VALUES (1, 'Alice', '1 Aspen Way')
INSERT INTO Contact VALUES (2, 'Bob', '2 Baker Rd')
INSERT INTO Contact VALUES (3, 'Chris', '3 Church St')

Changing a Column

Let’s say you want to change Address from VARCHAR(50) to VARCHAR(200).

BEGIN TRANSACTION

-- Backup existing data
SELECT * INTO #TempContact FROM Contact

-- Alter table
DROP TABLE Contact
CREATE TABLE Contact(
    ID INT PRIMARY KEY NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Address VARCHAR(200)
)

-- Restore data
INSERT INTO Contact
    SELECT * FROM #TempContact

DROP TABLE #TempContact

COMMIT TRANSACTION

Adding a Column

Now we want to add a phone number field. We have to be more careful when restoring data because the number of columns is different

BEGIN TRANSACTION

-- Backup existing data
SELECT * INTO #TempContact FROM Contact

-- Alter table
DROP TABLE Contact
CREATE TABLE Contact(
    ID INT PRIMARY KEY NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Address VARCHAR(200),
    PhoneNumber VARCHAR(20)
)

-- Restore data
INSERT INTO Contact (ID, Name, Address, PhoneNumber)
    SELECT ID, Name, Address, NULL
    FROM #TempContact

DROP TABLE #TempContact

COMMIT TRANSACTION

Note that the example just inserts NULL into the new field. You could just as easily specify a default value as a constraint or specify a one-time default value in the INSERT statement.

Dropping a Column

BEGIN TRANSACTION

-- Backup existing data
SELECT * INTO #TempContact FROM Contact

-- Alter table
DROP TABLE Contact
CREATE TABLE Contact(
    ID INT PRIMARY KEY NOT NULL,
    Address VARCHAR(200),
    PhoneNumber VARCHAR(20)
)

-- Restore data
INSERT INTO Contact (ID, Address, PhoneNumber)
    SELECT ID, Address, PhoneNumber
    FROM #TempContact

DROP TABLE #TempContact

COMMIT TRANSACTION

All this extra typing is really only a minor annoyance on an operation that is performed relatively infrequently… but it’s saved me from heartache on a few occasions.

Note… you’ll also need to recreate any indices, triggers, relations, permissions, etc. if applicable. (Building your database from script pays dividends)