Data type "bool"? What are you running?

Also, your statement is only true for the small number of rows in your example. Its a different story with real world volumes. Remember, SQL will choose what it feels is an optimal execution plan based on statistics.

Run the code below and compare the execution plan for your example against the execution plan for a table with even 100 rows. You'll see what I mean.

create table #t (id int,ch char,na varchar(20),flag char(1))

insert into #t values (2,'A','jack','Y')

insert into #t values (5,'b','amy','N')

insert into #t values (1,'$','adams','N')

insert into #t values (3,'*','anna','Y')

insert into #t values (7,'@','rose','N')

insert into #t values (4,'&','smith','Y')

insert into #t values (6,'!','sue','Y')

create nonclustered index nc_t on #t (id,ch,na)

-- query 1

select na from #t where ch = '!'

-- query 2

select na from #t where id = 6 and ch = '!'

-- query 3

select na from #t where ch = '!' and id = 6

-- query 4

select na from #t where flag = 'Y' and id = 6 and ch = '!'

;with tally (N) as (select row_number() over(order by id) from master..syscolumns)

select N as ID, ch, na, flag

into #bigT

from tally

cross join #t t

where N <=100

create nonclustered index nc_bigT on #bigT (id,ch,na)

-- query 1

select na from #bigT where ch = '!'

-- query 2

select na from #bigT where id = 6 and ch = '!'

-- query 3

select na from #bigT where ch = '!' and id = 6

-- query 4

select na from #bigT where flag = 'Y' and id = 6 and ch = '!'

drop table #t

drop table #bigT