Registrando tipos definidos pelo usuário no SQL Server
Para usar um UDT (tipo definido pelo usuário) no MicrosoftSQL 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. Quando o assembly do UDT é registrado e o tipo é criado, você pode usar a UDT no Transact-SQL e em código de cliente. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
Usando o Visual Studio para implantar UDTs
O modo mais fácil de implantar seu UDT é usar o Microsoft Visual Studio. Para cenários de implantação mais complexos e mais flexibilidade, entretanto, use o Transact-SQL como discutido posteriormente neste tópico.
Siga estas etapas para criar e implantar um UDT usando o Visual Studio:
Crie um novo projeto Banco de Dados no Visual Basic ou nós de linguagem do Visual C#.
Acrescente uma referência ao banco de dados do SQL Server que conterá a UDT.
Adicione uma classe Tipo Definido pelo Usuário.
Escreva o código para implementar a UDT.
No menu Compilação, selecione Implantar. O assembly será registrado e o tipo será criado no banco de dados do 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 a 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. O SQL Server diferencia várias versões de cultura de um assembly por nomes diferentes dos registrados 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, consulte 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 do Transact-SQL a seguir registra o assembly que usa o argumento <assembly_bits> na cláusula FROM. Este valor varbinary representa o arquivo como um fluxo de bytes.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78
Usando CREATE TYPE
Quando o assembly é carregado no banco de dados, você pode criar o tipo que usa a instrução 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.
.gif "Observação") Observação |
|---|
A sintaxe CREATE TYPE também é usada para criar tipos de dados de alias do SQL Server e tem o objetivo de substituir sp_addtype como uma forma de criar tipos de dados de alias. Alguns argumentos opcionais da sintaxe CREATE TYPE se referem à criação de UDTs e não são aplicáveis à criação de tipos de dados de alias (como um tipo de base). |
| Observação |
|---|
A partir do SQL Server 2005, em um banco de dados do SQL Server com um nível de compatibilidade "80", não é possível criar gatilhos, agregações, funções, procedimentos armazenados ou tipos de dados definidos pelo usuário gerenciados. Para usufruir esses recursos de integração do CLR do SQL Server, você deve usar o procedimento armazenado sp_dbcmptlevel (Transact-SQL) a fim de definir o nível de compatibilidade do banco de dados como "100". |
Para obter mais informações, consulte CREATE TYPE (Transact-SQL).
Exemplo
Em seguida, a instrução do Transact-SQL a seguir cria o tipo Point. EXTERNAL NAME é especificado usando a sintaxe com nome em duas partes 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 nesta ordem: Primeiro, a tabela que referencia a UDT Point deve ser descartada, depois o tipo e, finalmente, 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 do Transact-SQL a seguir localiza todas as colunas e parâmetros que usam uma 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 quando ele for criado em um banco de dados do SQL Server, embora você possa alterar o assembly em que o tipo é baseado. Na maioria dos casos, você deve remover a UDT do banco de dados com a instrução DROP TYPE do Transact-SQL, fazer alterações no assembly subjacente e recarregar usando a instrução ALETER 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 do Transact-SQL a seguir recarregar 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 a 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 com um determinado assembly_id recebendo o valor de 1.. Se houver vários objetos associados ao mesmo assembly_id, então cada valor file_id subseqüente 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 content a um 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 UDT Currency do banco de dados AdventureWorks fornece um exemplo útil da maneira recomendada de estruturar um UDT e suas funções associadas. O UDT Currency é usado para manipular dinheiro com base no sistema monetário de uma cultura específica e permite armazenar diferentes tipos de moeda, como dólares, euros etc. A classe do UDT expõe o nome da cultura como uma cadeia de caracteres e o valor 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 a outra é implementada como uma função externa denominada ConvertCurrency e essa função é localizada em um assembly separado. A função ConvertCurrency executa seu trabalho recuperando a taxa de conversão de uma tabela no banco de dados AdventureWorks. Se a fonte 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 a UDT Currency.
A listagem de código para a UDT Currency e para as funções ConvertCurrency pode ser localizada instalando os exemplos de CLR (common language runtime). Para obter mais informações, consulte Considerações para instalar exemplos e bancos de dados de exemplo do SQL Server.
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. Você não pode executar as conversões que usam explicitamente as funções CAST ou CONVERT do Transact-SQL.
Observe que não é necessário tomar nenhuma medida para usar UDTs quando o Mecanismo de Banco de Dados do SQL Server cria tabelas de trabalho no banco de dados do sistema tempdb. Isto inclui a manipulação de cursores, variáveis de tabela e funções com valor de tabela definidas pelo usuário que incluam UDTs e que usem de forma transparente tempdb. No entanto, se você criar explicitamente uma tabela temporária no tempdb, isso define uma coluna de UDT. Em seguida, a UDT deverá ser registrado em tempdb da mesma forma que para um banco de dados de usuário.