适用于 SQL Server 的 OLE DB 驱动程序的稀疏列支持Sparse Columns Support in OLE DB Driver for SQL Server

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

下载下载 OLE DB 驱动程序DownloadDownload OLE DB Driver

OLE DB Driver for SQL Server 支持稀疏列。OLE DB Driver for SQL Server supports sparse columns. 有关 SQL ServerSQL Server 中的稀疏列的详细信息,请参阅使用稀疏列使用列集For more information about sparse columns in SQL ServerSQL Server, see Use Sparse Columns and Use Column Sets.

若要详细了解 OLE DB Driver for SQL Server 中对稀疏列的支持,请参阅稀疏列支持 (OLE DB)For more information about sparse column support in OLE DB Driver for SQL Server, Sparse Columns Support (OLE DB).

有关演示此功能的示例应用程序的信息,请参阅 SQL Server 数据编程示例For information about sample applications that demonstrate this feature, see SQL Server Data Programming Samples.

稀疏列和 OLE DB Driver for SQL Server 的用户应用场景User Scenarios for Sparse Columns and OLE DB Driver for SQL Server

下表为具有稀疏列的 OLE DB Driver for SQL Server 用户总结了常见用户应用场景:The following table summarizes the common user scenarios for OLE DB Driver for SQL Server users with sparse columns:

场景Scenario 行为Behavior
select * from table 或 IOpenRowset::OpenRowset。select * from table or IOpenRowset::OpenRowset. 返回不是稀疏 column_set 的成员的所有列,以及包含是稀疏 column_set 的成员的所有非空列值的 XML 列 。Returns all columns that are not members of the sparse column_set, plus an XML column that contains the values of all non-null columns that are members of the sparse column_set.
按名称引用列。Reference a column by name. 可以不考虑其稀疏列状态或 column_set 成员身份如何而引用列 。The column can be referenced regardless of its sparse column status or column_set membership.
通过计算的 XML 列访问 column_set 成员列 。Access column_set member columns through a computed XML column. 作为稀疏 column_set 的成员的列可以通过按名称选择 column_set 进行访问,并且可通过在 column_set 列中更新 XML 插入和更新值 。Columns that are members of the sparse column_set can be accessed by selecting the column_set by name and can have values inserted and updated by updating the XML in the column_set column.

该值必须符合针对 column_set 列的架构 。The value must conform to the schema for column_set columns.
通过 DBSCHEMA_COLUMNS 架构行集检索某一表中所有列的元数据,不存在列限制 (OLE DB)。Retrieve metadata for all columns in a table through the DBSCHEMA_COLUMNS schema rowset with no column restriction (OLE DB). 为不是 column_set 的成员的所有列返回行 。Returns a row for all columns that are not members of a column_set. 如果该表具有稀疏 column_set,则将为其返回一行 。If the table has a sparse column_set, a row will be returned for it.

请注意,此操作并不返回是 column_set 的成员的列的元数据 。Note that this does not return metadata for columns that are members of a column_set.
检索所有列的元数据,而不管 column_set 中的稀疏性或成员身份如何 。Retrieve metadata for all columns, regardless of sparseness or membership in a column_set. 此操作可能返回大量的行。This might return a very large number of rows. 为 DBSCHEMA_COLUMNS_EXTENDED 架构行集调用 IDBSchemaRowset::GetRowset。Call IDBSchemaRowset::GetRowset for the DBSCHEMA_COLUMNS_EXTENDED schema rowset.
只为是 column_set 成员的列检索元数据 。Retrieve metadata only for columns that are members of a column_set. 此操作可能返回大量的行。This might return a very large number of rows. 为 DBSCHEMA_SPARSE_COLUMN_SET 架构行集调用 IDBSchemaRowset::GetRowset。Call IDBSchemaRowset::GetRowset for the DBSCHEMA_SPARSE_COLUMN_SET schema rowset.
确定列是否为稀疏列。Determine whether a column is sparse. 参考 DBSCHEMA_COLUMNS 架构行集 (OLE DB) 的 SS_IS_SPARSE 列。Consult the SS_IS_SPARSE column of the DBSCHEMA_COLUMNS schema rowset (OLE DB).
确定列是否为 column_set 。Determine if a column is a column_set. 参考 DBSCHEMA_COLUMNS 架构行集的 SS_IS_COLUMN_SET 列。Consult the SS_IS_COLUMN_SET column of the DBSCHEMA_COLUMNS schema rowset. 或者,请参阅由 IColumnsRowset::GetColumnsRowset 返回的行集中的 IColumnsinfo::GetColumnInfo 或 DBCOLUMNFLAGS 返回的 dwFlags 。Or, consult dwFlags returned by IColumnsinfo::GetColumnInfo or DBCOLUMNFLAGS in the rowset returned by IColumnsRowset::GetColumnsRowset. 对于 column_set 列,将设置 DBCOLUMNFLAGS_SS_ISCOLUMNSET 。For column_set columns, DBCOLUMNFLAGS_SS_ISCOLUMNSET will be set.
为没有 column_set 的表按 BCP 导入和导出稀疏列 。Import and export of sparse columns by BCP for a table with no column_set. 在行为上与 OLE DB Driver for SQL Server 的以前版本相比没有变化。No change in behavior from previous versions of OLE DB Driver for SQL Server.
为有 column_set 的表按 BCP 导入和导出稀疏列 。Import and export of sparse columns by BCP for a table with a column_set. 导入和导出 column_set 的方式与 XML 相同,也就是说,与 varbinary(max) 相同(如果绑定为 binary 类型)或与 nvarchar(max) 相同(如果绑定为 char 或 wchar 类型)。The column_set is imported and exported in the same way as XML; that is, as varbinary(max) if bound as a binary type, or as nvarchar(max) if bound as a char or wchar type.

