Alternative for Indexing on GUID 1

[wg26]

Hi Everyone:

I have a table, which use Uniqueidentifier as a Primary Key. Currently, I have two indexes on the table, one is clustered Index on the PK and the other is nonclustered composite index, which covers the rest of columns in the table....As I have learned that Setting Clustered Index on GUID could affect the performance, but I don't have another column which can be indexed...I am wondering if I can just set my composite index as clustered and leave GUID(PK) without index....Can anyone kindly give me some suggestion on this and what other choices I have? Thanks alot...

 

[chiph]

GUID's are typically used only when you need to assign a unique identifer to a row -- like an Invoice number, or a reference number of some kind. They're fairly expensive (in database terms), so you want to keep them to a minimum.

In your case, it sounds like you need an Identity column to use as your primary key, and then a UniqueIdentifier column to give you your GUID (assuming you still need it after using the Identity column). The Identity column will be your foreign key to other tables, and as such is automatically indexed. Putting a non-clustered index on a GUID can be done, but it should only be needed to speed up queries that use it in their WHERE clause.

Chip H.

 

[tlbroadbent]

If the GUID is the primary key and you want to insure uniqueness, you'll need the unique index on that column. It shouldn’t be clustered. I would choose a natural primary key if one exists on the table and create a unique index on the GUID column.

Clustered indexes should be small. I would not make the composite index clustered. In fact, I probably wouldn't make the composite on all columns minus the GUID column. That makes the index size as large as the data size. In addition, any update will cause indexes updates which could result in a lot of data movement.

Create indexes on columns that will be searched. If searches will routinely include three columns, make a non-clustered index on the three columns. If column searches will be made on various columns, separately, create separate indexes on those columns. A composite index is useless if the search doesn’t include the left-most column of the index. If there is one column that will be used most of the time for searches and it is relatively small, you may want to make it the clustered index.

Remember that clustered index keys are included on all non-clustered indexes. If you have a composite index comprising four columns with length of 100 characters, those 100 character keys will be carried on every non-clustered index created on the table. You could see significant increase in table size if the clustered index is large and several non-clustered indexes are created.
If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[wg26]

Thank you Chip and Terry for your helpful reply.

Another question I have is that if it will help to increase the performance if I put Index on the different filegroup, which resides on different hard drive? Thanks alot...

 

[SQLSister]

Yes, do put the index on a different hard drive if it has a separate drive controller. This should improve performance. It is also good to put the transaction log on a different drive as well.

 

[wg26]

Thank you SQLSister.I think I will do that.