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:
>

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>