DB Index - Guid Vs Numeric - which is better for performance?

[Jayananda_Kotri]

Hi,

I was trying to find which type of primary key or index (GUID Vs Numeric Key) would perform better in case of large data volumes of billions of records.

Intutively I felt that Numeric index performance would be better but I do not find any conclusive evidence or suggestions.

Typically GUIDs would serve the purpose of uniqueness but rarely would any application use them to search for a record, the search would be performed using other fields and then be read accordingly.

I tried to check with a test table and a report program but the readings are too close and varying, also simulating a real life DB with billions of records is difficult.

If uniqueness is enforced would numeric keys result in better DB performance?

Any suggestions?

Thanks and regards,

Jayanand

[Former Member]

Hi Jyananda,

If uniqueness is enforced,it is sure that numeric keys result in better DB performance.

with regards,

Hema.

[Former Member]

Hi,

The GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters (these are HEX value characters that range from A to F and numeric values that range between 0-9). This column, because it is the primary key, is going to be stored in the clustered index (unless specified to be a non-clustered index), and will be the page pointer for each leaf page in a non-clustered index. Also, if a GUID is used instead of an integer identity column, then the binary rendering of 33 characters needs to be matched for each row that is returned using that column in the WHERE clause.

If a high volume of inserts are done on these tables, then the large size of the GUID's will contribute to page splits, potentially resulting in performance problems. Another disadvantage is that typically, primary keys are searched frequently, further contributing to additional server overhead. And trying to recall a GUID from memory, giving it to someone verbally, or manually typing a GUID value into a query, is not fun or easy.

If you need a key is unique across all servers, I would recommend using a composite key as the PK instead of a GUID.

regards,

vasavi

reward if it is helpful.

[Jayananda_Kotri]

Hi,

Thank you for the answers.

We are looking at a scenario where there would be billions of records with millions of inserts (transactions) daily.

Insert and search performance would be critical.

As of now it looks like we would have to use GUIDs anyway and are evaluating for performance.

Thanks and regards,

Jayanand