Open the query and set your database focus to your chosen database, then execute
SELECT OBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, ISNULL(STUFF((SELECT ', ' + QUOTENAME(c2.name) +
WHEN 0 THEN ' ASC'
ELSE ' DESC'
FROM sys.indexes i2 INNER JOIN sys.index_columns ic2
ON i2.object_id = ic2.object_id AND i2.index_id = ic2.index_id
INNER JOIN sys.columns c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id
ORDER BY ic2.object_id
FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 1, N''), 'HEAP') AS IndexColumnOrder
, i.index_id AS IndexID
, p.rows AS NumRows
, au.total_pages AS NumPages
, au.total_pages / 128 AS TotMBs
, au.used_pages / 128 AS UsedMBs
, au.data_pages / 128 AS DataMBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1 AND au.type_desc = 'IN_ROW_DATA'
AND p.partition_number = 1
ORDER BY OBJECT_NAME(i.object_id), i.index_id
Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.
SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.
There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.