Inconsistent results with SQL Server Execution plan when using variables -

i have table 5 million records follows

id          baseprefix  destprefix  exchangesetid   classid 11643987    0257016         57016           1           3 11643988    0257016         57278           1           3 11643989    0257016         57279           1           3 11643990    0257016         57751           1           3 

sql tuning adviser recomended following index

create nonclustered index [exchangeidx] on [dbo].[exchanges] (     [exchangesetid] asc,     [baseprefix] asc,     [destprefix] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) 

however given following

declare @exchangesetid int = 1; declare @baseprefix nvarchar( 10 ) = '0732056456'; declare @destprefix nvarchar( 10 ) = '30336456'; declare @baseleft nvarchar( 10 ) = left(@baseprefix,4); 

these 2 queries give me vastly different execution plans

query 1

select top 1 classid   exchanges     exchangesetid = @exchangesetid    , baseprefix '0732' + '%'    , '0732056456' baseprefix + '%'    , '30336456' destprefix + '%'; 

---

query 2

select top 1 classid   exchanges     exchangesetid = @exchangesetid    , baseprefix @baseleft + '%'    , @baseprefix baseprefix + '%'    , @destprefix destprefix + '%'; 

---

the difference between 2 queries @baseleft , '0732' respectively

basically, in first example index used, , in second, not much

is there compelling reason why should so?

and if not fundamental flaw in thinking, how pass variable second query , make use of index?

the explanation of behavior tipping point ( #1, #2 ).

basically, depending on predicate's selectivity influences how many 8k data pages read buffer pool, sql server has 2 options filter rows:

1) index seek + key/rid lookup

2) table/[clustere] index scan.

why sql server use scan instead of seek + lookup? because, in cases (low/medium/medium-high selectivity) , using seek + lookup read buffer pool more pages single scan.

what do? should create covered index thus:

create nonclustered index ... on ... (...) include (classid);