SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Can I change a primary key while hot?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fparker
Starting Member

27 Posts
Posted - 08/11/2011 :  20:17:12  Show Profile  Reply with Quote
SQL2000.

I need to do the following.

drop a primary key

make a column not null

create a composite primary key

can I do this during the day when the db is being used or should I wait until the evening when everyone is off. The table is very small, under 1 million rows.


--
f

russell
Pyro-ma-ni-yak

USA
4977 Posts
Posted - 08/12/2011 :  00:17:39  Show Profile  Visit russell's Homepage  Reply with Quote
You'll have to decide based on your load, but I'd do it after hours.

In fact, we don't do anything to production outside of dire emergency work, during primary business hours.
Go to Top of Page

fparker
Starting Member

27 Posts
Posted - 08/12/2011 :  01:29:04  Show Profile  Reply with Quote
thanks. I always get nervous when I do stuff during the day. I am not a dba and since we don't have anyone in that role it is always a real crapshoot.


--
f
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts
Posted - 08/12/2011 :  04:31:14  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Your table will be completely unavailable while you do all of that.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

fparker
Starting Member

27 Posts
Posted - 08/12/2011 :  16:41:49  Show Profile  Reply with Quote
thanks gila. i figured that would be part of the operation. thanks again for clearing that up.


--
f
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts
Posted - 08/24/2011 :  13:48:10  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
You can have the shortest downtime if you use sp_rename.

1. Create new table using temporary name.
2. Insert data into new table selecting from existing one.
3. drop existing table.
4. Rename a new table.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts
Posted - 08/24/2011 :  13:55:50  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
5. Add all constraints (primary, foreign, check) to the new table
6. Add all indexes to the new table
7. Add any triggers to the new table

You can do step 6 before dropping and renaming, but if you do steps 5 and 7 before, you'll have to give all the constraints/triggers different names as they must be unique in the database

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000