SQL Clone
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40608 Visits: 38567
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16260 Visits: 11355
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40608 Visits: 38567
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
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 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 Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88988 Visits: 41137
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88988 Visits: 41137
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
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: 10700 Visits: 11970
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: 101
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