Query of 100 mil rows with multiple parameters

Question

Query of 100 mil rows with multiple parameters

Viewing 10 posts - 31 through 39 (of 39 total)

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

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

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 SSC Guru Points: 186127 More actions · Answer 2

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

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

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 SSC Guru Points: 186127 More actions · Answer 4

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

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

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 SSC Guru Points: 186127 More actions · Answer 6

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

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

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 SSC Guru Points: 186127 More actions · Answer 8

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

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

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

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

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