Overview

Arrays cannot be represented directly in SQL-92, but they are a common vendor language extension that became part of SQL-99. Arrays violate the rules of First Normal Form (1NF) required for a relational database, which say that the tables have no repeating groups in any column. A repeating group is a data structure that is not scalar; examples of repeating groups include linked lists, arrays, records, and even tables within a column.

The reason they are not allowed is that a repeating group would have to define a column like a data type. There is no obvious way to JOIN a column that contains an array to other columns, since there are no comparison operators or conversion rules. There is no obvious way to display or transmit a column that contains an array as a result set. Different languages and different compilers for the same language store arrays in column-major or row-major order, so there is no standard. There is no obvious way to write constraints on nonscalar values.

The goal of SQL was to be a database language that would operate with a wide range of host languages. To meet that goal, the scalar data types are as varied as possible to match the host language data types, but as simple in structure as they can be to make the transfer of data to the host language as easy as possible.