Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NEWID() - How does it work?


NEWID() - How does it work?

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
Paul White (2/23/2013)
Lynn Pettis (2/22/2013)
As to ordering by a uniqueidentifier, it sorts in ascending or descending order as if it is converted to a CHAR(36) value, or at least that is my experience with them.

They're not strings and don't sort as strings:

DECLARE @T AS TABLE (uuid uniqueidentifier);

INSERT @T
(uuid)
VALUES
({ guid'459F82E6-4C50-4777-8347-D313526EB4BA'}),
({ guid'E99F760A-80EC-40A7-B7E9-88F58006AA13'});

SELECT uuid FROM @T AS t ORDER BY t.uuid;





http://blogs.msdn.com/b/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx

More details here:

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx


I did qualify that it was in my experience. Could be they are being converted to strings before being sorted. I'll have to look at the app again when I get to work. We unfortunately use a lot of guids.


Always willing to learn, thank you.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
Lynn Pettis (2/23/2013)
I did qualify that it was in my experience. Could be they are being converted to strings before being sorted.

That's fine, I'm not posting to prove you wrong :-) more for the other readers of this thread, and SQL_Enthusiast is particular. I do wonder why anyone would covert a 16-byte uuid to a 36-character string though.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
Paul White (2/23/2013)
Lynn Pettis (2/23/2013)
I did qualify that it was in my experience. Could be they are being converted to strings before being sorted.

That's fine, I'm not posting to prove you wrong :-) more for the other readers of this thread, and SQL_Enthusiast is particular. I do wonder why anyone would covert a 16-byte uuid to a 36-character string though.


I do, lack of knowledge.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.

declare @test table (nid uniqueidentifier not null primary  key clustered )
insert into @test
select nid = newid() union all select nid = newid() union all select nid = newid() union all
select nid = newid() union all select nid = newid() union all select nid = newid()
order by 1

select
a.*,
l_bigint = convert(bigint,a.l_vbin),
r_bigint = convert(bigint,a.r_vbin)
from (
select
aa.*,
l_vbin = convert(binary(8),substring(aa.vbin,1,8)),
r_vbin = convert(binary(8),substring(aa.vbin,9,16))
from (
select
aaa.nid,
vbin = convert(binary(16),aaa.nid)
from
@test aaa
) aa ) a
order by a.nid


Results:

nid vbin l_vbin r_vbin l_bigint r_bigint
------------------------------------ ---------------------------------- ------------------ ------------------ -------------------- --------------------
4133B328-4043-4A44-9213-07E12E739234 0x28B333414340444A921307E12E739234 0x28B333414340444A 0x921307E12E739234 2932744137742500938 -7920978655886208460
4CD74D85-6338-49E5-BA08-18022410F816 0x854DD74C3863E549BA0818022410F816 0x854DD74C3863E549 0xBA0818022410F816 -8841173771094858423 -5041753385367177194
29F137BE-F5AD-4082-8838-1A9C87C63F66 0xBE37F129ADF5824088381A9C87C63F66 0xBE37F129ADF58240 0x88381A9C87C63F66 -4740054921469656512 -8631119426260418714
BE73EB30-7189-4AD8-B905-1EF2B188E8AE 0x30EB73BE8971D84AB9051EF2B188E8AE 0x30EB73BE8971D84A 0xB9051EF2B188E8AE 3525038395534465098 -5114647774099871570
51CB86F6-D234-4E47-8417-D936D9E27F13 0xF686CB5134D2474E8417D936D9E27F13 0xF686CB5134D2474E 0x8417D936D9E27F13 -682634743861065906 -8928428906631758061
822AAE67-2F2E-4723-B267-DBBC75CDB54D 0x67AE2A822E2F2347B267DBBC75CDB54D 0x67AE2A822E2F2347 0xB267DBBC75CDB54D 7470955570463187783 -5591258809880431283

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39932
Lynn Pettis (2/22/2013)
Jeff Moden (2/22/2013)
Lynn Pettis (2/22/2013)
I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?


Yes, but not directly.


SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)



Cool. Learned something tonight. Now, I should probably get some sleep since I have 4 soccer games to ref tomorrow at the Sunbelt Tournament; which I am calling the Frozen Belt since it snowed, it is only supposed to get to 40 or so tomorrow (colder on Sunday), But hey, soccer season is starting!!


Ah, crud. Sorry, Lynn. Not sure what I was thinking. Michael is correct. The code I posted seems to work but is technically incorrect because a GUID is 16 bytes and a BIGINT is only 8. It's the old 2 pound bag thing.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39932
Michael Valentine Jones (2/23/2013)

Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.


Not sure what I was thinking, Michael. Thanks for the correction.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
Paul White (2/23/2013)
I do wonder why anyone would covert a 16-byte uuid to a 36-character string though.


I wonder where do you find someone who would not... Crazy

Hehe
jeremysimmonsnet
jeremysimmonsnet
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 100
Casting binary(16) to a bigint will truncate it. bigint only has 8 bytes of storage.
You must convert both the first and second sets of 8 bytes to a bigint to get the equivalent value.
DECLARE @uuid AS UNIQUEIDENTIFIER = NEWID()
DECLARE @bits AS VARBINARY(16) = CAST(@uuid AS VARBINARY(16))
SELECT
CAST(@bits AS BIGINT) 'your answer',
@bits 'all16bits',
CAST(SUBSTRING(@bits, 1,8) AS BIGINT) leftBits,
CAST(SUBSTRING(@bits, 9,8) AS BIGINT) rightBits

guidaschar: 9E021A10-CD60-4275-A5E0-B208196B07B3
your answer: -6493994914812328013
all16bits: 0x101A029E60CD7542A5E0B208196B07B3
leftBits: 1160242733253489986
rightBits: -6493994914812328013

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search