Compartilhar via

Registrar tipos definidos pelo usuário no SQL Server

Aplica-se:SQL Server

Para usar um UDT (tipo definido pelo usuário) no SQL Server, você deve registrá-lo. O registro de um UDT envolve o registro do assembly e a criação do tipo no banco de dados em que deseja usá-lo. Os UDTs têm escopo para um banco de dados individual e não podem ser usados em vários bancos de dados, a menos que o assembly idêntico e o UDT sejam registrados em cada banco de dados. Depois que o assembly UDT for registrado e o tipo for criado, você poderá usar o UDT no Transact-SQL e no código do cliente. Para obter mais informações, consulte tipos clr definidos pelo usuário.

Usar o Visual Studio para implantar UDTs

A maneira mais fácil de implantar o UDT é usando o Visual Studio. Para cenários de implantação mais complexos e a maior flexibilidade, no entanto, use Transact-SQL conforme discutido posteriormente neste artigo.

Siga estas etapas para criar e implantar um UDT usando o Visual Studio:

  1. Crie um novo projeto de banco de dados nos nós da linguagem Visual Basic ou Visual C# .

  2. Adicione uma referência ao banco de dados do SQL Server que conterá o UDT.

  3. Adicione uma classe User-Defined Type .

  4. Escreva o código para implementar a UDT.

  5. No menu Compilar , selecione Implantar. Isso registra o assembly e cria o tipo no banco de dados do SQL Server.

Usar Transact-SQL para implantar UDTs

A sintaxe Transact-SQL CREATE ASSEMBLY é usada para registrar o assembly no banco de dados no qual você deseja usar o UDT. Ele é armazenado internamente em tabelas do sistema de banco de dados, não externamente no sistema de arquivos. Se a UDT for dependente de assembly externos, eles também deverão ser carregados no banco de dados. A instrução CREATE TYPE é usada para criar a UDT no banco de dados no qual ela deve ser usada. Para obter mais informações, consulte CREATE ASSEMBLY e CREATE TYPE.

Usar criar assembly

A sintaxe CREATE ASSEMBLY registra o assembly no banco de dados no qual você deseja usar o UDT. Quando o assembly é registrado, não possui dependências.

A criação de várias versões do mesmo assembly em um determinado banco de dados não é permitida. No entanto, é possível criar várias versões do mesmo assembly com base na cultura em um determinado banco de dados. O SQL Server distingue várias versões de cultura de um assembly por nomes diferentes, conforme registrado na instância do SQL Server. Para obter mais informações, consulte Criar e usar assemblies de nome forte.

Quando CREATE ASSEMBLY é executado com os conjuntos de permissões SAFE ou EXTERNAL_ACCESS, o assembly é verificado para verificar se ele é verificável e digite seguro. Se você omitir a especificação de um conjunto de permissões, SAFE será assumido. O código com o conjunto de permissões UNSAFE não está marcado. Para obter mais informações sobre conjuntos de permissões de assembly, consulte Assemblies de design.

Exemplo

A instrução Transact-SQL a seguir registra o assembly Point no SQL Server no banco de dados AdventureWorks2025, com a permissão SAFE definida. Se a cláusula WITH PERMISSION_SET for omitida, o assembly será registrado com o conjunto de permissões SAFE.

USE AdventureWorks2022;

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

A instrução Transact-SQL a seguir registra o assembly usando <assembly_bits> argumento na cláusula FROM. Esse valor varbinary representa o arquivo como um fluxo de bytes.

USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78

Usar o tipo de criação

Depois que o assembly for carregado no banco de dados, você poderá criar o tipo usando a instrução Transact-SQL CREATE TYPE. Isto acrescenta o tipo à lista de tipos disponíveis para esse banco de dados. O tipo tem escopo de banco de dados e só pode ser usado no banco de dados no qual foi criado. Se a UDT já existir no banco de dados, a instrução CREATE TYPE falhará com um erro.

Observação

A sintaxe CREATE TYPE também é usada para criar tipos de dados de alias nativos do SQL Server e destina-se a substituir sp_addtype como um meio de criar tipos de dados de alias. Alguns dos argumentos opcionais na sintaxe CREATE TYPE referem-se à criação de UDTs e não são aplicáveis à criação de tipos de dados de alias (como tipo base).

