Compartilhar via

  • Artigo

Usando dados sql_variant

O tipo de dados sql_variant opera de maneira semelhante ao tipo de dados variant no Microsoft Visual 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:

  • O OLE DB Provider for SQL Server versão 7.0.

  • O Driver ODBC do SQL Server do SQL Server 7.0.

Se a cadeia de caracteres resultante exceder 4.000 caracteres, o SQL Server retornará os primeiros 4.000 caracteres.

O SQL Server converte os valores de sql_variant em varchar(255) ao trabalhar com aplicativos que se conectaram usando as seguintes interfaces:

  • Os Drivers de ODBC do SQL Server do SQL Server versão 6.5 ou anterior.

Se a cadeia de caracteres resultante exceder 255 caracteres, o SQL Server retornará os primeiros 255 caracteres.