問(wèn)題描述

我試圖在 SQL Server VB.net 中創(chuàng)建一個(gè) ID 列，該列將為數(shù)據(jù)庫(kù)中創(chuàng)建的每個(gè)新行提供一個(gè)數(shù)字序列.所以我使用以下技術(shù)來(lái)創(chuàng)建 ID 列.

I was trying to create an ID column in SQL server, VB.net that would provide a sequence of numbers for every new row created in a database. So I used the following technique to create the ID column.

select * from T_Users
ALTER TABLE T_Users     
ADD User_ID INT NOT NULL IDENTITY(1,1) Primary Key

然后我在數(shù)據(jù)庫(kù)中注冊(cè)了幾個(gè)用戶名，它工作得很好.例如，前六行是 1、2、3、4、5、6.然后我第二天又注冊(cè)了4個(gè)用戶，但是這次ID號(hào)從6個(gè)跳到了一個(gè)非常大的數(shù)字，例如:1、2、3、4、5、6、1002、1003、1004、1005.然后兩天后，我又注冊(cè)了兩個(gè)用戶，新行顯示為 3002,3004.所以我的問(wèn)題是為什么每隔一天我注冊(cè)用戶就會(huì)跳過(guò)這么大的數(shù)字.我用來(lái)創(chuàng)建序列的技術(shù)是錯(cuò)誤的嗎?如果它是錯(cuò)的，任何人都可以告訴我如何做對(duì)嗎?現(xiàn)在，當(dāng)我對(duì)上面使用的技術(shù)感到沮喪時(shí)，我嘗試使用順序生成的 GUID 值.GUID 值的序列生成得很好.但是，唯一的缺點(diǎn)是，它會(huì)生成很長(zhǎng)的數(shù)字(INT 大小的 4 倍).我的問(wèn)題是使用 GUID 是否比 INT 有任何顯著優(yōu)勢(shì)?

Then I registered few usernames into the database and it worked just fine. For example the first six rows would be 1,2,3,4,5,6. Then I registered 4 more users the NEXT day, but this time the ID numbers jumped from 6 to A very large number such as: 1,2,3,4,5,6,1002,1003,1004,1005. Then two days later, I registered two more users and the new rows read 3002,3004. So my question is why is it skipping such a large number every other day I register users. Is the technique I used to create the sequence wrong? If it is wrong can anyone please tell me how to do it right? Now as I was getting frustrated with the technique used above, alternatively I tried to use sequentially generated GUID values. The sequence of GUID values were generated fine. However, the only downside is, it generates a very long numbers (4 times the INT size). My question here is does using GUID have any significant advantage over INT?

問(wèn)候，

推薦答案

GUID 的好處:

如果您希望離線客戶端能夠創(chuàng)建新記錄，GUID 非常有用，因?yàn)楫?dāng)新記錄同步回主數(shù)據(jù)庫(kù)時(shí)，您永遠(yuǎn)不會(huì)遇到主鍵沖突.

GUIDs are good if you ever want offline clients to be able to create new records, as you will never get a primary key clash when the new records are synchronised back to the main database.

GUID 的缺點(diǎn):

GUIDS 作為主鍵會(huì)對(duì)數(shù)據(jù)庫(kù)的性能產(chǎn)生影響，因?yàn)閷?duì)于集群主鍵，數(shù)據(jù)庫(kù)希望按鍵值的順序保留行.但這意味著在現(xiàn)有記錄之間進(jìn)行大量插入，因?yàn)?GUID 將是隨機(jī)的.

GUIDS as primary keys can have an effect on the performance of the DB, because for a clustered primary key, the DB will want to keep the rows in order of the key values. But this means a lot of inserts between existing records, because the GUIDs will be random.

使用 IDENTITY 列不會(huì)受此影響，因?yàn)楸ＷC下一條記錄具有最高值，因此每次都將行添加到末尾.無(wú)需重新洗牌.

Using IDENTITY column doesn't suffer from this because the next record is guaranteed to have the highest value and so the row is just tacked on the end every time. No re-shuffle needs to happen.

有一個(gè)折衷方案是生成一個(gè)偽 GUID，這意味著您預(yù)計(jì)每 70 年左右就會(huì)發(fā)生一次密鑰沖突，但對(duì)索引編制有很大幫助.

There is a compromise which is to generate a pseudo-GUID which means you would expect a key clash every 70 years or so, but helps the indexing immensely.

其他缺點(diǎn)是 a) 它們確實(shí)占用了更多的存儲(chǔ)空間，并且 b) 編寫 SQL 確實(shí)很痛苦，即更容易鍵入 UPDATE TABLE SET FIELD = 'value' where KEY = 50003 比 UPDATE TABLE SET FIELD = 'value' where KEY = '{F820094C-A2A2-49cb-BDA7-549543BB4B2C}'

The other downsides are that a) they do take up more storage space, and b) are a real pain to write SQL against, i.e. much easier to type UPDATE TABLE SET FIELD = 'value' where KEY = 50003 than UPDATE TABLE SET FIELD = 'value' where KEY = '{F820094C-A2A2-49cb-BDA7-549543BB4B2C}'

您對(duì) IDENTITY 列的聲明在我看來(lái)很好.鍵值中的差距可能是由于嘗試添加行失敗所致.IDENTITY 值將增加，但該行永遠(yuǎn)不會(huì)被提交.不要讓它打擾你，它幾乎發(fā)生在每張桌子上.

Your declaration of the IDENTITY column looks fine to me. The gaps in your key values are probably due to failed attempts to add a row. The IDENTITY value will be incremented but the row never gets committed. Don't let it bother you, it happens in practically every table.

這個(gè)問(wèn)題涵蓋了我所說(shuō)的偽 GUID 的含義.在聚集索引上使用順序 GUID 鍵的 INSERT 不顯著更快

This question covers what I was meaning by pseudo-GUID. INSERTs with sequential GUID key on clustered index not significantly faster

在 SQL Server 2005+ 中，您可以使用 NEWSEQUENTIALID() 獲得一個(gè)隨機(jī)值，該值應(yīng)該大于以前的值.請(qǐng)參閱此處了解更多信息 http://technet.microsoft.com/en-us/library/ms189786%28v=sql.90%29.aspx

In SQL Server 2005+ you can use NEWSEQUENTIALID() to get a random value that is supposed to be greater than the previous ones. See here for more info http://technet.microsoft.com/en-us/library/ms189786%28v=sql.90%29.aspx

這篇關(guān)于使用 INT 或 GUID 作為主鍵的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！