Which SQL standard is most broadly and completely supported

sql-standard

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 ?

Best Answer

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.

Related Solutions

SQL Server T-SQL – SQL ‘Standard’ Guide

The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree.

While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This almost always means using DBMS-specific versions of SQL and DBMS-specific features.

Your employer/client has paid good money for a license for their DBMS, and often gone through a selection process to do so. Not using the DBMS's features is a waste of money.

I like to say it's like buying a Ferrari and always sticking to the speed limit...

On the other hand, if you're just learning SQL, and T-SQL just happens to be the dialect you have at your disposal, then, yes, try to learn standard SQL as your knowledge will be more broadly applicable. But don't discount learning your particular DBMS's features if you are in a commercial (or non-academic) environment.

MySQL – Is It Possible to Reliably Number Rows in Some Order Conforming to SQL Standard?

There is a way to do that using only SQL 89 feature, but it will be horribly slow on any real world sized table:

select t1.id, 
       ... other columns ...,
       (select count(*) 
        from the_table t2 
        where t2.id <= t1.id) as row_num
from the_table t1
order by t1.id;

This assumes id is the column to sort the numbers by.

Best Answer

Related Solutions

SQL Server T-SQL – SQL ‘Standard’ Guide

MySQL – Is It Possible to Reliably Number Rows in Some Order Conforming to SQL Standard?

Related Question