SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


New Column Updates


New Column Updates

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30277 Visits: 9671
Yes you can both be right. The value will be set to the last row updated, however in a few rare cases the, update may be done in something that looks like a random order, using the index hint "should" aleviate that possibility.
Adrian Hains
Adrian Hains
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 261
Haven't tried it, but couldn't you just use a ranking function over ordering by the clustered index key to get a pseudo identity column? I can't think of any drawback to this approach, except that you can't do it in SS2K
Brian Munier
Brian Munier
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 80

One other caveat I thought of with either solution, is that if the column will end up being mandatory aka Not Null, there will need to be a check at the end to ensure that there are no null rows that were added after the fact. Also unless you can guarantee that the code will provide a value a default is almost always needed.

For the question on using a default when adding the table, I would first crank up the size of the log file by a few GB, and I expect it would possibly be shorter than the 35 minutes, but it only helps if you have the same value to insert or a derivable formula.

Enjoy, Brian


Rob Scholl
Rob Scholl
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 352
This is extra useful since SET ROWCOUNT affecting updates is going away in the next release of SQL. http://msdn2.microsoft.com/en-us/library/ms188774.aspx
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8314 Visits: 7163

Very interesting article, definitely worth seeing.

Btw, though, shouldn't the columns be NCHAR(2) instead of NVARCHAR(2)? It's a waste of space and processing overhead to make a 2-byte column variable.



SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Jeremy Brown
Jeremy Brown
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 310
Ha! Very nice. I'm facing a similar situation here. This article demonstrates a nice way of dealing with this situation. Not to mention the issue of blocking / locking on the table while just such an update is occurring. Bravo.



Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 350
Lets start from the beginning. [NameKey] [nvarchar](341) NOT NULL. Looks like the choice of column for the clustered index is very bad. Looks like update/changes to the table are never done by the application but only by the back end processes. It means that the initial physical table creation by DBA brought the problem and made clustered index useless not only for this task but for the many other tasks as well.



noeld
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10086 Visits: 2048
Am I missing something? ...

ALTER TABLE MyTable
ADD NewZip char(10) NULL DEFAULT 'Zipcode' WITH VALUES


* Noel
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1743 Visits: 2384

Agreed, an alpha field for clustered seems strange. But then so does 341 for a field length. Sometimes we are not in toal control of the design. I just hope that this field is generated some way in alpha order. Otherwise INSERT performance will suck. And let's not even talk about page splits.



ATBCharles Kincaid
MudLuck
MudLuck
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 519

Well done author.

Working with VLDB requires one to step back and take the time to think it out. While there may be other ways to go about the operation and get simullar times yours is nice for the two reasons you state in your article.

Rollbacks are small by using batches, and by following the order of the clustered index you assist SQL in a orderly writing to the data file.

One 125 hour roll back can only force us all to start working and thinking about SQL server in this way.

Thanks loved it.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search