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
