The CREATE ASSEMBLY syntax registers the assembly in the database in which you wish to use the UDT. Once the assembly is registered, it has no dependencies.

Creating multiple versions of the same assembly in a given database is not allowed. However, it is possible to create multiple versions of the same assembly based on culture in a given database. SQL Server distinguishes multiple culture versions of an assembly by different names as registered in the instance of SQL Server. For more information, see "Creating and Using Strong-Named Assemblies" in the .NET Framework SDK.

When CREATE ASSEMBLY is executed with the SAFE or EXTERNAL_ACCESS permission sets, the assembly is checked to make sure that it is verifiable and type safe. If you omit specifying a permission set, SAFE is assumed. Code with the UNSAFE permission set is not checked. For more information about assembly permission sets, see Designing Assemblies.

Example

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78

Once the assembly is loaded into the database, you can then create the type using the Transact-SQL CREATE TYPE statement. This adds the type to the list of available types for that database. The type has database scope and the type can only be used in the database in which it was created. If the UDT already exists in the database, the CREATE TYPE statement fails with an error.

Note:
The CREATE TYPE syntax is also used for creating native SQL Server alias data types, and is intended to replace sp_addtype as a means of creating alias data types. Some of the optional arguments in the CREATE TYPE syntax refer to creating UDTs, and are not applicable to creating alias data types (such as base type).

Note:
Beginning with SQL Server 2005, on a SQL Server database with a compatibility level of "80" you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "100".

For more information, see CREATE TYPE (Transact-SQL).

Example

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

The following Transact-SQL must execute in the following order. First the table which references the Point UDT must be dropped, then the type, and finally the assembly.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

If there are dependent objects, such as tables with UDT column definitions, the DROP TYPE statement fails. It also fails if there are functions, stored procedures, or triggers created in the database using the WITH SCHEMABINDING clause, if these routines use variables or parameters of the user-defined type. You must first drop all dependent objects, and then execute the DROP TYPE statement.

The following Transact-SQL query locates all of the columns and parameters that use a UDT in the AdventureWorks database.

USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
     , c.name AS minor_name, c.type_desc AS minor_type_desc
     , at.assembly_class
  FROM (
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
          FROM sys.columns
     UNION ALL
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
          FROM sys.parameters
     ) AS c
  JOIN sys.objects AS o
    ON o.object_id = c.object_id
  JOIN sys.assembly_types AS at
    ON at.user_type_id = c.user_type_id;

The ALTER ASSEMBLY statement is used after you have made changes to the source code in your UDT assembly and recompiled it. It copies the .dll file to the server and rebinds to the new assembly. For the complete syntax, see ALTER ASSEMBLY (Transact-SQL).

The following Transact-SQL ALTER ASSEMBLY statement reloads the Point.dll assembly from the specified location on disk.

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'

The ADD FILE clause in the ALTER ASSEMBLY syntax is not present in CREATE ASSEMBLY. You can use it to add source code or any other files associated with an assembly. The files are copied from their original locations and stored in system tables in the database. This ensures that you always have source code or other files on hand should you ever need to re-create or document the current version of the UDT.

The following Transact-SQL ALTER ASSEMBLY statement adds the Point.cs class source code for the Point UDT. This copies the text contained in the Point.cs file and stores it in the database under the name "PointSource".

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

Assembly information is stored in the sys.assembly_files table in the database where the assembly has been installed. The sys.assembly_files table contains the following columns.

assembly_id

The identifier defined for the assembly. This number is assigned to all objects relating to the same assembly.

name

The name of the object.

file_id

A number identifying each object, with the first object associated with a given assembly_id being given the value of 1. If there are multiple objects associated with the same assembly_id, then each subsequent file_id value is incremented by 1.

content

The hexadecimal representation of the assembly or file.

You can use the CAST or CONVERT function to convert the contents of the content column to readable text. The following query converts the contents of the Point.cs file to readable text, using the name in the WHERE clause to restrict the result set to a single row.

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

If you copy and paste the results in to a text editor, you will see that the line breaks and spaces that existed in the original have been preserved.

The Currency UDT in the AdventureWorks sample database provides a useful example of the recommended way to structure a UDT and its associated functions. The Currency UDT is used for handling money based on the monetary system of a particular culture, and allows for storage of different currency types, such as dollars, euros, and so forth. The UDT class exposes a culture name as a string, and an amount of money as a decimal data type. All of the necessary serialization methods are contained within the assembly defining the class. The function that implements currency conversion from one culture to another is implemented as an external function named ConvertCurrency, and this function is located in a separate assembly. The ConvertCurrency function does its work by retrieving the conversion rate from a table in the AdventureWorks database. If the source of the conversion rates should ever change, or if there should be any other changes to the existing code, the assembly can be easily modified without affecting the Currency UDT.

The code listing for the Currency UDT and ConvertCurrency functions can be found by installing the common language runtime (CLR) samples. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

UDTs are by definition scoped to a single database. Therefore, a UDT defined in one database cannot be used in a column definition in another database. In order to use UDTs in multiple databases, you must execute the CREATE ASSEMBLY and CREATE TYPE statements in each database on identical assemblies. Assemblies are considered identical if they have the same name, strong name, culture, version, permission set, and binary contents.

Once the UDT is registered and accessible in both databases, you can convert a UDT value from one database for use in another. Identical UDTs can be used across databases in the following scenarios:

• Calling stored procedure defined in different databases.
  
• Querying tables defined in different databases.
  
• Selecting UDT data from one database table UDT column and inserting it into a second database with an identical UDT column.
  

In these situations, any conversion required by the server occurs automatically. You are not able to perform the conversions explicitly using the Transact-SQL CAST or CONVERT functions.

Note that you do not need to take any action for using UDTs when SQL Server Database Engine creates work tables in the tempdb system database. This includes the handling of cursors, table variables, and user-defined table-valued functions that include UDTs and that transparently make use of tempdb. However, if you explicitly create a temporary table in tempdb that defines a UDT column, then the UDT must be registered in tempdb the same way as for a user database.