Artigo
09/28/2010

Usando dados sql_variant

O tipo de dados sql_variant opera de maneira semelhante ao tipo de dados variant no MicrosoftVisual Basic. O sql_variant habilita uma única coluna, parâmetro ou variável para armazenar valores de dados de diferentes tipos de dados. Por exemplo, uma coluna sql_variant pode manter valores int, decimal, char, binarye nchar. Cada instância de uma coluna sql_variant registra o valor dos dados e as informações de metadados. Isso inclui o tipo de dados base, tamanho máximo, escala, precisão e agrupamento.

Regras para uso do sql_variant

As seguintes regras se aplicam ao usar o tipo de dados sql_variant.

Atribuição geral de valores

Objetos sql_variant podem manter dados de qualquer tipo de dados do SQL Server, exceto text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp e tipos definidos pelo usuário do Microsoft.NET Framework Common Language Runtime (CLR). Uma instância de dados sql_variant também não pode ter sql_variant como seu tipo de dados base subjacente.
Constantes de qualquer tipo podem ser especificadas em predicados ou atribuições referentes a colunas sql_variant .
Se um valor de sql_variant for NULL, ele não será considerado como tendo um tipo de dados base subjacente. Essa regra se aplica até mesmo quando o valor nulo se origina de uma variável ou coluna com um tipo de dados específico.

No exemplo a seguir, o valor de VariantCol está definido como NULL sem um tipo de dados associado, embora o valor nulo seja oriundo de uma variável int.

DECLARE @IntVar int

SET @IntVar = NULL

UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123
Em atribuições de objetos sql_variant a um objeto que tenha qualquer outro tipo de dados, o valor de sql_variant deve ser explicitamente convertido para o tipo de dados do destino. Não há suporte para nenhuma conversão implícita quando um valor de sql_variant é atribuído a um objeto que tem outro tipo de dados.
Para compatibilidade com outros tipos de dados, os objetos do catálogo, como a função DATALENGTH, que relata o comprimento de objetos sql_variant, relatam o comprimento dos dados. O comprimento dos metadados que está contido em um objeto sql_variant não é retornado.
Colunas sql_variant sempre operam com ANSI_PADDING ON. Se os valores de char, nchar, varchar, nvarchar ou varbinary forem atribuídos a partir de uma origem que tenha ANSI_PADDING OFF, os valores não serão preenchidos.
A atualização de uma coluna no Assinante conduz à alteração de tipo básico em outra coluna sql_variant. O procedimento a seguir mostra este conceito:
1. Crie uma assinatura/publicação intercalada. A tabela publicada deveria ter uma coluna sql_variant e uma coluna c1. Insira alguns dados à coluna sql_variant. O tipo básico de dados é datetime.
2. Após a sincronização inicial, o tipo básico no Assinante ainda será datetime.
3. Atualize a coluna c1 no Assinante.
4. Os dados na coluna sql_variant no Editor foram alterados para datetime2.

sql_variant em tabelas

Colunas sql_variant podem ser usadas em índices e chaves exclusivas, desde que o comprimento dos dados nas colunas de chave não excedam 900 bytes.
Colunas sql_variant não oferecem suporte à propriedade IDENTITY, mas colunas sql_variant são permitidas como chave estrangeira ou chave primária de parte.
Colunas sql_variant não podem ser usadas em uma coluna computada.
Use ALTER TABLE para alterar uma coluna de qualquer tipo de dados, exceto text, ntext, image, timestamp ou sql_variant para sql_variant. Todos os valores existentes são convertidos para valores de sql_variant cujo tipo de dados base é o mesmo que o tipo de dados da coluna antes da instrução ALTER TABLE ser executada. ALTER TABLE não pode ser usado para alterar o tipo de dados de uma coluna sql_variant para qualquer outro tipo de dados porque não existem conversões implícitas com suporte do sql_variant para outros tipos de dados.

Agrupamento

