Primary Key ... Unique ID or Bigint??

[Forri]

Hi

I want to setup automatic primary keys...i usually set up a field name called ID having an attribute of Bigint with the Autonumber turned on!

Is that good or a unique ID is another way to go?

if not what is the attribute UniqueID used for?

Thanks
nick

 

[vongrunt]

Bigint? Ya have more than 2+ billion records?

 

[Forri]

Hehe..hope not...but i cannot judge now since its too early to justify quantities!

What's the maximum of a bigint? (999,999,999??)

Thanks for you help!

Ncik

 

[SQLSister]

From BOL:
" from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807)."

Questions about posting. See faq183-874

 

[donutman]

With UniqueID you are just setting a constraint. The implication is that you will roll your own keys as needed.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I think he means a GUID field. THe reaeson to use one of these is for replication where records are created on separte multiple databases and ,merged together later. This will ensure that each one has a unique ID no matter what.

Questions about posting. See faq183-874

 

[NoCoolHandle]

For my 1c

I would use the uniqueidentifier / New_id() if I was planning for replication as it does a very good job of allowing multipe inserts from multiple locations, but it is BIG (16 bytes) and takes up space.

If i wasn't planning on using replication (and it might be silly the way things are going now), then I would use the bigint. I also tend to start my identity property at the "small"end of any numric range. Not 0.

I.E. for smallint the first number would be -32000 (approx) and int -1,200,000,000 and bigint(-9,223,372,036,854,775,808)

That way I get to take full advantage of the range avaiable without waiting for it to run out(assuming that that is a possibility)

One thing the web has taught us is that we can collect more data than we ever expected

Rob

 

[SQLSister]

One thing the web has taught us is that we can collect more data than we ever expected

You got that right, Rob. And access more data than we ever expected too. Our web app accesses or sends data to an amazing variety of sources as well as using our own data.

Questions about posting. See faq183-874