6

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are valid for the proper type. The column changes do affect indexes, but not keys.

While discussing with colleagues, we have come to two ways to approach the problem. Both these would be done through T-Sql scripts.

  1. Create a temp table via select into, drop the old table and recreate the table with the proper datatypes. Recreate the indexes.
  2. Alter the current table/data types via ALTER TABLE x ALTER COLUMN Y datetime2 and then rebuild or recreate the indexes.

Because I am confident the data will convert cleanly, I am leaning towards #2. My colleague and a DBA friend prefer #1 but my colleague can't remember why they trained him that way. The DBA friend is on vacation so I didn't ask him why.

Can someone provide insight on which option they think is better and why? Ultimately it is my decision and I am wondering why #1 would be preferred over #2?

  • ALTER TABLE ALTER COLUMN is an all-or-nothing operation - it will either all work or it won't. If you make the changes in a separate table, you can do it piecemeal, and you can test it where if you have to roll back you can just drop the temp table (vs. roll back the change to the primary table, which means more waiting, and often longer than the original action took). – Aaron Bertrand Jul 15 '15 at 16:28
  • 2
    There's also option 3, you could create new column to the table, update that from the existing column, drop the old column and rename the new to old – James Z Jul 15 '15 at 17:02
  • 1
    "but my colleague can't remember why they trained him that way" How can he be of an opinion that he cannot argue for?! – usr Jul 15 '15 at 23:17
9

I recently did this in my organization wherein we wanted to handle a table with billion + rows.

All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo

Test below process on a small table and get your self comfortable before doing it in PROD.

  1. create 2 schemas fake and shadow with authorization dbo.
  2. Create a table with the columns and data types you want in shadow schema e.g. create table shadow.Correct_Table ...
  3. Insert the data and create all the indexes that the original table has in the shadow schema table.
  4. This way you have identical copies of table with data and indexes but they are in different schemas (logically separated).
  5. Once done update stats on the table with shadow schema.
  6. Switch the schemas (This is a metadata operation and is extremely fast)

    --- ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; 
    
    BEGIN TRANSACTION;
    
      ALTER SCHEMA fake TRANSFER     dbo.original_table;
      ALTER SCHEMA dbo  TRANSFER  shadow.Correct_Table;
    
    COMMIT TRANSACTION;
    
    ALTER SCHEMA shadow TRANSFER fake.Lookup;
    
  7. Do a final check to see if everything went as planned. You should do a select count(1) from dbo.Correct_table

  8. Once step 7 is confirmed and you are happy, drop the shadow.table, shadow schema and fake schema as clean up.

  • I like that method but it does require you script any object level permissions and assumes you have sufficient space for double the table (and can bloat your data file if it's particularly big). – Kenneth Fisher Jul 15 '15 at 16:42
  • @KennethFisher I don't think you need to script any object level permission, since we will end up with the same schema and table name. Also, if you go with creating separate table, it will require the same space. – Kin Shah Jul 15 '15 at 16:47
  • 1
    I haven't tried it with an ALTER SCHEMA but object level permissions are done by object_id not name so I wouldn't count on it working. The double the space is while both versions exist :) I've had cases where I couldn't do that because the single table was hundreds of GB and we didn't have that much free space on the drive. – Kenneth Fisher Jul 15 '15 at 16:52
  • In addition to permissions (in my case I grant at the schema level, so I don't need to worry about the object-level stuff), you'll also need to make sure stats get updated after every switch, because those follow the object_id too. – Aaron Bertrand Jul 15 '15 at 17:17
4

Here is the way I see it.

Pros for #1

  • Because you are using a separate table your production table stays in use until you are done. No locks on it (beyond those needed to read the data).
  • This also goes with what @AaronBertrand said: you can do it piecemeal, test etc
  • You can change column order at need

Pros for #2

  • It is an all or nothing operation. There is no chance you are going to lose data that got inserted/modified in the original table while you weren't looking.
  • Any permissions that are specifically assigned to the object are kept. If you use #1 you have to make sure you script and apply these.

All of that being said I will typically use #2 for small tables or when I can get an outage (always take a backup before hand though) and #1 if I can't get as large an outage or I have to re-arrange the column order etc. If I'm going to do #1 I'll typically generate the script through the GUI and then review it carefully before running it.

1

Be careful with the drop and recreate option: this can leave sys.depends in an odd state and cause problems for cached plans where the ordering or type of columns is changing.

You will also need to take steps to maintain any object level permissions, as these will be lost in the DROP and not automatically recreated with the subsequent CREATE.

ALTER TABLE is the cleaner option IMO, but make sure you test thoroughly before doing it in production both to make sure all is well afterwards and to make sure you know how long the operations will take (over a table with many rows this could be quite a time).

  • 1
    Cached plans should be invalidated when the underlying table is altered or dropped, so I would expect a recompile in all cases (and if not, it's almost certainly a bug). The recompile itself is a symptom to be aware of, though. – Aaron Bertrand Jul 15 '15 at 17:11
  • I've certainly seen the problem of queries returning incorrect columns after views have been dropped and recreated, until the affected views were forced to be reassessed (via sp_refreshview), though off the top of my head I can't name an example where it has happened with a table drop and rebuild so perhaps the issue is more specific than I have stated above. This may also be something that has improved in recent versions though: most of our clients are still running 2008r2 or in some cases 2005. I shall have to research further when time permits and update my response. – David Spillett Jul 15 '15 at 21:19
  • I think what you recall is if you drop or rename a column, and you have a view that uses SELECT *, the view will still show the old output (well, as well as it can, anyway). I talk about this here: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/… – Aaron Bertrand Jul 15 '15 at 21:31
  • @AaronBertrand: That rings a bell, it was certainly views using SELECT * where we experienced problems if a view (or table?) they depended on changed such that column ordering or types differed. IIRC it wasn't a problem if ALTER VIEW was used instead of DROP followed by CREATE. – David Spillett Jul 16 '15 at 8:44
1

My colleague ended up finding an article about what he was referring to: http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html. After reading this and realizing our year end reporting was coming up, we decided to not make the alterations to the column types and will revisit this in the next couple months. I think after reading the article, I may just go with the Drop/Create method.

Thanks to everyone for their feedback on this. Many interesting approaches to consider when we decide to move forward.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.