How to increase indexes performance for fast queries results

Question

How to increase indexes performance for fast queries results

Viewing 5 posts - 16 through 19 (of 19 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

MSzI (9/27/2012)
Hello,
First off all I would suggest to create a unique clustered index, on the column that is a primary key.
In Your case this would be the ROWID column.

Don't do that unless you have frequent searches for ranges of ROWID, which (given the column name) seems rather unlikely.

Do you do frequent range searches on the key of one of your existing idexes (or on an initialt substring of that key)? If so, drop that index and recreate it as the clustered index. If not, do you do frequent range searches on something that currently isn't indexed? If so, create a clustred index on that. If neither applies, pick one of your existing indexes (perhaps the one with the shortest key?) and make it clustered.

Tom

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

neellotus07 (9/27/2012)
tell me should i create more clustered indexes or i need to create clustered index for fast query results.

TO create , add , remove or modify the indexes, first you need to study the filters (like where clause , JOINs , GROUP BY , ORDER BY etc ) on that table

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

neellotus07 (9/28/2012)
...
I created clustered index on ROWID this column never used in Where Clause.
...

[font="Verdana"]

Hopes you have re-build the non-clustered indexes after creating the clustered one. The reason behind is that every nonclustered index refers to clustered index internally.

-- Mahesh

[/font]

MH-09-AM-8694

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

No need to rebuild nonclustered indexes after creating a clustered index. The nonclustered indexes will all be rebuild automatically when a clustered index is created or dropped

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

GilaMonster (10/12/2012)
No need to rebuild nonclustered indexes after creating a clustered index. The nonclustered indexes will all be rebuild automatically when a clustered index is created or dropped

[font="Verdana"]Thanks Gail[/font]

MH-09-AM-8694