title: In SQL Server, is a sort by short substring more efficient than a sort by the entire, long field?
body: <p>Consider the following SQL Server 2005/2008 query:</p>
  
  <pre><code>Select [UID], [DESC] From SomeTable Order By [Desc];
  </code></pre>
  
  <p>If Desc is a fairly long field (Varchar(125) with many entries > 70 chars) and you don't need a strict sorting, would it would be more efficient to do this:</p>
  
  <pre><code>Select [UID], [DESC] From SomeTable Order By Substring([Desc], 0, 20);
  </code></pre>
  
  <p>The advantage is that all comparisons are pretty short (20 characters, max).  The disadvantage is that it incurs the Substring call.  For present purposes, assume that you don't want to put an index on this field as this is not a primary key and the above is a fairly rare operation.  Which option would you choose?</p>
  
  <p>Note 2: I'm asking mostly out of curiosity here.  In my application, Desc <em>is</em> an indexed field and I am not using Substring.  However, I briefly considered using Substring and it occurred to me that I didn't truly know which of the above approaches would be more efficient.</p>
  
  <p>Finaly, a bonus question: is it true that using Substring on an Indexed field would make the optimizer skip the index and <em>really</em> slow things down?  I don't think the optimizer is smart enough to use the Index if Substring is used (even with a zero base) but I am a bit too busy to test it out right now.  However, if you know differently, please correct me!</p>
  
  <p><strong>Update/clarification:</strong> you should be assuming that the Desc field is <em>not</em> indexed for purposes of the original question.  If it is indexed, the answer is pretty easy.</p>