updating a primary key
-
This is sort of a weird question I guess, but happened to me the other day. I have a table with just shy of a million records in it, the thing is it has no primary key.
So when I go to add the primary key on and ID (GUID) field SQL gives me a timeout error. I did a work around where I selected the top 1 into another table, truncated the table, added the primary key and then inserted but I am wondering why SQL won't allow one to put the primary key after all the records are there.
Maybe the table was just too big (34 columns and 960,000 or so rows).
Just wondering in anybody has encountered the same situation.
-
I did this onc and had no issues. Could have been a blcking issue if users were in the system at the time. And it took a great deal of time, especially since was the new clustered key and had 4 other indexes which of course were rebuilt during the process. Pretty much cannot have anything else going on at the time you are doing it.
-
Yes this was probably the issue. Users where in the system so SQL was most likely "protecting me from my own actions".
Thanks
-
Did you happen to make the primary key the clustered index of your table? Also - did you do this from Enterprise manager or from Query Analyzer?
Enterprise manager seems to have a rather short built-in timeout on queries, so it might have just needed more than the 20 or so seconds to finish building this thing. This would be all the more true if the new primary Key was the clustered index on the table since this would force a "full rewrite" of the table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply