SQL Server Performance

Which below query will work better?

Discussion in 'SQL Server 2008 General DBA Questions' started by AJITH123, Aug 2, 2010.

  1. AJITH123 Member

    create table #test(id int, name varchar(100))
    insert into #test values (1,'aaa')
    insert into #test values (2,'bbb')
    insert into #test values (3,'ccc')

    declare @search varchar(100)= 'a'

    --(1)
    select * from #test where 1=1
    and ( name like isnull(@search,'') + '%') --OR @search is null

    --(2)
    select * from #test where 1=1
    AND ( @search IS NOT NULL AND (name like @search + '%')
    OR(@search IS NULL AND 1=1))
  2. FrankKalis Moderator

    You may want to look at Erland's article on dynamic search conditions over at http://sommarskog.se [:)]

Share This Page