Understanding Index Part 2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Understanding Index Part 2

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-03-25 : 19:13:34

Hi there

Background:
I have about 30 Million plus records in the prf_BatchItems which is the way I see it like FACT table in datawarehouse. It is used to that BatchItems has PK and IDENTITY which is I dropped this PK cause it's not used at all and I replace with the question 1 below:

I have question as follow:

1) I've created an index:
CREATE CLUSTERED INDEX IX_prf_BatchItems_1
ON prf_BatchItems(BatchID, AccountNo, Code1, Code2, Code3)
WITH (FILLFACTOR = 100, PAD_INDEX = ON);

My questions is if I have a query for instance:

SELECT * FROM
WHERE BatchID = 23 and Code1 = 'XXXX'

Is it going to utilise this clustered index when this query run eventhough the WHERE statement is only utilise for BatchID and Code1 NOT as a complete list (BatchID, AccountNo, Code1, Code2, Code3)?

2) If I've created another additional such as:

CREATE INDEX IX_prf_BatchItems_1
ON prf_BatchItems(ProductDescription1)
WITH (FILLFACTOR = 100, PAD_INDEX = ON);

If I run the query like this:

SELECT * FROM
WHERE BatchID = 23 and Code1 = 'XXXX' AND ProductDescription1 = 'AAA'

Is the indexing utilising both CLUSTERED and NON CLUSTERED index in this matter? From my understanding is that is always used the clustered index and after that looking at additional one? Is this correct?

Thanks

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-25 : 20:29:45

Well the easy way for you to get a definite answer is to use the query analyzer. Setup a trace and view the log to see what was chosen.

As I understand it the query optimizer sets the execution plan which you can view with the analyzer.

There is probably more that can be added to this post but again the quick way to determine which index is used is to run the analyzer or look at the execution plan.

r&r

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 06:10:54

Rather than running a trace I would simply click "display estimated execution plan" or "include actual execution plan" on your toolbar above query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources