The American National Standards Institute has defined a standard ANSI SQL standard. It is a standard that is accepted by the International Organization for Standardization (ISO) which is a consortium of national standard bodies that is accepted by more than 150 countries. Although various database vendors will try to keep to the ANSI SQL standards, each database will have its own enhancements. As we saw in the last tutorial, SQL Server and Oracle have their own added features in T-SQL and PL/SQL with many added features and functionality.
In almost any switch between databases for applications, there will be some changes that will be needed to accommodate the new database. Even though there are several SQL dialects, the underlying options can be quite minor, but it is important to be aware of what those are when you are working with a database. It is important to note that PostgreSQL, the database that we work with attempts to stick to the ANSI SQL standards as closely as possible. So, it will be much easier moving from PostgreSQL to another database option over working with a different dialect and moving to another option.
To be compliant with the ANSI standards, there are a specific set of commands that must be implemented by the database in the same way. This includes the SELECT, UPDATE, DELETE, INSERT, and WHERE commands. Every database has its own dialect but in some databases like MySQL, you can enable strict ANSI SQL mode to avoid users being able to use non-ANSI SQL code. This can be especially useful if there may be unwanted results that may be possible in non-standard code. It also allows an easier shift to other dataset products if needed.
One common difference for data types between ANSI SQL and other SQL dialects is the implementation of date and time data types. ANSI SQL defines them to be separate data types. However, databases like Oracle and SQL Server have a date data type that has both a date and time stored in it but no separate time data type. As such, the code in these databases cannot be ported between the databases without modifications to reflect them.
Note though that in a real-world setting, it is not as crucial to focus on ANSI compliance in an application as you will be using the SQL code specifically for a product and purpose. One question that you may present is why databases don’t support pure ANSI SQL and leave it at that. One of the big reasons is that the standards generally lag the technology needs. Different database vendors want to be able to respond to their customer needs and differentiate themselves from the competition. There may be things like encryption, XML, or JSON support that may not make it into the standard, but the need is there.
The opposite is also true where the standards have gotten so complex that not all standards are implemented either. The standards do not include certain behaviors like indexing, file storage but rather allows the database vendors to decide how those should work. As the standards are always being updated, database vendors may also have implemented certain features that conflict with a newer standard. However, if they made those changes to support the standard, it would be a great impact on their customers that depend on that feature.
From a database vendor perspective, it’s not always in their best interests to stick only with pure ANSI SQL standards either as it would make it easy to move from their database to another database of their choosing. Having features and functionality that are only available on their database allows them to lock an application in. Any changes that must be implemented will take a lot more work and effort to make that transition.