Unified ID's for legacy database

I found a handy T-SQL trick that helped to solve the following problem: during datawarehouse creation, we needed to consolidate multiple data sources from databases belonging to two generations of system: older one using int identity keys, the newer one, designed for replication and distributed data scenarios using GUID’s as primary keys.

In the consolidated database, we needed to create single representation that would allow to store both new GUID based identities as well as old int-based keys. Direct conversion between UNIQUEIDENTIFIER type and int is not allowed. The following one liner will do the trick:


The “fake GUID’s” it creates from integers are easy to identify as they have leading zeros. If you for any reason do not like it, replace the 0 in first convert with any suitable number.

Explore posts in the same categories: sql, Windows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: