Non Clustered Index

Question

Post reply

Non Clustered Index

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
November 9, 2022 at 5:03 pm

#4110601

Quick question If I create Non Clustered index on a table will that help if there is no Clustered Index on a table?
If yes how, I was in the impression to work Non clustered Index table should have clustered index or it will show Heap on the table

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

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

A non-clustered index may help.

In most cases, you should probably have a clustered index.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

Yes you can create a non clustered index on a heap, and yes(depending on the query/indexed and included columns) it will help.

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 3

Have a look at : "clustered-and-nonclustered-indexes-described"

and / or "Effective Clustered Indexes"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Measuring performance before and after the addition of the index and an examination of your execution plans (which are not a performance measure, merely a description of behavior) will tell you all you need to know.

And I agree with @ratbak, most tables should have a clustered index. And by most, I mean greater than 99%

"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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

IMHO, the best thing said on this thread is...

Grant Fritchey wrote:

Measuring performance before and after the addition of the index and an examination of your execution plans (which are not a performance measure, merely a description of behavior) will tell you all you need to know.

For everything else, beware of confirmation bias, especially about indexes. That has led many people into accepting supposed "Best Practices" that are actually worst practices and has misled people into thinking that things like non-sequential keyed indexes (such as Random GUIDs) are the worst thing you can do and believing that ever-increasing indexes are the best thing you can do.

The same holds true about Heaps v.s. Clustered Tables and much more.

Here are a couple of two word phrases to live by in the form of a sentance... "It Depends" which means "Try Everything" and "Be Skeptical" of peoples conclusions, especially your own and especially when it comes to indexes.

--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)

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

non-sequential keyed indexes (such as Random GUIDs) are the worst thing you can do
Not sure where you are going with this one. I've seen GUIDs used TOO many times where they were a poor choice to begin with. I agree that there is a certain confirmation bias. For example, I've seen many times the primary key should be the clustered key. This is often not true. But there are certain starting principles that hold in many if not most circumstances.