locked
Changing the primary key for a large table RRS feed

  • Question

  • I'm trying to change the primary key for a table containing some millions of data, unsuccessfully for a timeout connection problem.
    In order to solve this issue with success, can I execute an alter table statement?
    Many thanks

    Sunday, November 8, 2009 3:55 PM

Answers

  • You can change Primary Key with the following script. It would be usually faster than doing it in Object Explorer.

    Decision point: should the new Primary Key be CLUSTERED? If yes, just insert "CLUSTERED" after "PRIMARY KEY".

    Use exec sp_help yourtablename to find out the name of the current PK.

    -- T-SQL changing Primary Key on a table
    USE tempdb; 
    -- SELECT INTO table create
    SELECT ProductID, 
           ProductName = Name, 
           Color, 
           ListPrice 
    INTO   NewProduct 
    FROM   AdventureWorks2008.Production.Product 
    GO 
    
    ALTER TABLE dbo.NewProduct ADD CONSTRAINT
    	PK_NewProduct PRIMARY KEY  (ProductID)
    GO
    
    ALTER TABLE dbo.NewProduct
    	DROP CONSTRAINT PK_NewProduct
    GO
    
    ALTER TABLE dbo.NewProduct ADD CONSTRAINT
    	PK_NewProduct PRIMARY KEY (ProductName)
    GO
    
    DROP TABLE NewProduct
    GO
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Naomi N Sunday, November 8, 2009 6:41 PM
    • Marked as answer by Zongqing Li Friday, November 13, 2009 8:50 AM
    Sunday, November 8, 2009 4:57 PM

All replies

  • How and from where are you doing this?


    AMB
    Sunday, November 8, 2009 4:31 PM
  • You can change Primary Key with the following script. It would be usually faster than doing it in Object Explorer.

    Decision point: should the new Primary Key be CLUSTERED? If yes, just insert "CLUSTERED" after "PRIMARY KEY".

    Use exec sp_help yourtablename to find out the name of the current PK.

    -- T-SQL changing Primary Key on a table
    USE tempdb; 
    -- SELECT INTO table create
    SELECT ProductID, 
           ProductName = Name, 
           Color, 
           ListPrice 
    INTO   NewProduct 
    FROM   AdventureWorks2008.Production.Product 
    GO 
    
    ALTER TABLE dbo.NewProduct ADD CONSTRAINT
    	PK_NewProduct PRIMARY KEY  (ProductID)
    GO
    
    ALTER TABLE dbo.NewProduct
    	DROP CONSTRAINT PK_NewProduct
    GO
    
    ALTER TABLE dbo.NewProduct ADD CONSTRAINT
    	PK_NewProduct PRIMARY KEY (ProductName)
    GO
    
    DROP TABLE NewProduct
    GO
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Naomi N Sunday, November 8, 2009 6:41 PM
    • Marked as answer by Zongqing Li Friday, November 13, 2009 8:50 AM
    Sunday, November 8, 2009 4:57 PM
  • Inside Management Studio. I access to the server with SQL Server from remote desktop.
    Sunday, November 8, 2009 5:16 PM
  • Inside Management Studio. I access to the server with SQL Server from remote desktop.

    It is better to use t-sql script for speed.

    SSMS Object Explorer is doing it extra safe, therefore usually it takes much longer than a script, especially for large tables.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Sunday, November 8, 2009 5:26 PM
  • I agree with that this change should be done using ALTER TABLE to drop and recreate the primary key constraint.  I want to add that if the primary key is clustered, you should also drop non-clustered indexes beforehand and recreate afterward.  This will avoid rebuinding the non-clustered indexes twice.  You'll also need to do the same with referencing foreign key constraints.

    In any case, the operation will take some time with a large table.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, November 8, 2009 5:35 PM
  • Ok. I will prepare a script for an Agent job. Thanks
    Sunday, November 8, 2009 6:32 PM
  • Right, see http://forum.lessthandot.com/viewtopic.php?f=22&t=8517 (and the http://wiki.lessthandot.com/index.php/SQL_Server_Administration_Best_Practices - work in progress)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, November 8, 2009 7:10 PM
  • Inside Management Studio. I access to the server with SQL Server from remote desktop.

    That explains why is it that you are getting timeout. You can save the script instead saving the changes, and you can take a look at it to see how SSMS implements the change. Mainly, it creates a new table to hold previous data but witht the new schema.

    Using DDL statements directly will save you some time and space.

    Pay attention to the suggestion from Dan, if not you will be forcing SQL Server to re-process nonclustered indexes.


    AMB
    Sunday, November 8, 2009 9:54 PM