Generating unique incremental integer ID for database tables

[Claire]

Hi,
I can imagine this question has been brought up before but I've spent all
morning trying to find what I need on google without success.
My application sits on Mysql or MS sql server engines and to date I've just
been using auto-incremental int64 fields to generate my RecID field.
I know that in the future the database will need to support multiple sites,
so theres also a SiteID field in there too.

example table ABC
Primary Key = (int64)ABC.RecI D + (int32)ABC.fk_S ites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that the
sites can be synchronised across to a central server (another noob terror to
come)
I don't want to use triggers or stored procedures as I want my database to
be as simple/clean as possible.

How should I generate my "autoincrementa l" unique RecIDs please?

thank you

[GArlington]

On Mar 17, 2:38 pm, "Claire" <claire@no_hope .comwrote:

Hi,
I can imagine this question has been brought up before but I've spent all
morning trying to find what I need on google without success.
My application sits on Mysql or MS sql server engines and to date I've just
been using auto-incremental int64 fields to generate my RecID field.
I know that in the future the database will need to support multiple sites,
so theres also a SiteID field in there too.

example table ABC
Primary Key = (int64)ABC.RecI D + (int32)ABC.fk_S ites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that the
sites can be synchronised across to a central server (another noob terror to
come)
I don't want to use triggers or stored procedures as I want my database to
be as simple/clean as possible.

How should I generate my "autoincrementa l" unique RecIDs please?

thank you

Look into UUID

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

Having a primary key that is the sum of two integers could get you in a lot
of trouble, since there are many combinations of two integers that could add
up to the same number. But in general, you would need to get the SQL MAX(ID)
from the table, increment by 1, and that would be your next id integer.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net
"Claire" wrote:

Hi,
I can imagine this question has been brought up before but I've spent all
morning trying to find what I need on google without success.
My application sits on Mysql or MS sql server engines and to date I've just
been using auto-incremental int64 fields to generate my RecID field.
I know that in the future the database will need to support multiple sites,
so theres also a SiteID field in there too.

example table ABC
Primary Key = (int64)ABC.RecI D + (int32)ABC.fk_S ites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that the
sites can be synchronised across to a central server (another noob terror to
come)
I don't want to use triggers or stored procedures as I want my database to
be as simple/clean as possible.

How should I generate my "autoincrementa l" unique RecIDs please?

thank you

[Claire]

No, Im sorry if my shorthand is wrong Peter
I meant there are 2 fields in the primary key. (Im not daft enough to add
the values together)
1) 64 bit integer RecID field
2) 32 bit integer fk_Sites_RecID (foreign key, Sites table, RecID field)

but thank you for the MAX(ID) suggestion :)
Claire

"Peter Bromberg [C# MVP]" <pb*******@yaho o.NoSpamMaam.co mwrote in message
news:21******** *************** ***********@mic rosoft.com...

Having a primary key that is the sum of two integers could get you in a
lot
of trouble, since there are many combinations of two integers that could
add
up to the same number. But in general, you would need to get the SQL
MAX(ID)
from the table, increment by 1, and that would be your next id integer.

[Claire]

>How should I generate my "autoincrementa l" unique RecIDs please?

>
Look into UUID

Thank you for the UUID suggestion :)
I have to stick with my incremental big ints as they're more likely to be
compatible over a selection of different servers than guid/uuids. (ie the
version of MySQL I'm using doesnt support them)

Claire

[GArlington]

On Mar 18, 1:21 pm, "Christophe r Van Kirk"
<chris.vank...@ fdcjapan.comwro te:

There is a problem with UUID, though. They're huge, especially when
converted to a string form.

<snip>
http://en.wikipedia.org/wiki/UUID
<extract>
In its canonical form, a UUID consists of 32 hexadecimal digits,
displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for
a total of 36 characters...