I am testing Merge join in sql server. I have an INNER JOIN
and force the optimizer to do MERGE JOIN
:
ID
in Personal Table is a Primary keyID
in Abteilung Table is a primary keyThere is no other Indexes in these tables
select * from [dbo].[Personal] as P inner join [dbo].[Abteilung] as A on P.[ID]= A.[Personal_ID] OPTION (MERGE JOIN)
then Optimizer uses this plan to run my query:
I would like to force optimizer to use index seek
instead of Table Scan
What I did to achieve this aim:
- Defining a grouped index in the Table Abteilung on columns
ID
,Personal_ID
. If I run the query, the execution plan is changes like this:
here I have Clustered index scan, but not clustered index seek
- Using
FORCESEEK
hint. If I am using this hint, an error occures by running the query:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
If I add a where clause in the query, then the query would be change to:
select * from [dbo].[Personal] as P inner join [dbo].[Abteilung] as A on P.[ID]= A.[Personal_ID] where A.[Personal_ID]=2 OPTION (MERGE JOIN)
My question:
How do I change my query to have Clustered Index Seek in the bottom line?