SQL Server VECTOR data type

Informix®

A vector is used in AI models to represent the characteristics of an object, that can be a text document, and image or a sound. Recent database engines provide support to store vector data.

Informix does not provide a specific SQL type to store vector data.

With Genero BDL, you can use VARCHAR, STRING or TEXT variables, to store vector data as a JSON array of numbers:

[-3.45, -4.45, 9.234, ... ]

Use Genero JSON APIs to manipulate vectors as JSON arrays.

SQL SERVER

SQL Server 2025 introduced support for a native VECTOR data type, to store the data produced by embedding models along with business data:

CREATE TABLE tab1 (
   pkey INTEGER NOT NULL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   vect1 VECTOR(256),
   ...
);
INSERT INTO tab1 values ( 101, 'Mike STORN',
    '[-3.45, -4.45, 9.234, ... ]'
);

Read SQL Server documentation for more details about AI vector embeddings, and the VECTOR type.

Solution

The SQL Server VECTOR type is supported with dbmsnc_18 ODI driver and Microsoft ODBC for SQL Server version 18.6.1.1 or +.

The ODI driver relies on implicit VECTOR serialization/deserialization provided by SQL Server.

You can use VARCHAR, STRING and TEXT FGL variables as SQL input parameters for VECTOR columns, or as vector function parameters.

As the size of a VECTOR can be quite large, you must fetch VECTOR data into TEXT variables, when the SELECT statement retrieves VECTOR data without any type conversion.

In order to fetch VECTOR data into a VARCHAR or STRING variable, convert the VECTOR data to a character string with a CAST() SQL operator:

DEFINE k INTEGER
DEFINE s STRING
DECLARE c1 CURSOR FOR
    SELECT pkey, CAST(vect1 AS VARCHAR(200)) FROM tab1 ORDER BY pkey
FOREACH c1 INTO k, s
    DISPLAY k, ": ", NVL(s, "<null>")
END FOREACH

When extracting database schemas with the fgldbsch tool, columns with SQL Server VECTOR type are converted to the FGL TEXT data type.

SQL Server VECTOR functions can be used to query the database, as shown in the next example with the VECTOR_DISTANCE() SQL function:

VAR d DECIMAL(10,5)
VAR s STRING
VAR t TEXT
DECLARE c11 CURSOR FOR
   SELECT doc_id,
          VECTOR_DISTANCE('cosine',vect1,?) AS distance
     FROM mydoc ORDER BY distance
LET s = "[-4,5,3,-2,6]"
LOCATE t IN MEMORY
LET t = s
FOREACH c11
       USING s  -- or can also use TEXT variable t
       INTO rec.doc_id, d
    DISPLAY rec.doc_id, "    distance = ", d
END FOREACH