Tuesday, June 5, 2012

Alter Table Column Varchar to date

Altering a table column from varchar to datetime is pretty straight-forward in the SQL Server Management Studio (SSMS), until you look at the tsql generated.  For many operations SSMS will generate a tsql script that will:
  • create a temporary table
  • drop all the foreign keys
  • copy the data to the temporary table
  • create the new table with the correct data type
  • copy the data to the new table
  • drop the temporary table
  • add the foreign keys back
That is a lot of operations and on a really large table of millions of rows may take a very long time to complete.
SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.
SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.
In this particular case we were modifying a varchar(10) to a datetime.  All the data was in a valid format.  The easiest method is to alter the column, another method to complete this is to:
  • rename the existing column (tmp_varchar etc)
  • add a new column with the correct name
  • update the new column (in batches if necessary)
  • drop the original column (now with a tmp_name)
This is very quick, much safer operation and is much "nicer" to the database log file.  Example of tsql is below:
CREATE TABLE dbo.testing
(test_id bigint NOT NULL IDENTITY (1, 1) primary key,
some_dt varchar(10)
)
GO

insert into testing (some_dt) values ('2009-01-01')
insert into testing (some_dt) values ('2009-01-02')
insert into testing (some_dt) values ('2009-01-03')
insert into testing (some_dt) values ('2009-01-04')
go

EXECUTE sp_rename 'dbo.testing.some_dt', 
    'Tmp_some_dt', 'COLUMN' 
GO

Alter Table testing
Add some_dt DateTime Default('1900-01-01') NOT NULL
GO

Update testing
    Set some_dt = Convert(DateTime,Tmp_some_dt)
GO

Alter Table testing
    DROP Column Tmp_some_dt
GO

select * from testing
go

--drop table testing

No comments:

Post a Comment