Beginning SQL Programming: Pt. 1

Flavors of SQL

The computer industry (like most industries) both benefits and suffers from standards. We said that SQL is an open standard, not owned by a company, and the standard comes from ANSI. Therefore the SQL standard from ANSI is considered the "pure" SQL and called ANSI-SQL.

Two problems emerge to sully this pureness. First is that every DBMS vendor wants to differentiate their DBMS products. So if you look at the feature set of each DBMS product you see that not only does the product support ANSI-SQL but it also offers extra features, enhancements or extensions that are available only from individual vendors. For example, most vendors offer a field type which auto- increments even though this is not described in the SQL standards. These additions to ANSI-SQL are generally proprietary and will not work if you try to use them on competitor's SQL products. At the level we discuss in this book there are only very minor differences between the vendors that we will note throughout the book.

Many of these features are powerful and robust, but since they vary from vendor to vendor, programmers should use them with caution. It is always safest to stick with pure SQL whenever possible; if you stray it should be with full knowledge that you are losing the portability of your statements (and perhaps even your data).

Such enhancements are not all bad because these extensions are very useful. For example, ANSI-SQL does not contain an automatic way to assign a serial number to each new record but most DBMS sold today have added this feature. Since serial numbering is so common programmers are happy to have the enhancement. However, the method of implementation is not uniform, so code written to get the serial number from data in one DBMS may not work when used with another vendor's DBMS.

In this book we are helping people that use all of the flavors of SQL. As a beginner most of what you learn is pure ANSI-SQL and thus is consistent across all vendor's products. When we talk about vendor-specific features in this book we note it. Certainly as a student, not knowing what DBMS you will be using in a job, it is best to study the purest form of the language. Learning the extensions available in T-SQL, PL/SQL, Access SQL and others will come later. For an introduction to some of these variations see our Appendix D.