Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning

    method = 
        case methoddescription 
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
    testmethod = 
        case methoddescription 
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
    result = 
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
    datecomplete = COALESCE(CONVERT(varchar(10), NULL, 101),'N/A')
from db2.dbo.view ls
    join db1.dbo.table l
        on =
    where item = '19003'
        and = '732820'

The view ([ls]) calls a remote server (the remote query %41 on the right of the plan).

Here is an image of the plan:


I only ask this question because of this blog post and I want to make sure this won't come back to bite me later.

share|improve this question
up vote 20 down vote accepted

Because we know that = '732820' and = then SQL Server derives that = '732820'


FROM   db2.dbo.VIEW ls
       JOIN db1.dbo.table l
         ON =
WHERE = '732820' 

is the same as

  ( /*...*/ FROM   db2.dbo.VIEW ls WHERE id = '732820'  )
  ( /*...*/  FROM   db1.dbo.table l WHERE id = '732820'  )

This rewrite is not bad for performance .

This derivation is a good thing. It allows SQL Server to filter out rows ... earlier than would otherwise be possible.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.