convert function not using index

[swoodring]

I have a query that I'm converting a datetime field to a datetimefield that will have the time be 00:00:00 so I can do a direct comparision so I can say something like this:
orderdate = '2001-07-15' otherwise I have to do
orderdate >= 2001-07-15 and < 2001-07-16

When I use the convert in my quereis it no longer uses my index but if I don't use the convert function it uses my index. Is there a way to get it to use the index while using the convert function. I'm using sql server 7.0 SP2

Here' the sql statement with the convert:
SELECT
dbo.Store.ZoneCode,
sum(dbo.SalesHeaderRetail.TotalMerchandise),
CONVERT(SMALLDATETIME,CONVERT(CHAR(10),dbo.SalesHeaderRetail.TransactionDate,101))
FROM
dbo.Store,
dbo.SalesHeaderRetail
WHERE
( dbo.SalesHeaderRetail.StoreNumber=dbo.Store.StoreNum )
AND (
CONVERT(SMALLDATETIME,CONVERT(CHAR(10),dbo.SalesHeaderRetail.TransactionDate,101)) > '07/15/2001 00:0:0'
)
GROUP BY
dbo.Store.ZoneCode,
CONVERT(SMALLDATETIME,CONVERT(CHAR(10),dbo.SalesHeaderRetail.TransactionDate,101))

Basically the query above doesn't use any indexes. If I take the convert out it uses and nonclustered index from store table and a nonclustred index from salesheaderretail. Is there anything I can do to have it use the indexes when using the convert.

 

[rajeevnandanmishra]

Hi there,
If you have created an index on TransactionDate then just
make change in your query like following :
---------
.
.
.
WHERE
( dbo.SalesHeaderRetail.StoreNumber=dbo.Store.StoreNum )
AND dbo.SalesHeaderRetail.TransactionDate = dbo.SalesHeaderRetail.TransactionDate AND
(CONVERT(SMALLDATETIME,CONVERT(CHAR(10),dbo.SalesHeaderRetail.TransactionDate,101)) > '07/15/2001 00:0:0'
)
GROUP BY
.
.
.
---------

Hope it helps

 

[swoodring]

I just tried your suggestion, but its still picking it up as a tablescan. And yes the index is on the transactiondate or at least partially. Its a composite index. Could that be the reason. and I'd have to have scenerios for all of the composite just like you do with transactiondate = transactiondate Stacy

 

[rajeevnandanmishra]

Hi swoodring,

SQL uses index if they were used in the where in the same order as they were created.
Like if you are having a composite index like :
col1, col2, col3, col4
And in your where clause you use like :
WHERE col1='...' AND col3='...' Then it will not use the Index.
So it is always good to create the index by keeping, generally used where clause in mind.

 

[tlbroadbent]

When you use a function in the Where clause, SQL Server cannot use the index. Even trying to force the use of an index with the method suggested by rajeevnandanmishra or by using an optimizer hint will not cause SQL to use the index and may in fact hurt performance.

Performance will be much better if you use the range seach criteria you had previously. SQL Server uses clustered indexes very effectively for range searches. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin