This is a question of row processing orientation versus set processing orientation. One of the first things that an experienced third-generation language (3GL) programmer must do when first learning Transact-SQL (or any SQL variant for that matter) is to toss many of their programming techniques and tricks out the window. Yes, you must unlearn much of what made you a star 3GL programmer to become a star database programmer.
Procedural 3GLs, like FORTRAN, COBOL, or BASIC, perform their data operations in a manner that is quite contrary to effective Transact-SQL programming. That is because the basis of 3GL data manipulation requires the programmer to actually tell the program exactly how treat the data, one record at a time. Since the 3GL program cycles down through a list of records, performing its logic on one record after the other, this style of programming is frequently called row processing (or procedural programming because you define the exact procedures used to obtain, manipulate, and release data). Using this style of programming in Transact-SQL programs does to SQL Server what a parade does to traffic -- slows things down.
On the other side of the equation, programs written for SQL-based RDBMSs prefer to operate in logical sets of data. Unlike the row processing style, you only tell SQL Server what you want for the data, not how each individual piece of data should be handled. Sometimes you will see set processing also referred to as declarative processing, since you only declare what you want, as in "Give me all employees in the southern region who earn more than 70,000 per year."
To use an analogy, assume that you want to buy a long list of automobile parts for the 1970 Oldsmobile 442 convertible you're restoring (well, that's the car I'd want to restore). You go to the first part store on your list. There you have to pull out a shopping cart and wheel it up and down every single aisle searching for the parts you want. Furthermore, when you find a part you need, you'll have to take it out of the shipping crate, and later, you'll have to barscan it and bag it yourself. That is what row processing is like.
To compare set processing to the analogy, you take the same list to another store, where you hand it to a clerk at a receiving window. The clerk disappears behind the window, presumably to fetch your parts. Why's it taking so long? Is he having a talk at the water cooler or getting your parts? You really cannot tell because you can't see past the partition, but soon enough he returns with all your parts, packaged up and ready to go. It seems that the clerk has been gone a long time. But, in reality, the clerk got your list ready much faster than you would have at the first store because he was able to skip all the aisles that didn't have the parts you wanted. It's very similar with set processing.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in July 2005