Wednesday, February 28, 2007

Review topic: SQL, overview


WROX Publishing offers an Microsoft SQL Server 7.0 book dated 1999 (I am sure that there are newer versions) authored by Robert Vieira. He offers a lot of detailed discussions of normalization, and a perspective on various techniques for complex set processing, a comparison of
(1) nested queries with subqueries
(2) correlated subqueries (processing in both directions)
(3) joins
(4) cursors.

A cursor offers a query, which may be very complex, that returns a data set whose rows may then be processed sequentially as in a procedural language program, where there is a lot of processing for each "record" (like a row).

The SQL community tries to get programmers to think in terms of "set processing" whenever possible, which is why tests on the subject often present puzzling and complicated queries. But when a query is well written, complicated programming or hash logic outside the query is often avoided. On the other hand, system performance must be considered. Correlated subqueries and cursors sometimes degrade performance.

No comments: