2

I want to cast or convert newid() output to decimal (with minimum space required)

So far I did this :

SELECT  CAST( '0x'+REPLACE(CAST(NEWID() AS VARCHAR(50)), '-', '' ) AS DECIMAL(38,0) )

But I'm getting this error :

Error converting data type varchar to numeric.

Searched a lot but getting frustrated, came here to find some help.

Many thanks in advance.

update : decimal(8,0) became decimal(38,0) and still getting the same error.

Spongebob Comrade
  • 1,495
  • 2
  • 17
  • 32
  • 3
    This seems doomed to failure. A Guid is 16 bytes. Much more than 8 decimal places. If converting to a 16 byte signed integer the range would be `−170,141,183,460,469,231,731,687,303,715,884,105,728` to `170,141,183,460,469,231,731,687,303,715,884,105,727` – Martin Smith Mar 11 '13 at 10:13
  • @Martin I think that it supports up to 17 bytes : http://msdn.microsoft.com/en-us/library/ms187746.aspx , so I believe that it is not impossible. – Spongebob Comrade Mar 11 '13 at 10:22
  • It might not be impossible but what algorithm are you going to use to go from a GUID to a `decimal(38,X)`? Using the integer representation isn't going to be possible as that would require `decimal(39,0)` and what possible benefit are you going to get out of it? – Martin Smith Mar 11 '13 at 10:25
  • @Martin My goal is : Storing GUID in a more logical way than storing it as Varchar ! storing it as a varchar reduces join performance. so what about storing it as a decimal value. I guess it improves join time to half. But I should test it first. but for testing I need to store guid as a decimal value and test performance for decimal joins! – Spongebob Comrade Mar 11 '13 at 10:35
  • Store it as a `uniqueidentifier` not `varchar`. This **is** the most efficient format. It isn't actually stored as the human readable string. It is stored like this `SELECT CAST(NEWID() AS BINARY(16))` – Martin Smith Mar 11 '13 at 10:36
  • @Martin I'm not going to implement any algorithm and I just want to use existing features of TSQL. for example : select CAST(CONVERT(VARBINARY, '0x0000FFFF', 1) AS INT) but replace int with decimal and get the same error. – Spongebob Comrade Mar 11 '13 at 10:38

1 Answers1

5

This is an xy problem.

The correct datatype to use for GUIDs is uniqueidentifier. There is no need to convert it to some numeric datatype yourself.

The uniqueidentifier is stored in a 16 byte binary format not the char(36) string that is displayed.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845