4

Take the following minimally complete and verifiable example1 code:

USE tempdb;

DROP TABLE IF EXISTS dbo.t;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT t_pk
        PRIMARY KEY 
        CLUSTERED
        IDENTITY(1,1)
    , k sysname NOT NULL
        INDEX t_001
    , s sysname NOT NULL
        INDEX t_002
    , somedata varchar(1000) NOT NULL
        CONSTRAINT t_somedata_df
        DEFAULT REPLICATE('A', 1000)
    , INDEX t_003 (k, s)
);

INSERT INTO dbo.t (s, k)
SELECT sc1.name, sc2.name
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

CREATE STATISTICS t_st001 ON dbo.t (k) WITH FULLSCAN;
CREATE STATISTICS t_st002 ON dbo.t (s) WITH FULLSCAN;

The following query purposely uses the wrong variable types. I was hoping the query plan would include an implicit conversion warning, but it doesn't.

DECLARE @k char(128) = 'a';
DECLARE @s char(128) = '';
SELECT s 
FROM dbo.t 
    LEFT JOIN sys.syscolumns sc ON t.s = sc.name
WHERE dbo.t.s = @s
    OR dbo.t.k = @k;

Why is that?

The query plan is here.


1 - that's my website, BTW

0

1 Answer 1

10

Your parameters have a lower Data Type Precedence than the columns they are compared to. In this case the parameter values will be implicitly converted. This is a good thing. Converting the column values to the parameter’s type is problematic and will trigger the warning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.