0

I have a bunch of tables that I'm joining in a view. I need each resulting row in the view to have a unique id...not necessarily a guid, but some value that uniquely can identify each row...and further, can be decomposed into the corresponding primary keys that were used to construct the unique value.

At first I was using the formula suggested here: Formula for Unique Hash from Integer Pair

...and that's worked well, but now I've run into the problem that Ids may run outside of the integer address space...and further may even run (slightly) outside of the bigint address space.

Can someone offer a suggestion as to how to work around this (perhaps by creating a composite guid instead of a numeric value)?

Community
  • 1
  • 1
Jeff
  • 32,948
  • 13
  • 96
  • 198
  • 1
    Why take the trouble of trying to combine multiple keys into one at all? Can't you just add each table's primary key as a separate column to the view? The combination of *these* would be unique and you don't have to worry about overflows or calculations. – Lieven Keersmaekers Nov 24 '11 at 20:27
  • A very good point...but unforunately not possible because of constraints of our ORM – Jeff Nov 24 '11 at 20:50
  • Out of interest, which ORM? Perhaps you can post an answer [here](http://stackoverflow.com/questions/218100/orm-support-for-compound-primary-keys) ;) – onedaywhen Nov 25 '11 at 08:53

1 Answers1

2

This sounds almost too simple, but have you considered just creating a varchar field that is made up of the various primary keys, perhaps separated by some delimiter?

select 
  cast(tableA.id as varchar) + '-' + 
  cast(tableB.id as varchar)  + '-' + 
  cast(tableC.id as varchar)  as compositeKey, 
  tableA.foo //etc...
Jake Feasel
  • 15,917
  • 5
  • 48
  • 64
  • 1
    +1 Perhaps not the best performing solution but it crossed my mind to. It would be easy to implement. – Lieven Keersmaekers Nov 24 '11 at 20:29
  • Yes, easy to implement...but I'd like to make it something that would be more performant in terms of seeking. I'd like to create a unique clustered index on the view column. – Jeff Nov 24 '11 at 20:50
  • 1
    @JeffN825 - Creating a unique clustered index would not be a problem on a varchar column but I wonder how you would initiate a search (on either a varchar or integer column). – Lieven Keersmaekers Nov 24 '11 at 20:53
  • via a FK column in another view that performs the same computation. – Jeff Nov 24 '11 at 21:01
  • 2
    @JeffN825 - We don't know your database usage (rate of insert/update/delete) but I would suggest you to run some performance tests on using a `VARCHAR` and `INTEGER` solution. Rest assured, the `VARCHAR` solution *will* be slower but I doubt it would be the performance killer for your application. – Lieven Keersmaekers Nov 24 '11 at 21:07