NEWID() - How does it work?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » NEWID() - How does it work?

Add to briefcase

17 posts, Page 2 of 2

««1 2

NEWID() - How does it work?

Expand / Collapse

Author

Message

Posted Saturday, February 23, 2013 7:18 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 21,623, Visits: 27,459

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

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)

Post #1423380

Posted Saturday, February 23, 2013 7:35 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534

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 Smile

Smile

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1423384

Posted Saturday, February 23, 2013 7:39 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 21,623, Visits: 27,459

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 Smile

Smile

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

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)

Post #1423385

Michael Valentine Jones

Posted Saturday, February 23, 2013 8:12 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 2,945, Visits: 10,511

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

Post #1423387

Posted Monday, February 25, 2013 4:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1423819

Posted Monday, February 25, 2013 4:51 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1423820

Posted Monday, February 25, 2013 6:16 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 4,550, Visits: 8,201

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

Crazy

Hehe

Post #1423835

« Prev Topic | Next Topic »

Add to briefcase

17 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse