Preserve SQL Indexes While Altering Column Datatype

Question

I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it. The indexes are performance related and would need to be retained only using the new data type. Is it possible to write a statement that will allow me to retain the relevant information while still altering the column datatype? If so, how can this be done?

Answer 1

You can not change the datatype from smalldatetime to datetime with the indexes, unique constraints, foreign key constraints or check constraints in place. You will have to drop them all prior to changing the type. Then:

alter table T alter column TestDate datetime not null

Then recreate the constraints and indexes that still apply.

---

Some different approaches for generating the drop and creates:

1) If you have given explicit names to all indexes and constraints then your installer can run a static script in each environment (dev, test, user acceptance testing, performance testing, etc, production.)

To generate this explicit script you can: a) Use SSMS (or with SQL Server 2000, enterprise manager) to script the create and drop statements. b) Work from you source code repository to discover the names and definitions of the dependent objects and put together the appropriate static script. c) Attempt to run the alter statement. See what it fails on. Look up the definitions and hand write the drop and create. (Personally, this would be great for writing the drop, not so good at the create.)

2) If you have not given explicit names to all indexes and constraints, then your installer will have to query the data dictionary for the appropriate names and use dynamic SQL to run the drops, in the correct order, prior to the alter column statement and then the creates, in the correct order, after the alter column.

This will be simpler if you know that there are no constraints, and just indexes.

There may be tools or libraries that already know how to do this.

Also, if this is a packaged application, you may not be assured that the local DBAs have not added indexes.

NOTE: If there is a unique constraint, it will have built an index, which you will not be able to drop with DROP INDEX.