The Cost of Function Use In A Where Clause

  • Comments posted to this topic are about the item The Cost of Function Use In A Where Clause

    Gregory A Jackson MBA, CSM

  • Good Article...

    I often see querries who do this wrong... Especially the "Date-30" querries.

  • Good article.

    Mostly we ignore this when we write T-SQL. That time our main concern is the right solution (mostly). Thanx for a good article.

  • Good article, I guess the same applies to the use of case statements in the where clause which the performance hit has caused us to rewrite a few SPs!!!

  • I found this a very understandable article. Assuming that everything that has been written is correct (and I have no reason to doubt that), this is very valuable information for me: it gives some understanding, plus practical, everyday examples with solutions. What else does someone wants to know ?

    I am eager to read other people's comments.

    Suggestion: see also the very valuable SSC article "TSQL LAB 2 - Writing Conditional WHERE Clauses", posted on February 27, 2008.


  • Thanks for this good Article

    If you wont use time data for searching you need to cut off the time information.

    The query can look like:

    [font="Courier New"]WHERE PlacedOnQueue <= DateAdd(mm,-30,DateAdd(dd,DateDiff(dd,0,GetDate()),0)) [/font]

  • Hi,

    I have table with one of its column(Created_Date) as smalldatetime, having index created on it.

    Now I have to query this table to get all records with created_date equal to some specific date. I am using following query for this

    select * from table_name where dateDiff(d,@varDate,Created_Date) =0

    But this query doesn't seems to be optimized as its not making any use of index created on Created_Date.

    Can any one help me on this.

  • try something along the lines of

    select * from table_name where convert(varchar(11), Created_Date, 106) = convert(varchar(11), @varDate, 106)

  • you can also try this

    [font="Courier New"]

    SELECT *

    FROM table_name

    WHERE Created_Date BETWEEN


    AND DATEADD(ms, -3,DATEADD(d,DATEDIFF(d,0,@varDate)+1,0))


  • Well this too will work, but my problem is still same if use convert in where clause it'll not make use of index and thus performane issue remains the same

  • Thanks

    I think this'll help me.

  • This looks complex but will use your index.

    where CreatedDate between dateadd(dd, datediff(dd,0,Getdate()),0) --midnight TODAY

    and dateAdd(ss,-1,dateAdd(dd,1,dateadd(dd, datediff(dd,0,Getdate()),0))) --23:59:59 TODAY


    Dave J

    Edit: Oops! I see w.lengenfelder posted this already! :w00t:
    "I don't know what I don't know."

  • Great article! I'm glad I read it, but now i'm having trouble writing one of my queries...

    I need to join 2 tables on smalldatetime columns. One of the tables stores the date and time and the other table doesn't. So table A has '2008-02-27 10:32:31' and table B has '2008-02-27 00:00:00' and I need to join them on table A date = table B date (ignoring the time info).

    Normally I use:


    TableA JOIN TableB

    ON Cast(Cast(TableA.DateColumn AS INT) AS Smalldatetime) =

    Cast(Cast(TableB.DateColumn AS INT) AS SmallDateTime)


    Will wrapping these columns in the JOIN clause also cause a degradation of performance like wrapping them in a where clause would?

    Is there a more optimized way of writing this?



  • What about using DATEDIFF on days where it equals zero?

    Still, joining on something like this is going to be problematic. Most problematic of all, you're joining on columns that clearly have no referential integrity. You can't reliably put these together.

    Nice article.

    You didn't mention implicit functions, like comparing a string to a datetime or an int to a smallint where SQL Server supplies a function for you even though you didn't write one.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Good article.

    One small quibble with it. In one of the examples, you use "((FullName1 = 'Ed Jones') OR (FullName1 IS NULL))", as an example of how to get Index Seeks, instead of Scans.

    In many cases, the moment you put an "or" in a Where clause, you get a scan instead of a seek. Not always, but it is something to watch out for. The same applies for "in ()" (since that's just a way to shorthand multiple "or" statements).

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 98 total)

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