Index Design Question

I want to create an index that will cause the cost of the query to be as low as possible and also
must minimize the space that is used by the index. What type of index/parameters I can associate when I create an Index. I already have a clustered index

Only option will be non clustered index becuase you have already one clustered index...

You can't minimize the space used by the index, it is depends on columns used to create index...

but you can minimize the space used for index creation using SORT_TEMPDB option.

|||my query is like this
select productid,
sum(qty) as totalQTY,
count(*) as total
from table1
where desqty=2
group by productid

I have clusted index already defined in the id which is the identity

I have 2 options create non clustered on desqty and include product id as the included column
second is non clustered desqty and inculde both the productid and qty as included column

which one will be the better option?|||

Try both options and study the execution plans to find out for yourself.

You should find the second option to be the most performant as the index would be a 'covering index' and the base table would not need to be touched when the data is read.

Chris

|||

Turn on statistics IO by running the statement SET STATISTICS IO ON

Then, try running the query with the graphical execution plan turned on, and study the io statistics results.

I would try to create a "covering" index by creating an index that has desqty, productid, and qty (in that order) as regular index columns rather than included columns.

|||

Admin, there are some new features in SQL 2005.. whre you can name other columns in an index, and that will help make covered indexes so SQL Server might not need to query as many pages... see teh INCLUDE parameter of a CREATE INDEX...

Also, there are some new system views... that can help isolate missing stats/indexes...

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

A sample of how to join them follows...

SELECT mig.*, statement AS table_name,

column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

GO

Bruce