Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NEWID() - How does it work?


NEWID() - How does it work?

Author
Message
SQL_Enthusiast
SQL_Enthusiast
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 422
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!
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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;-)
SQL_Enthusiast
SQL_Enthusiast
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 422
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
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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.
ramesh.sadhu21
ramesh.sadhu21
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 32
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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.

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)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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!!

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
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: 10332 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
ssc.png (276 views, 6.00 KB)
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