All Columns as Included

Question

All Columns as Included

VastSQL

SSC-Dedicated

Points: 36097
More actions
March 5, 2017 at 5:34 am

#335762

Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

VastSQL - Sunday, March 5, 2017 5:34 AM
Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.

Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.

Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered 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)

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

The write speed is probably the least of the problems (unless you've got 10+ indexes). You've just duplicated that table, meaning it's taking up twice the space, twice the memory, twice the network bandwidth, backup are larger, maintenance takes longer, etc.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

Jeff Moden - Sunday, March 5, 2017 12:05 PM
VastSQL - Sunday, March 5, 2017 5:34 AM
Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.
Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered indexes.

Included columns are for Non-Clustered indexes

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

GilaMonster - Sunday, March 5, 2017 12:10 PM
The write speed is probably the least of the problems (unless you've got 10+ indexes). You've just duplicated that table, meaning it's taking up twice the space, twice the memory, twice the network bandwidth, backup are larger, maintenance takes longer, etc.

Thanks Gail,

We have done this to avoid the scan and clustered index lookup? We will remove this for now and observe.

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

So, a key lookup operation is added cost. However, there is not reason to automatically attempt to remove that cost unless you determine that in fact that key lookup operation is the cause of performance headaches. Do you have measures that suggest you need to make that change? If so, it's possible (not likely, and very rare, and no, most people are not the exception that they think they are) that doing what you did with the index is worth all the overhead that Gail lists. However, you have to have very good measures to ensure that all that added overhead is offset by benefits. If you can't prove it absolutely, mathematically, don't do it.

"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: 1004339 More actions · Answer 6

VastSQL - Sunday, March 5, 2017 10:32 PM
Jeff Moden - Sunday, March 5, 2017 12:05 PM
VastSQL - Sunday, March 5, 2017 5:34 AM
Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.
Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered indexes.
Included columns are for Non-Clustered indexes

Yep. I know that and said nothing to the contrary. The reason why you can't INCLUDE columns in a Clustered Index is because all of them are already included in the CI.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

VastSQL - Sunday, March 5, 2017 5:34 AM
Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.

Never mind all of the "bad design" answers you've already received (all excellent): it's not even possible: the stated limit is 16 columns max or 900 bytes (whichever is smaller). There's also a list of data types that can't be included etc....

https://msdn.microsoft.com/en-us/library/ms190806.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Matt Miller (#4) - Monday, March 6, 2017 12:41 PM
it's not even possible: the stated limit is 16 columns max or 900 bytes (whichever is smaller).

That's the limit for the index key. Include columns can be many more than 16, can be just about any size.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

GilaMonster - Monday, March 6, 2017 1:07 PM
Matt Miller (#4) - Monday, March 6, 2017 12:41 PM
it's not even possible: the stated limit is 16 columns max or 900 bytes (whichever is smaller).
That's the limit for the index key. Include columns can be many more than 16, can be just about any size.

And that's what I get for answering too fast. Correct as always, Gail - thanks!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?