Combine two int columns into one bigint column

Hello
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:

> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>