Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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
Or with this:
CREATE TABLE MYTABLE
    [ID]    UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    [NAME]  nVARCHAR(MAX)   NOT NULL
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
                FIrst INSERT INTO MYTABLE ( NAME ) VALUES ('Ashraf Abusada'); and then INSERT INTO MYTABLE ( NAME ) VALUES ('Ashraf Abusada'); -- didn't we just create a duplicate? Yes, stop using IDENTITY and don't use NEWID() or NEWSEQUENTIALID() for the same reason.
– onedaywhen
                Oct 26, 2016 at 7:46
                I'm suggesting a table with a key only on the IDENTITY column has no real data integrity and that you should rethink your entire approach rather than investigate other sequence numbers.
– onedaywhen
                Oct 26, 2016 at 8:22
                @onedaywhen ,what example you have taken to prove your point is wrong ?Name can be duplicate.It is not efficient to make varchar column PK.Suppose even if any varchar column like email are unique in any table then we don't make email PK.we take identity column as PK and check the duplicate conbstraint via code.
– KumarHarsh
                Oct 26, 2016 at 10:15
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.
                Guids are sortable. But due to their random nature a new record can be stuck anywhere in the index leading to index fragmentation
– Doctor Two
                Jun 25, 2020 at 9:50
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
                Can you elaborate on the difference between newsequentialid() and COMB GUIDs? Or provide a link to something explaining the differences?
– StriplingWarrior
                Aug 31, 2018 at 15:10
                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().
– JamieSee
                Oct 2, 2018 at 15:46
                this doesn't answer the question which asks for differences between newid() and newsequentialid() - actually it doesn't take differences into account at all.
– eis
                May 27, 2021 at 7:31
                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).
– Jeff Moden
                Jan 22, 2021 at 17:58
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.