July 16, 2008 at 7:24 am
I have these questions:
1. How to index an uniqueidentifier column?
2. What index should be used if uniqueidentifier column is the primary field in a table & if it is a non-primary field?
3. Also, how to achive partitioning using uniqueidentifier column?
July 16, 2008 at 7:54 am
You index a uniqueID column the same way you index any other column.
If it's the PK, it's indexed. The question is whether or not it should be the clustered index. Probably not, since a natural key for the table is probably better, but there might be cases where it could be.
You partition on it the same way you partition any other PK.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 8:17 am
The testing I've seen and read about suggests that, by and large, uniqueidentifiers are poor choices for indexes, especially clustered indexes, because of their width. I've also seen testing that showed the ordered uniqueidentifier, available in 2005, actually works fairly well.
Just some stuff to keep in mind.
"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
July 16, 2008 at 1:00 pm
I had this message posted here because I'm working with an application which is highly normalized & have uniqueidentifier as the primary key in all tables. When this application tries to retrieve a single record it takes a lot of time (you can go for a coffee break during this time :crazy: )
I'm going to have a tough time with it.
July 16, 2008 at 1:14 pm
You might check to see if the PK is the clustered index, and what level of fragmentation it's sitting at if so.
You might also check to see if the table and index statistics are up-to-date.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2008 at 5:22 am
What do the execution plans look like? Maybe it's not using the uniqueidentifier at all?
"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
July 17, 2008 at 2:21 pm
You should also look into the NEWSEQUENTIALID ( ) it can help a lot in these cases.
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply