0

I am going to perform a table-wide update on a huge table (+200Millon records) so I am going to populate the data via SELECT into a separate table, than drop the original and rename the new one.

In one of the articles someone mentioned that it is better to create the new table with the same name in a temporary schema (e.g. 'clone') and switch it to the used one (e.g. 'dbo'), than to use the original schema with a temporary name and call sp_rename after the data is in place and the old table is dropped.

I was looking into this, but I cannot think of anything why the schema switch is better than the sp_rename. Can anyone find any good reason why is better to use the first or the second approach?

Thanks!

EDIT: I want to update the values in a specific column

EDIT2: Ultimately my question is, if I decide to go down the way of creating a new table to transfer data to which alternative to use:

CREATE TABLE dbo.newTable
...
DROP TABLE dbo.originalTable
EXEC sp_rename N'dbo.newTable', N'dbo.originalTable'

OR 

CREATE TABLE clone.originalTable
...
DROP TABLE dbo.originalTable
ALTER SCHEMA dbo TRANSFER clone.originalTable
Janos Gyorgy
  • 3
  • 1
  • 4
  • 'sp_rename' doesn't drop the data into table.. AFAIK. Why do you wan't to create another table? – Anuj Tripathi Jun 18 '15 at 12:54
  • Copying a table when you need to update is not a sustainable solution. It will work now, but as you get more data you won't be able to do this. We have many tables with 1 billion plus rows, the largest being over 2 billion rows, with many of these tables taking up 1TB of space or more each. If we copied a table every time we had to make an update we'd soon be out of space. If you're having trouble with logs filling, then do the updates in batches. However, this is not a good long term way of doing things. – Patrick Tucci Jun 18 '15 at 12:55
  • @anujtripathi your right, sp_rename is only aware for table renames. If you try to use sp_rename this way it will produce a buggy table name: sp_rename N'schema1.tab1', N'schema2.tab2' it will produce this table name: [schema1].[schema2.tab2] – Ionic Jun 18 '15 at 12:58
  • @Ionic It is not a buggy; this is something how SQL Server sp_rename is designed to work. It rename table only but keep the old table schema. Nevertheless, +1 for you answer and great point – Anuj Tripathi Jun 18 '15 at 13:01
  • @anujtripathi I am following the instructions described in one of the solutions from the link I am referencing in the question. – Janos Gyorgy Jun 18 '15 at 13:05
  • @JanosGyorgy Can you eloborate what exactly are you planning to do with the table? I mean, do you want to rename it, or change any data type or you want to include any column or you want to update table column with specific value? – Anuj Tripathi Jun 18 '15 at 13:08
  • @anujtripathi Edited the question to include that I am trying to update the values in a specific column – Janos Gyorgy Jun 18 '15 at 13:13
  • @PatrickTucci This is something I should seriously consider given the nature of the DB – Janos Gyorgy Jun 18 '15 at 13:34
  • @patrickTucci Statement 1 in your edit is renaming table and statement 2 is about changing schema, they both have different purpose and are not related to updating data or moving data. – Anuj Tripathi Jun 18 '15 at 13:39
  • @JanosGyorgy, I think anujtripathi's comment above was meant for you. – Patrick Tucci Jun 18 '15 at 13:42

3 Answers3

3

By the way, I would suggest that you WON'T populate the table by using SELECT * INTO. This will lock your source table for everyone else during the insertion, which could take quite a time.

Just a suggestion, try this instead:

SELECT TOP 0 INTO [newTable]
FROM [oldTable]

INSERT INTO [newTable]
SELECT * FROM [oldTable]

By the way, you can use sp_rename to rename your table to another name. But it won't change the schema. If you try to change the schema too it will produce a buggy table name.

You can instead try to move the table to another name. Example below:

EXEC sp_rename N'oldTable', N'oldTable_Backup'
EXEC sp_rename N'newTable', N'oldTable'

Hopefully this will help you.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • The part about `SELECT * INTO` locking the source table is a very important catch to pay attention to. – Patrick Tucci Jun 18 '15 at 12:58
  • Why does `select * into` lock the _source_ table? It only ever reads from it, so why should the source table be locked? –  Jun 18 '15 at 12:59
  • Wasn't locking a problem in older versions only? – Janos Gyorgy Jun 18 '15 at 13:01
  • 1
    Just try it on a demo table and try to query the source table. ;-) As you query the source and use the source to generate a ddl statement (into) you leave an exclusive (X) lock on the source table which will prevent all other locks on it even shared (S) locks to read the source. – Ionic Jun 18 '15 at 13:02
  • 1
    @JanosGyorgy the locking was an issue in 2005 and was fixed in 2008 officially. But if you take a look at the `tranlocks` you'll see there is a X lock too. Anyway, don't trust MS. ;-) Not the first time, that an fixed bug gets back into a later release. :-D – Ionic Jun 18 '15 at 13:03
  • @Ionic Thanks for the insight but what I am really trying to find answer to is which alternative to use (EDIT2 above) – Janos Gyorgy Jun 18 '15 at 13:27
1

Based on your edited answer the quickest way to do that is:

  • If you have to include default value to the column

    ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [WITH VALUES]

and then drop the old column from the table.

ALTER TABLE {TABLENAME} DROP COLUMN {OLD COLUMN}

  • If you have to update table column based calculated values

    1. Disable index on the column which you are updating
    2. Create index on the column which are in WHERE clause
    3. Update statistics
    4. Use WITH(NOLOCK) table hint [if you are fine with dirty read]

Update

As per edit 2, your first statement is about changing table name and second statement is about changing schema. They both are different and does not related to moving data or updating value. In this case, changing schema would be the best bet

Anuj Tripathi
  • 2,251
  • 14
  • 18
0

If locking was an issue before it still is regardless of what version SQL Server that you are using. When you drop and rename you are also losing all the rights on the table.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22