When to use NEWID or NEWSEQUENTIALID and why

Question

I am trying to understand the practical real-life usage of NewId and 'NewSequentialId, I usually use the Identity property when creating new table like this:

CREATE TABLE MYTABLE
(
    [ID]    INT             PRIMARY KEY IDENTITY,
    [NAME]  NVARCHAR(MAX)   NOT NULL
);
GO

Is the above code not the best practice, If so should I replace it with this:

CREATE TABLE MYTABLE
(
    [ID]    UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    [NAME]  VARCHAR(MAX)    NOT NULL
);
GO

Or with this:

CREATE TABLE MYTABLE
(
    [ID]    UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    [NAME]  nVARCHAR(MAX)   NOT NULL
);
GO

Question: Is NewId or NEWSEQUENTIALID a replacement for Identity, if not when to use each one?

I am not trying to use the NewId or NewSequentialId as Primary Key, the code above is for demonstration purpose only, and maybe this code is better like this:

CREATE TABLE MYTABLE
(
    [ID]        INT                 NOT NULL PRIMARY KEY,
    [ROWGUID]   UNIQUEIDENTIFIER    ROWGUIDCOL DEFAULT NEWSEQUENTIALID(),
    [NAME]      nVARCHAR(MAX)       NOT NULL
);
GO

Solution 1

The primary use of GUIDs as primary keys is in distributed systems, where you want unique IDs among clients without the overhead of a roundtrip to the server/database.

They can come with their own problems, such as fragmentation if used as a clustering key (which can be mitigated with appropriate periodic index rebuilds).

I would not use SEQUENTIALID; instead use a 'Comb' GUID if you want ordering.

The Cost of GUIDs as Primary Keys

Solution 2

If I understood it right than a GUID is not always good in performance because it is not an sortable field for an index.
The sequentialid can overcome this problem by creating GUID's that are much better sortable and thus makes better index for your table.

I know it is a bit more complicated than this but I am trying to explain as simple as possible.

So if you want to use GUID as primary keys than I suggest using the sequentialID.

You should ask yourself why you want a GUID as primary key, if not needed than dont.

Comments

[Will I Am]

Doesn't really answer the OP's full question.

[StriplingWarrior]

Can you elaborate on the difference between newsequentialid() and COMB GUIDs? Or provide a link to something explaining the differences?

[JamieSee]

The article referenced is far out of date and although some of it still applies it's missing important information. From looking at it the so called COMB guid is pretty similar to what newsequentialid() does and thus the performance gains touted by the author are not really applicable. His comparison only accounts for newid() and not the newer newsequentialid().

[Doctor Two]

Guids are sortable. But due to their random nature a new record can be stuck anywhere in the index leading to index fragmentation

[Jeff Moden]

It also incorrect. NEWID() in SQL Server uses Type 4 GUIDs, which are nothing more than large random numbers with a huge domain of values (2^122 {6 bits are reserved for GUID Type and Version identification) but they are NOT guaranteed to be unique. Collisions are possible even between just 2 machines).

[eis]

this doesn't answer the question which asks for differences between newid() and newsequentialid() - actually it doesn't take differences into account at all.

[Mitch Wheat]

@eis: sometimes you have to answer the real question....