Trent,
what you show here is similar to the following syntax:
CREATE
NONCLUSTERED INDEX [Your_index_name] ON [dbo].[GuidTest]
(
[my_pk] ASC,
[my_name] ASC
)and what I want to do with DDT is similar to :
CREATE NONCLUSTERED INDEX [Your_index_name] ON [dbo].[GuidTest]
(
[my_pk] ASC,
[my_name] ASC
)
INCLUDE
(
[my_other_field ASC,
[and_another_one_here] ASC
)
With the keyword include, the values of the "other fields" are store IN the index itself (really in the b-tree leaf corresponding to the record defined by the my_pk and my_name combination).
Say we have a clustered index on the PK identity column, so the index is physically the table itself, and we need to retrieve a row that whe define by the 3 fields. In my real table, these fields are cde_cmd, no_ligne_1, and cde_mat). They define the combination I need in one of the JOIN of a view. But in the SELECT statement of that view, I'll ask for cal_surf, surf_lq, lq_m2, and so on.
If these columns are in the INCLUDE statement of the index definition, their values will be brought up during the Non-Clustered index scan of the execution plan, and doing so, I shall avoid the cost of a Hash-Match table, using only a Merge Join.
The more you need columns in the select statement, the more you need to look at the INCLUDE clause in your indexes definition.
Does it make sense?