• Artigo

Implementando tipos definidos pelo usuário

Este tópico descreve como criar e descartar tipos de dados CLR (Common Language Runtime) definidos pelo usuário no SQL Server.

Criando tipos definidos pelo usuário

Para criar um tipo definido pelo usuário no SQL Server, as seguintes etapas devem ser executadas em ordem:

  • Configure o tipo definido pelo usuário como uma classe ou estrutura em uma linguagem que tenha suporte pelo Microsoft.NET Framework. Para mais informações sobre como programar tipos CLR, consulte Tipos CLR definidos pelo usuário. Em seguida, compile a classe ou estrutura para construir um assembly no .NET Framework, usando o compilador de linguagem apropriado.

  • Registre o assembly no SQL Server usando a instrução CREATE ASSEMBLY. Para obter mais informações sobre assemblies no SQL Server, consulte Assemblies (Mecanismo de Banco de Dados).

  • Crie o tipo que referencia o assembly registrado.

ObservaçãoObservação

Implantar um projeto SQL Server no MicrosoftVisual Studio registra um assembly no banco de dados especificado para o projeto. Implantar um projeto também cria tipos de dados CLR definidos pelo usuário no banco de dados para todas as definições de classe com o atributo SqlUserDefinedType. Para obter mais informações, consulte Implantando objetos de banco de dados CLR.
ObservaçãoObservação

Por padrão, a capacidade do SQL Server de executar o código CLR é definida como OFF. Você pode criar, modificar e descartar objetos do banco de dados que referenciam módulos de códigos gerenciados, mas essas referências não serão executadas no SQL Server exceto se a Opção clr enabled estiver habilitada e usando sp_configure.

Para criar, modificar ou descartar um assembly

Para criar um tipo definido pelo usuário

Descartando tipos definidos pelo usuário

Para descartar um tipo definido pelo usuário

ObservaçãoObservação

Tipos definidos pelo usuário não podem ser modificados depois que eles são criados, porque as alterações podem invalidar dados em tabelas ou índices. Para modificar um tipo, é necessário descartar o tipo e recriá-lo em seguida ou emitir uma instrução ALTER ASSEMBLY usando a cláusula WITH UNCHECKED DATA.

Você não pode descartar um tipo definido pelo usuário até que todas as referências a esse tipo estejam removidas. Essas referências podem incluir o seguinte:

  • Colunas definidas no tipo.

  • Colunas computadas e restrições CHECK cujas expressões referenciam o tipo.

  • Exibições associadas a esquema e funções com expressões em suas definições que referenciam o tipo.

  • Parâmetros de funções e procedimentos armazenados.

Para localizar colunas dependentes de um tipo definido pelo usuário

O exemplo a seguir recupera metadados sobre colunas definidas no tipo definido pelo usuário ComplexNumber.

SELECT * FROM sys.columns 
WHERE user_type_id = TYPE_ID('ComplexNumber');

O exemplo a seguir recupera metadados limitados para usuários menos privilegiados sobre colunas definidas em tipo ComplexNumberdefinido pelo usuário.

SELECT * FROM sys.column_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');

Para encontrar expressões em colunas computadas, expressões de restrição CHECK e expressões de módulo dependente em um tipo definido pelo usuário

O exemplo a seguir recupera os nomes de colunas computadas (e suas tabelas) com uma dependência no tipo definido pelo usuário SimpleUdt.

SELECT OBJECT_SCHEMA_NAME (referencing_id) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_name,
    COL_NAME(referencing_id, referencing_minor_id) AS column_name,
    is_caller_dependent,
    is_ambiguous
FROM sys.sql_expression_dependencies
WHERE referenced_id = TYPE_ID('SimpleUdt')
    AND referenced_class = 6 
    AND OBJECTPROPERTY(referencing_id, 'IsTable')=1;

O exemplo a seguir recupera os nomes de restrições CHECK (e os objetos nos quais eles são definidos) com uma dependência no tipo definido pelo usuário SimpleUdt.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
    OBJECT_NAME(o.parent_object_id) AS table_name,
    OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
ON o.object_id = d.referencing_id
WHERE referenced_id = TYPE_ID('SimpleUdt')
AND referenced_class = 6 
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst')=1;

O exemplo a seguir recupera os nomes dos módulos cujas definições referenciam o tipo definido pelo usuário SimpleUdt.

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('SimpleUdt', 'TYPE');
GO

Para localizar parâmetros dependentes de um tipo definido pelo usuário

O exemplo a seguir recupera os nomes de parâmetros (e os objetos aos quais eles pertencem) no tipo definido pelo usuário ComplexNumber.

SELECT OBJECT_NAME(object_id) AS object_name,
    NULL AS procedure_number,
    name AS param_name,
    parameter_id AS param_num,
    TYPE_NAME(p.user_type_id) AS type_name
FROM sys.parameters AS p
WHERE p.user_type_id = TYPE_ID('ComplexNumber')
ORDER BY object_name, procedure_number, param_num;

O exemplo a seguir recupera metadados limitados para usuários menos privilegiados sobre parâmetros definidos no tipo definido pelo usuário ComplexNumber.

SELECT * FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');