Difference between newid () and newsequentialid ()

1.: The newsequentialid function has the biggest advantage over the newid function: If you create an index on a uniqueidentifier field, the new value generated by using newid is not fixed, therefore, the new value leads to random changes to the index B + tree. While the new value produced by newsequentialid is regular, the changes of index B + tree are regular. Regular and irregular operations will lead to performance improvement.

 

2: uniqueidentifier is a very convenient task for primary keys. It has irreplaceable advantages in operations such as data merging.
However, due to the dispersion of common guids, if the primary key is added with the clustered index, the validity rate of the inserted records is greatly reduced.

A newsequentialid function is added to SQL Server 2005. The msdn explanation is as follows:
Create a guid on the specified computer that is greater than any guid previously generated through this function.
Newsequentialid () cannot be referenced in the query.
Note: This statement can only be used as the default value of the database column. You cannot execute statements similar to select newsequentialid ().
The GUID generated by newsequentialid () is unique only when the computer does not have a nic.
Note: This statement is incorrect. It should be the guid generated only when the computer has a nic.
You can use newsequentialid () to generate guid to reduce page contention on leaf-level indexes.

 

Example:

Create Table # dd
(
FID uniqueidentifier null default (newsequentialid ()),
Fname [nvarchar] (20 ))

 

Insert into # dd (fname) values ('dddff ')

 

Select * from # dd where FID> 'd8407c7d-0e7c-de11-94b0-001a4ddd5f17 'and FID <'e2507993-0e7c-de11-94b0-001a4ddd5f17'

 

 

However, using newsequentialid is not so smooth
1. How to obtain the generated guid
If the generated guid field is used as a foreign key for other tables, we need to obtain the generated value.
Generally, PK is an identity field. We can execute select scope_identity () after insert to obtain the newly generated ID.
However, since newsequentialid () is not an indetity type, this method cannot be used, and it can only be used in the default value. It cannot be selected before insertion. How can we get it? There are two methods:

-- 1. Define temporary table Variables
Declare @ outputtable table (ID uniqueidentifier)
Insert into Table1 (col1, col2)
Output inserted. ID into @ outputtable
Values ('value1', 'value2 ')
Select ID from @ outputtable

-- 2. Mark the ID field as rowguid (a table can have only one rowguid)
Insert into Table1 (col1, col2)
Values ('value1', 'value2 ')
-- Here, rowguidcol is actually equivalent to an alias
Select rowguidcol from Table1

2. How to set the default value to newsequentialid ()
When we set the default value through the UI, the following error occurs when we set the default value newsequentialid () because of the SQL Server 2005 bug (even SP2 is not resolved:
Warning were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?
'Table1' table
-Error validating the default for column 'id'
You can either click yes or create table.

You can call the Windows API to generate the sequential guid through the client. Although you can save the two troubles mentioned above, after my test, the effect is not so good.
I have created a table with two fields: ID and timestamp. Records are generated using newsequentialid () and client, and sorted by ID and timestamp.
The results of newsequentialid () are always the same. The client generation has some problems. If the program runs continuously and performs well, if the program continues to run after a period of time, the newly generated records are not necessarily greater than the previously generated records, the continuous running of each interval still performs well.
The code for generating the sequential guid from the client is as follows:

1 public static class sequentialguid
2 {
3 [dllimport ("rpcrt4.dll", setlasterror = true)]
4 static extern int uuidcreatesequential (Out guid );
5
6 public static guid newguid ()
7 {
8 const int rpc_s_ OK = 0;
9
10 guid;
11 int result = uuidcreatesequential (Out guid );
12 if (result! = Rpc_s_ OK)
13 {
14 throw new applicationexception ("create sequential guid failed:" + result );
15}
16
17 return guid;
18}
19}