Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2007-12-19 : 21:57:49
|
| Hi all,I need to change the primary key of a table from a combination of columns to an integer column. I was trying this on SQL 2005. When I did that in enterprise manager, and I did the view change script option, it gave me the script where it copies the actual table into a temp table and then drops all the keys, renames that temp table with the original tablename and creates the new primary key.My question is, is that the best way to do that?What happens if I just drop primary key constraint and create again with new column?I need to write a script that can work for both sql 2000 and sql 2005.Thanks,Ujjaval |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-19 : 22:43:07
|
| Yes, you can drop pkey then create a new one with 'alter table'. |
 |
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2007-12-19 : 23:27:25
|
| What is the difference, when I create a temp table, copy data into temp table, drop the actual table and renames the temp table with the actual table name and between just droping the key and creating it again on the same table? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-19 : 23:28:47
|
| Enterprise Manager does not always pick the most efficient script, which is why you should always verify if there is an easier way to do it. I too use EM to generate my scripts so that I don't have to do all of the typing, but when I notice that it has done the wrong thing, I then write a script from scratch the right way.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-19 : 23:30:13
|
quote:
Originally posted by ujjaval
What is the difference, when I create a temp table, copy data into temp table, drop the actual table and renames the temp table with the actual table name and between just droping the key and creating it again on the same table?
Enterprise Manager picked a very inefficient way of doing what you want. Imagine if you had billions of rows in your data. It would take a very long time to do it that way. The most efficient and best way to do what you want is to just drop the PK constraint then add it back. Both are done via ALTER TABLE.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2007-12-20 : 18:28:21
|
| Thanks Tara,That gives me more confidence about my thinking. :-)Cheers,Ujjaval |
|
|
|
|
|
|
|
|
changing the primary key of table