Edit

Sparse Columns Support in SQL Server Native Client (OLE DB)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.

This topic provides information about SQL Server Native Client OLE DB support for sparse columns. For more information about sparse columns, see Sparse Columns Support in SQL Server Native Client. For a sample, see Display Column and Catalog Metadata for Sparse Columns (OLE DB).

OLE DB Statement Metadata

Beginning with SQL Server 2008 (10.0.x), a new DBCOLUMNFLAGS flag value, DBCOLUMNFLAGS_SS_ISCOLUMNSET, is available. This value should be set for columns that are column_set values. The DBCOLUMNFLAGS flag can be retrieved through the dwFlags parameter of IColumnsInfo::GetColumnsInfo and the DBCOLUMN_FLAGS column of the rowset returned by IColumnsRowset::GetColumnsRowset.

OLE DB Catalog Metadata

Two additional SQL Server-specific columns have been added to DBSCHEMA_COLUMNS.

Column name Data type Value/comments
SS_IS_SPARSE DBTYPE_BOOL If the column is a sparse column, this has the value VARIANT_TRUE; otherwise, VARIANT_FALSE.
SS_IS_COLUMN_SET DBTYPE_BOOL If the column is the sparse column_set column, this has the value VARIANT_TRUE; otherwise, VARIANT_FALSE.

Two additional schema rowsets have also been added. These rowsets have the same structure as DBSCHEMA_COLUMNS but return different content. DBSCHEMA_COLUMNS_EXTENDED returns all columns regardless of column_set membership. DBSCHEMA_SPARSE_COLUMN_SET returns only columns that are members of the sparse column_set.

OLE DB DataTypeCompatibility Behavior

Behavior with DataTypeCompatibility=80 (in the connection string) is consistent with a SQL Server 2000 (8.x) client, as follows:

  • The new schema rowsets are not visible, and there are no rows for them in the schema rowsets rowset.

  • New columns in the COLUMNS rowset are not visible.

  • DBCOLUMNFLAGS_SS_ISCOLUMNSET is not set for column_set columns.

  • DBCOMPUTEMODE_NOTCOMPUTED is set for column_set columns.

OLE DB Support for Sparse Columns

The following OLE DB interfaces were modified in SQL Server Native Client to support sparse columns:

Type or member function Description
IColumnsInfo::GetColumnsInfo A new DBCOLUMNFLAGS flag value DBCOLUMNFLAGS_SS_ISCOLUMNSET is set for column_set columns in dwFlags.

DBCOLUMNFLAGS_WRITE is set for column_set columns.
IColumsRowset::GetColumnsRowset A new DBCOLUMNFLAGS flag value, DBCOLUMNFLAGS_SS_ISCOLUMNSET, is set for column_set columns in DBCOLUMN_FLAGS.

DBCOLUMN_COMPUTEMODE is set to DBCOMPUTEMODE_DYNAMIC for column_set columns.
IDBSchemaRowset::GetSchemaRowset DBSCHEMA_COLUMNS returns two new columns: SS_IS_COLUMN_SET and SS_IS_SPARSE.

DBSCHEMA_COLUMNS returns only columns that are not members of a column_set.

Two new schema rowsets have been added: DBSCHEMA_COLUMNS_EXTENDED will return all columns regardless of sparseness of column_set membership. DBSCHEMA_SPARSE_COLUMN_SET returns only columns that are members of a column_set. These new rowsets have the same columns and restrictions as DBSCHEMA_COLUMNS.
IDBSchemaRowset::GetSchemas IDBSchemaRowset::GetSchemas includes the GUIDs for the new rowsets DBSCHEMA_COLUMNS_EXTENDED and DBSCHEMA_SPARSE_COLUMN_SET in the list of available schema rowsets.
ICommand::Execute If select * from table is used, it returns all columns that are not members of the sparse column_set, plus an XML column that contains values of all non-null columns that are members of the sparse column_set, if present.
IOpenRowset::OpenRowset IOpenRowset::OpenRowset returns a rowset with the same columns as ICommand::Execute, with a select * query on the same table.
ITableDefinition There is no change to this interface for sparse columns or for column_set columns. Applications that have to make schema modifications must execute the appropriate Transact-SQL directly.

See Also

SQL Server Native Client (OLE DB)