Using User-Defined Types

SQL Server 2005 introduced user-defined types (UDTs). UDTs extend the SQL type system by allowing you to store objects and custom data structures in a SQL Server database. UDTs can contain multiple data types and can have behaviors, differentiating them from the traditional alias data types that consist of a single SQL Server system data type. UDTs are defined using any of the languages supported by the .NET common language runtime (CLR) that produce verifiable code. This includes Microsoft Visual C#® and Visual Basic® .NET. The data is exposed as fields and properties of a .NET class or structure, and behaviors are defined by methods of the class or structure.

A UDT can be used as the column definition of a table, as a variable in a Transact-SQL batch, or as an argument of a Transact-SQL function or stored procedure.

SQL Server Native Client OLE DB Provider

The SQL Server Native Client OLE DB provider supports UDTs as binary types with metadata information, which allows you to manage UDTs as objects. UDT columns are exposed as DBTYPE_UDT, and their metadata are exposed through the core OLE DB interface IColumnRowset, and the new ISSCommandWithParameters interface.

Note

The IRowsetFind::FindNextRow method does not work with the UDT data type. DB_E_BADCOMPAREOP is returned if the UDT is used as a search column type.

Data Bindings and Coercions

The following table describes the binding and coercion that occurs when using the listed data types with a SQL Server UDT. UDT columns are exposed through the SQL Server Native Client OLE DB provider as DBTYPE_UDT. You can get metadata through the appropriate schema rowsets so you can manage your own defined types as objects.

Data type

To Server

UDT

To Server

non-UDT

From Server

UDT

From Server

non-UDT

DBTYPE_UDT

Supported6

Error1

Supported6

Error5

DBTYPE_BYTES

Supported6

N/A2

Supported6

N/A2

DBTYPE_WSTR

Supported3,6

N/A2

Supported4,6

N/A2

DBTYPE_BSTR

Supported3,6

N/A2

Supported4

N/A2

DBTYPE_STR

Supported3,6

N/A2

Supported4,6

N/A2

DBTYPE_IUNKNOWN

Not supported

N/A2

Not supported

N/A2

DBTYPE_VARIANT (VT_UI1 | VT_ARRAY)

Supported6

N/A2

Supported4

N/A2

DBTYPE_VARIANT (VT_BSTR)

Supported3,6

N/A2

N/A

N/A2

1If a server type other than DBTYPE_UDT is specified with ICommandWithParameters::SetParameterInfo and the accessor type is DBTYPE_UDT, an error occurs when the statement is executed (DB_E_ERRORSOCCURRED; the parameter status is DBSTATUS_E_BADACCESSOR). Otherwise the data is sent to the server, but the server returns an error indicating that there is no implicit conversion from UDT to the parameter’s data type.

2Beyond the scope of this topic.

3 Data conversion from hex string to binary data occurs.

4 Data conversion from binary data to hex string occurs.

5Validation can occur at create accessor time, or at fetch time, the error is DB_E_ERRORSOCCURRED, binding status set to DBBINDSTATUS_UNSUPPORTEDCONVERSION.

6BY_REF may be used.

DBTYPE_NULL and DBTYPE_EMPTY can be bound for input parameters but not for output parameters or results. When bound for input parameters, the status must be set to DBSTATUS_S_ISNULL or DBSTATUS_S_DEFAULT.

DBTYPE_UDT can also be converted to DBTYPE_EMPTY and DBTYPE_NULL, but DBTYPE_NULL and DBTYPE_EMPTY cannot be converted to DBTYPE_UDT. This is consistent with DBTYPE_BYTES.

Note

A new interface is used for dealing with UDTs as parameters, ISSCommandWithParameters, which inherits from ICommandWithParameters. Applications must use this interface to set at least the SSPROP_PARAM_UDT_NAME of the DBPROPSET_SQLSERVERPARAMETER property set for UDT parameters. If this is not done, ICommand::Execute will return DB_E_ERRORSOCCURRED. This interface and property set is described later in this topic.

If a user-defined type is inserted into a column that is not large enough to hold all its data, ICommand::Execute will return S_OK with a status of DB_E_ERRORSOCCURRED.

Data conversions supplied by OLE DB core services (IDataConvert) are not applicable to DBTYPE_UDT. No other bindings are supported.

OLE DB Rowset Additions and Changes

SQL Server Native Client adds new values or changes to many of the core OLE DB schema rowsets.

The PROCEDURE_PARAMETERS Schema Rowset

The following additions have been made to the PROCEDURE_PARAMETERS schema rowset.

Column name

Type

Description

SS_UDT_CATALOGNAME

DBTYPE_WSTR

The three-part name identifier.

SS_UDT_SCHEMANAME

DBTYPE_WSTR

The three-part name identifier.

SS_UDT_NAME

DBTYPE_WSTR

The three-part name identifier.

SS_UDT_ASSEMBLY_TYPENAME

DBTYPE_WSTR

The Assembly Qualified Name, which includes the type name and all the assembly identification necessary to be referenced by the CLR.

The SQL_ASSEMBLIES Schema Rowset

The SQL Server Native Client OLE DB provider exposes a new provider specific schema rowset that describes the registered UDTs. The ASSEMBLY server may be specified as a DBTYPE_WSTR, but is not present in the rowset. If not specified, the rowset will default to the current server. The SQL_ASSEMBLIES schema rowset is defined in the following table.

Column name

Type

Description

ASSEMBLY_CATALOG

DBTYPE_WSTR

The catalog name of the assembly that contains the type.

ASSEMBLY_SCHEMA

DBTYPE_WSTR

The schema name, or owner name, of the assembly that contains the type. Although assemblies are scoped by database and not by schema, they still have an owner which is reflected here.

ASSEMBLY_NAME

DBTYPE_WSTR

The name of the assembly that contains the type.

ASSEMBLY_ID

DBTYPE_UI4

The object id of the assembly that contains the type.

PERMISSION_SET

DBTYPE_WSTR

A value that indicates the scope of access for the assembly. Values include "SAFE", "EXTERNAL_ACCESS", and "UNSAFE".

ASSEMBLY_BINARY

DBTYPE_BYTES

The binary representation of the assembly.

The SQL_ASSEMBLIES_ DEPENDENCIES Schema Rowset

SQL Server Native Client OLE DB provider exposes a new provider-specific schema rowset that describes the assembly dependencies for a specified server. ASSEMBLY_SERVER may be specified by the caller as a DBTYPE_WSTR, but is not present in the rowset. If not specified, the rowset will default to the current server. The SQL_ASSEMBLY_DEPENDENCIES schema rowset is defined in the following table.

Column name

Type

Description

ASSEMBLY_CATALOG

DBTYPE_WSTR

The catalog name of the assembly that contains the type.

ASSEMBLY_SCHEMA

DBTYPE_WSTR

The schema name, or owner name, of the assembly that contains the type. Although assemblies are scoped by database and not by schema, they still have an owner, which is reflected here.

ASSEMBLY_ID

DBTYPE_UI4

The object id of the assembly.

REFERENCED_ASSEMBLY_ID

DBTYPE_UI4

The object id of the referenced assembly.

The SQL_USER_TYPES Schema Rowset

SQL Server Native Client OLE DB provider exposes new schema rowset, SQL_USER_TYPES, that describes when the registered UDTs for a specified server is added. UDT_SERVER must be specified as a DBTYPE_WSTR by the caller but is not present in the rowset. The SQL_USER_TYPES schema rowset is defined in the following table.

Column name

Type

Description

UDT_CATALOGNAME

DBTYPE_WSTR

For UDT columns this property is a string specifying the name of the catalog where the UDT is defined.

UDT_SCHEMANAME

DBTYPE_WSTR

For UDT columns this property is a string specifying the name of the schema where the UDT is defined.

UDT_NAME

DBTYPE_WSTR

The name of the assembly containing the UDT class.

UDT_ASSEMBLY_TYPENAME

DBTYPE_WSTR

Full type name (AQN) includes type name prefixed by namespace if applicable.

The COLUMNS Schema Rowset

Additions to the COLUMNS schema rowset include the following columns.

Column name

Type

Description

SS_UDT_CATALOGNAME

DBTYPE_WSTR

For UDT columns this property is a string specifying the name of the catalog where the UDT is defined.

SS_UDT_SCHEMANAME

DBTYPE_WSTR

For UDT columns this property is a string specifying the name of the schema where the UDT is defined.

SS_UDT_NAME

DBTYPE_WSTR

The name of the UDT

SS_UDT_ASSEMBLY_TYPENAME

DBTYPE_WSTR

Full type name (AQN) includes type name prefixed by namespace if applicable.

OLE DB Property Set Additions and Changes

SQL Server Native Client adds new values or changes to many of the core OLE DB property sets.

The DBPROPSET_SQLSERVERPARAMETER Property Set

In order to support UDTs through OLE DB, SQL Server Native Client implements the new DBPROPSET_SQLSERVERPARAMETER property set which contains the following values.

Name

Type

Description

SSPROP_PARAM_UDT_CATALOGNAME

DBTYPE_WSTR

The three-part name identifier.

For UDT parameters, this property is a string that specifies the name of the catalog where the user-defined type is defined.

SSPROP_PARAM_UDT_SCHEMANAME

DBTYPE_WSTR

The three-part name identifier.

For UDT parameters, this property is a string that specifies the name of the schema where the user-defined type is defined.

SSPROP_PARAM_UDT_NAME

DBTYPE_WSTR

The three-part name identifier.

For UDT columns, this property is a string that specifies the single part name of the user-defined type.

SSPROP_PARAM_UDT_NAME is mandatory. SSPROP_PARAM_UDT_CATALOGNAME and SSPROP_PARAM_UDT_SCHEMANAME are optional. If any of the properties are specified incorrectly DB_E_ERRORSINCOMMAND will be returned. If both SSPROP_PARAM_UDT_CATALOGNAME and SSPROP_PARAM_UDT_SCHEMANAME are not specified, then the UDT must be defined in the same database and schema as the table. If the UDT definition is not in the same schema as the table (but is in the same database), then SSPROP_PARAM_UDT_SCHEMANAME must be specified. If the UDT definition is in a different database then both SSPROP_PARAM_UDT_CATALOGNAME and SSPROP_PARAM_UDT_SCHEMANAME must be specified.

The DBPROPSET_SQLSERVERCOLUMN Property Set

To support the creation of tables in the ITableDefinition interface, SQL Server Native Client adds the following three new columns to the DBPROPSET_SQLSERVERCOLUMN property set.

Name

Description

Type

Description

SSPROP_COL_UDT_CATALOGNAME

UDT_CATALOGNAME

VT_BSTR

For columns of type DBTYPE_UDT, this property is a string specifying the name of the catalog where the UDT is defined.

SSPROP_COL_UDT_SCHEMANAME

UDT_SCHEMANAME

VT_BSTR

For columns of type DBTYPE_UDT, this property is a string specifying the name of the schema where the UDT is defined.

SSPROP_COL_UDT_NAME

UDT_NAME

VT_BSTR

For columns of type DBTYPE_UDT, this property is a string specifying the single part name of the UDT. For other column types, this property returns an empty string.

Note

UDTs do not appear in the PROVIDER_TYPES schema rowset. All columns have read and write access.

ADO will refer to these properties by using the corresponding entry in the Description column.

SSPROP_COL_UDTNAME is mandatory. SSPROP_COL_UDT_CATALOGNAME and SSPROP_COL_UDT_SCHEMANAME are optional. If any of the properties are specified incorrectly, DB_E_ERRORSINCOMMAND will be returned.

If neither SSPROP_COL_UDT_CATALOGNAME nor SSPROP_COL_UDT_SCHEMANAME is specified, the UDT must be defined in the same database and schema as the table.

If the UDT definition is not in the same schema as the table (but is in the same database), SSPROP_COL_UDT_SCHEMANAME must be specified.

If the UDT definition is in a different database, both SSPROP_COL_UDT_CATALOGNAME and SSPROP_COL_UDT_SCHEMANAME must be specified.

OLE DB Interface Additions and Changes

SQL Server Native Client adds new values or changes to many of the core OLE DB interfaces.

The ISSCommandWithParameters Interface

To support UDTs through OLE DB, SQL Server Native Client implements a number of changes, including the addition of the ISSCommandWithParameters interface. This new interface inherits from the core OLE DB interface ICommandWithParameters. In addition to the three methods inherited from ICommandWithParameters; GetParameterInfo, MapParameterNames, and SetParameterInfo; ISSCommandWithParameters provides the GetParameterProperties and SetParameterProperties methods that are used to handle server specific data types.

Note

The ISSCommandWithParameters interface also makes use of the new SSPARAMPROPS structure.

The IColumnsRowset Interface

In addition to the ISSCommandWithParameters interface, SQL Server Native Client also adds new values to the rowset returned from calling the IColumnsRowset::GetColumnRowset method including the following.

Column Name

Type

Description

DBCOLUMN_SS_UDT_CATALOGNAME

DBTYPE_WSTR

A UDT catalog name identifier.

DBCOLUMN_SS_UDT_SCHEMANAME

DBTYPE_WSTR

A UDT schema name identifier.

DBCOLUMN_SS_UDT_NAME

DBTYPE_WSTR

A UDT name identifier.

DBCOLUMN_SS_ASSEMBLY_TYPENAME

DBTYPE_WSTR

The assembly qualified name, which includes the type name and all the assembly identification necessary to be referenced by the CLR.

You can differentiate a server UDT column from other binary types when the DBCOLUMN_TYPE is set to DBTYPE_UDT by looking at the added UDT metadata specified above. If that data is partially complete, the server type is a UDT. For non-UDT server types, these columns are always returned as NULL.

SQL Server Native Client ODBC Driver

A number of changes have been made in the SQL Server Native Client ODBC driver to support UDTs. The SQL Server Native Client ODBC driver maps the SQL Server UDT to SQL_SS_UDT driver-specific SQL data type identifier. UDT columns are surfaced as SQL_SS_UDT. If you map a UDT column explicitly to another type in a SQL statement by using the ToString or ToXMLString methods of the UDT or via the CAST/CONVERT function, the type of the column in the result set reflects the actual type the column was converted to

SQLColAttribute, SQLDescribeParam, SQLGetDescField

Four new driver-specific descriptor fields have been added to provide additional information for either a UDT column of a result set, or a UDT parameter of stored procedure/parameterized query, to be retrieved via the SQLColAttribute, SQLDescribeParam, and SQLGetDescField functions.

The four new descriptor fields that have been added are SQL_CA_SS_UDT_CATALOG_NAME, SQL_CA_SS_UDT_SCHEMA_NAME, SQL_CA_SS_UDT_TYPE_NAME, and SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAME.

SQLColumns, SQLProcedureColumns

In addition, three new driver specific columns are added to the result set returned from the SQLColumns and SQLProcedureColumns functions to provide additional information about either a UDT result set column or a UDT parameter. These three new columns are SS_UDT_CATALOG_NAME, SS_UDT_SCHEMA_NAME, and SS_UDT_ASSEMBLY_TYPE_NAME.

Supported Conversions

When converting from SQL to C data types, SQL_C_WCHAR, SQL_C_BINARY, and SQL_C_CHAR can all be converted to SQL_SS_UDT. Note, however, that binary data is converted to a hex string when converting from the SQL_C_WCHAR and SQL_C_CHAR SQL data types.

When converting from C to SQL data types, SQL_C_WCHAR, SQL_C_BINARY, and SQL_C_CHAR can all be converted to SQL_SS_UDT. Note however that binary data is converted to a hex string when converting from the SQL_C_WCHAR and SQL_C_CHAR SQL data types.

See Also

Reference

ISSCommandWithParameters (OLE DB)

Other Resources

SQL Server Native Client Features