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


Fastes way to increment an id column


Fastes way to increment an id column

Author
Message
Florian Reischl
Florian Reischl
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3934
Comments posted to this topic are about the item Fastes way to increment an id column


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
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9284 Visits: 3439
???????????????
Florian Reischl
Florian Reischl
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3934
Hi Carlo

You have been faster than me... I wanted to explain directly after it becomes published.

Sure since SQL Server 2005 this is not needed any more because of the ROW_NUBER() function!

The reason for the script is handling of unique identities within SQL Server 7.0/2000 in bulk operations. It is no big clue but I often get the question from some of my developers how to handle a incremental id without a loop. (In SQL Server 2000)

For SQL Server 2005 this makes no sense! I will update the description to explain.

Hope that explains...

Greets
Flo


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
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9284 Visits: 3439
-- what about IDENTITY??
SET NOCOUNT ON

-- Just a simple test table
IF (OBJECT_ID('tempdb..#my_tab') IS NOT NULL)
DROP TABLE #my_tab
CREATE TABLE #my_tab (id INT NOT NULL IDENTITY(1000,1), any_date DATETIME)

-- Create 100000 test values
PRINT 'Test data'
PRINT '-> Creating'
DECLARE @i INT
SET @i = 0
WHILE (@i < 100000)
BEGIN
INSERT INTO #my_tab (any_date) VALUES (DATEADD(MINUTE, @i * -1, GETDATE()))
SET @i = @i + 1
END
PRINT '<- done'
Florian Reischl
Florian Reischl
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3934
Hi Carlo

E.g. in our business case we are not able to use IDENTITY due to problems with replication. If you can/could use IDENTITY columns in SSE 2000 you don't need it. But if you use replications there some cases which make problems with them because of the seed.

Greets
Flo


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
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9284 Visits: 3439
-- try to use this for replication:
set identity_insert my_table on
Florian Reischl
Florian Reischl
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3934
Hi Carlo

Thanks for feedback! There is another reason for me that I cannot use IDENTITIES, our C# OR-Mapper cannot handle them (not my design...).

I'm no DBA, I'm a developer so I cannot explain all the problems with replication. My DBA told me and Noeld confirmed it here:
http://www.sqlservercentral.com/Forums/Topic669595-338-1.aspx

I will update the description of the script to ensure that everybody understands the reason of it!

Greets
Flo


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)

Group: General Forum Members
Points: 334929 Visits: 42575
I agree with Flo... 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. That method is good for a lot of things like running totals, ranking, and some incredibly odd groupings.

There are, however, some fairly strict rules to using it if you actually want to control what it does. Those rules and some of the methods are covered by an article (currently being rewritten) that will reappear at the following URL sometime in the next month or so.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

If I could also make a wee bit of a suggestion... if you want to build a test table for something, there's simply no reason to go through the complexity of building a While Loop nor the wait while it works... not even in SQL Server 2000. To wit, the While Loop in the article takes a little over 7 seconds to build the test table. Using a little set-based programming and a little knowledge of the functions available in SQL Server, it can be done with much less complexity and it executes in about 250 milli-seconds.

--===== Conditionally drop the test table for test reruns
     
IF OBJECT_ID('tempdb..#my_tab'IS NOT NULL
        
DROP TABLE #my_tab

--===== Build the test table and populate it with data on the fly
    
SET STATISTICS TIME ON
 SELECT TOP 
100000
        
CAST(NULL AS INTAS ID,
        
DATEADD(mi,-ABS(CHECKSUM(NEWID())%100000),GETDATE()) AS Any_Date
   
INTO #My_Tab
   
FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2
    
SET STATISTICS TIME OFF


I mention this because, frequently, testing against larger numbers of rows is ensential to ensuring performance in the face of scalability. To test against a million rows, the While Loop takes a whopping big 80+ seconds on my box. Worse yet, it also practices you in RBAR thinking instead of set based thinking. The simple set based solution only takes 2.319 seconds to produce the same million row test table. Folks are much more likely to test repeatedly if they can setup test data that quickly.

Heh... yeah... I know... Someone once told me that this was easy for me but not for them. That's my point... if they keep practicing RBAR methods instead of learning how to do the very high speed set based methods, it will never be easy for them. :-P

Anyway... good tip, Flo. Keep 'em coming. :-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Florian Reischl
Florian Reischl
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13795 Visits: 3934
Thanks Jeff!

It's not a big deal but if I get one Dollar for every time I showed somebody to remove a cursor I would be a rich man...

Best wishes
Flo


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)SSC Guru (334K reputation)

Group: General Forum Members
Points: 334929 Visits: 42575
Florian Reischl (5/17/2009)
Thanks Jeff!

It's not a big deal but if I get one Dollar for every time I showed somebody to remove a cursor I would be a rich man...

Best wishes
Flo


Heh... OK... you owe me a Dollar. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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