Degrading "Developer Performance"


Michael Otey's Editorial: "Multilingual SQL Server" (December 2001, InstantDoc ID 22997) was right on target in analyzing the advantages and possible potholes of increasing SQL Server's programmability. Although having the ability to write SQL Server code in procedural languages might be good "me too" marketing, it has potential pitfalls. Otey mentioned the possibility of degraded query performance. With more people writing in procedural languages, you'll likely see more use of procedural approaches, such as cursors, when set-based SQL would have been more efficient. But what concerns me more is "developer performance"—how quickly a developer can develop and maintain the needed queries. With their favorite procedural language available, many developers might spend more time developing procedural code when they could have developed an easier-to-maintain solution more quickly by using SQL.

Using Computed Columns


I found Itzik Ben-Gan's T-SQL Black Belt: "Storing Computations" (October 2001, InstantDoc ID 22091) very interesting. Then, I read the news story "SP1 Bug: SELECT Query on Table with Computed Column Generates Access Violation" (SQL Server Magazine UPDATE, http://www
.sqlmag.com, InstantDoc ID 23172) about a problem with using computed columns in SQL Server 2000 Service Pack 1 (SP1) and Microsoft's general recommendation against using them. I'm considering using computed columns as a solution in our database, which currently contains columns that have computed amounts. We keep the amounts up-to-date through manual intervention, which has proven an error-prone process. How best can I use computed columns?

I use computed columns when I have long, complex computations or when using them improves performance (e.g., by creating an index on a computed column and using the index in a filter). I wouldn't use computed columns if my computations were short and clear or if using the computed columns wouldn't yield a performance gain. When the computation is lengthy and complex, embedding it in your queries has maintenance implications. Storing the computation as a computed column means easier maintenance and more readable code. As for the SP1 bug, if Microsoft has recognized the problem as a bug, the company will likely fix the problem in a future service pack. I would use a trigger for now, and when the bug is fixed, implement computed columns.

An Oldie but a Goodie


In browsing SQL Server Magazine's online article archives recently, I found Kalen Delaney's Inside SQL Server: "Transaction Isolation Levels" (June 1999, InstantDoc ID 5336). I had difficulty explaining how SQL Server 7.0's row-level locking mechanism worked until I came across this article and its examples. Now, I use similar examples to illustrate the concepts to others I work with. Thanks for the helpful code snippets. For many of us, seeing is believing.