SQL Server Sequence Number Generator

Question

SQL Server Sequence Number Generator

wwang-690571

SSC Enthusiast

Points: 171
More actions
March 27, 2008 at 8:50 am

#184560

Hi,
Does any one have information on what the max number
the generator can generate, like 1 to 1 billion, or to 12 zeros?
Thanks.
--John

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Do you mean the identity field? If that is what you mean it would depend on the datatype you use. For example, int, the most commonly used indentity data type will store -2,147,483,648 through 2,147,483,647 so if you set the seed to the low end you get over 4 billion. IF you go to bigint (Sql 2000+) you get –2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). You can use any precise numeric type (eliminates float, real, and money types) as an identity field so your max is based on that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

wwang-690571 SSC Enthusiast Points: 171 More actions · Answer 2

Jack,

Thanks for your reply.

So, the bigint will serve up to 2^63.

What is the datatype will serve up to 2^127 integer number?

Or, the SQL Server 2005 can have 'bigint' served up t 2^127?

--John

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 3

If you need stuff that is nearly that huge you might try DECIMAL(38):

declare @what table (x decimal(38) identity(10000000000000000000000000000000000000, 1))

insert into @what default values

select * from @what

/* -------- Sample Output: --------

x

---------------------------------------

10000000000000000000000000000000000000

*/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

wwang (3/27/2008)
Jack,
Thanks for your reply.
So, the bigint will serve up to 2^63.
What is the datatype will serve up to 2^127 integer number?
Or, the SQL Server 2005 can have 'bigint' served up t 2^127?
--John

If you really need that big a number, you probably don't want an identity column anyway. How many records are being inserted a minute? What type of data are you storing where you will have that active a system? With 100 records created a minute 1 billion with get you 19 years so 2^63 should last you quite awhile.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

wwang (3/27/2008)
What is the datatype will serve up to 2^127 integer number?

2^127 is a really big number. What are you counting that you would ever need that many distinct ID's?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

wwang-690571 SSC Enthusiast Points: 171 More actions · Answer 6

Hi,

Thanks for your reply.

Just need to know what the limitation on SQL Server is on

this regard. This is especially important when there is

more than one RDBMS's are under reviewing for a project.

Have a good one.