Re: Find the first available integer.

From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 14:10:10 -0500

Insert as many as you need. If it's "millions and millions" and not 2
billion+, then insert "millions and millions" and not 2 billion+. Do you
really suppose you'll need all 2 billion+? If not, then your extreme
argument is moot.

I've never found the need to create such a large numbers table, but even if
you really do need all 2 billion+ possible integers, I can't imagine
inserting 2 billion rows would take more than a few hours (assuming optimal
conditions, e.g. the MDF file doesn't have to grow, and logging is minimized
by being in simple recovery model). It should be even faster if you're
using a real server with a real disk system, and if you insert a reasonable
number of rows instead of jumping right to 2 billion+.

CREATE TABLE numbers
(
    n INT IDENTITY(1,1)
    PRIMARY KEY CLUSTERED
)
GO

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 2147483647
BEGIN
    INSERT numbers DEFAULT VALUES
END
GO

You only have to do the actual population one time. Once you have the
numbers table, when you "deliver your product," you can copy the numbers
table into the database that is being delivered, rather than wait around for
the insert.

I'm not trying to twist your arm here. I'm just showing you that there are
many good uses for a numbers table, and with a clustered index on the
integer column, the performance will beat any ad-hoc derived table or
looping mechanism. Whether the space is worth it to you is up to YOU, not
ME. You seem to be trying very hard to avoid doing this... so, my
suggestion even after explaining all of this, is don't do it. Your loss,
IMHO. But if all you're going to do is poke holes in it, I'm done trying to
point out its advantages. 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Star" <----> wrote in message news:ug8oiwNGEHA.2664@TK2MSFTNGP11.phx.gbl...
> One more thing, Aaron...
>
> The problem that I see with that is with big tables. Iīm going to have a
> very big table, so the number of records
> that I have to insert in the Numbers table could be millions and millions.
> How am I supposed to do that? It would take
> forever if every time we want to deliver our product to a customer we have 
> a
> batch process that populates that table, donīt you think?
>
> We are talking of a integer of 4 bytes, which is a number
> between -2,147,483,648 and 2,147,483,647.
> Do I need to insert  2,147,483,647 records in that table????
>
> Thanks
>
>

Relevant Pages

  • Re: datetime problem
    ... parameter statement. ... How many language tables do you have? ... >> Aaron Bertrand ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: back to referring page
    ... were a direct result of my fingers and brain not being synchronized or my ... "Aaron Bertrand - MVP" wrote in message ... > SQL Server MVP ... >> lack of caffeine. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Trigger for tracking moddate & moduser
    ... update as many rows as were affected by the original statement. ... (SELECT primaryKey FROM Inserted) ... >> Aaron Bertrand ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: HTTP 500 - Internal Server Error - on a Non-Default Web Site
    ... Why are you using ODBC!? ... >>Aaron Bertrand ... >>SQL Server MVP ... >>> C2 Security Settings implemented on the server. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Move site from Server A > Server B (without downloading)
    ... "Steven Burn" wrote in message ... I've only got FTP access unfortunately:o( ... >> Aaron Bertrand ... >> SQL Server MVP ...
    (microsoft.public.inetserver.asp.general)
Loading