Registrando tipos definidos pelo usuário no SQL Server

Aplica-se a:SQL Server

Para usar um UDT (tipo definido pelo usuário) no Microsoft 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 em um único banco de dados e não podem ser usados em vários bancos de dados, a menos que o assembly e a UDT idênticos sejam registrados em cada banco de dados. Depois que o assembly UDT for registrado e o tipo criado, você poderá usar o UDT no Transact-SQL e no código do cliente. Para obter mais informações, veja Tipos CLR definidos pelo usuário.

Usando o Visual Studio para implantar UDTs

A maneira mais fácil de implantar o UDT é usando o Microsoft Visual Studio. No entanto, para cenários de implantação mais complexos e a maior flexibilidade, use o Transact-SQL, conforme discutido posteriormente neste tópico.

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 SQL Server que conterá o UDT.

3. Adicione uma classe tipo definido pelo usuário .

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 SQL Server.

Usando o Transact-SQL para implantar UDTs

A sintaxe CREATE ASSEMBLY do Transact-SQL é usada para registrar o assembly no banco de dados no qual você deseja usar o UDT. Ele é armazenado internamente em tabelas do sistema do banco de dados, e 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 será usada. Para obter mais informações, consulte CREATE ASSEMBLY (Transact-SQL) e CREATE TYPE (Transact-SQL).

Usando CREATE ASSEMBLY

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

Não é permitido criar várias versões do mesmo assembly em um determinado banco de dados. Entretanto, é possível criar várias versões do mesmo assembly com base em cultura em um determinado banco de dados. 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 “Criando e usando assemblies de nome forte”, no SDK do .NET Framework.

Quando CREATE ASSEMBLY for executada com o conjunto de permissões SAFE ou EXTERNAL_ACCESS, o assembly será marcado para garantir que seja verificável e fortemente tipado. Se você omitir a especificação de um conjunto de permissões, SAFE será considerado. Códigos com o conjunto de permissões UNSAFE não serão marcados. Para obter mais informações sobre conjuntos de permissões de assembly, confira Criando assemblies.

Exemplo

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

USE AdventureWorks;  
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 AdventureWorks;  
CREATE ASSEMBLY Point  
FROM 0xfeac4 ... 21ac78  

Usando CREATE TYPE

Depois que o assembly for carregado no banco de dados, você poderá criar o tipo usando a instrução CREATE TYPE do Transact-SQL. 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 de CREATE TYPE falhará com um erro.

Para obter mais informações, veja CREATE TYPE (Transact-SQL).

Exemplo

A instrução Transact-SQL a seguir cria o tipo Point . O NOME EXTERNO é especificado usando a sintaxe de nomenclatura de duas partes de AssemblyName. UDTName.

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

Removendo uma UDT do banco de dados

A instrução de DROP TYPE remove uma UDT do banco de dados atual. Quando uma UDT é descartada, você pode usar a instrução de DROP ASSEMBLY para descartar o assembly do banco de dados.

A instrução de 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 da UDT, criadas 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 UDT de Ponto deve ser descartada, depois o tipo e, por fim, o assembly.

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

Localizando dependências do UDT

Se houver objetos dependentes, como tabelas com definições de coluna de UDT, a instrução DROP TYPE falhará. Ela 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. Você deve descartar todos os objetos dependentes primeiro 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 AdventureWorks .

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;  

Mantendo UDTs

Não é possível modificar um UDT depois que ele é criado em um banco de dados SQL Server, embora você possa alterar o assembly no qual o tipo se baseia. Na maioria dos casos, você deve remover o UDT do banco de dados com a instrução DROP TYPE do Transact-SQL, fazer alterações no assembly subjacente e recarregá-lo 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ê faz alterações no código-fonte no assembly do seu UDT e o recompila. Ele copia o arquivo .dll no servidor e o associa novamente ao novo assembly. Para obter a sintaxe completa, consulte ALTER ASSEMBLY (Transact-SQL).

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

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

Usando ALTER ASSEMBLY para adicionar código-fonte

A cláusula ADD FILE na sintaxe ALTER ASSEMBLY não está presente em 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 ALTER ASSEMBLY do Transact-SQL a seguir adiciona o código-fonte da classe Point.cs para o UDT point . O texto contendo o arquivo Point.cs será copiado e armazenado no banco de dados com 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.

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 valor de file_id subsequente será incrementado em 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 de conteúdo em texto legível. A consulta a seguir converte o conteúdo do arquivo Point.cs a texto legível usando o nome da cláusula WHERE para restringir o conjunto de resultados definido 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 foram preservados.

Gerenciando 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 à parte permite atualizar o código sem afetar os dados que podem ser armazenados em uma coluna de UDT na tabela. A modificação dos assemblies de UDT sem descartar colunas de UDT e outros objetos dependentes só é possível quando a nova definição puder ler os valores iniciais e a assinatura do tipo não for alterada.

Separar o código de procedimento que pode ser alterado do código necessário para implementar a UDT simplifica muito a manutenção. Incluir somente do código necessário para que a UDT funcione e manter suas definições de UDT o mais simples possível reduzem o risco da necessidade de descarte do próprio UDT do banco de dados para revisões de código ou correção de bug.

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

O Conversor de Moedas UDT no banco de dados de exemplo AdventureWorks fornece um exemplo útil da maneira recomendada de estruturar um UDT e suas funções associadas. O Conversor de Moedas 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 quantidade 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 ConvertCurrency e 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 AdventureWorks . Se a origem das taxas de conversão precisar ser alterada ou se houver outras alterações no código existente, o assembly poderá ser facilmente modificado sem afetar o Conversor de Moedas UDT.

A listagem de código para as funções Conversor de Moedas UDT e ConvertCurrency pode ser encontrada instalando os exemplos de CLR (Common Language Runtime).

Usando UDTs nos bancos de dados

Por definição, os UDTs têm escopo em um único banco de dados. Por isso, um UDT definido em um banco de dados não pode ser usado em uma definição de coluna de 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.

Observe que você não precisa executar nenhuma ação para usar UDTs quando SQL Server Mecanismo de Banco de Dados 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 do 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.

Consulte Também

Tipos definidos pelo usuário de CLR