4

I have a sql table in sql server 2012 that I need to rename. I know in other database systems a way of executing this is the following:

ALTER TABLE table_name
  RENAME TO new_table_name;

However, it seems SQL Server requires different syntax. From SQL Management Studio I renamed the table in the Design View and right clicked to Generate Change Script, and it produced the following:

BEGIN TRANSACTION
GO
EXECUTE sp_rename N'table_name', N'new_table_name', 'OBJECT' 
GO
ALTER TABLE new_table_name SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Is using sp_rename the best practice for renaming?

Also there is an additional line it generated to set the lock_esclation = table. Is this required?

dotNetE
  • 616
  • 1
  • 6
  • 27
  • 5
    The `ALTER TABLE ... RENAME ...` syntax isn't valid on any version of SQL Server I've ever worked with - it doesn't appear on [MSDN](http://technet.microsoft.com/en-us/library/ms190273%28v=sql.90%29.aspx) either. From a quick google it appears to be postgresql syntax. – Bridge Oct 03 '13 at 14:52
  • 2
    Indeed. Even [MSDN](http://technet.microsoft.com/en-us/library/aa337520.aspx) lists `sp_rename` in its example. (Also, re:LOCK_ENCAPSULATION, http://stackoverflow.com/questions/1703597/lock-escalation-whats-happening-here) – Brad Christie Oct 03 '13 at 14:54

1 Answers1

15

I guess the (slightly sarcastic) answer is the advantage to using sp_rename is that it is actually valid syntax on SQL Server, whereas the former is only valid on PostgreSQL (documentation).

To see the valid options for ALTER TABLE - see MSDN.

Here is an example of using Sp_rename:

EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

(Documentation on sp_rename)

Bridge
  • 29,818
  • 9
  • 60
  • 82