Press "Enter" to skip to content

Relational Database Proprietary Extensions

Standard Query Language – or SQL for short – is a language designed for relational databases that allows end users such as developers and database administrators (DBAs) to manipulate data.

Whether that be INSERT-ing, UPDATE-ing, DELETE-ing, SELECT-ing or otherwise ALTER-ing almost any amount of data; SQL provides an effective way of utilising the database system/engine by providing standardised language to do so.

The American National Standards Institute (ANSI) SQL became the standard in 1986 when the specification described the official features of the SQL language (Karmin, 2015).

Since then some database vendors have extended SQL by means of “proprietary extensions” in order to enhance or functionality in their engines that they found otherwise difficult to achieve with standardised SQL (Hilker, 2013).

While there are a number of SQL standards, vendors and open-source projects have liberally extended SQL (VoltDB, n.d.).

Key Advantages

  • Quickly achieve more complex tasks
  • Take advantage of vendor specific features

Key Disadvantages

  • Difficult to migrate to a different database engine
  • Harder to find developers, DBAs due to a more niche product offering

The usage of proprietary SQL extensions can be seen as very advantageous when used in an isolated project where the technical stack will undoubtedly remain the same and end users can really get ahead more quickly due to additional functionalities built directly into the proprietary vendor specification.

However, on the opposing hand, deviating from the ANSI – or more commonly, ISO/IEC (which standards for “International Organization for Standardization” and “International Electrotechnical Commission” respectively) – standard is not recommended and can lead to many problems down the road should the project’s technical stack change or if the database contents need to be moved to a different database engine where the extensions are not supported. This would mean rewriting and testing large portions of the application stack.

Should we compare SQL to a programming language such as Java, we can notice many crucial findings.

SQL is a declarative language that has nothing to do with either object-oriented or imperative thinking (Eder, 2014) where on the contrary, Java is object-oriented and required a different type of thinking when writing it by comparison (Cline, 2014).

This often means that developers can struggle to write concise SQL as the way you think of the two are particularly different, however, a large percentage of developers tend to use both languages daily in their applications.

In my opinion, SQL is a very well thought out and simple language that has many benefits while keeping the overall pattern unobtrusive.

Once you grasp how SQL works, it is actually a very simple language and extremely useful to anyone that needs to use a relational database.

 

References

Karmin, B. (2015) What is ANSI SQL, Oracle SQL and MySQL? [Online] Quora, Available from: https://www.quora.com/What-is-ANSI-SQL-Oracle-SQL-and-MySQL (Accessed on 11th November 2017)

Hilker, S. (2013) SQL Compatibility Extensions Overview [Online] ToadWorld, Available from: https://www.toadworld.com/platforms/ibmdb2/w/wiki/7682.sql-compatibility-extensions-overview (Accessed on 12th November 2017)

VoltDB (n.d.) Why SQL Database? [Online] VoltDB, Available from: https://www.voltdb.com/product/features-benefits/sql-database/ (Accessed on 12th November 2017)

Eder, Lukas. (2014) 10 Common Mistakes Java Developers Make when Writing SQL [Online] Jooq, Available from: https://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/ (Accessed on 11th November 2017)

Cline, K. (2014) Choosing value in SQL query vs (Java) code [Online] Stackexchange, Available from: https://softwareengineering.stackexchange.com/questions/228604/choosing-value-in-sql-query-vs-java-code (Accessed on 12th November 2017)