When to Use SQL
My students frequently ask, "When should I be using SQL?" They may have created a database in a
lower level desktop system and are considering switching to a DBMS that will require SQL. SQL is a
language, not a software product, so the real question is best phrased as "When do I need to use a
heavy-duty DBMS, which would then require me to start communicating in SQL?" There are several
factors to consider which we'll look at in turn in this section.
Many small sites or applications start in desk top systems such as Access. As they grow, a number of
problems become apparent with the Access JET database engine. The primary problem is that it was
never designed to support many users at once. Database software designed for the desktop generally
fails when more than a few people try to use the data at the same time. Microsoft Access will perform
well if five users are on a LAN. When you get to ten or twenty concurrent users, problems begin.
Obviously a desktop database cannot handle the problems introduced by large numbers of concurrent
hits from a web site.
Second, the Access file-based system becomes fragile when the amount of data starts to climb into the
gigabyte range. Lower scale systems like Access also lack the security that more powerful systems offer.
Access databases, for example, are quite easy to copy and walk away with. The individual installations
can also be readily accessed.
So for these reasons companies move from Access type desk top systems to a more robust DBMS.
Almost all of these heavy duty DBMS rely on SQL as the main form of communication. In summary,
although there is nothing about SQL itself that concerns scaling, there is a need to use SQL in order to
communicate with DBMS that scale well.
When SQL-enabled DBMS first appeared they were slower than previous DBMS. However, as they
have taken over the market during the last ten years, SQL data engines have been the focus of an
intense effort to improve performance. There are heated contests between the major vendors for
bragging rights to the fastest machine with the lowest transaction price. But the intense competition
drives the vendors to produce faster, more robust DBMS that work at lower and lower costs per
When you consider your quest for improved speed, consider that pure speed problems will be evident
at all times, not just at peak usage. They are usually the result of more complex queries, particularly
with the advanced techniques we study later like multiple joins on data that cannot be indexed.
Although SQL itself does not cure speed problems, the implementation of faster DBMS does, and those
faster DBMS will probably require communications in SQL.
It is more expensive to run a server-centric DBMS (that only speaks SQL) than it is to use a desktop
system such as Paradox or Access (for which you do not necessarily need to use SQL). First, the
software is more expensive. Second, in most cases you have to run more expensive operating systems in
order to support the DBMS. Third, you have to tune the OS differently to optimize for a DBMS than for
other applications, so you generally need a server dedicated to the DBMS. Last, you will need personnel
with more expensive qualifications.
However, the price of hardware and software is frequently the smallest item in an IT budget. As your
data center grows, at some point the reliability, performance and standardization benefits of a DBMS
that uses SQL will outweigh the cost.
A note on price you can get started on a SQL-centric DBMS for almost nothing. In fact, we have
included the 120-day trial version of Microsoft SQL Server 2000 with this book. You can download
other trial versions such as Oracle Personal Edition. But remember, that these are trial versions; when
you are ready to build your business on a SQL-centric DBMS you will be paying a considerable amount
more than you did for Access.
An alternative to using SQL statements is to write code in a procedural language like C++. The
problem with this approach is that you are then closely tied to the procedural language, the metadata
and the specific DBMS. If there is a change in the structure of the tables the code must change. If a new
DBMS is installed, most of the code must be revised to mesh with the new DBMS system of pointers,
recordset definitions, etc. But by using SQL statements almost all changes are handled by the DBMS,
behind the scenes from the programmer.
An advanced feature of SQL (which is beyond the scope of this book) is the emergence of analytical
tools that allow managers to extract business knowledge from large amounts of data. These tasks
frequently require the constructions of multi-dimensional aggregates of data. These tools are referred to
by the generic names of Online Analytical Processing (OLAP), Decision Support Systems (DSS) and
Executive Information Systems (EIS). Each vendor then has proprietary trademarks for their version.
All of these tools are only available in full-scale DBMS that require SQL for their means of