CODEWITHSUNDEEP
sql-serverperformanceindexing
nickc
nickc

Reputation: 291

Is it a good idea to always define a clustered index on a database table?

I am currently looking at a performance problem with a widely used .NET CMS system, and have a particular table with approximately 5,000,000 records in it which is the root cause of these problems, just querying the contents of this table takes up to 2 minutes on my local development environment.

Looking at the schema for the table, I have noticed there is just a single unique nonclustered index and no clustered index.

The table & index are defined as follows

CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] NOT NULL,
    [ItemId] [uniqueidentifier] NOT NULL,
    [Language] [nvarchar](50) NOT NULL,
    [FieldId] [uniqueidentifier] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Updated] [datetime] NOT NULL
)


CREATE UNIQUE NONCLUSTERED INDEX [IX_Unique] ON [dbo].[MyTable] 
(
    [ItemId] ASC,
    [Language] ASC,
    [FieldId] ASC
)

Does anyone have any suggestions around indexes on this table to improve query performance and specifically, is it generally good practice to always define a clustered index on a table?

Thanks

Upvotes: 4

Views: 220

Answers (3)

Whimsical
Whimsical

Reputation: 6355

Clustered index sorts the table on the index key and stores it physically in that order. Which is why only 1 clustered index can be defined on any table. It is advisable to have ur clustered index on unique values for best results.

If there are a variety of queries which access your table(using columns which are not in the clustered index) it would be better to have more non clustered index on those columns filterd by these queries.

check this msdn link for details on clustered index

Upvotes: 0

Yuck
Yuck

Reputation: 50865

I'm agreeing with Randy in that it depends on what the table is primarily going to be used for. This is a great article on "the clustered index debate".

There's way too much to sum up here, but in general INSERT is always faster with a clustered index, UPDATE is usually faster, and SELECT depends more upon other factors like having covering non-clustered indexes.

Upvotes: 1

Randy
Randy

Reputation: 16673

i don't think you can say 'always' good or bad.

do you have an explain plan for the query that does not perform?

if the where clause of that query does not use the indexed columns, then an additional index may help dramatically.

Upvotes: 3

Related Questions