InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:39:13 GMT20http://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Paul White (2/23/2013)[/b][hr]I do wonder why anyone would covert a 16-byte uuid to a 36-character string though.[/quote]I wonder where do you find someone who would not... :crazy::hehe:Mon, 25 Feb 2013 18:16:03 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Michael Valentine Jones (2/23/2013)[/b][hr]Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.[/quote]Not sure what I was thinking, Michael. Thanks for the correction.Mon, 25 Feb 2013 16:51:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Lynn Pettis (2/22/2013)[/b][hr][quote][b]Jeff Moden (2/22/2013)[/b][hr][quote][b]Lynn Pettis (2/22/2013)[/b][hr]I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?[/quote]Yes, but not directly.[code="sql"]SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)[/code][/quote]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!![/quote]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.Mon, 25 Feb 2013 16:50:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspxStrictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.[code="sql"]declare @test table (nid uniqueidentifier not null primary key clustered )insert into @testselect nid = newid() union all select nid = newid() union all select nid = newid() union allselect nid = newid() union all select nid = newid() union all select nid = newid()order by 1select 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 ) aorder by a.nid[/code]Results:[code="plain"]nid vbin l_vbin r_vbin l_bigint r_bigint------------------------------------ ---------------------------------- ------------------ ------------------ -------------------- --------------------4133B328-4043-4A44-9213-07E12E739234 0x28B333414340444A921307E12E739234 0x28B333414340444A 0x921307E12E739234 2932744137742500938 -79209786558862084604CD74D85-6338-49E5-BA08-18022410F816 0x854DD74C3863E549BA0818022410F816 0x854DD74C3863E549 0xBA0818022410F816 -8841173771094858423 -504175338536717719429F137BE-F5AD-4082-8838-1A9C87C63F66 0xBE37F129ADF5824088381A9C87C63F66 0xBE37F129ADF58240 0x88381A9C87C63F66 -4740054921469656512 -8631119426260418714BE73EB30-7189-4AD8-B905-1EF2B188E8AE 0x30EB73BE8971D84AB9051EF2B188E8AE 0x30EB73BE8971D84A 0xB9051EF2B188E8AE 3525038395534465098 -511464777409987157051CB86F6-D234-4E47-8417-D936D9E27F13 0xF686CB5134D2474E8417D936D9E27F13 0xF686CB5134D2474E 0x8417D936D9E27F13 -682634743861065906 -8928428906631758061822AAE67-2F2E-4723-B267-DBBC75CDB54D 0x67AE2A822E2F2347B267DBBC75CDB54D 0x67AE2A822E2F2347 0xB267DBBC75CDB54D 7470955570463187783 -5591258809880431283[/code]Sat, 23 Feb 2013 20:12:48 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Paul White (2/23/2013)[/b][hr][quote][b]Lynn Pettis (2/23/2013)[/b][hr]I did qualify that it was in my experience. Could be they are being converted to strings before being sorted.[/quote]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.[/quote]I do, lack of knowledge.Sat, 23 Feb 2013 19:39:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Lynn Pettis (2/23/2013)[/b][hr]I did qualify that it was in my experience. Could be they are being converted to strings before being sorted.[/quote]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.Sat, 23 Feb 2013 19:35:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Paul White (2/23/2013)[/b][hr][quote][b]Lynn Pettis (2/22/2013)[/b][hr]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.[/quote]They're not strings and don't sort as strings:[code="sql"]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;[/code][img]http://www.sqlservercentral.com/Forums/Attachment13250.aspx[/img][url]http://blogs.msdn.com/b/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx[/url]More details here:[url]http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx[/url][/quote]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.Sat, 23 Feb 2013 19:18:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Lynn Pettis (2/22/2013)[/b][hr]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.[/quote]They're not strings and don't sort as strings:[code="sql"]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;[/code][img]http://www.sqlservercentral.com/Forums/Attachment13250.aspx[/img][url]http://blogs.msdn.com/b/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx[/url]More details here:[url]http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx[/url]Sat, 23 Feb 2013 19:01:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Jeff Moden (2/22/2013)[/b][hr][quote][b]Lynn Pettis (2/22/2013)[/b][hr]I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?[/quote]Yes, but not directly.[code="sql"]SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)[/code][/quote]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!!Fri, 22 Feb 2013 22:25:08 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]Lynn Pettis (2/22/2013)[/b][hr]I haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?[/quote]Yes, but not directly.[code="sql"]SELECT CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)[/code]Fri, 22 Feb 2013 21:40:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspxI haven't tried, but are you sure you can convert a uniqueidentifier to a bigint?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.Fri, 22 Feb 2013 21:08:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]ramesh.sadhu21 (2/22/2013)[/b][hr]If you covert this New ID as integer then you can easily identify uniqueness and if you order by this then you will get the result in manner[/quote]Never the less and to confirm what Howard is alluding to, the older versions of NEWID() where TYPE 1 GUIDs, which were guaranteed to absolutely be unique provided there was no duplication of MAC addresses ever.That is no longer the case in SQL Server. MS changed to a TYPE 4 GUID for NEWID() quite some time back because of the security aspects of being able to reverse engineer the MAC address from the TYPE 1 GUIDs. TYPE 4 GUIDs are nothing more than a pseudo-random number with a couple of reserved columns (one is always a "4" to identify the GUID as a TYPE 4 GUID). Even though there are a HUGE number of possible GUIDs (if each GUID were a mile long, the domain of GUIDS would span 14 Quadrillion Milky Way galaxies), even MS states that TYPE 4 GUIDs are actually NOT guaranteed to be "globally unique".I'm also unsure why you would need to convert them to INTs (BIGINTs would actually be required) to confirm uniqueness. Just add a unique index to the column (as you should) and call it a day.Fri, 22 Feb 2013 16:31:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspxIf you covert this New ID as integer then you can easily identify uniqueness and if you order by this then you will get the result in mannerFri, 22 Feb 2013 05:12:19 GMTramesh.sadhu21http://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]SQL_Enthusiast (2/21/2013)[/b][hr]Bhuvnesh, Thank you for the response. [quote]why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code.[/quote]I didn't mean random like, "What is my eight year boy going to do next?" That is truly random. I meant, there has to be a rhyme or reason (aka logic) behind how to "randomly" generates the 32 bit alphanumeric code. Does anyone know the logic on how it builds that character string? Does it use the MAC address, date, time, tap into my deepest thoughts and encrypt them :w00t:[/quote]Older versions used to tie to MAC addresses, I think, but I believe the later versions use a pseudo random seed (for all intents and purposes, this is considered pretty similar to "real" randomness) rather than anything that's specifically tied to a specific machine. You may ask how that's guaranteed to be globally unique, and in the strictest sense, it's not. But you have to comprehend just how big a GUID is and just how low the chance of collision is. It's a difficult thing for a lot of DBAs to accept as we're programmed to want everything to be guaranteed, regardless of the odds, but in reality, there are all sorts of logical guarantees in SQL Server that at extreme probabilities are violated. E.g. any kind of table constraint could be violated by hard disk/RAM corruption that happens to corrupt in a logically consistent way. CPU malfunction could cause calculation errors etc.The odds against the collision of any two GUIDs generated randomly is in a similar or higher order of magnitude to these examples.Fri, 22 Feb 2013 03:16:57 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspxBhuvnesh, Thank you for the response. [quote]why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code.[/quote]I didn't mean random like, "What is my eight year boy going to do next?" That is truly random. I meant, there has to be a rhyme or reason (aka logic) behind how to "randomly" generates the 32 bit alphanumeric code. Does anyone know the logic on how it builds that character string? Does it use the MAC address, date, time, tap into my deepest thoughts and encrypt them :w00t:The ARD.SYSID is generated when the record is safed by the end user in the application. I'm pulling that ARD.SYSID into my select query and ordering by it. Therefore, the NEWID() was already set and should not give me a new value each time. I was more curious on how it gets ordered regardless of which ASC or DESC method I use, but I think you "kind of" answered that. Thank youThu, 21 Feb 2013 07:12:53 GMTSQL_Enthusiasthttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspx[quote][b]SQL_Enthusiast (2/20/2013)[/b][hr] it can't just be random because it's supposed to be unique, correct?)[/quote] why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code. [quote][b]SQL_Enthusiast (2/20/2013)[/b][hr]2. How does a NEWID() get ordered by? [/quote] you will get random order as it will give you new values every time. try [code="sql"]ORDER BY NEWID() ASC [/code]in your any testing query[quote][b]a. Does it look at the most left character(s) and say, "You start with an "H...", you with a "BD...", you with a "BA..." you with a "9...", and you with a "3..."; therefore, return you in this order BA..., BD..., H..., 3..., 9...")[/quote] Yes, a kind ofWed, 20 Feb 2013 23:16:26 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1422347-392-1.aspxOkay, I have generalize SQL quesiton that I think belongs in this forum more than the General Quesitons. In my query, I'm ending it with: [code="sql"]ORDER BY ARD.SYSID DESC[/code]SYSID is a NEWID() generated for every new record entered into the system. 1. How does it get calculated? (i.e. What logic is used to create a NEWID() - it can't just be random because it's supposed to be unique, correct?)2. How does a NEWID() get ordered by? a. Does it look at the most left character(s) and say, "You start with an "H...", you with a "BD...", you with a "BA..." you with a "9...", and you with a "3..."; therefore, return you in this order BA..., BD..., H..., 3..., 9...")Thank you in advance!Wed, 20 Feb 2013 16:09:07 GMTSQL_Enthusiast