Which index run first Clustered index or Non_Clustered index

Question

Which index run first Clustered index or Non_Clustered index

parthi-1705

SSCrazy Eights

Points: 9277
More actions
April 13, 2011 at 3:23 am

#237602

I am having Clustered index (not primay key)and Non-Clustered index on a table of 20 column.I would like to know which index will run first Clustered index or Non-Clustered index.The data in the table is about some 50K records.I want to just know which index will runs first on which basis
Thanks
Parthi

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean 'run first'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

parthi-1705

SSCrazy Eights

Points: 9277

April 13, 2011 at 3:45 am

#1310890

Which index will will Act First

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I still don't understand what you're asking.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hunterwood SSCrazy Points: 2074 More actions · Answer 4

I guess you're asking which index the Query Optimizer will choose to use when you are querying the table?

That depends on the query. The Optimizer will choose to use the most efficient index to satisfy the query.

If you want to know which index is used for a specific query, you activate "Include Actual Query Plan" in the "Query" menu in SSMS and run the query. Then you can have a look in the Query Plan and see what index was used...

Regards,

Markus

MUR SSC Rookie Points: 36 More actions · Answer 5

Hi,

I think, your question might be like this..

A table having clustered index (but the column not defined a primary key) and some other columns has non clustered index. The question is

MUR SSC Rookie Points: 36 More actions · Answer 6

Hi,

I think, your question might be like this..

A table having clustered index (but the column not defined a primary key) and some other columns has non clustered index. The question is

the sql server executes which clustered index periodically first.. or

which index is excutes first ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

armurugesh (4/17/2011)
A table having clustered index (but the column not defined a primary key) and some other columns has non clustered index. The question is
the sql server executes which clustered index periodically first.. or
which index is excutes first ?

Doesn't make any sense either. Indexes aren't executed and they don't act.

We're all guessing unless the OP comes back and clarifies their statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 8

It has to be something along the lines of which one will the optimizer pick first, but that's so subject to a million different factors, it's still not really possible to just answer without a lot more detail.

"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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

Hi

1.create table #Temp (t int,J int)

2.create clustered index T_temp on #Temp(t)

3.Select * from #Temp where t=0 and j=1

4.create nonclustered index T_tempNon on #Temp(j)

5.Select * from #Temp

6.Drop table #Temp

1.Table created

2.Clustered index created on the table

3.A select statement with execution plan

4.NonClustered index created on the table

5.A select statement with execution plan

6.Table has been droped.

At step 3 we can see that Clustered index scan takes place

At step 5 we can see that NonClustered index scan takes place

1.Why NonClustered index scan alone take place & not Clustered index

2.If we give where condition to "SELECT" statement both for "t" and "j" what index will goes first on which basis.

This is my need if i am wrong please correct me.

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

parthi-1705 (4/18/2011)
1.Why NonClustered index scan alone take place & not Clustered index

Because all the columns needed for the query are in the nonclustered index and SQL did not need to go back to the cluster (which is in general wider, though not in this specific case)

2.If we give where condition to "SELECT" statement both for "t" and "j" what index will goes first on which basis.

Depends what the where clause is, what columns and what order those columns are in the index, what % of the table is returned, whether or not the index is covering for the query, whether or not the predicate is SARGable or not, and a whole lot more factors.

p.s. it's impossible to draw meaningful conclusions about index usage on a table that has no rows. SQL knows it has no rows, any option is fast on 0 rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 11

Its depend on Execution Plan create by the optimizer which index will be use,It depends which query you are passing to the optimzer just create the query and then check the DISPLAY Estimated Plan will show you which index is using

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 12

Heh... great interview questions and scenario. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)