May 23, 2005 at 3:00 pm
I havent done any hard testing, but mr. sean McCown says that you should NEVER use GUID, but I wonder if he's ever had to do this with a large table, say 16,000,000 rows. I have a really hard time believing that inserting into a table that has guid is slower than iterating through with a cursor. Anyone have any hard numbers?
May 24, 2005 at 1:34 pm
McCown's article was based on a couple of incorrect assumptions. The first (and worst) is that if you have a GUID it would be the clustered index. This is a pretty dumb move as his article points out, but he aparently didn't consider using a different column for clustering... As the counter article points out, one possible big advantage of GUID's is that the client can generate them, reducing the need for round trips to the server.
Now your question seems to imply that if you don't have a GUID then you are forced to use a cursor. I don't see the connection.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 24, 2005 at 4:22 pm
What I was trying to imply was that if I wanted to insert an identity that was an integer instead of a guid, I would have to use a cursor to populate the value(iterate through and give each row a successive number). If I'm wrong, please correct me, but I cant see how to pass the row number to the int column any other way, like in the select statement ... such as select @@identity, PartNo,Description from abc123
There probably is a trick to do it, that I'm not familiar with.
thanks
chris
May 25, 2005 at 8:38 am
OK, so you are trying to add an IDENTITY column to an existing table and populate the data for the existing rows appropriately?
The easiest way would probably be to create a new table with the IDENTITY column (along with all the others in your current table) Then do a INSERT INTO...SELECT FROM to populate the new table from the existing table. You will need to document/script any foreign key constraints that exist on the old table before you drop them, then rename the old table, rename the new table to the old table's original name, create the constraints on it and verify that all your data and constraints are there, then you can drop the original table.
With 16,000,000 rows you may need to split up your INSERT...SELECT statement so that your t-log doesn't get out of control...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply