Performance comparison of a primary key with a GUID field using the From Increment field in the database

1. Overview:

In our database design, the primary key of database is essential, the design of the primary key has a great influence on the design of the whole database. I'll compare the performance of the AutoIncrement field with the GUID field, and welcome the discussion.

2. Introduction:

1. Self-increment field

Since increment fields are incremented sequentially each time, you can guarantee that the primary keys in one table are not duplicated. This is almost impossible unless you exceed the maximum value of the self-added field type and increment it from the beginning. Using the self-increment field to make the key is very simple, you can generally simply declare the self-added attribute when you are building the table.

The value of the increment is the need to maintain a global data value in the system, and the value is incremented each time the data is inserted. In concurrent environments in which the equivalent produces a unique identity, each incremental fetch must unlock the maximum global value to guarantee the uniqueness of the increment. This may be a concurrent bottleneck, with some performance issues involved.

It is a nightmare (I already suffer) from the possibility of duplication in the incremental field as the database migrates or imports data.

This is a problem with the incremental field if you want to make a distributed database. Because, in a distributed database, tables with the same name as different databases may need to be replicated synchronously. The increment value of a database table is likely to be duplicated by the increment value of the same table as another database.

2.uniqueidentifier (Guid) field

In a MS SQL database, you can create a table structure that specifies that the field type is uniqueidentifier, and that its default value can be generated by using NEWID () to generate a unique GUID (globally unique identifier). Use NEWID to generate a relatively random, if SQL 2005 can be generated sequentially using newsequentialid (), where NEWID () is used to accommodate the use of SQL 2000.

Guid: A number generated on a single machine that guarantees that all machines in the same space-time are unique, and that the algorithm is generated by Ethernet card address, nanosecond time, chip ID code, and many possible digits. The format is: 04755396-9a29-4b8c-a38d-00042c1b9028.

The advantage of a GUID is that the generated ID is unique, whether you are exporting data or doing step-by-step development, there is no problem. However, it generates a long ID, the database space to occupy more, with the loss of external storage prices, this also need not be considered. In addition, the GUID is not easy to remember, in this regard as an automatic increment field, It's not very convenient when you're debugging a program.

3. Test:

1. Test environment

Operating system: Windows Server 2003 R2 Enterprise Edition Service Pack 2

Database: MS SQL 2005

Cpu:intel (R) Pentium (r) 4 CPU 3.40GHz

Memory: ddrⅱ667 1G

Hard drive: WD 80G

2. Database Scripts

--From Increment field table
CREATE table [dbo].[ TABLE_ID] (
[Id] [int] IDENTITY (1,1) not NULL,
[Value] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
constr aint [pk_table_id] PRIMARY KEY CLUSTERED
(
[Id] ASC
) with (Ignore_dup_key = out) on [PRIMARY]
) on [PRI MARY] Go
--guid Field table
CREATE table [dbo].[ Table_guid] (
[Guid] [uniqueidentifier] not NULL CONSTRAINT [Df_table_guid_guid]

DEFAULT (NEWID ()),
[V　　Alue] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
CONSTRAINT [pk_table_guid] PRIMARY KEY CLUSTERED
(
[Guid] ASC
) with (Ignore_dup_key = out) on [PRIMARY]
) on [PRIMARY]
Go