Are there any theoretical performance differences between these queries in the scenarios below.
Scenarios are:
MyTable has Value nvarchar(300)
MyTable has Value nvarchar(max)
Q1:
declare @p nvarchar(3);
set @p = 'Foo';
select foo from MyTable where cast(value as varbinary) = cast(@p as varbinary);
Q2:
declare @p nvarchar(3);
set @p = 'Foo';
/****
x below should be:
- 300 when table allows value to me nvarchar(300)
- `max` when table allows value to be `nvarchar(max)`
****/
select foo from MyTable where cast(value as varbinary(x)) = cast(@p as varbinary(x));
cast(value as varbinary)
will truncate the value to 30 bytes. You should avoid any functions on columns includingcasts
if you care about performance as it almost always makes the expression unsargable. Why do you need to do this?where value = @p AND cast(value as varbinary(x)) = cast(@p as varbinary(x));
would probably be better as at least that can use the index then discard those that aren't an exact binary match.value = @p
is first in the WHERE clause (because that will use an index). A type conversion like this will not use an index.