Para obter mais informações, consulte CREATE TYPE.

Exemplo

A instrução Transact-SQL a seguir cria o tipo Point. O EXTERNAL NAME é especificado usando a sintaxe de nomenclatura de duas partes de <assembly_name>.<udt_name>.

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

Remover um UDT do banco de dados

A instrução DROP TYPE remove um UDT do banco de dados atual. Depois que um UDT for removido, você poderá usar a instrução DROP ASSEMBLY para remover o assembly do banco de dados.

A instrução DROP TYPE não é executada nas seguintes situações:

  • Tabelas no banco de dados que contêm colunas definidas usando a UDT.

  • Funções, procedimentos armazenados ou gatilhos que usam variáveis ou parâmetros do UDT, criados no banco de dados com a cláusula WITH SCHEMABINDING.

Exemplo

O Transact-SQL a seguir deve ser executado na ordem a seguir. Primeiro, a tabela que faz referência ao Point UDT deve ser descartada, depois o tipo e, por fim, o assembly.

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

Localizar dependências de UDT

Se houver objetos dependentes, como tabelas com definições de coluna UDT, a instrução DROP TYPE falhará. Ele também falhará se houver funções, procedimentos armazenados ou gatilhos criados no banco de dados usando a cláusula WITH SCHEMABINDING, se essas rotinas usarem variáveis ou parâmetros do tipo definido pelo usuário. Primeiro, você deve remover todos os objetos dependentes e, em seguida, executar a instrução DROP TYPE.

A consulta Transact-SQL a seguir localiza todas as colunas e parâmetros que usam um UDT no banco de dados AdventureWorks2025.

USE AdventureWorks2022;

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
     INNER JOIN sys.objects AS o
         ON o.object_id = c.object_id
     INNER JOIN sys.assembly_types AS at
         ON at.user_type_id = c.user_type_id;

Manter UDTs

Você não pode modificar um UDT depois que ele é criado em um banco de dados do SQL Server, embora você possa alterar o assembly no qual o tipo se baseia. Na maioria dos casos, você deve remover a UDT do banco de dados com a instrução Transact-SQL DROP TYPE, fazer alterações no assembly subjacente e recarregá-la usando a instrução ALTER ASSEMBLY. Em seguida, será necessário recriar a UDT e todos os objetos dependentes.

Exemplo

A instrução ALTER ASSEMBLY é usada depois que você fez alterações no código-fonte no assembly UDT e a recompilou. Ele copia o arquivo .dll no servidor e o associa novamente ao novo assembly. Para obter a sintaxe completa, consulte ALTER ASSEMBLY.

A instrução Transact-SQL ALTER ASSEMBLY a seguir recarrega o assembly Point.dll do local especificado no disco.

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

Usar o assembly de alteração para adicionar o código-fonte

A cláusula ADD FILE na sintaxe ALTER ASSEMBLY não está presente no CREATE ASSEMBLY. Você pode usá-la para adicionar código-fonte ou outros arquivos associados a um assembly. Os arquivos serão copiados dos seus locais originais e armazenados em tabelas do sistema do banco de dados. Isso garante que você sempre tenha código-fonte ou outros arquivos à mão, se precisar recriar ou documentar a versão atual do UDT.

A instrução Transact-SQL ALTER ASSEMBLY a seguir adiciona o código-fonte da classe Point.cs para o Point UDT. Isso copia o texto contido no arquivo Point.cs e o armazena no banco de dados sob o nome PointSource.

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

As informações do assembly são armazenadas na tabela sys.assembly_files no banco de dados em que o assembly foi instalado. A tabela sys.assembly_files contém as colunas a seguir.

Coluna Descrição
assembly_id O identificador definido para o assembly. Este número é atribuído a todos os objetos relacionados ao mesmo assembly.
name O nome do objeto.
file_id Um número que identifica cada objeto, com o primeiro objeto associado a um determinado assembly_id recebendo o valor de 1. Se houver vários objetos associados ao mesmo assembly_id, cada file_id subsequente incrementa o valor 1.
content A representação hexadecimal do assembly ou arquivo.

