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

Answer 1

If I understood it right, a GUID is not always good in performance because it is not a 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, then I suggest using the sequentialID.

You should ask yourself why you want a GUID as primary key; if not needed, then don't.

Answer 2

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