Databases Reference
In-Depth Information
CREATE CLUSTERED INDEX [_dta_index_SalesOrderDetail_c_5_1915153868__K5]
ON [dbo].[SalesOrderDetail]
(
[ProductID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF) ON [PRIMARY]
Listing 4-12.
In the next statement, and for demonstration purposes only, I will go ahead and create
the index recommended by the DTA but, instead of a regular index, I will create it as a
hypothetical index by adding the
WITH STATISTICS_ONLY
clause.
CREATE
CLUSTERED
INDEX
cix_ProductID
ON
dbo
.
SalesOrderDetail
(
ProductID
)
WITH
STATISTICS_ONLY
Listing 4-13.
You can validate that a hypothetical index was created by running the next query:
SELECT
*
FROM
sys
.
indexes
WHERE
object_id
=
object_id
(
'dbo.SalesOrderDetail'
)
AND
name
=
'cix_ProductID'
Listing 4-14.
The output is shown next below; note that the
is_hypothetical
field shows that this
is, in fact, just a hypothetical index.
object_id name index_id type type_desc is_hypothetical
---------- ------------- -------- ----- --------- ----------------
1915153868 cix_ProductID 3 1 CLUSTERED 1
Listing 4-15.