SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query of 100 mil rows with multiple parameters


Query of 100 mil rows with multiple parameters

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
- The size stats of the two ordinary indexes is virtually the same.


Exactly.

A column cannot be present in an index twice. If a column is explicitly specified as part of the index and it is part of the clustered index key, it will be present in the index once. If it's specified as a key column, it will be a key column. If it's specified as an include column then, if the index is non-unique, it will be a key column (which it would have been anyway if it was just implicitly part of the index), otherwise it will be an include column.

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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16071 Visits: 19532
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
- The size stats of the two ordinary indexes is virtually the same.


Exactly.

A column cannot be present in an index twice.
Yes I know but...

If a column is explicitly specified as part of the index and it is part of the clustered index key, it will be present in the index once.
...I didn't know this.

If it's specified as a key column, it will be a key column. If it's specified as an include column then, if the index is non-unique, it will be a key column (which it would have been anyway if it was just implicitly part of the index), otherwise it will be an include column.


Thank you Gail. That's an interesting and useful lesson.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)

A column cannot be present in an index twice.
Yes I know but...


Not 'cannot be specified twice', actual 'cannot be present twice', so precluding SQL putting a column there a second time implicitly.

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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16071 Visits: 19532
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)

A column cannot be present in an index twice.
Yes I know but...


Not 'cannot be specified twice', actual 'cannot be present twice', so precluding SQL putting a column there a second time implicitly.


It's worth pointing out that this feature is well hidden. If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows in the script as an include column.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows in the script as an include column.


Of course it does. You specified the column as an include column, so the scripts show it as an include column because that's the way you defined the index. What you create is what you script out. It has to be.

Not sure what feature you're saying is well hidden there.

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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16071 Visits: 19532
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows in the script as an include column.


Of course it does. You specified the column as an include column, so the scripts show it as an include column because that's the way you defined the index. What you create is what you script out. It has to be.

Not sure what feature you're saying is well hidden there.


An include column which isn't part of the clustered index is treated differently to one that is.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows in the script as an include column.


Of course it does. You specified the column as an include column, so the scripts show it as an include column because that's the way you defined the index. What you create is what you script out. It has to be.

Not sure what feature you're saying is well hidden there.


An include column which isn't part of the clustered index is treated differently to one that is.


That's not a feature, that's an internal implementation detail. Asking that that be scripted differently is like asking for the script table to change your Varchar(x) column to varchar(max) because the total size of the row can go over 8000. It's not permitted by the relational model, the internal implementation details should be hidden from the user, the user shouldn't need to know them.

An include column is an include column. If you specify a column as include, you should assume, for all purposes, that it is an include column. What SQL does with it internally should not be a concern.
This is the other reason I prefer that columns which are needed in an index be explicitly specified where they are needed (key/include). The person looking at my create index should not need to understand index internals and know that clustered index keys are implicitly contained within the index before they can figure out what columns are needed in my index.

If you looked at this, would you say that was a covering index or not?

CREATE INDEX idx_test on SomeTable (Col1) include (Col3)

SELECT Col2, Col3 FROM SomeTable WHERE Col1 = 'A' and Col2 > 0



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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16071 Visits: 19532
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows in the script as an include column.


Of course it does. You specified the column as an include column, so the scripts show it as an include column because that's the way you defined the index. What you create is what you script out. It has to be.

Not sure what feature you're saying is well hidden there.


An include column which isn't part of the clustered index is treated differently to one that is.


That's not a feature, that's an internal implementation detail. Asking that that be scripted differently is like asking for the script table to change your Varchar(x) column to varchar(max) because the total size of the row can go over 8000. It's not permitted by the relational model, the internal implementation details should be hidden from the user, the user shouldn't need to know them.

An include column is an include column. If you specify a column as include, you should assume, for all purposes, that it is an include column. What SQL does with it internally should not be a concern.
This is the other reason I prefer that columns which are needed in an index be explicitly specified where they are needed (key/include). The person looking at my create index should not need to understand index internals and know that clustered index keys are implicitly contained within the index before they can figure out what columns are needed in my index.

If you looked at this, would you say that was a covering index or not?

CREATE INDEX idx_test on SomeTable (Col1) include (Col3)

SELECT Col2, Col3 FROM SomeTable WHERE Col1 = 'A' and Col2 > 0



It doesn't matter. Depending upon your depth of knowledge you run the risk of falling into one trap or another, all the way through until you reach "full and complete understanding". I think most folks do now know that the cluster keys behave similarly to include columns in nc indexes but won't yet know that you can add cluster keys to the INCLUDE list at very little cost. A quick surf backs this up: a huge number of hits, showing recommendations for removing cluster keys from INCLUDE lists.
If the person looking at your query is a bogstandard TSQL developer, what would you expect them to know about index internals? And next year?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
If the person looking at your query is a bogstandard TSQL developer, what would you expect them to know about index internals?


Nothing. I have to assume that because I'm working with client after client with people at various levels. Is the developer who I taught index internals going to be there next month? Is he going to remember, when looking at the scripts I give him, details of internals? Is he going to be in a rush? Is he going to pass it off to the brand new junior? 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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86544 Visits: 45244
ChrisM@Work (3/5/2014)
I think most folks do now know that the cluster keys behave similarly to include columns in nc indexes


Key columns, unless the nonclustered index is defined UNIQUE. Not similar to, identical to (except for the not appearing in script, but that's because they weren't specified explicitly)

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search