Sparse Columns Support (OLE DB)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This topic provides information about OLE DB Driver for SQL Server support for sparse columns. For more information about sparse columns, see Sparse Columns Support in OLE DB Driver for SQL Server. 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 OLE DB Driver for SQL Server 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. |
| IColumnsRowset::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. |