Alan.B (3/19/2013)
If you have some sample data and DDL I can tell you what I think. I don't know if you have a clustered index on either table. If there was a clustered index or unique index you will likely see nested loops for the inner join queries.
You are right. I should have given the table structures. Here it is:
CREATE TABLE [dbo].[Names](
[_id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TableB](
[_id] [int] IDENTITY(1,1) NOT NULL,
[ChildCount] [int] NULL
) ON [PRIMARY]
I ran both these queries:
select n.[_id]
from Names n
inner join TableB b
on b.[_id] = n.[_id]
and b.[ChildCount] > 50
where n.[Name] Like 'A%'
select n.[_id]
from Names n
inner join TableB b
on b.[_id] = n.[_id]
and b.[ChildCount] > 50
and n.[Name] Like 'A%'
And you were right! In both cases, hash match was used for the merger.