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

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

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Jun 28, 2001

Beginning SQL Programming: Pt. 1 - Page 5

By DatabaseJournal.com Staff

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.

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