Fastes way to increment an id column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Florian Reischl » Fastes way to increment an id column

27 posts, Page 1 of 3

1 2 3 »»»

Fastes way to increment an id column

Rate Topic

Display Mode

Topic Options

Author

Message

Florian Reischl

Posted Wednesday, February 25, 2009 1:21 AM

SSCrazy

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

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

Post #664064

Carlo Romagnano

Posted Thursday, April 02, 2009 1:19 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819

???????????????

Post #688576

Florian Reischl

Posted Thursday, April 02, 2009 2:28 AM

SSCrazy

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

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

Post #688612

Carlo Romagnano

Posted Thursday, April 02, 2009 2:45 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819

-- 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'

Post #688625

Florian Reischl

Posted Thursday, April 02, 2009 3:29 AM

SSCrazy

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

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

Post #688652

Carlo Romagnano

Posted Thursday, April 02, 2009 4:04 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819

-- try to use this for replication:
set identity_insert my_table on

Post #688683

Florian Reischl

Posted Thursday, April 02, 2009 4:29 AM

SSCrazy

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

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

Post #688707

Jeff Moden

Posted Sunday, May 17, 2009 11:27 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 32,893, Visits: 26,764

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 INT) AS 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. Tongue

Anyway... good tip, Flo. Keep 'em coming. Smile

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #718711

Florian Reischl

Posted Sunday, May 17, 2009 11:36 AM

SSCrazy

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

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

Post #718712

Jeff Moden

Posted Sunday, May 17, 2009 11:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 32,893, Visits: 26,764

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. BigGrin

--Jeff Moden

Post #718718

« Prev Topic | Next Topic »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions