You probably won’t find many database professionals arguing about the necessity for SELECT statements, but many argue about whether cursors are necessary. Arguments also arise about the use of temporary tables; dynamic code; and integrated XML, XQuery, and CLR. There must be a reason why database professionals are in complete agreement about some aspects of Microsoft SQL Server 2005 but have conflicting opinions about other aspects (call the constructs under conflicting opinions arguable constructs). Let me offer my two cents’ worth on the subject.
These arguable constructs have a high potential for misuse because database professionals often lack knowledge and experience in set-based querying and the relational model. Such misuse can lead to very poor implementations. Defenders of these arguable constructs would argue that any construct can be abused because of lack of knowledge and experience. Still, I think that there is a difference between these constructs and many others. Knives and matches are very useful tools, but only in the hands of responsible people. You wouldn’t want those devices in the hands of children. Even with no bad intentions on the part of the users, the potential for catastrophe is high. A child could also do damage with crayons and books, but the likelihood of that happening is much lower and the damage wouldn’t be as severe.
I didn’t say that I side with those who oppose the arguable constructs, or that I’m on any side for that matter. But I do think that placing such tools in the hands of programmers who lack adequate knowledge of set-based querying and the relational model can yield bad results. The key is having the maturity to recognize the appropriate time and place to use each construct (static set-based queries, dynamic SQL, cursors, XML, CLR, table expressions, temporary tables, and so on). This book tries to guide you to that level of maturity.
I hope you will forgive me for the philosophical approach to this subject, but for me SQL is a "way" that has important philosophical aspects. In my mind–and you don’t have to agree–I separate the careers of T-SQL programmers into three typical phases:
Procedural. This is the phase in which programmers have just started to work with databases. They have insufficient experience working with the relational model and set-based thinking. In this phase, it’s common to see misuse of tools such as cursors, temporary tables, dynamic execution, and procedural coding in general. Programmers at this stage are usually oblivious to the damage that they’re causing.
Becoming sober. This is the phase in which programmers realize there’s more to database programming–that SQL is not a nuisance that interferes with writing procedural code but, rather, it’s based on the strong foundations of set theory and the relational model. In this phase, programmers tend to believe "experts" who say cursors, temporary tables, and dynamic execution are evil and should never be used. At this point, programmers either avoid using such constructs altogether or really feel bad about the code they write. There’s usually lack of confidence at this stage.
Maturity. This stage is characterized by the void or Zen mindset. In this phase, programmers have deep knowledge and understanding, and they feel confident about their code. This doesn’t mean they stop pursuing deeper knowledge or improving fundamental techniques. In this phase, programmers apply set-based thinking for the most part, but they realize that there’s a time and place for other constructs as well. I refer to this phase as the "void" in the positive and abstract sense–that is, programmers develop intuition regarding the type of solution that would fit a given task and don’t need to spend much time determining which technique is appropriate.
Developing the intuition described in phase three involves knowing when the typical approach of using pure static SQL programming will not do the job. Although pure static SQL programming is typically the way to go, it will only get you so far in some cases. There are cases where using temporary tables can substantially improve performance; where dynamic execution actually overcomes complex problems; where the use of procedural languages such as C# and Visual Basic allows more flexibility without conflicting with the relational model; and where storing states of data in XML format makes sense. This book explores these cases in dedicated chapters and sections.