Você pode usar a função CAST ou CONVERT para converter o conteúdo da coluna content em texto legível. A consulta a seguir converte o conteúdo do arquivo Point.cs em texto legível, usando o nome na cláusula WHERE para restringir o conjunto de resultados a uma única linha.

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

Se você copiar e colar os resultados em um editor de texto, verá que as quebras de linha e os espaços que existiam no original serão preservados.

Gerenciar UDTs e assemblies

Ao planejar a implementação de UDTs, considere quais métodos são necessários no assembly do UDT propriamente dito e quais métodos devem ser criados em assemblies separados e implementados como funções definidas pelo usuário ou procedimentos armazenados. Separar métodos em assemblies separados permite que você atualize o código sem afetar os dados que podem ser armazenados em uma coluna UDT em uma tabela. Você pode modificar assemblies UDT sem descartar colunas UDT e outros objetos dependentes somente quando a nova definição puder ler os valores anteriores e a assinatura do tipo não for alterada.

A separação do código processual que pode mudar do código necessário para implementar a UDT simplifica muito a manutenção. Incluir apenas o código necessário para que o UDT funcione e manter suas definições de UDT o mais simples possível reduz o risco de que o próprio UDT precise ser removido do banco de dados para revisões de código ou correções de bug.

A função de conversão de moeda UDT e moeda

O Currency UDT no banco de dados de exemplo AdventureWorks2025 fornece um exemplo útil da maneira recomendada de estruturar um UDT e suas funções associadas. O Currency UDT é usado para lidar com dinheiro com base no sistema monetário de uma cultura específica e permite o armazenamento de diferentes tipos de moeda, como dólares, euros e assim por diante. A classe UDT expõe um nome de cultura como uma cadeia de caracteres e uma quantia de dinheiro como um tipo de dados decimal . Todos os métodos de serialização necessários são contidos no assembly que define a classe. A função que implementa a conversão de moeda de uma cultura para outra é implementada como uma função externa chamada ConvertCurrencye essa função está localizada em um assembly separado. A função ConvertCurrency faz seu trabalho recuperando a taxa de conversão de uma tabela no banco de dados AdventureWorks2025. Se a origem das taxas de conversão for alterada ou se houver outras alterações no código existente, o assembly poderá ser facilmente modificado sem afetar o Currency UDT.

A listagem de código para as funções Currency UDT e ConvertCurrency pode ser encontrada instalando os exemplos clr (common language runtime).

Usar UDTs entre bancos de dados

Por definição, os UDTs têm escopo em um único banco de dados. Portanto, um UDT definido em um banco de dados não pode ser usado em uma definição de coluna em outro banco de dados. Para usar UDTs em vários bancos de dados, você deve executar as instruções CREATE ASSEMBLY e CREATE TYPE em cada banco de dados em assemblies idênticos. Os assemblies são considerados idênticos se tiverem os mesmos nome, nome forte, cultura, versão, conjunto de permissões e conteúdo binário.

Quando a UDT for registrado e estiver acessível nos dois bancos de dados, você poderá converter o valor de UDT de um banco de dados para o outro. UDTs idênticos podem ser usados por bancos de dados nos seguintes cenários:

  • Chamar um procedimento armazenado definido em bancos de dados diferentes.

  • Consultar tabelas definidas em bancos de dados diferentes.

  • Selecionar dados de UDT de uma coluna de UDT da tabela do banco de dados inserindo-a em um segundo banco de dados com coluna de UDT idêntica.

Nessas situações, qualquer conversão necessária do servidor ocorre automaticamente. Não é possível executar as conversões explicitamente usando as funções Transact-SQL CAST ou CONVERT.

Você não precisa executar nenhuma ação para usar UDTs quando o Mecanismo de Banco de Dados do SQL Server cria tabelas de trabalho no banco de dados do sistema tempdb. Isso inclui o tratamento de cursores, variáveis de tabela e funções com valor de tabela definidas pelo usuário que incluem UDTs e que fazem uso transparente de tempdb. No entanto, se você criar explicitamente uma tabela temporária no tempdb que define uma coluna UDT, o UDT deverá ser registrado no tempdb da mesma maneira que para um banco de dados de usuário.

Conteúdo relacionado

  • Last updated on