Thoughts on using guid and identity as the primary key

Generally, adding a "meaningless" primary key to all tables in the database can greatly simplify program development. What type is used for this primary key? In fact, each type can be up to 900 bytes in size, but the two most common choices are GUID (uniqueidentifity) and identity Int.

In the "5.2.2 select primary key" section of ADO. NET 2.0 advanced programming, we have made some comparisons and recommend using the guid type as the primary key type. However, in this article, Liu will introduce his feelings in actual development.

When Liu writes a program in the company, the vast majority of databases use identity as the primary key. When he goes home to write his own program, he is superstitious about ADO. NET 2.0 advanced programming, so the database design uses guid as the primary key type. Therefore, there are some ideas about these two primary key types.

GUID is better than identify

First, for primary keys of the guid type, you can use guid when creating a new data entry. the newguid () method generates a primary key for it. For a primary key of the identify type, the primary key value can be obtained only when the data entry is actually inserted into the database. When data needs to be inserted into multiple related tables in the same transaction, if the guid primary key is used, all data can be inserted using one commit operation. If the identify type is used as the primary key, you must insert the data in the table where the primary key column is located, and then the data in the table where the foreign key column is located. See the following two sections of code.

// Use guid as the primary key type
Using (mydatacontext DB = new mydatacontext ())
{
Category C = new category
{
Id = guid. newguid (),
Name = "Category 1"
};
Article A = new article
{
Id = guid. newguid (),
Title = "Title 1 ",
Body = "content ",
Categoryid = C. ID // the ID of the category object has been determined.
};
DB. categories. insertonsubmit (C );
DB. Articles. insertonsubmit ();

DB. submitchanges (); // One commit. If an error occurs, all rollback is performed automatically.
}

// Use identity as the primary key type
Using (mydatacontext DB = new mydatacontext ())
{
Category C = new category // because the identity type is used as the primary key, the primary key value cannot be explicitly specified either.
{
Name = "Category 2"
};
DB. categories. insertonsubmit (C );
DB. submitchanges (); // The id value can be obtained after submission.

Article A = new article
{
Title = "the title is coming again ",
Body = "NO content ",
Categoryid = C. ID
};
DB. Articles. insertonsubmit ();
DB. submitchanges (a); // The second commit. If an error occurs, Category C will remain in the database.
}

Copy code

As described in the comment, when an error occurs, a single commit can be rolled back in a simple and clean manner; however, multiple commits have to take some measures.

Identify is superior to guid

First, identify is basically an int, which only occupies 4 bytes, while guid occupies 16 bytes. Of course, this is nothing to ignore.

Second, you need to know that the primary key is a clustered index, which means that the physical order of data on the disk is the same as that of the primary key. There is a problem. The GUID generated each time is not from small to large in chronological order. In other words, the guid generated for the second time may be smaller than the first time, the third time may be smaller than the second time. This results in sorting the entire row of data in the table every time a row of data is inserted. Fields of the identify type can clearly ensure that the generated values are larger than the previously generated values. Therefore, newly inserted data is simply appended to the end of the table. Therefore, for tables that frequently Insert new data, the performance of the identify primary key is better. (Note: This article is purely speculative by Liu and has not been tested .)

Finally, the identify primary key is easier for humans to read. Hmm ...... For all scenarios. In the company, I do not have the permission to view the database in the production environment, so when the customer submits a bug, I will shout to my boss: help me retrieve the data of users whose ID is 1234 on the server ~~ Think about it. If the guid primary key is used, what should I say?

-----

Note: writing it here is over. But I found that some tend to identify the primary key. I hope you don't have such prejudice. Take a good look at ADO. NET 2.0 advanced programming and consider it. You are welcome to discuss it here.

 

From: http://www.cnblogs.com/AndersLiu/archive/2008/07/03/primer-key-guid-vs-identify-int.html

Generally, adding a "meaningless" primary key to all tables in the database can greatly simplify program development. What type is used for this primary key? In fact, each type can be up to 900 bytes in size, but the two most common choices are GUID (uniqueidentifity) and identity Int.

In the "5.2.2 select primary key" section of ADO. NET 2.0 advanced programming, we have made some comparisons and recommend using the guid type as the primary key type. However, in this article, Liu will introduce his feelings in actual development.

When Liu writes a program in the company, the vast majority of databases use identity as the primary key. When he goes home to write his own program, he is superstitious about ADO. NET 2.0 advanced programming, so the database design uses guid as the primary key type. Therefore, there are some ideas about these two primary key types.

GUID is better than identify

First, for primary keys of the guid type, you can use guid when creating a new data entry. the newguid () method generates a primary key for it. For a primary key of the identify type, the primary key value can be obtained only when the data entry is actually inserted into the database. When data needs to be inserted into multiple related tables in the same transaction, if the guid primary key is used, all data can be inserted using one commit operation. If the identify type is used as the primary key, you must insert the data in the table where the primary key column is located, and then the data in the table where the foreign key column is located. See the following two sections of code.

// Use guid as the primary key type
Using (mydatacontext DB = new mydatacontext ())
{
Category C = new category
{
Id = guid. newguid (),
Name = "Category 1"
};
Article A = new article
{
Id = guid. newguid (),
Title = "Title 1 ",
Body = "content ",
Categoryid = C. ID // the ID of the category object has been determined.
};
DB. categories. insertonsubmit (C );
DB. Articles. insertonsubmit ();

DB. submitchanges (); // One commit. If an error occurs, all rollback is performed automatically.
}

// Use identity as the primary key type
Using (mydatacontext DB = new mydatacontext ())
{
Category C = new category // because the identity type is used as the primary key, the primary key value cannot be explicitly specified either.
{
Name = "Category 2"
};
DB. categories. insertonsubmit (C );
DB. submitchanges (); // The id value can be obtained after submission.

Article A = new article
{
Title = "the title is coming again ",
Body = "NO content ",
Categoryid = C. ID
};
DB. Articles. insertonsubmit ();
DB. submitchanges (a); // The second commit. If an error occurs, Category C will remain in the database.
}

Copy code

As described in the comment, when an error occurs, a single commit can be rolled back in a simple and clean manner; however, multiple commits have to take some measures.

Identify is superior to guid

First, identify is basically an int, which only occupies 4 bytes, while guid occupies 16 bytes. Of course, this is nothing to ignore.

Second, you need to know that the primary key is a clustered index, which means that the physical order of data on the disk is the same as that of the primary key. There is a problem. The GUID generated each time is not from small to large in chronological order. In other words, the guid generated for the second time may be smaller than the first time, the third time may be smaller than the second time. This results in sorting the entire row of data in the table every time a row of data is inserted. Fields of the identify type can clearly ensure that the generated values are larger than the previously generated values. Therefore, newly inserted data is simply appended to the end of the table. Therefore, for tables that frequently Insert new data, the performance of the identify primary key is better. (Note: This article is purely speculative by Liu and has not been tested .)

Finally, the identify primary key is easier for humans to read. Hmm ...... For all scenarios. In the company, I do not have the permission to view the database in the production environment, so when the customer submits a bug, I will shout to my boss: help me retrieve the data of users whose ID is 1234 on the server ~~ Think about it. If the guid primary key is used, what should I say?

-----

Note: writing it here is over. But I found that some tend to identify the primary key. I hope you don't have such prejudice. Take a good look at ADO. NET 2.0 advanced programming and consider it. You are welcome to discuss it here.

 

From: http://www.cnblogs.com/AndersLiu/archive/2008/07/03/primer-key-guid-vs-identify-int.html