Sql inner query issue

Question

I have a table tbl_test:

create table tbl_test (
    tabid int identity
)

with the values:

 Insert into tbl_test values 1 union 2 union 3 .... union 1000

Query:

select MAX(b.tabid) from
(  
    select top 100 * from tbl_test
) as b  

I expect this query to return 100 but instead it returns 1000.

Answer 1

select top 100 * from tbl_test 

There is no explicit order on the inner statement, so there is no guarentee in which order the rows are read. If you order it by tabid ASC you should see the expected 100.

Answer 2

You're not including an order by clause in your subquery (which is allowed in conjunction with TOP), so there's no telling what records will come back. 1000 is obviously being included in the data returned from the subquery, which means it will be returned by MAX.