title: ORDER BY column that has allow null is slow. Why?
body: <p>So really my question is <strong>WHY</strong> this worked. </p>
  
  <p>Anyway, I had this query that does a few inner joins, has a where clause and does an order by on a nvarchar column. If I run the query WITHOUT order by, the query takes less than a second. If I run the query WITH order by, it takes 12 seconds. </p>
  
  <p>Now I had a great idea and changed all the INNER JOINs to LEFT JOINs. And also included the ORDER BY clause. That took less than a second. So I remembered the difference between LEFT JOINs and INNER JOINs. INNER JOINs check for NULL and LEFT JOINs don't. So I went into the table design and unchecked "Allow Nulls". Now I run the query WITH INNER JOINs and a ORDER BY clause and the query takes less than a second. <strong>WHY?</strong> </p>
  
  <p>From what I understand, the FROM, JOINS, WHERE, then SELECT clauses should run first and return a result set. Then the ORDER BY clause runs at the very end on the resultant record set. Therefore the query should have taken <strong>AT MOST</strong> a second, yes, even with the column allowing nulls. So why would the query take less than a second WITHOUT the order by clause, but take 12 seconds WITH order by clause? That doesn't make sense to me. </p>
  
  <p>Query below:</p>
  
  <pre><code>SELECT PlanInfo.PlanId, PlanName, COALESCE(tResponsible, '') AS tResponsible, Processor, CustName, TaskCategoryId, MapId, tEnd,
  CASE MapId WHEN 9 THEN 1 ELSE 2 END AS sor
  FROM PlanInfo INNER JOIN [orders].dbo.BaanOrders_Ext ON PlanInfo.PlanName = [orders].dbo.BaanOrders_Ext.OrderNo
  INNER JOIN [orders].dbo.BaanOrders ON PlanInfo.PlanName = [orders].dbo.BaanOrders.OrderNo
  INNER JOIN Tasks ON PlanInfo.PlanId = Tasks.PlanId
  INNER JOIN EngSchedToTimingMap ON Tasks.CatId = EngSchedToTimingMap.TaskCategoryId
  WHERE (MapId = 9 OR MapId = 11 or MapId = 13 or MapId = 15)
  AND([orders].dbo.BaanOrders_Ext.Processor = 'metest' OR tResponsible = 'metest')
  ORDER BY PlanInfo.PlanId
  </code></pre>