Temporary variables in SQL 2000 queries RRS feed

  • Question

  • Hello,

    I encountered the following problem on SQL Server 2000.
    I have a large table x and i want to search one of it's varchar fields using LIKE.

    The following query:
    select top 100 * 
    from MYDB.dbo.x  with (nolock) 
    where (mycol like 'john%'
    returns valid results in less then a second.

    However when I use the following query:

    declare @tmp varchar(255) 
    set @tmp = 'john%' 
    select top 100 * 
    from MYDB.dbo.x with (nolock) 
    where (mycol like @tmp) 
    then the query takes a lot (many seconds) longer.

    My question is why is this happening and are there any workarounds for this issue?

    Thursday, January 15, 2009 2:00 PM


All replies