Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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));
share|improve this question
    
cast(value as varbinary) will truncate the value to 30 bytes. You should avoid any functions on columns including casts if you care about performance as it almost always makes the expression unsargable. Why do you need to do this? –  Martin Smith Jul 12 '12 at 14:50
    
So then I need to specify the max length of the nvarchar column. I need to do a exact match and don't want to go the collate path. –  Daniel Jul 12 '12 at 15:12
1  
If the column is indexed 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. –  Martin Smith Jul 12 '12 at 15:28
    
The column is indexed, yes, but will it not evaluate the second part anyway? –  Daniel Jul 12 '12 at 17:40
    
It'll evaluate it, but it won't use any index, which is why the value = @p is first in the WHERE clause (because that will use an index). A type conversion like this will not use an index. –  user1240 Jul 13 '12 at 21:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.