I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and energy on it ?
Asked
Active
Viewed 719 times
4 Answers
15
I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.
While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.
It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.
SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.
Aaron Bertrand
- 181,950
- 28
- 405
- 624
11
All major RDBMSs support the different versions of the SQL spec to different degrees, with the older versions of the spec more fully supported. Not all take conformance equally seriously (for example, Oracle started supporting 'ansi joins' in 2001, nearly a decade after SQL-92), and as @gbn already said, in practice you need to know the flavour of SQL used by each product you use.
Postgres is an exception in that it "prides itself in standards compliance". For "learning and reference purposes", you need hands-on practice with a database, not just knowledge from a book or standard, and postgres is an ideal platform for learning the ropes with because:
- It is free and available on most platforms
- The aforementioned standards compliance
- It has excellent documentation
- It is mature and widely used
- It has many of the features found in the most popular commercial RDBMSs, and quite a few that aren't
Jack Douglas
- 40,517
- 16
- 106
- 178
3
Agreed with Jack and gbn, there is no standard and you need to make a choice. To make this choice, you need to select RDBMS first. I'd recommend to think about following things: 1) do you want to be DB-developer or DBA? 2) Which operation system(s) do you want to work with? I agree, this question is little strange, but when I talk with students, they say that it is important.
For example (just example, maybe I'm wrong) if you want to be a DBA and don't want to work with Windows, you don't need to think about MSSQL. And if you want to be DBA and you like to work with command-string and config-files - maybe Oracle is what you need. If you want to be developer want to use your knowledge in freelance projects, maybe you need MySQL?
