Skip to content

Latest commit

 

History

History
59 lines (43 loc) · 5.1 KB

sparse-columns-support-ole-db.md

File metadata and controls

59 lines (43 loc) · 5.1 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic
Sparse Columns Support (OLE DB)
Sparse Columns Support in SQL Server Native Client (OLE DB)
markingmyname
maghan
03/17/2017
sql
native-client
reference

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

[!INCLUDE SQL Server]

Important

[!INCLUDEsnac-removed-oledb-only]

This topic provides information about [!INCLUDEssNoVersion] 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 [!INCLUDEsql2008-md], 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 [!INCLUDEssNoVersion]-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 [!INCLUDEssVersion2000] 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 [!INCLUDEssNoVersion] 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.
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 [!INCLUDEtsql] directly.

See Also

SQL Server Native Client (OLE DB)