Non cluster index vs non cluster index include

Question

Non cluster index vs non cluster index include

89netanel

SSCrazy

Points: 2130
More actions
February 12, 2017 at 2:31 pm

#327107

Hello all,
I noticed that there is 2 ways to create non cluster index
1) Regular
2) with include
What is the difference between Non cluster index and non cluster index with include
And when di ee use each option?
Thaks in advance!

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

89netanel - Sunday, February 12, 2017 2:31 PM
Hello all,I noticed that there is 2 ways to create non cluster index1) Regular2) with includeWhat is the difference between Non cluster index and non cluster index with includeAnd when di ee use each option?Thaks in advance!

They're both nonclustered indexes. They both have the key values in the B-tree. The leaf level contains all the key values. The difference is that when you have included columns, they're added to the leaf level. Including columns makes your index consume more space, but can cover queries that need to return the included columns.

Gail has an article (one of a series of 3) that covers nonclustered indexes at http://www.sqlservercentral.com/articles/Indexing/68636/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

89netanel - Sunday, February 12, 2017 2:31 PM
What is the difference between Non cluster index and non cluster index with includeAnd when di ee use each option?Thaks in advance!

Let's ask the horse! 😉
https://msdn.microsoft.com/en-US/library/bb522682.aspx

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

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Jeff Moden - Sunday, February 12, 2017 6:56 PM
89netanel - Sunday, February 12, 2017 2:31 PM
What is the difference between Non cluster index and non cluster index with includeAnd when di ee use each option?Thaks in advance!
Let's ask the horse! 😉
https://msdn.microsoft.com/en-US/library/bb522682.aspx

Looks like a broken link - That page's title says:

ALTER DATABASE
SET Options (Transact-SQL)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

89netanel SSCrazy Points: 2130 More actions · Answer 4

Hi Ed,

This is a great link!

This paragraph in the article helped me a lot:

"

Typically columns that appear only in the select clause of a query and not within the WHERE, FROM or GROUP BY are candidates for INCLUDE columns.

"

Thank you very much for your assistance!!!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

89netanel - Monday, February 13, 2017 11:33 AM
Hi Ed,This is a great link!This paragraph in the article helped me a lot:"Typically columns that appear only in the select clause of a query and not within the WHERE, FROM or GROUP BY are candidates for INCLUDE columns."Thank you very much for your assistance!!!

No problem. Thanks for the feedback.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions