SQL Server : does NEWID() always gives a unique ID?

Question

Does the NEWID() function never give me the same ID as it already did? Let's say I select a NEWID() and it returns '1' (just as an example). Will it never return '1' again? Is it impossible?

The probability is so low, that for all intents and purposes it is impossible. — Mitch Wheat, Jul 28 '13 at 13:39
It is not *impossible*, because `NEWID()` is based on a pseudorandom number (plus the MAC address of the computer on which is is generated), so yes, it *could* happen. But it almost certainly won't. — David Faber, Jan 29 '15 at 12:57
Read more here: http://stackoverflow.com/questions/12550346/how-sql-server-creates-uniqueidentifier-using-newid — David Faber, Jan 29 '15 at 12:58

score 32 · Answer 1 · edited Mar 28 '19 at 02:40

Both NEWID() and NEWSEQUENTIALID() give globally unique values of type uniqueidentifier.

NEWID() involves random activity, thus the next value is unpredictable, and it's slower to execute.

NEWSEQUENTIALID() doesn't involve random activity, thus the next generated value can be predicted (not easily!) and executes faster than NEWID().

So, if you're not concerned about the next value being predicted (for security reasons), you can use NEWSEQUENTIALID(). If you're concerned about predictability or you don't mind the tiny performance penalty you can use NEWID().

However, in strict sense, there are still negligible chances that GUIDs generated by different machines have the same value. In practice, it's considered as being impossible.

If you want further info, read this: Which method for generating GUIDs is best for ensuring the GUID is really unique?

Note NEWID() complies RFC 4122. And the other function uses a Microsoft's algorithm for generating the value.

score 12 · Answer 2 · edited May 23 '17 at 12:10

12

If you're running NEWID() on the same machine then the return value will always be unique because it incorporates the current time stamp in its calculation.

On separate machines/systems, however, you could technically get the same id but the probability of that happening is so low that today's SQL DB community has essentially accepted that it IS impossible. Microsoft has more or less banked their reputation on it.

Community

1
1

answered Feb 09 '16 at 15:45

LCIII

3,102
3
26
43

1

what about asynchronous operations which has same timestamp ? – Muflix Mar 09 '17 at 14:56
1

ok, i tried some little tests and it seems ok, records with same timestamp has different newid on the same machine.. its probably due to random part of that function. – Muflix Mar 09 '17 at 15:04

score 3 · Answer 3 · answered Jan 05 '18 at 15:31

3

I had the same question, so I ran this simple query to see how unique the newid () could be, as you'll see there is no repeated IDs even in the same milisecond:

DECLARE @TRIES BIGINT, @ID UNIQUEIDENTIFIER, @REPEATED_ID UNIQUEIDENTIFIER
SET @TRIES = 1
SET @REPEATED_ID=NEWID()
WHILE @TRIES <= 1000
BEGIN
    SET @ID=NEWID() 
    IF @REPEATED_ID=@ID
        PRINT 'SAME -> ID '+CONVERT(NVARCHAR(MAX),@TRIES)+': '+ CONVERT(CHAR(36),@ID)
    ELSE
        PRINT 'DISTINCT -> ID '+CONVERT(NVARCHAR(MAX),@TRIES)+': '+ CONVERT(CHAR(36),@ID) + ' AT ' + CONVERT(VARCHAR,CAST(GETDATE() AS DATETIME2(3)) 
)
    SET @TRIES += 1
    SET @REPEATED_ID=@ID
END

You can define @TRIES as you wish.

answered Jan 05 '18 at 15:31

Francisco Morales

39
2

That proves little, because 1) T-SQL's interpreter is pretty slow and 2) not getting the same IDs in the same millisecond does not prove that, say, the values wouldn't be recycled next day, or next month, or next year. GUIDs *are* unique, but establishing that empirically isn't practical. – Jeroen Mostert Jan 05 '18 at 15:50
exactly, but this is only a guide. All depends that you want to do and for what do you need this IDs. – Francisco Morales Jan 12 '18 at 14:57

SQL Server : does NEWID() always gives a unique ID?

3 Answers3

Linked