My table creation script is as follows
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[quotation_section]( [quotation_id] [varchar](24) NOT NULL, [section_id] [varchar](24) NOT NULL, [name] [nvarchar](100) NOT NULL, [description] [nvarchar](1000) NULL, [sort_order] [tinyint] NULL, [copied_from_library_flag] [bit] NULL, [max_sequence] [tinyint] NULL, [collaborator_id] [varchar](24) NULL, [status] [tinyint] NULL, [mandatory_flag] [bit] NULL, [read_only_flag] [bit] NULL, [draft_flag] [bit] NULL, [has_comment_flag] [bit] NULL, [revision_time] [datetime] NULL, [report_revision_time] [datetime] NULL, [last_submitted_max_sequence] [tinyint] NOT NULL, [buyer_code] [varchar](4) NULL, CONSTRAINT [PK_quotation_SECTION_01] PRIMARY KEY CLUSTERED ( [quotation_id] ASC, [section_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
In one of my database queries the execution planner is reporting the following:
<MissingIndexGroup Impact="25.8056"> <MissingIndex Database="[myschema]" Schema="[dbo]" Table="[quotation_section]"> <ColumnGroup Usage="EQUALITY"> <Column Name="[section_id]" ColumnId="2" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> </MissingIndexes>
I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed (unless I have messed up something here, sorry in that case). I do admit that other tables I am using for queries do have the columns present in both clustered and non-clustered indexes. So what should the approach be here? Does adding the missing index (as reported by SQL server execution planner) is a good idea?