Artigo
04/01/2012

Usando tipos de dados especiais

Este tópico descreve os tipos de dados especiais que estão disponíveis no SQL Server. Tipos de dados especiais são aqueles que não se ajustam a nenhuma outra das categorias de tipo de dados. No SQL Server, os tipos de dados especiais incluem bit, hierarchyid, sql_variant, sysname, table, timestamp e tipos de dados alias.

bit

O tipo de dados bit é numérico e armazena 0 ou 1. Os valores de cadeia de caracteres true e false podem ser convertidos em valores bit, conforme mostrado neste exemplo:

SELECT CONVERT (bit, 'true')
SELECT CONVERT(bit, 'false')

Neste exemplo, true é convertido em 1 e false é convertido em 0. Os dados bit não precisam ser colocados entre aspas simples.

hierarchyid

O tipo de dados hierarchyid é usado para gerenciar dados hierárquicos e tabelas que têm uma estrutura hierárquica. Para trabalhar com dados hierárquicos em código Transact-SQL, use funções hierarchyid. Para obter mais informações, consulte Usando tipos de dados hierarchyid (Mecanismo de Banco de Dados).

sql_variant

O tipo de dados sql_variant permite que uma única coluna, parâmetro ou variável armazene valores de dados de diferentes tipos de dados. Cada instância de uma coluna sql_variant registra o valor e os metadados que descrevem o valor. Os seguintes metadados estão disponíveis:

Tipo de dados base
Tamanho máximo
Escala
Precisão
Agrupamento

Para recuperar metadados de uma determinada instância sql_variant, use a função SQL_VARIANT_PROPERTY.

Neste exemplo, a segunda tabela contém uma coluna sql_variant:

CREATE TABLE ObjectTable (
   ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY,
   ObjectName nvarchar(80),
   ObjectWeight decimal(10,3),
   ObjectColor nvarchar(20))
CREATE TABLE VariablePropertyTable (
   ObjectID int REFERENCES ObjectTable(ObjectID),
   PropertyName nvarchar(100),
   PropertyValue sql_variant,
   CONSTRAINT PKVariablePropertyTable
   PRIMARY KEY(ObjectID, PropertyName))

sysname

O tipo de dados sysname é usado para colunas de tabela, variáveis e parâmetros de procedimento armazenado que armazenam nomes de objeto. A definição exata de sysname está relacionada às regras para identificadores. Portanto ela pode variar entre instâncias do SQL Server. A funcionalidade de sysname é igual à de nvarchar(128) exceto que, por padrão, sysname é NOT NULL. Em versões anteriores do SQL Server, sysname é definido como varchar(30).

Importante
Em bancos de dados que diferenciam maiúsculas de minúsculas ou que têm um agrupamento binário, sysname é reconhecido como um tipo de dados do sistema SQL Server somente quando aparece em letras minúsculas.

table

O tipo de dados table funciona como uma tabela temporária. Ele é usado para armazenar um conjunto de resultados para processamento posterior. Esse tipo de dados pode ser usado apenas para definir variáveis locais de tipo table e o valor retornado de uma função definida pelo usuário.

A definição de uma variável de tabela ou valor de retorno inclui definições das colunas, o tipo de dados, a precisão e a escala de cada coluna e as restrições de PRIMARY KEY, UNIQUE, NULL e CHECK. Uma tabela definida pelo usuário não pode ser utilizada como tipo de dados.

O formato das linhas que são armazenadas em uma variável table ou retornadas por uma função definida pelo usuário deve ser definido quando a variável é declarada ou quando a função é criada. A sintaxe baseia-se na sintaxe de CREATE TABLE; por exemplo:

DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
SELECT * FROM @TableVar
GO

Variáveis table e funções definidas pelo usuário que retornam uma table podem ser usadas em determinadas instruções SELECT e INSERT e quando as tabelas têm suporte nas instruções UPDATE, DELETE e DECLARE CURSOR. As variáveis table e funções definidas pelo usuário que retornam uma table não podem ser usadas em nenhuma outra instrução Transact-SQL.

Índices ou outras restrições que são aplicadas à tabela devem ser definidos como parte da variável DECLARE ou da instrução CREATE FUNCTION. Eles não podem ser aplicados posteriormente, porque as instruções CREATE INDEX ou ALTER TABLE não podem fazer referência a variáveis de tabela e a funções definidas pelo usuário.

Para obter mais informações sobre a sintaxe que é utilizada para definir as variáveis table e funções definidas pelo usuário, consulte DECLARE @local_variable (Transact-SQL) e CREATE FUNCTION (Transact-SQL).

timestamp

O tipo de dados timestamp não tem nada a ver com horas ou datas. Os valores de timestamp são números binários que indicam a seqüência relativa em que ocorreram modificações de dados em um banco de dados.

Nunca use colunas timestamp em chaves, principalmente em chaves primárias, porque o valor de timestamp muda sempre que a linha é modificada.

Para registrar as horas em que ocorrem modificações de dados em uma tabela, use um tipo de dados datetime2 ou smalldatetime para registrar os eventos e gatilhos e atualizar os valores automaticamente quando ocorrer qualquer modificação.

Tipos de dados alias

Os tipos de dados alias permitem estender um tipo de dados base do SQL Server, como varchar, com um nome descritivo e formato que pode ser personalizado para uso específico. Por exemplo, a seguinte instrução implementa um tipo de dados birthday definido pelo usuário que é baseado no tipo de dados datetime e permite valores nulos (NULL):

EXEC sp_addtype birthday, datetime, 'NULL'

Tenha cuidado ao selecionar os tipos base para implementar tipos de dados definidos pelo usuário. Por exemplo, nos Estados Unidos, os números de previdência social têm o formato nnn-nn-nnnn. Embora os números de previdência social contenham números, eles formam um identificador e não estão sujeitos a operações matemáticas. Portanto, é prática comum criar um tipo de dados de número de previdência social definido pelo usuário como varchar e criar uma restrição CHECK para impor o formato dos números de previdência social armazenados na tabela, conforme mostrado neste exemplo:

EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage (EmployeeID int PRIMARY KEY, EmployeeSSN SSN, CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
)
GO

Se as colunas SSN forem usadas como colunas de chave em índices, principalmente em índices clusterizados, o tamanho das chaves poderá ser reduzido de 11 para 4 bytes quando o tipo de dados definido pelo usuário SSN for implementado usando o tipo de dados base int. Essa redução no tamanho da chave melhora a recuperação de dados. A eficiência aprimorada da recuperação de dados e a eliminação da necessidade da restrição CHECK normalmente superam o processamento de conversão extra de int em um formato de caractere quando os valores de SSN são exibidos ou modificados.