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 |
|
fparker
Starting Member
27 Posts |
 Posted - 2011-08-11 : 20:17:12
|
| SQL2000. I need to do the following.drop a primary keymake a column not nullcreate a composite primary keycan 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
5072 Posts |
Posted - 2011-08-12 : 00:17:39
|
| 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. |
 |
|
|
fparker
Starting Member
27 Posts |
Posted - 2011-08-12 : 01:29:04
|
| 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 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-12 : 04:31:14
|
| Your table will be completely unavailable while you do all of that.--Gail ShawSQL Server MVP |
|
|
|
fparker
Starting Member
27 Posts |
Posted - 2011-08-12 : 16:41:49
|
| thanks gila. i figured that would be part of the operation. thanks again for clearing that up.--f |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-08-24 : 13:48:10
|
| 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.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-24 : 13:55:50
|
| 5. Add all constraints (primary, foreign, check) to the new table6. Add all indexes to the new table7. Add any triggers to the new tableYou 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 ShawSQL Server MVP |
|
|
|
|
|
|
Can I change a primary key while hot?