Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Exclude Functions from where clause in Sql server

I have used below query in one of my stored procedures

SELECT officeid, rdate
FROM dbo.mytable
Where OfficeID   = OfficeID
  AND YEAR(h.rDate) = @year
  AND MONTH(h.rDate) BETWEEN 1 AND 4

The above query fails to be a SARG ( Search Argument) due to the usage of fuctions MONTH and YEAR in Where clause. This leads Index scan during stored procedure execution. Is there any way to rewrite the above query to handle the above logic (without function)

(P.S: rdate is datetime datetype and @year is INT datatype)

like image 853
bmsqldev Avatar asked Mar 12 '23 17:03

bmsqldev


2 Answers

Use Pseudo values..

This below function is SARGABLE(but will be lengthy) ,since CAST( DATETIME to DATE) is SARAGABLE,So Index will be used .

Example:

Cast(h.rDate as DATE) 
between datefromparts(@year,01,01) 
 and datefromparts(@year,04,30)
like image 55
TheGameiswar Avatar answered Mar 16 '23 22:03

TheGameiswar


Use date range comparisons. For example,

 SELECT officeid, rdate
    FROM dbo.mytable
    Where OfficeID   = OfficeID
    --Filter by dates that are between January 1st, midnight, inclusive, and
    --May 1st, exclusive, in the desired year
    AND h.rDate >= Convert(DateTime,'1/1/' + Convert(VarChar(4),@year))
    AND h.rDate < Convert(DateTime,'5/1/' + Convert(VarChar(4),@year))
like image 33
Robert Columbia Avatar answered Mar 16 '23 23:03

Robert Columbia