Sql Function in where clause

Question

Sql Function in where clause

Gaurav Srivastava

SSChasing Mays

Points: 621
More actions
February 11, 2016 at 10:30 am

#324314

Is sql function such as Month, Year or day sucks the query performance if we use these in where clause.
If yes please suggest an alternative options???
I am using sql server 2008 r2
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

itsgaurav (2/11/2016)
Is sql function such as Month, Year or day sucks the query performance if we use these in where clause.
If yes please suggest an alternative options???
I am using sql server 2008 r2
Thanks

Not sure why you have this question in the section you do. It belongs in a sql location.

That being said, your question is extremely vague. Yes functions with columns as arguments in a where clause will render your query nonSARGable and as such the performance will suffer. We can help you find an alternative but you have to provide some actual details first.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gaurav Srivastava SSChasing Mays Points: 621 More actions · Answer 2

Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtl

Where MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014

In this table have approx 10 lacks rcords

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

itsgaurav (2/11/2016)
Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtl
Where MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014
In this table have approx 10 lacks rcords

Simple to remove these functions and add some range logic.

Select j.mdoc_no

, j.doc_no

, j.doc_dt

, j.del_dt

from jcdtl j

Where j.doc_dt >= '20140301'

and j.doc_dt < '20140401'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Sean Lange (2/11/2016)
itsgaurav (2/11/2016)
Select Jcdtl.mdoc_no,Jcdtl.doc,no,jcdtl.doc_dt,jcdtl.del_dt from jcdtl
Where MONTH(jcdtl.doc_dt)=3 AND YEAR(jcdtl.doc_dt)=2014
In this table have approx 10 lacks rcords
Simple to remove these functions and add some range logic.
Select j.mdoc_no
, j.doc_no
, j.doc_dt
, j.del_dt
from jcdtl j
Where j.doc_dt >= '20140301'
and j.doc_dt < '20140401'

That'll do it. And if you have an index on doc_dt, the query will be able to use it. Include doc_no and mdoc_no and it's a covering index. Of course, you'll want to design your indexes so they're used efficiently, which depends on how you use the table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions