Professional Web Applications Themes

Datetime Question - Microsoft SQL / MS SQL Server

You can use QA to find out for yourself: just tell it to show the estimated query plan, or the actual query plan. The answer is: it depends on whether or not the index is usable (just because there's an index does not mean it will be used - sometimes a scan will be chosen by the optimizer). However, if the index is usable, it will be used with "between" See this page: [url]http://www.sql-server-performance.com/transact_sql.asp[/url]. The keyword is "sargable" HTH, Bob Barrows Shamim wrote: > SQL 2K > > I understand if we use a CONVERT function in a datetime column, ...

  1. #1

    Default Re: Datetime Question

    You can use QA to find out for yourself: just tell it to show the estimated
    query plan, or the actual query plan.

    The answer is: it depends on whether or not the index is usable (just
    because there's an index does not mean it will be used - sometimes a scan
    will be chosen by the optimizer). However, if the index is usable, it will
    be used with "between"

    See this page: [url]http://www.sql-server-performance.com/transact_sql.asp[/url]. The
    keyword is "sargable"

    HTH,
    Bob Barrows

    Shamim wrote:
    > SQL 2K
    >
    > I understand if we use a CONVERT function in a datetime column, it
    > will not make use of index attached to the column.
    >
    > I have a SP with 2 datetime arguments, if I use the argument in the
    > below format , will it make use of index ??
    >
    > Arguments :- in_startdatetime, in_enddatetime
    >
    > declare in_cvt_startdatetime varchar(10), in_cvt_enddatetime
    > varchar(10) select in_cvt_startdatetime = CONVERT(varchar(10,
    > in_startdatetime,101) select in_cvt_enddatetime =
    > CONVERT(varchar(10, in_enddatetime,101)
    >
    > select * from mytable where movedate between in_cvt_startdatetime
    > and in_cvt_enddatetime
    >
    > ** What are all the ways to compare a datetime column to make use of
    > index ??
    >
    > Thx
    > Sh

    Bob Barrows Guest

  2. #2

    Default Re: Datetime Question

    The short answer to "will it make use of the index?" is NO. The answer to
    "how do you get it to use the index" isn't short :).

    BUT - what I don't understand is why you're passing in a string that looks
    like a date and then converting to varchar? If it's a date then keep it as a
    date and SQL Server will use the index? BUT if you really want to convert to
    something other than a date and get SQL Server to use indexes then there are
    a couple of options:

    1) You could store only the converted version of the column (and have a
    trigger, stored procedure or the application convert the data to the version
    you really want to index/lookup) BUT the problem with this approach is that
    you'll need to convert back to a datetime datatype when you want to do
    datetime manipulation.
    2) You could look into using a computed column and then indexing that (there
    are numerous requirements for this though - see the BOL topic "Set Options
    that Affect Results". This works well when queries are highly selective.
    3) You could consider using an indexed view IF the queries are NOT highly
    selective.

    There are some good resources out there - especially the white paper on
    Indexed Views. Check out the
    [url]http://msdn.microsoft.com/library/en-us/dnsql2k/html/indexedviews1.asp?frame=true[/url].

    hth,
    kt

    --
    Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
    Kimberly L. Tripp
    President, SYSolutions, Inc. [url]www.SQLSkills.com[/url]
    Principal Mentor, Solid Quality Learning [url]www.SolidQualityLearning.com[/url]


    "Shamim" <shamim.abdulrailamerica.com> wrote in message
    news:%23zabDVXRDHA.2240TK2MSFTNGP11.phx.gbl...
    > SQL 2K
    >
    > I understand if we use a CONVERT function in a datetime column, it will
    not
    > make use of index attached to the column.
    >
    > I have a SP with 2 datetime arguments, if I use the argument in the below
    > format , will it make use of index ??
    >
    > Arguments :- in_startdatetime, in_enddatetime
    >
    > declare in_cvt_startdatetime varchar(10), in_cvt_enddatetime varchar(10)
    > select in_cvt_startdatetime = CONVERT(varchar(10,
    in_startdatetime,101)
    > select in_cvt_enddatetime = CONVERT(varchar(10, in_enddatetime,101)
    >
    > select * from mytable where movedate between in_cvt_startdatetime and
    > in_cvt_enddatetime
    >
    > ** What are all the ways to compare a datetime column to make use of index
    > ??
    >
    > Thx
    > Sh
    >
    >

    Kimberly L. Tripp Guest

  3. #3

    Default Re: Datetime Question

    I think one of us may have misread the question. With this query:
    select * from mytable where movedate between in_cvt_startdatetime and
    in_cvt_enddatetime

    If there is a usable index on movedate, there is nothing here preventing id
    from being used, unless I'm totally missing something ... (which is always
    possible <grin>)

    Bob Barrows

    Kimberly L. Tripp wrote:
    > The short answer to "will it make use of the index?" is NO. The
    > answer to "how do you get it to use the index" isn't short :).
    >
    > BUT - what I don't understand is why you're passing in a string that
    > looks
    > like a date and then converting to varchar? If it's a date then keep
    > it as a
    > date and SQL Server will use the index? BUT if you really want to
    > convert to something other than a date and get SQL Server to use
    > indexes then there are
    > a couple of options:
    >
    > 1) You could store only the converted version of the column (and have
    > a
    > trigger, stored procedure or the application convert the data to the
    > version
    > you really want to index/lookup) BUT the problem with this approach
    > is that you'll need to convert back to a datetime datatype when you
    > want to do
    > datetime manipulation.
    > 2) You could look into using a computed column and then indexing that
    > (there
    > are numerous requirements for this though - see the BOL topic "Set
    > Options
    > that Affect Results". This works well when queries are highly
    > selective. 3) You could consider using an indexed view IF the queries
    > are NOT highly selective.
    >
    > There are some good resources out there - especially the white paper
    > on
    > Indexed Views. Check out the
    >
    [url]http://msdn.microsoft.com/library/en-us/dnsql2k/html/indexedviews1.asp?frame=true[/url].
    >
    > hth,
    > kt
    >
    >
    > "Shamim" <shamim.abdulrailamerica.com> wrote in message
    > news:%23zabDVXRDHA.2240TK2MSFTNGP11.phx.gbl...
    >> SQL 2K
    >>
    >> I understand if we use a CONVERT function in a datetime column, it
    >> will
    > not
    >> make use of index attached to the column.
    >>
    >> I have a SP with 2 datetime arguments, if I use the argument in the
    >> below format , will it make use of index ??
    >>
    >> Arguments :- in_startdatetime, in_enddatetime
    >>
    >> declare in_cvt_startdatetime varchar(10), in_cvt_enddatetime
    >> varchar(10) select in_cvt_startdatetime = CONVERT(varchar(10,
    > in_startdatetime,101)
    >> select in_cvt_enddatetime = CONVERT(varchar(10,
    >> in_enddatetime,101)
    >>
    >> select * from mytable where movedate between in_cvt_startdatetime
    >> and in_cvt_enddatetime
    >>
    >> ** What are all the ways to compare a datetime column to make use of
    >> index ??
    >>
    >> Thx
    >> Sh

    Bob Barrows Guest

  4. #4

    Default Re: Datetime Question

    As far as I can see :
    An index if present would be used.
    (Depending on the range length and the size of the table, but in general
    yes).

    Small tip :
    When comparing dates (or reals) for a range I would use

    WHERE movedate >= startdate and movedate < enddate
    or
    use enddate+1

    Why :
    If there are times in the fields, then you do not have to trunc the
    date's to get only the dates. Even when comparing with the times
    fields included it is always better to compare up to a certain value
    with dates, reals or fractionals.
    Suppose your enddate is today the 8th of july 2003. It
    is better to compare with less then 2003-07-09 00:00:00.000,
    because all times of 8th of july get included.
    (In this way you do not depend on how accurate a date and time
    is stored.)

    ben brugman





    Shamim <shamim.abdulrailamerica.com> schreef in berichtnieuws
    #zabDVXRDHA.2240TK2MSFTNGP11.phx.gbl...
    > SQL 2K
    >
    > I understand if we use a CONVERT function in a datetime column, it will
    not
    > make use of index attached to the column.
    >
    > I have a SP with 2 datetime arguments, if I use the argument in the below
    > format , will it make use of index ??
    >
    > Arguments :- in_startdatetime, in_enddatetime
    >
    > declare in_cvt_startdatetime varchar(10), in_cvt_enddatetime varchar(10)
    > select in_cvt_startdatetime = CONVERT(varchar(10,
    in_startdatetime,101)
    > select in_cvt_enddatetime = CONVERT(varchar(10, in_enddatetime,101)
    >
    > select * from mytable where movedate between in_cvt_startdatetime and
    > in_cvt_enddatetime
    >
    > ** What are all the ways to compare a datetime column to make use of index
    > ??
    >
    > Thx
    > Sh
    >
    >

    ben brugman Guest

Similar Threads

  1. PHP DateTime Formats
    By osalazar in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: July 4th, 03:05 PM
  2. datetime function
    By TaeHo Yoo in forum Macromedia ColdFusion
    Replies: 8
    Last Post: March 9th, 07:33 PM
  3. DATETIME
    By Anne in forum Microsoft SQL / MS SQL Server
    Replies: 34
    Last Post: August 25th, 07:06 PM
  4. [PHP] datetime
    By Larry Li in forum PHP Development
    Replies: 1
    Last Post: August 22nd, 08:44 AM
  5. datetime changes?
    By Travis Pupkin in forum ASP
    Replies: 0
    Last Post: July 18th, 07:21 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