Geek Answers Handbook

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

I am currently considering a performance issue with the widely used .NET CMS system and have a specific table with approximately 5,000,000 entries in it, which is the main cause of these problems, it just takes 2 minutes to query the contents of this table in my local development environment.

Looking at the table schema, I noticed that there is only one unique non-clustered index and a clustered index.

The table and 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 for indexes in this table to improve query performance and, in particular, is it always good to define a clustered index in a table?

thanks

+4
source share
3 answers

I do not think you can say “always” good or bad.

Do you have an explanation plan for a query that is not being executed?

if the where clause of this query does not use indexed columns, then an extra index can greatly help.

+3
source

I agree with Randy that it depends on what the table is mainly used for. This is a great article on "cluster index discussions."

There is too much to summarize here, but in general INSERT always faster with a clustered index, UPDATE usually faster, and SELECT is more dependent on other factors, such as coverage of non-clustered indexes.

+1
source

The clustered index sorts the table in the index key and stores it physically in that order. This is why only one clustered index can be defined in any table. It is advisable to have an ur-clustered index by unique values ​​to achieve the best results.

If there are many queries that access your table (using columns that are not part of the clustered index), it would be better to have more non-clustered index for these columns filterd by these queries.

check this msdn link for more details on clustered index

0
source

More articles:


All Articles