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
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.
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.
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.