Sparse Columns Support (OLE DB)

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

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).

Beginning with SQL Server 2008, 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.

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

Column nameData typeValue/comments
SS_IS_SPARSEDBTYPE_BOOLIf the column is a sparse column, this has the value VARIANT_TRUE; otherwise, VARIANT_FALSE.
SS_IS_COLUMN_SETDBTYPE_BOOLIf 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.

Behavior with DataTypeCompatibility=80 (in the connection string) is consistent with a SQL Server 2000 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.

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

Type or member functionDescription
IColumnsInfo::GetColumnsInfoA new DBCOLUMNFLAGS flag value DBCOLUMNFLAGS_SS_ISCOLUMNSET is set for column_set columns in dwFlags.

DBCOLUMNFLAGS_WRITE is set for column_set columns.
IColumsRowset::GetColumnsRowsetA 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::GetSchemaRowsetDBSCHEMA_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::GetSchemasIDBSchemaRowset::GetSchemas includes the GUIDs for the new rowsets DBSCHEMA_COLUMNS_EXTENDED and DBSCHEMA_SPARSE_COLUMN_SET in the list of available schema rowsets.
ICommand::ExecuteIf 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::OpenRowsetIOpenRowset::OpenRowset returns a rowset with the same columns as ICommand::Execute, with a select * query on the same table.
ITableDefinitionThere 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.

SQL Server Native Client (OLE DB)

Community Additions

ADD
Show: