Uwe, do you have a test showing that creating a Clustered Index wouldn't improve the execution plan?
of course you can test it with a clustered index on one or both tables but that will not lead to a sufficient solution because neither a heap nor a table will solve the generic problem what the query optimizer has in this case
-- Would the estimates been better with clustered indexes?
CREATE UNIQUE CLUSTERED INDEX cuix_languages_id ON dbo.languages (language_id);
The execution plan will look nearby exactly the same as before. The only thing what changes is the TABLE SCAN which turns into a CLUSTERED INDEX SCAN.
BTW: The same result will happen if you put a clustered index on the [language] attribute.
The problem is coming from one special issue: The result from the outer table (dbo.languages) is not predictable: What Id will I get when I ask for the language = 'english').
Due to THAT fact Microsoft SQL Server has to guess. What does Microsoft SQL Server know in the moment when you hit [F5]?
- I will get ONE record out of two (50%) from dbo.languages!
- I have 8.100 records in the "inner table"
What would you now estimate? You don't have more information!
If I have 8.100 records I have a chance to get 50% of the records back = 4050.
Unfortunately I cannot upload depictions here - I will take the time in my holidays to write an article about it at simple-talk and will send the link to this discussion.
All the best from cold Germany...
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)