Is there a simple way to create a unique integer key from a two-integer composite key?

tsql math primary-key uniqueidentifier composite-key
abeger picture abeger · Nov 16, 2009 · Viewed 8.3k times · Source

For various reasons that aren't too germane to the question, I've got a table with a composite key made out of two integers and I want to create a single unique key out of those two numbers. My initial thought was to just concatenate them, but I ran into a problem quickly when I realized that a composite key of (51,1) would result in the same unique key as (5,11), namely, 511.

Does anyone have a clever way to generate an integer out of two integers such that the generated integer is unique to the pair of start integers?

Edit: After being confronted with an impressive amount of math, I'm realizing that one detail I should have included is the sizes of the keys in question. In the originating pair, the first key is currently 6 digits and will probably stay in 7 digits for the life of the system; the second key has yet to get larger than 20. Given these constraints, it looks like the problem is much less daunting.

Answer

recursive picture recursive · Nov 16, 2009

You can mathematically prove this is impossible if you want the resulting key to comprise the same number of bits as its two components. However, if you start with two 32 bit ints, and can use a 64 bit int for the result, you could obviously do something like this:

key1 << 32 | key2

SQL Syntax

SELECT key1 * POWER(2, 32) + key2

Related questions

Calculating distance between two points (Latitude, Longitude)

I am trying to calculate the distance between two positions on a map. I have stored in my data: Longitude, Latitude, X POS, Y POS. I have been previously using the below snippet. DECLARE @orig_lat DECIMAL DECLARE @orig_lng …

Read More
In T-SQL how can I get a column of 2-decimal points(or percentages)?

In SQL Server 2008, I am running a report where I need to divide two columns (called Completes and Prescreens, whole integers), and then I go to Excel and get the Prescreens divided by the Completes, and turn that into a …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More