How hard is it to change a column datatype?

One of our clients initially specified one of their columns as an int, and after being deployed for a month realized it needed to support other characters.  I found a couple of ways to do this in SQL Server 2008.

We'll use this sample table:

CREATE TABLE dbo.Orders

      (  OrderId int NOT NULL IDENTITY (1,1),

         OrderNumber int NOT NULL

      ) 

INSERT INTO Orders

      SELECT 234

     

SELECT * FROM Orders

The way I normally do things is by generating a change script in SQL Server Management Studio.  Here is the SQL output by that process.

CREATE TABLE dbo.Tmp_Orders

      (

      OrderId int NOT NULL IDENTITY (1, 1),

      OrderNumber varchar(50) NOT NULL

      )  ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_Orders SET (LOCK_ESCALATION = TABLE)

GO

SET IDENTITY_INSERT dbo.Tmp_Orders ON

GO

IF EXISTS(SELECT * FROM dbo.Orders)

       EXEC('INSERT INTO dbo.Tmp_Orders (OrderId, OrderNumber)

            SELECT OrderId, CONVERT(varchar(50), OrderNumber) FROM dbo.Orders WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_Orders OFF

GO

DROP TABLE dbo.Orders

GO

EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'

GO

I wasn't thrilled about copying the entire table for our whole dataset.  I thought the change script should create a new column, copy the data to it, remove the old column, and rename the new column to the old column's name.  Googling about for a way to do this I found it was even easier in SQL Server:

ALTER table dbo.Orders ALTER Column OrderNumber varchar(50)

It's a good old one liner :)