Difference between NEWSEQUENTIALID() and NEWID()

In this article, we will be looking at the Difference between NEWSEQUENTIALID() and NEWID() and How NEWSEQUENTIALID() is generated.

NEWSEQUENTIALID() generates the unique identifier values sequentially.
Whereas NEWID() generates the unique identifier values randomly.

NEWSEQUENTIALID() function creates the GUIDs greater than the previously generated GUIDs, since the last restart of the system, because after restarting the system the next NEWSEQUENTIALID can be started from the lower range.
Both NEWID() and NEWSEQUENTIALID() are globally unique. Starting of NEWSEQUENTIALID after the restart of the computer does not affect its globally uniqueness.

NEWSEQUENTIALID() uses the default constraint, and it can't be used in SELECT or SET queries like NEWID().
The following query SELECT NEWSEQUENTIALID() will generate error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This will also generate the similar error

declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

while if you use
SELECT NEWID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

No error will be produced and guid will be returned.

NEWID() is not good for performance because it is generated randomly so that it increases the page splits in the indexes.
NEWSEQUENTIALID() can be used to generate GUIDs to reduce page contention at the leaf level of indexes.

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer.
GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.
NEWSEQUENTIALID() returns the value that includes the MAC of the NIC of the system. If the system has the network card, then it is guaranteed that the GUID generated using NEWSEQUENTIALID() will globally unique across the servers.
If the NEWSEQUENTIALID() used on a computer that doesn't have NIC, the values generated using NEWSEQUENTIALID() are not guaranteed to be unique across the servers, it will be unique only for that system.
Internally NEWSEQUENTIALID() usses a Windows API called UuidCreateSequential(), which generates the value based on the NIC's MAC address and an internal hardware timestamp.

Here you can see the Newsequentialid (Histrory/Benefits and Implementation)

SQL Server Performance Killers

There might be several individual factors that can kill the performance of your database. If you aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
You also need to look the hardware, operating and SQL Server settings
The main performance killers in SQL SERVER performance are as follows

• Poor Indexing
• Inaccurate statistics
• Excessive blocking and deadlocks
• No-set-based operations, usually T-SQL cursors
• Non set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and sub-queries. SQL has rich mechanism to get the data instead of trying to loop or row by row approach to be used in the code, this will kill the performance

• Poor query design
• Querying the data from a table that is not required, or using a table in joins that is not required.
• Poor database design
• No reusable execution plans
• Poor execution plans, usually caused by parameter sniffing
• Frequent recompilation of execution plans
• Improper use of cursors
• Always use set based tsql query. By using cursors, you add a large amount of overhead on SQL SERRVER.

• But if you are forced to use cursors, try to use the efficient cursor types such as fast-forward only.
• Remember cursors are called the performance killers.
• Improper configuration of database log
• Excessive use or improper use configuration of tempdb
• Every SQL Server instance has only one tempdb, which is used to store the information such as operations involving temporary tables, table variables, also operations such as sorts and row versioning, tempdb can become a bottleneck.