NC index seek vs NC index scan with implicit conversion

  • Hi all,

    I've been looking thru our production plan cache and have found a number of implicit conversions. Most are of similar "type", such as NVARCHAR(4) --> VARCHAR(15). I built a small test script to see if there is any significant impact of the conversion: I discovered when there is no conversion, I get a NC index seek; when there is conversion, I get a NC index scan. But the cost, estimated vs. actual rows, etc. is the same (perhaps my sample size is too smal, or not "diverse" enough?)

    Any info is appreciated.

    SCRIPT:

    IF OBJECT_ID(N'JSHTest', N'U') IS NOT NULL

    DROP TABLE JSHTest;

    GO

    CREATE TABLE JSHTest

    (

    Col01 INT NOT NULL

    ,Col02 VARCHAR(15)

    );

    INSERT INTO JSHTest

    VALUES

    ( 1, 'ABC' ),

    ( 2, 'DEF' ),

    ( 3, 'GHI' ),

    ( 4, 'JKL' ),

    ( 5, 'MNO' ),

    ( 6, 'PQR' ),

    ( 7, 'STU' ),

    ( 8, 'GHI' ),

    ( 9, 'YZ' ),

    ( 10, 'GHI' );

    CREATE NONCLUSTERED INDEX JSHTest_NCIX01 ON JSHTest

    (Col02);

    ALTER TABLE JSHTest

    ADD CONSTRAINT JSHTest_PK PRIMARY KEY (Col01);

    UPDATE STATISTICS JSHTest

    WITH FULLSCAN;

    DECLARE @VarNVC NVARCHAR(4) = N'GHI';

    DECLARE @VarVC VARCHAR(15) = 'GHI';

    SELECT

    *

    FROM

    JSHTest

    WHERE

    Col02 = @VarNVC;

    SELECT

    *

    FROM

    JSHTest

    WHERE

    Col02 = @VarVC;

  • Your sample set is way too small. Everything's fast on 10 rows.

    Estimated and actual rows will be the same for the seek and scan, because it's the number of rows returned by the operator, not the number of rows it read.

    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
  • Thanks, I'll cook up a test on a much larger scale. I was hoping the "quick" test would show not only the operator difference but some indicator why the difference.

    Thanks Gail.

  • The why is pretty simple. Any function when used on a column makes the predicate non-SARGable, or not usable as a seek predicate. Implicit conversions are same as explicit conversions, same as any other function on the column.

    SELECT

    *

    FROM

    JSHTest

    WHERE

    Col02 = '' + @VarNVC;

    SELECT

    *

    FROM

    JSHTest

    WHERE

    Col02 = UPPER(@VarNVC);

    Same thing in both of those cases

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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