Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2575

You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page

Nov 5th, 2009

Re: Get records from current year

Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used on the opposite side of the column it has no effect on the index seek and limited to no query cost.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Nov 6th, 2009

Re: Get records from current year

This might be true on SQL 2005. I compared execution plans and the cost was 50/50 using functions and constants. There was no discernable difference in retrieval speeds, but I wasn't using a large table to do so.
What I do know is a user had defined a hash function and was using it on the other side from the column. It was taking 13 seconds to retrieve the data that matched the hash. Changed the routine to use a constant varchar variable and the response time went to 25 milliseconds. I also know I read a book that says the function is executed against every row in a table and shouldn't be used when the result is a constant value.
Of course the book and I can be out of date now.

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

Permalink

Aug 21st, 2010

Re: Get records from current year

i am search in table admin columns
admin columns current year display in the system
admin columns datatype is DATE/TIME

please give me sql query

Tarang Raval

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Aug 2010

Permalink

Aug 23rd, 2010

Re: Get records from current year

The point isn't to avoid using functions at all, just don't use them in the query.

MS SQL Syntax (Toggle Plain Text)
DECLARE @dt1 datetime = CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101'
DECLARE @dt2 datetime = DATEADD(YEAR,1,@dt1)
-- You now have a range of dates for the current year in variables that remain constant in the query.
...WHERE DateOfInterest BETWEEN @dt1 AND @dt2
DECLARE @dt1 datetime = CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101'
DECLARE @dt2 datetime = DATEADD(YEAR,1,@dt1)
-- You now have a range of dates for the current year in variables that remain constant in the query.
...WHERE DateOfInterest BETWEEN @dt1 AND @dt2

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

Previous
1
Page 2

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Reading values from database

Next Thread in MS SQL Forum Timeline: Replication: problem of connecting interruption

DaniWeb Message

Cancel Changes

User Name
Password