Skip to content

Latest commit

 

History

History
67 lines (54 loc) · 5.12 KB

clr-user-defined-types.md

File metadata and controls

67 lines (54 loc) · 5.12 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
CLR User-Defined Types
This article describes the process for creating user-defined types (UDTs) to store CLR objects in a SQL Server database.
rwestMSFT
randolphwest
12/27/2024
sql
clr
reference
validation [CLR integration]
types [CLR integration]
UserDefined serialization format [CLR integration]
null values [CLR integration]
serialization
Native serialization format [CLR integration]
databases [CLR integration]
building database objects [CLR integration], user-defined types
user-defined types [CLR integration]
common language runtime [SQL Server], user-defined types
UDTs [CLR integration]
database objects [CLR integration], user-defined types
turning on CLR functionality
customizing UDT expression return types [CLR integration]
UDTs [CLR integration], about UDTs
comparing UDT values
annotations [CLR integration]
user-defined types [CLR integration], about UDTs
variables [CLR integration]
invoking UDT methods
indexes [CLR integration]

CLR user-defined types

[!INCLUDE SQL Server]

[!INCLUDE ssNoVersion] gives you the ability to create database objects that are programmed against an assembly created in the [!INCLUDE dnprdnshort-md] common language runtime (CLR). Database objects that can take advantage of the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.

Note

The ability to execute CLR code is set to OFF by default in [!INCLUDE ssNoVersion]. The CLR can be enabled by using the sp_configure system stored procedure.

You can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a [!INCLUDE ssNoVersion] database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single [!INCLUDE ssNoVersion] system data type.

Because UDTs are accessed by the system as a whole, their use for complex data types might negatively affect performance. Complex data is generally best modeled using traditional rows and tables. UDTs in [!INCLUDE ssNoVersion] are well suited to the following type of data:

  • Date, time, currency, and extended numeric types
  • Geospatial applications
  • Encoded or encrypted data

The process of developing UDTs in [!INCLUDE ssNoVersion] consists of the following steps:

  1. Code and build the assembly that defines the UDT. UDTs are defined using any of the languages supported by the [!INCLUDE dnprdnshort-md] common language runtime (CLR) that produce verifiable code. This includes [!INCLUDE c-sharp-md] and [!INCLUDE visual-basic-md] .NET. The data is exposed as fields and properties of a [!INCLUDE dnprdnshort-md] class or structure, and behaviors are defined by methods of the class or structure.

  2. Register the assembly. UDTs can be deployed through the Visual Studio user interface in a database project, or by using the [!INCLUDE tsql] CREATE ASSEMBLY statement, which copies the assembly containing the class or structure into a database.

  3. Create the UDT in SQL Server. Once an assembly is loaded into a host database, you use the [!INCLUDE tsql] CREATE TYPE statement to create a UDT and expose the members of the class or structure as members of the UDT. UDTs exist only in the context of a single database, and, once registered, have no dependencies on the external files from which they were created.

  4. Create tables, variables, or parameters using the UDT. A user-defined type can be used as the column definition of a table, as a variable in a [!INCLUDE tsql] batch, or as an argument of a [!INCLUDE tsql] function or stored procedure.

In this section

Article Description
Create user-defined types Describes how to create UDTs.
Register user-defined types in SQL Server Describes how to register and manage UDTs in [!INCLUDE ssNoVersion].
Work with user-defined types in SQL Server Describes how to create queries using UDTs.
Access user-defined types in ADO.NET Describes how to work with UDTs using the [!INCLUDE dnprdnshort-md] Data Provider for [!INCLUDE ssNoVersion] in ADO.NET.