是稀疏 column_set 的成员的列不导出为非重复列;它们只导出在 column_set 的值中 。Columns that are members of the sparse column_set are not exported as distinct columns; they are only exported in the value of the column_set.
BCP 的 queryout 行为。queryout behavior for BCP. 在处理显式命名的列方面与 OLE DB Driver for SQL Server 的以前版本相比没有变化。No change in the handling of explicitly named columns from previous versions of OLE DB Driver for SQL Server.

如果应用场景涉及在具有不同架构的表之间进行导入和导出,则可能要求特殊处理。Scenarios involving import and export between tables with different schemas may require special handling.

有关 BCP 的详细信息,请参阅本章后面的“针对稀疏列的大容量复制 (BCP) 支持”。For more information about BCP, see Bulk Copy (BCP) Support for Sparse Columns, later in this topic.

下级客户端行为Down-Level Client Behavior

下级客户端将只为不属于 SQLColumns 和 DBSCHMA_COLUMNS 的稀疏 column_set 的成员的列返回元数据 。Down-level clients will return metadata only for columns that are not members of the sparse column_set for SQLColumns and DBSCHMA_COLUMNS.

下级客户端将按名称访问作为稀疏 column_set 的成员的列,并且 column_set 列将可作为 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 客户端的 XML 列访问。Down-level clients can access columns that are members of the sparse column_set by name, and the column_set column will be accessible as an XML column to SQL Server 2005 (9.x)SQL Server 2005 (9.x) clients.

针对稀疏列的大容量复制 (BCP) 支持Bulk Copy (BCP) Support for Sparse Columns

对于稀疏列或 column_set 功能,在 OLE DB 中针对 BCP API 没有任何变化 。There are no changes to the BCP API in OLE DB for the sparse columns or column_set features.

如果某一表具有 column_set,则稀疏列不作为非重复列处理 。If a table has a column_set, sparse columns are not handled as distinct columns. 所有稀疏列的值都包括在 column_set 值中,后者采用与 XML 列相同的方式导出;也就是说,与 varbinary(max) 相同(如果绑定为 binary 类型)或与 nvarchar(max) 相同(如果绑定为 char 或 wchar 类型)。The values of all sparse columns are included in the value of the column_set, which is exported in the same way as an XML column; that is, as varbinary(max) if bound as a binary type, or as nvarchar(max) if bound as a char or wchar type). 在导入时,column_set 值必须符合 column_set 的架构。On import, the column_set value must conform to the schema of the column_set.

对于 queryout 操作,在处理显式引用的列的方式上没有变化 。For queryout operations, there is no change to the way explicitly referenced columns are handled. column_set 列具有与 XML 列相同的行为,并且稀疏性对于命名稀疏列的处理没有影响 。column_set columns have the same behavior as XML columns and sparseness has no effect on the handling of named sparse columns.

但是,如果 queryout 用于导出并且引用的稀疏列属于按名称的稀疏列集的成员,则不能执行向类似结构表的直接导入 。However, if queryout is used for export and you reference sparse columns that are members of the sparse column set by name, you cannot perform a direct import into a similarly structured table. 这是因为 BCP 使用与 select 操作一致的元数据进行导入,并且无法将 column_set 成员列与此元数据进行匹配 *This is because BCP uses metadata consistent with a select * operation for the import and is unable to match column_set member columns with this metadata. 若要单独导入 column_set 成员列,必须对引用所需 column_set 列的表定义一个视图,并且必须使用该视图执行导入操作 。To import column_set member columns individually, you must define a view on the table that references the desired column_set columns, and you must perform the import operation using the view.

另请参阅See Also

适用于 SQL Server 的 OLE DB 驱动程序OLE DB Driver for SQL Server