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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Can I change a primary key while hot?

Author  Topic 

fparker
Starting Member

27 Posts

Posted - 2011-08-11 : 20:17:12
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

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.
Go to Top of Page

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
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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
Go to Top of Page

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.

Mirko

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

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 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
   

- Advertisement -