title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |
---|---|---|---|---|---|---|---|---|---|
Implementing assemblies |
Learn how to work with assemblies hosted on SQL Server, including how to create/modify assemblies, drop or enable/disable assemblies, and manage versions. |
rwestMSFT |
randolphwest |
08/02/2024 |
sql |
clr |
reference |
|
[!INCLUDE SQL Server]
This article provides information about the following areas to help you implement and work with assemblies in the database:
- Creating assemblies
- Modifying assemblies
- Dropping, disabling, and enabling assemblies
- Managing assembly versions
Assemblies are created in [!INCLUDE ssNoVersion] by using the [!INCLUDE tsql] CREATE ASSEMBLY
statement, or in the [!INCLUDE ssManStudioFull] by using the Assembly Assisted Editor. Additionally, deploying a SQL Server Project in [!INCLUDE msCoName] [!INCLUDE vsprvs] registers an assembly in the database that was specified for the project. For more information, see Deploying CLR Database Objects.
- With Transact-SQL: CREATE ASSEMBLY (Transact-SQL)
- With SQL Server Management Studio: Assemblies - Properties
Assemblies are modified in [!INCLUDE ssNoVersion] by using the [!INCLUDE tsql] ALTER ASSEMBLY
statement or in [!INCLUDE ssManStudioFull] by using the Assembly Assisted Editor.
- With Transact-SQL: ALTER ASSEMBLY (Transact-SQL)
- With SQL Server Management Studio: Assemblies - Properties
You can modify an assembly when you want to perform the following actions:
-
Change the implementation of the assembly by uploading a newer version of the binaries of the assembly. For more information, see Manage assembly versions later in this article.
-
Change the permission set of the assembly. For more information, see Designing assemblies.
-
Change the visibility of the assembly. Visible assemblies are available for referencing in [!INCLUDE ssNoVersion]. Nonvisible assemblies aren't available, even if they're uploaded in the database. By default, assemblies uploaded to an instance of [!INCLUDE ssNoVersion] are visible.
-
Add or drop a debug or source file associated with the assembly.
Assemblies are dropped by using the [!INCLUDE tsql] DROP ASSEMBLY
statement or [!INCLUDE ssManStudioFull].
- With Transact-SQL: DROP ASSEMBLY (Transact-SQL)
- With SQL Server Management Studio: Delete Objects
By default, all assemblies that are created in [!INCLUDE ssNoVersion] are disabled from executing. You can use the clr enabled
option of the sp_configure
system stored procedure, to disable or enable the execution of all assemblies that are uploaded in [!INCLUDE ssNoVersion]. Disabling assembly execution prevents common language runtime (CLR) functions, stored procedures, triggers, aggregates, and user-defined types from executing, and stops any that are currently executing. Disabling assembly execution doesn't disable the ability to create, alter, or drop assemblies. For more information, see Server configuration: clr enabled.
For more information, see sp_configure.
When an assembly is uploaded to an instance [!INCLUDE ssNoVersion], the assembly is stored and managed within the database system catalogs. Any changes made to the definition of the assembly in the [!INCLUDE msCoName] [!INCLUDE dnprdnshort] should be propagated to the assembly that is stored in the database catalog.
When you have to modify an assembly, you must issue an ALTER ASSEMBLY
statement to update the assembly in the database. This statement updates the assembly to the latest copy of [!INCLUDE dnprdnshort] modules holding its implementation.
The WITH UNCHECKED DATA
clause of the ALTER ASSEMBLY
statement instructs [!INCLUDE ssNoVersion] to refresh even those assemblies upon which persisted data in the database is dependent. Specifically, you must specify WITH UNCHECKED DATA
if any of the following exist:
-
Persisted computed columns that reference methods in the assembly, either directly, or indirectly, through [!INCLUDE tsql] functions or methods.
-
Columns of a CLR user-defined type that depend on the assembly, and the type implements a
UserDefined
(non-Native
) serialization format.
Caution
If WITH UNCHECKED DATA
isn't specified, [!INCLUDE ssNoVersion] tries to prevent ALTER ASSEMBLY
from executing if the new assembly version affects existing data in tables, indexes, or other persistent sites. However, [!INCLUDE ssNoVersion] doesn't guarantee that computed columns, indexes, indexed views, or expressions will be consistent with the underlying routines and types when the CLR assembly is updated. Be careful when you execute ALTER ASSEMBLY
to make sure that there's no mismatch between the result of an expression and a value that is based on that expression stored in the assembly.
Only members of the db_owner and db_ddlowner fixed database role can execute run ALTER ASSEMBLY
by using the WITH UNCHECKED DATA
clause.
[!INCLUDE ssNoVersion] posts a message to the Windows Application event log that the assembly was modified with unchecked data in the tables. [!INCLUDE ssNoVersion] then marks any tables that contain data dependent on the assembly as having unchecked data. The has_unchecked_assembly_data
column of the sys.tables
catalog view contains the value 1
for tables that contain unchecked data, and 0
for tables without unchecked data.
To resolve the integrity of unchecked data, run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS
against each table that has unchecked data. If DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS
fails, you must either delete the table rows that aren't valid or modify the assembly code to address problems, and then issue more ALTER ASSEMBLY
statements.
ALTER ASSEMBLY
changes the assembly version. The culture and public key token of the assembly remain the same. [!INCLUDE ssnoversion-md] doesn't allow registering different versions of an assembly with the same name, culture, and public key.
If references to assemblies stored in [!INCLUDE ssNoVersion] are redirected to specific versions by using publisher policy or computer-wide administrator policy, you must do either of the following actions:
-
Make sure the new version to which this redirection is made is in the database.
-
Modify any statements to the external policy files of the computer or publisher policy to make sure that they reference the specific version that is in the database.
Otherwise, an attempt to load a new assembly version to the instance of [!INCLUDE ssNoVersion] fails.
For more information, see ALTER ASSEMBLY.