From: jmostert@xs4all.nl   
      
   On 2012-06-21 23:31, Erland Sommarskog wrote:   
   > Jeroen Mostert (jmostert@xs4all.nl) writes:   
   >> Not particularly well, since the optimizer doesn't know what order you   
   >> actually want until the query is executed. You may or may not get   
   >> acceptable performance out of it. I know the author explicitly wrote the   
   >> article as a way of illustrating how to get *away* from dynamic SQL, and   
   >> certainly, if you can get these queries to work well for you they're   
   >> easier than cobbling together ORDER BY clauses as text, but   
   >> unfortunately dynamic SQL often remains the best choice here where   
   >> performance is concerned. The author demonstrates the query on 4 rows,   
   >> but neglects to demonstrate it on 4 million rows.   
   >   
   > Well, if you have umpteen sort conditions, the odds are very good that the   
   > very most of them will call for sorting anyway. But, true, if a sort   
   > condition is used 80% of the time, and there is a index matching this   
   > condition dynamic SQL wins.   
   >   
   > Although, these days you can avoid the syntax overhead of dynamic SQL   
   > by using OPTION (RECOMPILE). (Requires SQL 2008 SP2 or SQL 2008 R2 SP1.)   
   > Then SQL Server will compile the statement every time, and consider   
   > the parameters as constants.   
   >   
   I was about to dispute your claim to say the optimizer wasn't actually   
   clever enough to do this particular optimization even with OPTION   
   (RECOMPILE), but I'm wrong -- it is. It took me a while to find a server   
   that had the required version -- turns out we still have plenty that are   
   running 2008 SP1, and of course RECOMPILE isn't good enough on those versions.   
      
   So when you've got a working RECOMPILE, this approach can be just as   
   efficient as dynamic SQL.   
      
   --   
   J.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)