XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)