Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ozSQLServer
Starting Member
32 Posts |
 Posted - 2011-09-05 : 00:25:44
|
| Hi,As I'm new to SQL Server with Oracle background, I've been trying to understand how to optimize a SELECT statment with indexes.Basically my question is: in order to make SQL Server use an index for a SELECT statment, should the CREATE INDEX command have thecolumn list used in the SELECT statement included in its INCLUDE keyword?Following the what I tested:[code]-- the select to optimize is:SELECT ORDER_TOTALFROM SA.ORDERSWHERE ORDER_DATE BETWEEN GETDATE()-60 AND GETDATE()-- first, the following index created:CREATE NONCLUSTERED INDEX [IX_ORDER_DATE]ON [SA].[ORDERS] ([ORDER_DATE])-- the execution plan showed the index wasn't used-- secondly: the index re-created using the following code:CREATE NONCLUSTERED INDEX [IX_ORDER_DATE]ON [SA].[ORDERS] ([ORDER_DATE])INCLUDE ([ORDER_ID],[ORDER_MODE],[CUSTOMER_ID],[ORDER_STATUS],[ORDER_TOTAL],[SALES_REP_ID],[PROMOTION_ID])-- the execution plan showed the index was used[code]Test on SQL Server 2008 on Windows 2003Cheers,ozSQL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-05 : 02:13:29
|
| depends on lots of other factors too. if query optimiser thinks that overhead in reading index keys from table and then doing bookmarklookup for getting the related fields is more, it might not use it.in the secodn case it was a covering index created specifically for the query. hence it was used as it had all required details in index table itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Why an index isn't being used