0

im using sql server 2012, is it possible to generate a uniqueidentifier value based on two or three values mostly varchars or decimal, i mean any data type which takes 0-9 and a-z.

Usually uniqueidentifier varies from system to system. For my requirement, I need a custom one, when ever i call this function, it should get me the same value in all the systems.

I have been thinking of converting the values into varbinary and taking certain parts of it and generating a uniqueidentifier. How good is this approach.

Im still working on this approach.

Please provide your suggestions.

Harsha
  • 1,161
  • 4
  • 18
  • 38
  • What's wrong with "select NEWID()"? – David Brabant Aug 28 '12 at 06:13
  • "For my requirement, I need a custom one, when ever i call this function, it should get me the same value in all the systems" Then you don't need a uniqueidentifier .... – Mitch Wheat Aug 28 '12 at 06:13
  • @David Brabant, NEWID() generates random values, but i need the value to be specific to the strings i pass. – Harsha Aug 28 '12 at 06:17
  • @Mitch Wheat, i need the type to be of uniqueidentifier, its for other purpose. – Harsha Aug 28 '12 at 06:18
  • based on the answers in other forums, i tried this, "SELECT distinct cast(Hashbytes('SHA1',CAST(TableName + ColumnName + ColumnValue AS VARBINARY(max))) as uniqueidentifier) from Locale" can anyone tell me how good is this query. – Harsha Aug 28 '12 at 10:28

2 Answers2

0

The standards document for Uniqueidentifier goes to some length showing how they are generated. http://www.ietf.org/rfc/rfc4122.txt

I would give this a read (especially 4.1.2. as that breaks down how a guid should be generated) and maybe I would keep use the timestamp components but hard code your network location element which will give you what you are looking for.

u07ch
  • 13,324
  • 5
  • 42
  • 48
0

What you describe is a hash of the values. Use HASHBYTES function to digest your values into a hash. But your definition of the problem contradicts the requirement for uniqueness since, by definition, reducing an input of size M to a hash of size N, where N < M, may generate collisions. If you truly need uniqueness then redefine the requirements in a manner which would at least allow for uniqueness. Namely, the requirement for it should get me the same value in all the systems must be dropped since the only way to guarantee it is to output exactly the input. If you remove this requirement then the new requirement are satisfied by NEWID() (yes, it does not consider the input, but it doesn't have to in order to meet your requirements).

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • thank you for mentioning HASHBYTES, it helped a lot. even though there are limitations, i still prefer this approach. I checked this with 113443 records in a table, and I got unique values. – Harsha Aug 29 '12 at 04:50