Como realizar consultas Case Sensitive no SQL Server? – Parte 03

Como realizar consultas Case Sensitive no SQL Server? – Parte 03

Método 4: Mudando a colação da coluna permanentemente (funciona a partir do SQL Server 2000)

O SQL Server 2000 permite que você especifique a colação no nível de coluna também. Com isso, você pode tornar suas colunas CustID e CustPassword do tipo case sensitive por padrão. Isso evita muito esforço, pois você não precisa empregar nenhum método especial em suas consultas para ter o comportamento de consulta case sensitive. Eis um exemplo na Listagem 10.

Na Listagem 10, inicialmente é apagada a restrição de chave primária associada à coluna CustID. Em seguida, é feita a alteração da colação desta coluna para um novo formato usando a funcionalidade de colação por coluna do SQL Server já comentada no Método 2 (usando COLLATE). A seguir é novamente criada a restrição de chave primária na coluna CustID, porém já com o novo formato de colação. Por fim, é feita a consulta na tabela de forma semelhante ao que foi apresentado nas consultas anteriores neste artigo. Os comentários inseridos na própria listagem indicam exatamente onde se inicia e termina cada um desses passos.

Listagem 10. Consulta usando coluna Case Sensitive

/* Devido ao fato de nós termos uma restrição de chave primária definida na coluna CustID, não podemos alterá-la diretamente.

Primeiro precisamos apagar (drop) a restrição. Para isso, precisamos do nome da restrição. Este procedimento fornecerá o nome da restrição de chave primária */

EXEC sp_help 'dbo.Customers'

GO

/* Substituir o nome de sua restrição de chave primária no comando ALTER TABLE para apagar a restrição */

ALTER TABLE dbo.Customers DROP CONSTRAINT PK__Customers__71F07EBE

GO

/* Muda a colação da coluna CustID */

ALTER TABLE dbo.Customers ALTER COLUMN      CustID char(8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

GO

/* Adiciona a restrição de chave primária de volta */

ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY(CustID)

GO

/* muda a colação da coluna CustPassword */

ALTER TABLE dbo.Customers ALTER COLUMN      CustPassword varchar(15) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL

GO

/* agora tente esta consulta e observe o comportamento do case sensitive */

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'

SET @CustPassword = 'theunbreakable'

IF EXISTS

(

         SELECT 1

         FROM dbo.Customers

         WHERE         CustID = @CustID

                   AND CustPassword = @CustPassword

)

BEGIN

         PRINT 'Cliente Encontrado!'

END

ELSE

BEGIN

         PRINT 'ID de Cliente ou Senha Inválida!'

END

GO

Método 5: Usando colunas computadas (funciona a partir da versão 7.0)

Você pode adicionar uma coluna computada em sua tabela utilizando uma cópia da coluna que se deseja consultar, porém transformada para um formato case sensitive. Uma coluna computada é uma coluna virtual que deriva seu valor a partir de uma coluna existente. O SQL Server 2000 permite que você crie um índice em uma coluna computada para fazer consultas nessas colunas mais rapidamente. Nós iremos explorar esta funcionalidade no exemplo da Listagem 11.

Na Listagem 11 temos inicialmente a criação de duas colunas calculadas chamada CustID_CS e CustPassword_CS a partir respectivamente das colunas originais CustID e CustPassword. A primeira coluna calculada usou uma cópia da coluna CustID transformada com a função CAST (citada no método 1), enquanto que a segunda coluna usou uma cópia da coluna CustPassword transformada para o formato case sensitive através da cláusula COLLATE (Método 2).

Listagem 11. Consulta Case Sensitive com coluna computada

/* Adicionando 2 colunas computadas – uma para CustID e outra para CustPassword */

ALTER TABLE dbo.Customers ADD CustID_CS AS (CAST(CustID AS varbinary(8))),

CustPassword_CS AS (CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS)

GO

/* Criar um índice não clusterizado na coluna CustID_CS */

CREATE NONCLUSTERED INDEX NC_NI_Customers_CustID ON dbo.Customers(CustID_CS)

GO

/* O seguinte exemplo usará o índice sobre a coluna computada para realizar consultas case sensitives eficientemente */

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'

SET @CustPassword = 'theunbreakable'

IF EXISTS

(

         SELECT 1

         FROM dbo.Customers

         WHERE         CustID_CS = CAST(@CustID AS varbinary(8))

                   AND CustPassword_CS = @CustPassword

)

BEGIN

         PRINT 'Cliente Encontrado!'

END

ELSE

BEGIN

         PRINT 'ID de Cliente ou Senha Inválida!'

END

GO

Método 6: Fazer uso de uma linguagem do lado do cliente como VB ou VBScript para comparações case sensitive (funciona a partir do SQL Server 7.0)

Ao invés de tentar realizar comparações case sensitive do lado do servidor, tentaremos levar vantagem de suas aplicações do lado do cliente. Isso pode não ser apropriado para todas as situações, mas é uma possibilidade e vale a pena citarmos aqui. O código descrito na Listagem 12 pode ser usado em uma aplicação Visual Basic para realizar consultas case sensitive.

Nessa listagem, inicialmente são criadas duas variáveis que serão os argumentos da consulta e é feita a conexão com o banco de dados. Em seguida, a consulta é realizada no banco de dados sem qualquer cláusula adicional, ou seja, um simples WHERE com os argumentos da consulta. Após isso, o procedimento verifica se a linha retornada da tabela possui exatamente os mesmos caracteres que os argumentos de busca usando a função rs() do Visual Basic, que força uma comparação no formato case sensitive. Se for igual, a mensagem de cliente encontrado é exibida. Caso contrário, o procedimento informa que não foram encontrados clientes com os argumentos definidos.

Listagem 12. Consulta Case Sensitive via código em Visual Basic

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset

Dim CustID As String, CustPassword As String

CustID = "usa00001"

CustPassword = "theunbreakable"

cn.Open "Server=(local);Integrated Security=SSPI;Database=tempdb;Provider=SQLOLEDB"

Set rs = cn.Execute("SELECT CustID, CustPassword FROM dbo.Customers WHERE CustId = '" + CustID + "' AND CustPassword = '" + CustPassword + "'")

If CustID = rs("CustID") And CustPassword = rs("CustPassword") Then

    MsgBox "Cliente Encontrado!"

Else

    MsgBox "ID de Cliente ou Senha Inválida!"

End If

Set rs = Nothing

Set cn = Nothing

Conclusão

Acreditamos que você encontrou ao longo do artigo diversas abordagens úteis para lidar com problemas envolvendo consultas case sensitive em versões mais antigas do SQL Server. A seguir, algumas informações adicionais que podem ajudá-lo a lidar com collations. O seguinte comando mostra a você a colação padrão de seu SQL Server:

SELECT SERVERPROPERTY('Collation') AS 'Server Level Collation'

Para ver a colação padrão de seu banco de dados:

SELECT DATABASEPROPERTYEX('Pubs', 'Collation') AS 'Database Level Collation'

Para ver a colação ao nível de coluna da tabela Customers:

EXEC sp_help 'dbo.Customers'

Para ver as configurações de colação no nível do servidor no SQL Server 2000, assim como em versões anteriores:

EXEC sp_helpsort

Para uma listagem de todas as colações disponíveis no SQL Server 2000:

SELECT * FROM ::fn_helpcollations()

Para maiores informações sobre colações específicas:

SELECT COLLATIONPROPERTY('German_PhoneBook_CI_AS', 'CodePage')

SELECT COLLATIONPROPERTY('French_CI_AS', 'LCID')

SELECT COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle')