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

Fastes way to increment an id column Expand / Collapse
Author
Message
Posted Monday, September 7, 2009 2:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
Florian Reischl (9/7/2009)
There are a couple of better ways to handle this in SQL Server 2005/2008.

Sure. But for a table which just needs sequential row numbers adding to it, even 2008 doesn't have a faster method.
I thought it was an interesting use of IDENTITY, and SWITCH.
I enjoyed the article too.

Haven't seen you around much recently...?

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783669
Posted Monday, September 7, 2009 3:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Paul White (9/7/2009)
But for a table which just needs sequential row numbers adding to it, even 2008 doesn't have a faster method.

It depends. In SQL Server 2005/2008 you can avoid the usage of a temporary table and directly create an incremental ID by bulk selecting data from source tables (if available).

I thought it was an interesting use of IDENTITY, and SWITCH.

Sorry, didn't answer the IDENTITY part and even notice the SWITCH. Didn't ever see the SWITCH command. How cool is that!

Haven't seen you around much recently...?

Quiet busy these days... We started the second part of our system redesign. So I have to do many project management things like specifications, cost estimates, ... (and way too much meetings!).



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #783681
Posted Monday, September 7, 2009 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
It's good to hear you are keeping busy anyway
Though the management side of things sounds quite dull.

Florian Reischl (9/7/2009)
Paul White (9/7/2009)
But for a table which just needs sequential row numbers adding to it, even 2008 doesn't have a faster method.

It depends. In SQL Server 2005/2008 you can avoid the usage of a temporary table and directly create an incremental ID by bulk selecting data from source tables (if available).

I think you're referring to windowing functions like ROW_NUMBER...is that right? I'd like to be sure I understand you correctly.
That sure can be an efficient way to add a sequence number 'on the fly' - but I stand by my claim that the script I posted remains the fastest way to add persistent sequential numbering to a large table (Jeff's caveats noted).

Hope you are well.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783686
Posted Monday, September 7, 2009 4:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Paul White (9/7/2009)
It's good to hear you are keeping busy anyway
Though the management side of things sounds quite dull.

The management side of things is dull! I would like to get rid of it - really - ...

I think you're referring to windowing functions like ROW_NUMBER...is that right? I'd like to be sure I understand you correctly.
That sure can be an efficient way to add a sequence number 'on the fly' - but I stand by my claim that the script I posted remains the fastest way to add persistent sequential numbering to a large table (Jeff's caveats noted).

Yep, that's what I mean.

I'm sure that your way to create a sequential numbering is the fastest way. I already added your post to my snippets . The only thing is, it requires the (temporary) table not to exist.

Hope you are well.

Yes, I'm well. Weather is really nice and Oktoberfest starts in less than two weeks. (And there will be thousands of guys from New Zealand here in Munich )
What about you? Things are fine?



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #783700
Posted Monday, September 7, 2009 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
Florian Reischl (9/7/2009)
(And there will be thousands of guys from New Zealand here in Munich )
What about you? Things are fine?

All good here yes. Spring has sprung so we are getting some warmer weather - which is always good.
Not sure we can spare 'thousands' though - the place will seem empty in October




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783905
Posted Monday, September 7, 2009 3:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Good morning Paul

Paul White (9/7/2009)
Not sure we can spare 'thousands' though - the place will seem empty in October

You can - and you do . Every year at same time all the Munich habitats become touring guides for tourists from all over the world (especially Italians, English, Australians and guys from New Zealand).

Just give me a hint if you ever want to visit Munich and see how your fellows forget their own name.



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #783909
Posted Monday, September 7, 2009 4:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
I have an Uncle and Aunt in Bogen (near Straubing), so you never know...

http://maps.google.co.nz/maps?rlz=1C1CHMB_en-GBNZ317NZ318&sourceid=chrome&q=maps+bogen+germany&um=1&ie=UTF-8&split=0&gl=nz&ei=HIOlSuibB4i0sgPd3qWNDw&sa=X&oi=geocode_result&ct=title&resnum=1




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #783932
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse