Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

NEWID() - How does it work? Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 4:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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 DESC

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!
Post #1422347
Posted Wednesday, February 20, 2013 11:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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() ASC 

in your any testing query



[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...")
Yes, a kind of


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1422407
Posted Thursday, February 21, 2013 7:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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

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
Post #1422570
Posted Friday, February 22, 2013 3:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:31 AM
Points: 1,191, Visits: 9,882
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


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.
Post #1422948
Posted Friday, February 22, 2013 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 1:09 AM
Points: 3, Visits: 19
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
Post #1422990
Posted Friday, February 22, 2013 4:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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 manner


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423290
Posted Friday, February 22, 2013 9:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,704, Visits: 32,349
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

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 #1423314
Posted Friday, February 22, 2013 9:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423316
Posted Friday, February 22, 2013 10:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,704, Visits: 32,349
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

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 #1423319
Posted Saturday, February 23, 2013 7:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
ssc.png (190 views, 6.89 KB)
Post #1423379
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse