February 16, 2011 at 6:05 am
Hello there
I have been having a read about best practices when designing a database.
I have developed an application using OIDs (object identifiers) for any class I will store in the database.
The idea is to have an unique identifier over all the tables/databases.
This OID will be created manually (won't use neither NEWID() nor NewSequenceID()), and its creation will follow a sequence, i.e., I will compose the OID with databaseId-class-...-SequentialNumber.
Having a read about Uniqueidentifier types, I've seen people don't recommend it when using it as PK on clustered index columns. But this problem comes mainly with semi-randomly created uniqueidentifier (using NewId() or NewSequenceId()), for database paging reasons.
My questions are:
1- Will I have any problem if I create my own -sequential- uniqueidentifier and use it as PK (clustered/non-clustered)?
2- How much better is to use 3 or 4 composite integer column as PK?
3 - Is there any improvement in SQL 2008 related to uniqueidentifier's clustered index issue?
Thanks!
February 16, 2011 at 6:53 am
muten79 (2/16/2011)
Hello thereI have been having a read about best practices when designing a database.
I have developed an application using OIDs (object identifiers) for any class I will store in the database.
The idea is to have an unique identifier over all the tables/databases.
This OID will be created manually (won't use neither NEWID() nor NewSequenceID()), and its creation will follow a sequence, i.e., I will compose the OID with databaseId-class-...-SequentialNumber.
Having a read about Uniqueidentifier types, I've seen people don't recommend it when using it as PK on clustered index columns. But this problem comes mainly with semi-randomly created uniqueidentifier (using NewId() or NewSequenceId()), for database paging reasons.
My questions are:
1- Will I have any problem if I create my own -sequential- uniqueidentifier and use it as PK (clustered/non-clustered)?
2- How much better is to use 3 or 4 composite integer column as PK?
3 - Is there any improvement in SQL 2008 related to uniqueidentifier's clustered index issue?
Thanks!
1 - You'll run into the same issues as you would with NewSequenceId, you're going to have a 16 bit column. This extends the storage at all levels of the cluster, allowing for fewer rows per page, reducing the efficiency of the index. Then you have to deal with the fact that the key for the clustered index is the reference value for all non-clustered indexes and will be stored with them, making them somewhat less efficient. The fact that you're sequencing the IDs reduces the biggest problem, fragmentation.
2 - Four integer columns would cumulatively add up to be as big as the uniqueidentifier and could cause some similar issues in terms of reducing the efficiency of indexes.
3 - Nope. It's how a clustered index is built that causes the fundamental fragementation issues that a completely random thing like uniqueidentifier creates. Sequencing the identifer helps eliminate the fragmentation, but it doesn't, and can't reduce the storage issue. But, you can look into storage compression as a way around that (my company sells a simply marvelous compression technology BTW).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2011 at 7:41 am
Thanks Grant.
I can see it now clear.
Will contact you in case of needing a compression software 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply