As mentioned in the previous article, it is possible to configure the way you wish your collections to be stored and queried in SQL.

To set this up, you will need to use the following field parameters and settings:

The STORAGEDEFAULT parameter tells the IRIS compiler which physical data structure to use when writing a collection to a Global.

For a list of properties, the default value is "list", meaning that the data is serialized into a single string using $LISTBUILD (an internal binary format). It is stored as part of the main object’s data node in the global.

For an array of properties, the default value is "array", meaning that each element is stored in a separate global subnode. This essentially creates a "child" structure on the disk. It is best for large collections where you need to update or delete single items without rewriting the entire record.

But, at the same time, you can toggle these settings, and you can force a List to act like an Array (and vice versa) for performance reasons. For example,

Property Notes As list of %String (STORAGEDEFAULT = "array");

This will store the list in subnodes, making individual item updates much faster.

To go further, you can tell the system how you wish your data to be mapped in SQL. For this, use the field parameter SQLPROJECTION. It is the most powerful tool for making collections "visible" to SQL.

You can set both column and table setting to both list and array.

Beyond the big two, several parameters influence the nuances of how these collections behave in SQL.

SqlListType  (for list properties only), as mentioned in the previous article, defines the format of the data when it is projected as a single column, and can get one of the following values:

• LIST (default) returns the internal IRIS $LIST format. Best for IRIS-to-IRIS integrations.
• DELIMITED returns a string separated by a character (defined by SqlListDelimiter ). Best for legacy CSV exports or simple reporting.
• SUBNODE - the data is physically stored in subnodes (like an array) but seen in SQL as a single field.

When SQLPROJECTION =  "table" is used, these determine the naming convention:

• SQLTABLENAME - name of the generated child table (e.g., Employee_Phones)
• SQLFIELDNAME - name of the value column within that child table

Now let's look at the example. Let's say we have the following class:

Class CRM.Contact Extends (%Persistent, %Populate) [ SqlRowIdName = Contact_PK ]
{

/// 1. Standard Property: Custom SQL field name
Property FullName As %String(MAXLEN = 100) [ SqlFieldName = Contact_Name ];

/// 2. Simple List: Projected as a single column
/// Reason: Use this for small sets of data where you just need to see them in one cell.
/// Goal: Compatibility with legacy tools using comma-delimited strings.
Property Tags As list Of %String [ SqlFieldName = Category_Tags, SqlListType = DELIMITED ];

/// 3. Performance-Optimized List: Projected as a Child Table
/// Reason: 'STORAGEDEFAULT = array' ensures that adding a 10th phone number 
///         doesn't require rewriting the whole contact record (O(1) vs O(N)).
/// Goal: Allow BI tools to JOIN on phone numbers without complex parsing.
Property PhoneNumbers As list Of %String(SQLPROJECTION = "table", SQLTABLENAME = "Contact_Phones", STORAGEDEFAULT = "array");

/// 4. Key-Value Array: Projected as a Table
/// Reason: Arrays are naturally stored as subnodes. Projecting as a table 
///         allows SQL users to query specific keys easily.
Property Metadata As array Of %String(SQLPROJECTION = "table", SQLTABLENAME = "Contact_Metadata");
}

If we now create 10 instances of this class, we can execute the following queries and see the results:

SELECT *
  FROM CRM.Contact

So we can see that the FullName field is called Contact_Name, the primary field is called Contact_PK and the only other field we have is Category_Tags that is a delimited list.

At the same time, we have 2 additional tables: CRM.Contact_Metadata and CRM.Contact_Phones. 

If we execute the query

SELECT *
  FROM CRM.Contact_Phones

we will see that our list of PhoneNumbers actually looks like it's an array with the primary key from the main table (CRM.Contact), primary key of this table which is a composition of the Contact PK and position of the data in the list, list data and the position of the data in the list:

If we execute the query

SELECT *
  FROM CRM.Contact_Metadata

we will see a usual table with the primary key from the main table (CRM.Contact), primary key of this table, which is a composition of the Contact PK and array key, array key, and array data:

So basically, the difference between the last two results is that for the list represented as a table, the element_key is the position of the element in the list, and for the array - it's the actual key.

Hopefully, this will help you to make an informed decision on how you would like to store and represent your collections!