I think that there is a problem with trying to lay down hard and fast rules as to what is 'Best Practice' with Transact SQL. For some reason, we are always far too eager to act like Old Testament prophets when passing on our knowledge. It is a tendency that afflicts a lot of programmers but we Database Developers and DBAs seem to make a beeline for the hard and fast rule.
The real message, I believe, should be this. Your code must perform well, be reliable, and be easy to understand and maintain. I also think it is important to get something up and running quickly once the design is complete, and then refine, and re-factor as you increasingly understand the key points that determine the reliability and performance of the system.
I realise this is much more boring than sounding off on the evils of various 'SQL Profanities', but hell, it is the way to get the job done.
I don't care if you use cursors, Dynamic SQL or any other naughtiness. The advice I always give is:
* Design first, then code. The application's architecture, its overall design, is the largest contributing factor to its performance and reliability. Indexes, constraints, and views should all be planned up-front wherever possible.
* Have the right tools and techniques available to measure performance, and use them to understand how long the various routines and processes take to execute, under various loads and table-sizes, and why.
* Write code that is clear and is easy to maintain and understand. By far the greatest optimisations come through changing the algorithm, so the clearer the code, the easier it is to subsequently optimise.
* Never assume that an error event, such as a particular constraint violation, can't happen. For some reason, they always seem to happen, usually at midnight on a vital production system.
* Set performance goals as early as possible. You must decide what represents a 'good' performance: perhaps by measuring the speed of the previous version of the application, or of other similar applications. Make sure that clear, documented, performance objectives on all parts of the project are agreed 'upfront', so that you know when to start and stop optimising, and where the priorities are. Never micro-optimise. You should revise performance goals at each milestone
* Only optimise routines when necessary. Because detailed optimisation is time-consuming, it should be carefully targeted where it will have the most effect. The worst thing is to plough through DML indiscriminately, changing it to optimise its execution. It takes time, usually makes the code more difficult to understand and maintain, and usually has very little effect.
* Avoid optimising too early. Detailed optimisation of SQL code should not be done until the best algorithm is in place and checked for throughput and scalability.
* Do not delay optimisation too far. The most performance-critical code is often that which is referenced from the most other places in the application, such as an inline function: if the fix requires a large change to the way things operate, you can end up having to rewrite or refactor a huge portion of your application!
* Assume that poor performance is caused by human error rather than SQL Server.
* Employ an iterative routine of measuring, investigating, refining/correcting from the beginning to the end of the product cycle