Uma cláusula COLLATE não pode ser usada para atribuir um agrupamento de coluna a uma coluna sql_variant. Os valores baseados em caracteres (char, nchar, varchar e nvarchar) em uma coluna sql_variant podem ser de qualquer agrupamento e uma única coluna sql_variant pode manter valores baseados em caracteres de agrupamentos mistos.
Quando um valor é atribuído a uma instância sql_variant, os valores dos dados e o tipo de dados base da origem são atribuídos. Se o valor de origem tiver um agrupamento, o agrupamento também será atribuído. Se o valor de origem tiver um tipo de dados definido pelo usuário, o tipo de dados base do tipo definido pelo usuário será atribuído, não o tipo de dados definido pelo usuário. A instância sql_variant não herda nenhuma regra ou padrões associados ao tipo de dados definido pelo usuário. Se um valor de uma coluna com uma propriedade de identidade for atribuída a uma instância sql_variant, o sql_variant usará o tipo de dados base da coluna de origem, mas não herdará a propriedade IDENTITY. É um erro atribuir um valor de text, ntextou image a uma instância sql_variant. Conversões implícitas têm suporte ao atribuir valores de objetos que têm outros tipos de dados a um objeto sql_variant.

Comparações de sql_variant

Colunas sql_variant podem conter valores de vários tipos de dados base e de agrupamentos. Portanto regras especiais são aplicadas quando você compara operandos de sql_variant. Estas regras se aplicam a operações que envolvem comparações, como:

Operadores de comparação Transact-SQL
ORDER BY, GROUP BY
Índices
As funções de agregação MAX e MIN
UNION (sem ALL)
Expressões CASE

Para comparações de sql_variant, a ordem da hierarquia de tipos de dados do SQL Server é agrupada em famílias de tipos de dados. A família de sql_variant tem a precedência familiar mais alta.

Hierarquia de tipos de dados	Família de tipos de dados
sql_variant	sql_variant
datetime	Data e Hora
smalldatetime	Data e Hora
Float	Numérico aproximado
Real	Numérico aproximado
decimal	Numérico exato
money	Numérico exato
smallmoney	Numérico exato
bigint	Numérico exato
int	Numérico exato
smallint	Numérico exato
tinyint	Numérico exato
bit	Numérico exato
nvarchar	Unicode
nchar	Unicode
varchar	Unicode
char	Unicode
varbinary	Binário
binary	Binário
uniqueidentifier	Uniqueidentifier

Essas regras se aplicam a comparações de sql_variant:

Quando valores de sql_variant de diferentes tipos de dados base são comparados e os tipos de dados base estão em diferentes famílias de tipos de dados, o valor cuja família de tipo de dados está mais alta no gráfico da hierarquia é considerado o mais alto dos dois valores.
Quando valores de sql_variant de diferentes tipos de dados base são comparados e os tipos de dados base estão na mesma família de tipos de dados, o valor cujo tipo de dados base estiver mais baixo no gráfico da hierarquia é convertido implicitamente no outro tipo de dados e a comparação é feita.
Quando valores de sql_variant dos tipos de dados char, varchar, nchar ou varchar são comparados, eles são avaliados com base nos seguintes critérios: LCID, versão do LCID, sinalizadores de comparação e ID de classificação. Cada um desses critérios é comparado como valores de inteiro na ordem listada.

Essas regras podem levar a diferentes resultados de comparações entre valores de sql_variant do que comparações entre valores do mesmo tipo de dados base.

Operando A	Operando B	Resultado da comparação de non-variant	resultado da comparação de sql_variant
'123' char	111 int	A > B	B > A
50000 int	5E1 float	A > B	B > A

Como os valores de diferentes famílias de tipos de dados devem ser convertidos explicitamente antes de serem referidos em predicados de comparação, os efeitos das regras são observados apenas ao ordenar conjuntos de resultados em uma coluna sql_variant. Os valores na tabela seguinte são exemplos das regras relativas à precedência de tipos de dados.

PriKey	VariantCol
1	50,0 (tipo float base)
2	5000 (tipo int base)
3	'124000' (tipo char(6) base)

A tabela a seguir mostra o resultado da instrução: SELECT * FROM VariantTest ORDER BY VariantCol ASC.

PriKey	VariantCol
3	'124000' (tipo char(6) base)
2	5000 (tipo int base)
1	50,0 (tipo float base)

Os valores na tabela seguinte são exemplos das regras relativas à precedência de agrupamento que usa agrupamentos diferentes.

