July 22, 2011 at 10:54 am
Hello All
This is my table
CREATE TABLE [dbo].[XYZ](
[DATE] [datetime] NOT NULL,--FOREIGN KEY
[EX_TRAN_ID] [bigint] NOT NULL,--FOREIGN KEY
[TP_CD] [int] NULL,
[ACCT_ID] [bigint] NOT NULL,--FOREIGN KEY
[MGR_ID] [bigint] NULL,--FOREIGN KEY
[ADD_DT] [datetime] NULL,
[IND] [char](1) NULL
)
1. Select * from XYZ
where DATE= '2010-01-31'
output: select,table scan
I have created a NON CLUSTERED INDEX ON DATE But When i was seeing in execution plan then it's say that missing index....
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[XYZ] ([DATE])
INCLUDE ([EX_TRAN_ID],[TP_CD],[ACCT_ID],[MGR_ID],[ADD_DT],[IND])
GO
*/
I Don't understand ...why it's saying that missing index?
What will i Do?
July 22, 2011 at 11:03 am
It's trying to get you to create a covering index. Just because SQL reports an index as missing doesn't mean you need to create it. There is a lot that goes into whether or not an index should be created.
If this query were a part of an application I would drop the * and put in just the columns I need and then, if there is a performance concern with the query, consider putting the included columns in. It would need to be looked at in light of all the queries that hit the data though, not just this one.
July 22, 2011 at 11:16 am
As soon as you perform a SELECT * and you're looking for an index seek, you could either make Date the clustered index or add another index with all columns included (this would basically be similar to a copy of the original table in a different logical sort order).
cfradenburg already mentioned some of the more appropriate alternatives than to create the index as recommended by the execution plan.
As a side note: I'd also recommend to rename the column DATE. It's a SQL keyword and it doesn't mean anything descriptive... 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply