Fastes way to increment an id column

Question

Fastes way to increment an id column

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

This isn't a true 'quirky' update unless there's a clustered index to determine the order of the ID assignment

I kind of agree and kind of don't. In the code Flo posted, order meant nothing. A clustered index was not required to establish the order. And, it's still a "quirky update" in that a 3 part SET statement was used... if you don't think so, ask an Oracle user. 😉

Paul White (9/6/2009)
Jeff Moden (5/17/2009)
.. if, for some reason, you cannot use IDENTITY and you're stuck with SQL Server 2000 or less which prevents the use of ROW_NUMBER and the like, then the "quirky" update Flo used is absolutely the fastest method available.
Heh :laugh:

Heh? Good idea and good code but you forgot to copy things like indexes, privs, restrictions, triggers, and named constraints to the new table. If I recall correctly, you may also need to find and recompile any views due to the table changes but that might only be if you change the number of columns or their datatypes. 😉

I will admit that even with those additions, the copy job you built may be quicker. I just can't verify your findings for the next week because I won't have access to an SQL computer for that time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Heh - because the whole thing was kinda jokey - a bit of fun for an otherwise fairly tedious Monday at work.

I agree with what you say about the quirkiness (order not being important) and about the object permissions, indexes, constraints and so on. But it was just funny - to me anyway 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 3

--- duplicate ---

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

--- duplicate ---

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi Paul

Paul White (9/6/2009)
2. This method runs in less than half the time (2005 only):

This script was just a little trick for SQL Server 2000 and previous versions ;-). There are a couple of better ways to handle this in SQL Server 2005/2008.

Greets

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 6

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

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!).

Paul White SSC Guru Points: 150467 More actions · Answer 8

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 9

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 :-P)

What about you? Things are fine?

Paul White SSC Guru Points: 150467 More actions · Answer 10

Florian Reischl (9/7/2009)
(And there will be thousands of guys from New Zealand here in Munich :-P)
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

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 :-D. 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. :hehe:

Paul White SSC Guru Points: 150467 More actions · Answer 12

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi