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