Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Jul 26, 2001

Beginning SQL Programming: Pt. 2

By DatabaseJournal.com Staff

Procedural Versus Declarative Languages

We mentioned at the beginning of the chapter, that SQL is not a procedural language. Before we move on to look at how we can actually implement SQL, we'll first see exactly what we mean by a declarative rather than procedural language. Many readers will have experience in languages like Visual Basic or C++ in which they mastered how to write a series of statements in order to achieve a goal. You have probably even used these commands to "walk through" a set of data checking to see if each item is the item of interest. If you wanted to change data you wrote a series of steps that somehow opened up the datastore, then put a pointer in the correct location, modified the data, moved to the next bit of data (all the while checking if you were at the end of the data yet), modified the next record and finished by closing the datastore. In a procedural language we tell the computer each step to perform. The computer performs those steps and, if they are well written, we will end up with our desired result.

SQL is not a procedural language but a declarative language. You write a single SQL declaration and hand it to the DBMS. The DBMS then executes internal code, which is hidden from us. The DBMS returns a set, which is a group of data that is somehow defined. For example, we write a SQL statement that would translate as something like Give me the last names of all the employees. The DBMS will work away for a few milliseconds and then produce a set that contains Adams, Barrett, Cao, etc. In a declarative language, we carefully phrase what we want and then let the DBMS get it for us. If we have written a good SQL statement then the resulting set of data will be correct.

Procedural languages result in many lines of code. Declarative languages result in one statement of the desired result.

The distinction between these classes of languages drives the entire way that we think about database programming. Many of the mistakes that I see are a result of thinking in a procedural way when writing SQL statements. In this book we will show you hundreds of examples of good SQL statements that will help you on your way to thinking declaratively.

Languages in a Taxi Ride

A good analogy exists between these types of languages and different types of taxi rides. When I get in a taxi I can give a direction to the driver in one of two ways. If the driver looks and sounds like he knows what he is doing then I just give him the address. If the driver looks like he just moved to town yesterday, then I talk him through the directions to get to my destination.

If I give the driver an address I am communicating like a SQL statement, it is a clear description of the result and the driver carries it out as he or she sees fit. We may go south then east or we may go east then south. The driver is free to adapt to local conditions like a closed road or a traffic jam. It doesn't really matter to me; a good taxi driver will get me to my stated destination in the best way the driver knows. During the trip I just sit still and wait for the result. If the address is written on a piece of paper then I can show that same card to any good driver.

If I give the driver exact directions then I am communicating in a procedural language. I tell the driver to head south four blocks then turn left at the Luck Hunan Restaurant. Procedural languages work fine when the taxi driver is not too experienced and when the situation is very uniform. But I must constantly interact with the driver. If I get involved in a book we are likely to overshoot our destination. Likewise, if I tell the driver to turn left at the Luck Hunan Restaurant and the name has changed to the Lucky Duck Bar, then the communication will fail.

In this analogy we see a number of important points that apply to SQL. First, a declarative language only works when you have a strong, SQL-enabled DBMS (taxi driver). Second, procedural languages can fail when there are unexpected changes in the situation (changes in the city's landmarks). Third, procedural languages require interaction during the task (like me instructing the driver at every turn) whereas declarative languages only require that the requester wait for the process to finish. And fourth, a well-written SQL statement will work with any SQL-enabled DBMS (like an address on a paper will work with any good taxi driver).

Comparing Procedural and Declarative Languages

Procedural (Basic, C++, Cobol, etc.) Declarative (SQL)
Most work done by interpreter of the languages Most work done by Data Engine within the DBMS
Many lines of code to perform a task One SQL statement to perform task
Programmer must be skilled in translating the objective into lines of procedural code Programmer must be skilled in clearly stating the objective as a SQL statement
Requires minimum of management around the actual data Relies on sophisticated, SQL-enabled DBMS to hold the data and execute the SQL statement against the data
Programmer understands and has access to each step of the code Programmer has no interaction with the execution of the SQL statement
Data exposed to programmer during execution of the code Programmer receives data at end as an entire set
More susceptible to failure due to changes in the data structure More resistant to changes in the data structure
Traditionally faster, but that is changing Originally slower, but now setting speed records
Code of procedure tightly linked to front end Same SQL statements will work with most front ends
Code loosely linked to front end.
Code tightly integrated with structure of the datastore Code loosely linked to structure of data; DBMS handles structural issues
Programmer works with a pointer or cursor Programmer not concerned with positioning
Knowledge of coding tricks applies only to one language Knowledge of SQL tricks applies to any language using SQL

To summarize this table, declarative languages are quite different from the procedural languages you may be using now. Procedural languages like C++ place an emphasis on the programmer writing many lines of code to describe the exact steps of obtaining a result. Declarative languages like SQL place an emphasis on the programmer writing an exact description of the desired result. The DBMS then handles the task of obtaining the result. A good SQL programmer becomes very skilled at carefully describing the result but remains blissfully ignorant of the internal code of the DBMS that executes the result.

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM