Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INDEXING


INDEXING

Author
Message
tony28
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Calibear
Calibear
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
suneet.mlvy
suneet.mlvy
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 70
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
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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 ..



How to post data/code on a forum to get the best help: Option 1 / Option 2
suneet.mlvy
suneet.mlvy
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 70
If u post select scripts too with index details that will be more helpful to see what happened and why.
tony28
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17641 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search