What can SQL Server 2005 do for you

If you've read my previous post on performance issues with a stored procedure I thought it would be good to revisit the SP with a view of what SQL Server 2005 can do to assist. There are quite a few

  • Parameter values based on values at first runtime
    • SQL Server 2005 allows you to specify values which the optimiser should use to produce its plan
    • If the SP was split into more statements, individual statements could be recompiled.
  • Use of a function in the SELECT clause
    • CLR functions will likely perform better that the equivalent TSQL version when used in a SELECT statement.
    • Use of function is often to get round a deficiency in TSQL, a Common Table Expression may be able to be used instead
  • Dynamic SQL
    • If the Dynamic SQL option was chosen then in SQL 2000 you had to give the calling user access to the tables, in SQL 2005 you can use the EXECUTE AS clause to avoid granting these permissions
Published Friday, January 27, 2006 11:52 AM by simonsabin
Filed under:


No Comments