IntKey	VariantCol
1	qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)
2	abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)
3	qrs (varchar SQL_Latin1_General_CP1_CS_AS)
4	17,5 (decimal)
5	abc (varchar SQL_Latin1_General_CP1_CS_AS)
6	klm (varchar SQL_Latin1_General_CP1_CS_AS)
7	1,2 (decimal)

A tabela a seguir mostra o resultado da instrução: SELECT * FROM CollateTest ORDER BY VariantCol. Esta tabela mostra valores da família de tipos de dados de número exato agrupados em conjunto e valores de varchar agrupados dentro de seus respectivos agrupamentos.

IntKey	VariantCol
5	abc (varchar SQL_Latin1_General_CP1_CS_AS)
6	klm (varchar SQL_Latin1_General_CP1_CS_AS)
3	qrs (varchar SQL_Latin1_General_CP1_CS_AS)
2	abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)
1	qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)
7	1,2 (decimal)
4	17,5 (decimal)

Funções e dados sql_variant

As funções Transact-SQL a seguir fornecem suporte a parâmetros sql_variant e retorna um valor sql_variant quando um parâmetro sql_variant é especificado:

COALESCE	MIN
MAX	NULLIF

As seguintes funções oferecem suporte a referências a colunas ou variáveis sql_variant e não usa sql_variant como o tipo de dados de seus valores retornados:

COL_LENGTH	DATALENGTH	TYPEPROPERTY
COLUMNPROPERTY	ISNULL

As seguintes funções Transact-SQL não oferecem suporte a parâmetros sql_variant:

AVG	RADIANS	STDEV[P]
IDENTITY	ROUND	SUM
ISNUMERIC	SIGN	VAR[P]
POWER

As funções CAST e CONVERT oferecem suporte a sql_variant.

A nova função SQL_VARIANT_PROPERTY() pode ser usada para obter informações de propriedades sobre valores de sql_variant, como tipo de dados, precisão ou escala.

Outros elementos Transact-SQL e dados sql_variant

Colunas sql_variant não têm suporte no predicado LIKE.

Colunas sql_variant não têm suporte em índices de texto completo. As colunas sql_variant não podem ser especificadas em funções de texto completo, como CONTAINSTABLE e FREETEXTTABLE.

As seguintes instruções Transact-SQL oferecem suporte à especificação de sql_variant na mesma sintaxe que outros tipos de dados inteiros são especificados:

ALTER PROCEDURE
ALTER TABLE
CREATE PROCEDURE
CREATE TABLE
DECLARE variable

Os componentes do catálogo do SQL Server relatam informações sobre colunas sql_variant.

O resultado da expressão CASE será sql_variant se qualquer uma das expressões de entrada ou de resultados for avaliada como sql_variant. O tipo básico subjacente do resultado é o da expressão avaliada como o resultado em tempo de execução.

Operandos de operadores de concatenação de numérica ou de cadeia de caracteres não podem ser sql_variant. Por exemplo, o seguinte código gera um erro:

SELECT VariantCol + @CharacterVar

FROM MyTable

Porém, convertendo o operando sql_variant, é possível executar a operação:

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

Aplicativos e dados sql_variant

Se um aplicativo solicitar um conjunto de resultados no qual uma coluna específica retorna dados sql_variant de um único tipo de dados base subjacente, o aplicativo poderá usar as funções CAST ou CONVERT nas instruções Transact-SQL para retornar os dados sql_variant usando o tipo de dados base subjacente. Nesse caso, o aplicativo trata os dados exatamente como uma coluna do conjunto de resultados do tipo de dados base subjacente.

O Provedor OLE DB do SQL Server do SQL Server Native Client introduz um tipo OLE DB DBTYPE_SQLVARIANT específico ao provedor para uso com colunas e parâmetros sql_variant.

O Driver ODBC do SQL Server Native Client do SQL Server introduz um tipo de dados ODBC SQL_SS_VARIANT específico ao provedor para uso com colunas e parâmetros sql_variant.

O SQL Server converte valores de sql_variant em nvarchar(4000) quando você está trabalhando com aplicativos que se conectaram usando as seguintes interfaces: