Post reply

Preceding with N'

  • February 24, 2009 at 2:10 am

    #77259

    Hi

    The below query is invoked by apps.

    SELECT TOP 100 S.*, STA.VALUE STATUS_DESC FROM MSGS MG (NOLOCK) join STATUS STA on STA.STATUS=MG.STATUS where 1=1 AND (oid IN ( 100, 200 ) or pid IN ( 201,301 ) ) and Timein >= N'02/23/2009 04:09:19' and

    Timein >= N'02/23/2009 04:09:19'

    In Timein variable's value preceding with N'....

    1. How does sqlserver treat this

    2. What is disadvantage of preceding with N'

    3. Any performance issue

    Please clarify ....

    Thanks

    Kindest Regards,

    karthik

  • February 24, 2009 at 2:27 am

    #949195

    N Denotes that variable in nVarchar... No performance issue is related to this!

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • February 24, 2009 at 7:35 am

    #949359

    Actually if the column being compared to is nchar or nvarchar preceding the constant with N actually slightly improves performance as no implicit conversion has to take place.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 24, 2009 at 8:00 am

    #949385

    And if the column been compared to is char or varchar, preceding the constant with N can kill performance as it prevents index seeks. (Inplicit conversion on the column)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 24, 2009 at 8:34 am

    #949431

    anyone test how much of a performance impact this is? I think Jack is right, but it would be interesting.

    Article, anyone.....?

  • February 24, 2009 at 4:38 pm

    #949708

    Don't have time for article but I several times eliminated table/index scans by applying correct type to char constants.

    To avoid even thinking about this problem I'm trying everywhere:

    1) not to use constants in queries - there are look up tables for the purpose;

    2) declare variables with appropriate datatype and assign constrant values to them.

    _____________
    Code for TallyGenerator

  • March 2, 2009 at 8:41 pm

    #952206

    karthikeyan (2/24/2009)

    Hi

    The below query is invoked by apps.

    SELECT TOP 100 S.*, STA.VALUE STATUS_DESC FROM MSGS MG (NOLOCK) join STATUS STA on STA.STATUS=MG.STATUS where 1=1 AND (oid IN ( 100, 200 ) or pid IN ( 201,301 ) ) and Timein >= N'02/23/2009 04:09:19' and

    Timein >= N'02/23/2009 04:09:19'

    In Timein variable's value preceding with N'....

    1. How does sqlserver treat this

    2. What is disadvantage of preceding with N'

    3. Any performance issue

    Please clarify ....

    Thanks

    and Timein >= N'02/23/2009 04:09:19' and

    Timein >= N'02/23/2009 04:09:19'

    Probably not a performance issue, but that little chunk of computational heaven seems unnecessary. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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