Preserve SQL Indexes While Altering Column Datatype

Preserve SQL Indexes While Altering Column Datatype

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.

How to alter column of a table with indexes?

Since is VARCHAR and you're increasing the size, then simply ALTER TABLE ... ALTER COLUMN ... should be enough.

The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

Otherwise you would drop the index(es), alter the column, then add back the index(es).

Be aware though that SQL Server maximum index key size is 900 (or 1700 for newer editions), so even though the ALTER will succeed, a future INSERT of
data over the 900 length limit will fail with error:

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length ... bytes for the index '...' exceeds the maximum length of 900 bytes.

SQL Server: how to write an alter index statement to add a column to the unique index?

You cannot alter an index - all you can do is

  1. drop the old index (DROP INDEX (indexname) ON (tablename))

  2. re-create the new index with the additional column in it:

       CREATE UNIQUE NONCLUSTERED INDEX (indexname)
    ON dbo.YourTableName(columns to include)

The ALTER INDEX statement in SQL Server (see docs) is available to alter certain properties (storage properties etc.) of an existing index, but it doesn't allow changes to the columns that make up the index.

How to change datatype whithout dropping dependencies

We found a way to do this. Although it probably isn't the best solution, it worked for us so if anyone has the same problem, try the following:

In SQL Server Management Studio go to Tools -> Options -> Designer and uncheck the box of "Prevent saving changes that require table re-creation"

Next rightclick the table you want to modify the column datatypes and click on "Design".

In the designer, edit the column datatype to the one you need.

Finally, right-click and choose "Generate Change Script".


What it does is the following:

  • Drop the constraints on the table
  • Create a temp table with the new datatype of the column
  • Readd constraints to the temp table
  • Set the IDENTITY_INSERT on the temp table to ON
  • Copy all data from the old table to the new temp table
  • Set the IDENTITY_INSERT on the temp table to OFF
  • Drop the old table
  • Rename the temp table to the name of the old table
  • Readd the primary key constraint
  • Recreate the indexes
  • Readd the foreign key constraints

Additionally you have to refresh all depending views.
You can generate the statements with this script:

SELECT DISTINCT 'EXEC sp_refreshview ''' + s.name + '.' + so.name + '''' AS 'dbo.TABLENAME'
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
INNER JOIN sys.schemas AS s
ON so.schema_id = s.schema_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.TABLENAME');
  • Check the box of "Prevent saving changes that require table re-creation" in Tools -> Options -> Designer

Please be careful about this! See if it really does what you are looking for. Keep in mind that this drops the old table. Test this in a development environment!

SQL Server performance for alter table alter column change data type

Coincidentally, I had to do something very similar about 3 hours ago. The table was 35m rows, it is fairly wide, and it was taking forever to just do this:

alter table myTable add myNewColumn int not null default 0;

Here's what what I ended up going with:

alter table myTable add myNewColumn int null;

while 1=1
begin
update top (100000) myTable
set
myNewColumn = 0
where
myNewColumn is null;

if @@ROWCOUNT = 0 break;
end

alter table myTable alter column myNewColumn int not null;
alter table myTable add constraint tw_def_myNewColumn default (0) for myNewColumn;

This time around, the alter table statements were near-instant. It took about 7-8 minutes (on a slow server) to do the update batches. I'm speculating that SQL Server was generating undo in my original query to restore the values, but I didn't expect that starting off.

Anyway, in your case, maybe something similar would help. You could try adding a new bigint column, update the new column in batches, then set the constraints on it.

MySQL What Happens to an Index if I Alter the Column it's on?

Basically, it rebuilds the index when you change a column's data type.

Actually, it rebuilds all the indexes in that table.

Some types of ALTER TABLE statements in MySQL result in a table restructure:

  1. Lock the table.
  2. Create a new empty table with the new definition for columns.
  3. Copy all the data, row by row, from the old table to the new table. This naturally fills indexes in the new table, just as you would if you were INSERTing new data.
  4. Swap the names on the tables.
  5. Drop the original table.
  6. Unlock the table.

This is especially true when you change the primary key column in an InnoDB table, since every InnoDB table is stored as a clustered index for its primary key.

So if you change the data type of a column, that applies to the new table, and it fills the index as it copies rows from the original table to the new table.



Related Topics



Leave a reply



Submit