CODEWITHSUNDEEP
sqlsql-servert-sql
Konrad Viltersten
Konrad Viltersten

Reputation: 39068

Different result set despite (seemingly) equivalent query

I'm running the following queries (the only difference being that I'm applying a fix string in the first case and the predeclared variable name in the second one.

declare @name as varchar = 'directdebit'

select S.name as [Schema], T.name as [Table], 'Table' as Type
from sys.tables as T
inner join sys.schemas as S on S.schema_id = T.schema_id
where T.name like '%directdebit%'

select S.name as [Schema], T.name as [Table], 'Table' as Type
from sys.tables as T
inner join sys.schemas as S on S.schema_id = T.schema_id
where T.name like '%' + @name + '%'

Of course, I do realize that the concatenation of the string isn't equivalent and that the computer matches differently. However, I do get three different set sizes, depending on if I go without where (256 hits), if I go with concatenated where (163 hits) or if I go with the fixed where (45 hits).

What exactly am I being ignorant of? And how do I force the expression to be fully evaluated prior to filtration (adding parentheses didn't gave any difference).

Upvotes: 0

Views: 39

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Because your varchar is 1 char length:

DECLARE @name AS VARCHAR = 'directdebit';
SELECT '%' + @name + '%';

Result:

%d%

So it's quite straightforward that more results are found using LIKE '%d%' than LIKE '%directdebit%'

Declare your VARCHAR with fixed or MAX length:

DECLARE @name VARCHAR(MAX) = 'directdebit';

However, don't misuderstand. If you're casting some other value AS VARCHAR, it's going to work fine. For instance:

SELECT CAST(1024 AS VARCHAR); will return 1024 as as varchar, not a INT.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Always use length when using varchar() in SQL Server:

declare @name varchar(255) = 'directdebit';

The default length in this context is 1, so @name is being set to 'd'.

Upvotes: 2

Related Questions