Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

New Column Updates Expand / Collapse
Author
Message
Posted Monday, May 21, 2007 6:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #367600
Posted Monday, May 21, 2007 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 2012 1:18 PM
Points: 110, 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
Post #367601
Posted Monday, May 21, 2007 7:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, 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

Post #367606
Posted Monday, May 21, 2007 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:37 AM
Points: 11, Visits: 262
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
Post #367621
Posted Monday, May 21, 2007 7:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 2,027, Visits: 3,024

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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #367634
Posted Monday, May 21, 2007 8:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 202, Visits: 307
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.


Post #367637
Posted Monday, May 21, 2007 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
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.


Post #367646
Posted Monday, May 21, 2007 9:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Am I missing something? ...

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



* Noel
Post #367674
Posted Monday, May 21, 2007 9:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.



ATB

Charles Kincaid

Post #367678
Posted Monday, May 21, 2007 10:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 126, Visits: 504

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.

 




Post #367696
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse