NEWID() - How does it work?
-
Okay, 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:
ORDER BY ARD.SYSID DESCSYSID 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!
-
SQL_Enthusiast (2/20/2013)
it can't just be random because it's supposed to be unique, correct?)why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code.
SQL_Enthusiast (2/20/2013)
2. How does a NEWID() get ordered by?you will get random order as it will give you new values every time. try
ORDER BY NEWID() ASCin your any testing querya. 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...")
Yes, a kind of
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh,
Thank you for the response.
why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code.
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 you
-
SQL_Enthusiast (2/21/2013)
Bhuvnesh,Thank you for the response.
why you think that random cant be unique ? sql server internally generate this 32 bit alphanumeric code.
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:
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.
-
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
-
ramesh.sadhu21 (2/22/2013)
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 mannerNever 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.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I 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.
-
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)
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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!!
-
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;
More details here:
http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
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;
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.
-
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
SQLkiwi blog
@SQL_Kiwi -
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.
-
Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.
declare @test-2 table (nid uniqueidentifier not null primary key clustered )insert into @test-2
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-2 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
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply