Hello all,

I had some SQL queries which are using department ID for join , filter , Group By and Select 

so , i am having index on department ID of my table File Master

scheme ..

CREATE TABLE [dbo].[FILE_MASTER](
[FILE_ID] [INT] IDENTITY(1,1) NOT NULL,
[DEPARTMENT_ID] [INT] NULL,

       [CLIENT_ID] [INT] NULL

       ,[LEAD_DETAIL_ID] [INT] NULL

       ,[FILE_NO] [NVARCHAR](50) NULL,
[INWARD_DATE] [DATETIME] NULL,
      [LOAN_SANCTION_DATE] [DATETIME] NULL,

 CONSTRAINT [PK_FILE_MASTER] PRIMARY KEY CLUSTERED 
(
[FILE_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] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [Indx_FM_department_ID] ON [dbo].[FILE_MASTER]

(
[DEPARTMENT_ID] ASC
)
INCLUDE ( [FILE_ID],
[FILE_NO],
[INWARD_DATE],
[CLIENT_ID],
[LOAN_SANCTION_DATE],
     ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

The above index only working when there is condition or group by on department ID .

and i when i am querying ..

SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward
FROM dbo.FILE_MASTER AS CL 
GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE

and the plan is showing Index scan on index Indx_FM_department_ID ..

can any one tell me why it is not using Index seek , i guess i have both group by Columns in cover index included columns 

what is the use of cover index then ??

because if i am giving where condition before group by for specific Client ID , Loan Sanction Date 

it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..

please advice ..

and i when i am querying ..

SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward
FROM dbo.FILE_MASTER AS CL 
GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE

and the plan is showing Index scan on index Indx_FM_department_ID ..

can any one tell me why it is not using Index seek , i guess i have both group by Columns in cover index included columns 

what is the use of cover index then ??

because if i am giving where condition before group by for specific Client ID , Loan Sanction Date 

it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..

You do not put any where clause in your query and then you expect it to to seek why would it go for seek you want to select all records for particular column , yes there is distict but Optimizer had to look for each row to fetch what your query satifies .

Group by is just for grouping on result set retuned by query it would not change scan to seek.

Bottom line add where clause backed by proper index and you would see scan turned into seek

Do not you have a WHERE condition?

CREATE TABLE #t (id  INT IDENTITY(1,1) NOT NULL , c1 CHAR(1))

INSERT INTO #t (c1) VALUES ('A')
GO 10000


CREATE INDEX myidx ON #t(id) INCLUDE (c1)


SELECT id,c1 FROM #t  ----Index scan
SELECT id,c1 FROM #t  WHERE id =5 ----Index seek

Hello Dilip - Without the where clause, the index will not be used at all. In other words, the query with no where clause will be equivalent to a table scan however since you have an Index therefore INDEX SCAN. Additionally, if had Clustered Index in this table then you would have got Clustered Index Scan.

Having Index but not utilizing it (In terms for appropriate where Clause) is almost same as performing a table scan with No real performance benefits. I would advise you to add a WHERE clause in your queries

Hope this helps !

Hi,

I suppose your confusion is that you have include columns those are part of your select list and still your query is not making use of that index.

You should be very clear that include columns (covering index) is useful to get rid of key lookups.

For instance, if you have used WHERE clause filtering DEPARTMENT_ID your query would have used nonclustered index without having to go through lookup as all columns in the  SELECT is the part of nonclustred index.

Also refer below article for further clarification on this topic.

Using Covering Indexes to Improve Query Performance

Thanks all , Got it.