Professional Web Applications Themes

Month(x) vs Between(x and y) - Microsoft SQL / MS SQL Server

With few exceptions, if a column is in an expression in a search condition, no index on that column will be useable by the query processor. In this case, Month() could hinder performance, by making it impossible to use an index on ADateField to execute the query. The alternative is not quite correct, though. You at least need quotes: .... between '20030801' and '20030830'. If you want the query to be identical to the Month() one, you also need to include ADateField values after midnight on the 30th and any time on the 31st. You might have business rules to ...

  1. #1

    Default Re: Month(x) vs Between(x and y)

    With few exceptions, if a column is in an expression in a
    search condition, no index on that column will be useable
    by the query processor.

    In this case, Month() could hinder performance, by making
    it impossible to use an index on ADateField to execute the
    query.

    The alternative is not quite correct, though. You at least need quotes:
    .... between '20030801' and '20030830'. If you want the query to
    be identical to the Month() one, you also need to include ADateField
    values after midnight on the 30th and any time on the 31st. You
    might have business rules to make it certain that no value of ADateField
    has a time part, or none is a weekend day, but it's safest, and just
    as efficient to write

    where ADateField >= '20030801' and ADateField < '20030901'

    The alternative also will not return any results in years before or
    after 2003, but the original will.

    Steve Kass
    Drew University

    London Developer wrote:
    >Hello,
    >
    >I wrote this query that had the line:
    >
    > Month(ADateField)=8
    >
    >and was told it is 'inefficient' and should be written:
    >
    >and DateEntered between (20030801 and 20030830)
    >
    >as it uses indexes and is more reliable e.t.c
    >
    >I countered this saying I thought the month function was easier to read and
    >can still use indexes and would also be efficient enough.
    >
    >Am I far wrong?
    >
    >
    >
    >
    Steve Kass Guest

  2. #2

    Default Re: Month(x) vs Between(x and y)

    You're right that the Month() function is much more readable, but it is true
    that using a column inside any function means that an index on the column
    cannot be used. The index is organized by the date value itself, not by some
    function of the date value. It is much too cost prohibitive to apply the
    function to every key value while searching the index, so the index is not
    even considered.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    [url]www.SolidQualityLearning.com[/url]


    "London Developer" <devnowhere.com> wrote in message
    news:OBmBQj#PDHA.1216TK2MSFTNGP11.phx.gbl...
    > Hello,
    >
    > I wrote this query that had the line:
    >
    > Month(ADateField)=8
    >
    > and was told it is 'inefficient' and should be written:
    >
    > and DateEntered between (20030801 and 20030830)
    >
    > as it uses indexes and is more reliable e.t.c
    >
    > I countered this saying I thought the month function was easier to read
    and
    > can still use indexes and would also be efficient enough.
    >
    > Am I far wrong?
    >
    >

    Kalen Delaney Guest

Similar Threads

  1. Get last day of month
    By YogeshM in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: October 1st, 03:03 PM
  2. Getting the day of the month
    By xyco45 in forum Macromedia Flex General Discussion
    Replies: 3
    Last Post: May 18th, 10:26 PM
  3. to convert a month to previous month
    By eddie wang in forum ASP
    Replies: 9
    Last Post: October 9th, 11:15 PM
  4. [PHP] LAST DAY OF MONTH
    By Ralph Guzman in forum PHP Development
    Replies: 1
    Last Post: August 15th, 08:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139