INDEXING

Question

INDEXING

tony28

SSCertifiable

Points: 7108
More actions
May 7, 2013 at 5:36 pm

#277239

Hello,
i have maybe stupid question
why sometimes the SELECT use Noncluster index instead cluster index
I thought that still use PK like primary, if there is value what I searched, but if I created new one just with the searched value is faster.
Does someone any good explain about background indexing and using it?
CREATE NONCLUSTERED INDEX [IX_TB_INSPECTION_DETAIL] ON [dbo].[TB_INSPECTION_DETAIL]
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TB_INSPECTION_DETAIL] ADD CONSTRAINT [PK_TB_INSPECTION_DETAIL] PRIMARY KEY CLUSTERED
(
[LINE_CODE] ASC,
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[BODY_NO] ASC,
[ITEM_CODE] ASC,
[DEFECT_CODE] ASC,
[CREATE_DTTM] ASC,
[POSITION] ASC,
[POSITION_CODE] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
thx for response

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Calibear Ten Centuries Points: 1377 More actions · Answer 1

From my understanding, it is better to create your clustered index on primary keys, while leaving the non-clustered indexes to everything else you might search on. If you have a clustered index that has as many columns as yours, it might be more efficiently run for SQL to use the non-clustered index (which only has two columns to find the data.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 2

tony28 (5/7/2013)
Hello,
i have maybe stupid question
why sometimes the SELECT use Noncluster index instead cluster index
I thought that still use PK like primary, if there is value what I searched, but if I created new one just with the searched value is faster.
thx for response

Hey tony,

Its depend on SQL Server query optimizer to select a clustered or nonclustered index for any script(But in general optimizer select index on the basis of columns used in where clause).

If u r not using those columns which are in PK or clustered index then it not worth to use such clustered index instead of a nonclustered index which have those column which are used in where clause in your scripts.

tony28 SSCertifiable Points: 7108 More actions · Answer 3

hi, thx for reply

I didnt write all about it ..heh

these columns are in PK

when I executed query, in tab of execution plan was the label - MISSING INDEX and etc...

so according to this I created this index, and the query was faster ...

and I think that it will be like Calibear wrote

from my view now , optimizer will use smaller index ..

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 4

If u post select scripts too with index details that will be more helpful to see what happened and why.

tony28 SSCertifiable Points: 7108 More actions · Answer 5

here is simple select, I found in little difficult, but here you can see also

select ORDER_DATE from TB_INSPECTION_DETAIL WHERE ORDER_DATE='20130507' and COMMIT_NO='0085'

subtree cost

IX 0,0032838 - rows 1,62897

PK 0,0464984 - rows 1,62897

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

tony28 (5/8/2013)
here is simple select, I found in little difficult, but here you can see also
select ORDER_DATE from TB_INSPECTION_DETAIL WHERE ORDER_DATE='20130507' and COMMIT_NO='0085'
subtree cost
IX 0,0032838 - rows 1,62897
PK 0,0464984 - rows 1,62897

The query you are showing uses columns that are all within the nonclustered index you defined above. This makes the index into what is known as a covering index. In effect, for this query, it's a clustered index since all the information it needs is there. The optimizer is smart enough to recognize that the nonclustered index is going to be a better selection for this query because it's going to filter off the leading edge of that index whereas with the clustered index, the necessary columns, while in the key (that's a VERY large key by the way and may lead to other issues), would require more filtering.

As to those costs, they're useful for comparing operations to each other within a plan, but they're not very useful outside the plan because they are estimated costs, even on an actual execution plan.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning