Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Query of 100 mil rows with multiple parameters Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 4:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547708
Posted Wednesday, March 5, 2014 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 7,127, Visits: 13,504
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
Post #1547713
Posted Wednesday, March 5, 2014 4:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547718
Posted Wednesday, March 5, 2014 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 7,127, Visits: 13,504
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
Post #1547730
Posted Wednesday, March 5, 2014 5:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547736
Posted Wednesday, March 5, 2014 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 7,127, Visits: 13,504
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
Post #1547755
Posted Wednesday, March 5, 2014 6:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547767
Posted Wednesday, March 5, 2014 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 7,127, Visits: 13,504
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
Post #1547776
Posted Wednesday, March 5, 2014 7:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547778
Posted Wednesday, March 5, 2014 7:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
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 2008, MVP
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

Post #1547781
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse