Como realizar consultas Case Sensitive no SQL Server? – Parte 02
Dando continuidade a consultas Case Sensitive no SQL Server.
Método 1: Convertendo dados para tipo binário antes da comparação (funciona no SQL Server 7.0 e 2000)
Quando você converte um caractere para um tipo de dados binary ou varbinary, o valor ASCII deste caractere passa a ser representado em binário. Visto que 'A' e 'a' possuem diferentes valores ASCII, quando você converte–os para binário, a representação binária desses valores não casam, e portanto ocorre o comportamento case sensitive.
Listagem 3. Consulta Case Sensitive com transformação de dados em valores binários – com index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))
AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
O exemplo da Listagem 3 apresenta a mensagem 'ID de Cliente ou Senha Inválida!', pois a entrada provida está em letras minúsculas, enquanto que os dados na tabela estão armazenados de uma forma mista. Tudo parece perfeito. Mas se você observar o plano de execução desta consulta (pressionando Ctrl + K no Query Analyzer ou através da execução do comando SET SHOWPLAN_TEXT ON), você irá ver um 'index scan'. Um index scan não é bom do ponto de vista de desempenho, pois ele percorre a tabela de índice inteira – que é tão ruim quanto um table scan.
Se você modificar a consulta acima como exibido na Listagem 4, você irá ver um 'index seek', que é bem mais rápido. Você irá perceber essa diferença de desempenho facilmente em grandes tabelas.
Listagem 4. Consulta Case Sensitive com transformação de dados em valores binários – sem index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))
AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))
AND CustID = @CustID
AND CustPassword = @CustPassword
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
Curiosamente, por que a primeira consulta vasculha o índice inteiro? Eis o motivo: quando uma coluna usada na cláusula WHERE é usada junto com uma função (neste caso CAST), o otimizador de consulta (Query Optimizer) não pode adivinhar o resultado da função e por isso ele tem que vasculhar o índice inteiro e ver se existe algum valor igual ao parâmetro. Então, para evitar o problema nós adicionamos à cláusula WHERE "AND CustID = @CustID AND CustPassword = @CustPassword", e o otimizador faz uso do índice clusterizado.
Método 2: Usando a cláusula COLLATE para ditar o uso de case sensitive na consulta (funciona a partir do SQL Server 2000)
A partir do SQL Server 2000 é possível especificar uma colação ao nível de banco de dados, coluna e T-SQL, assim como no nível do servidor. O mecanismo para fazer isso é através da palavra chave COLLATE, que pode ser invocada a partir de varias declarações SQL.
Você pode usar a palavra chave COLLATE para especificar uma colação de banco de dados padrão que é diferente da colação padrão do servidor. O código a seguir, por exemplo, define um banco de dados como sendo case insensitive, mesmo este banco estando executando em um servidor case sensitive.
CREATE DATABASE Foo COLLATE SQL_Latin1_General_Cp1_CS_AS
O uso da cláusula COLLATE em uma expressão de consulta (WHERE) nos permite especificar uma forma de colação especial. Nos exemplos seguintes, nós iremos usar a cláusula COLLATE para fazer as nossas consultas no formato case sensitive. Para isso, precisamos especificar uma colação case sensitive junto com a cláusula COLLATE. O seguinte exemplo usa a colação SQL_Latin1_General_CP1_CS_AS, que trabalha com caracteres no formato Latin1. Os indicadores CS e AS no seu nome são abreviações para case sensitive e accent sensisitive, que possibilitará exatamente o que desejamos: realizar consultas case sensitives em uma determinada tabela. Se você está trabalhando com dados que não estejam em inglês, escolha uma colação apropriada. Se seu banco de dados está utilizando uma codificação de caractere diferente do Latin1, procure uma colação específica para ele.
Listagem 5. Consulta Case Sensitive com a cláusula COLLATE – com index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
O exemplo da Listagem 5 irá imprimir a mensagem 'ID de Cliente ou Senha Inválida!', pois a entrada provida está toda em letras minúsculas, enquanto que os dados da tabela estão em letras mistas (maiúsculas e minúsculas). Mas novamente, o plano de execução mostra um index scan. Nós podemos mudar isso para um index seek adotando o mesmo procedimento apresentado no Método 1. A consulta reescrita está apresentada na Listagem 6.
Listagem 6. Consulta Case Sensitive com a cláusula COLLATE – sem index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustID = @CustID
AND CustPassword = @CustPassword
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
Método 3: Usando a função BINARY_CHECKSUM (funciona a partir do SQL Server 2000)
Normalmente, muitas pessoas utilizam a função BINARY_CHECKSUM para realizar consultas case sensitive. A função BINARY_CHECKSUM() aceita uma entrada e retorna um valor de checksum para esta entrada fornecida. Apesar de ela ser uma função útil para rastrear mudanças e verificar a integridade dos dados, não achamos que ela seja apropriada para fazer consultas case sensitive. Ela funciona apenas para comparações simples. A Listagem 7 apresenta um exemplo disso.
Listagem 7. Consulta Case Sensitive com a função BINARY_CHECKSUM – com index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)
AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
O exemplo da Listagem 7 irá apresentar a mensagem 'ID de Cliente ou Senha Inválida!' e você irá ver um index scan no plano de execução. O exemplo da Listagem 8 irá convertê-lo em um index seek.
Listagem 8. Consulta Case Sensitive com a função BINARY_CHECKSUM – sem index scan
DECLARE @CustID char(8), @CustPassword varchar(15)
SET @CustID = 'usa00001'
SET @CustPassword = 'theunbreakable'
IF EXISTS
(
SELECT 1
FROM dbo.Customers
WHERE BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)
AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)
AND CustID = @CustID
AND CustPassword = @CustPassword
)
BEGIN
PRINT 'Cliente Encontrado!'
END
ELSE
BEGIN
PRINT 'ID de Cliente ou Senha Inválida!'
END
GO
Como você pode ver, este método funciona para comparar pequenas strings. Mas o script apresentado na Listagem 9 irá provar que a função BINARY_CHECKSUM pode retornar o mesmo valor de checksum para diferentes valores de entrada. Isso é muito ruim, especialmente quando ele é usado para validar nomes de usuários e senhas. Alguém pode ultrapassar a autenticação especificando uma senha não correta, mas que produz o mesmo valor de checksum que a senha correta. Execute o script da Listagem 9 e você entenderá por que esta abordagem não seria recomendada.
Listagem 9. Verificando problemas no uso da função BINARY_CHECKSUM
SET NOCOUNT ON
DECLARE @i varchar(500)
CREATE TABLE #t (CharValue varchar(500), BinaryChecksum int)
SET @i = 'A'
WHILE @i <> REPLICATE('A', 500)
BEGIN
INSERT #t SELECT @i, BINARY_CHECKSUM(@i)
SET @i = @i + 'A'
END
SELECT CharValue, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY CharValue
SELECT BinaryChecksum, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY BinaryChecksum
SELECT BINARY_CHECKSUM('A') AS [Checksum value for 'A'],
BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA') AS [Checksum value for 'AAAAAAAAAAAAAAAAA']
DROP TABLE #t
Confira outros conteúdos:
Por
Devmedia
Em 2008
<Perguntas frequentes>
Nossos casos de sucesso
Eu sabia pouquíssimas coisas de programação antes de começar a estudar com vocês, fui me especializando em várias áreas e ferramentas que tinham na plataforma, e com essa bagagem consegui um estágio logo no início do meu primeiro período na faculdade.
Estudo aqui na Dev desde o meio do ano passado!
Nesse período a Dev me ajudou a crescer muito aqui no trampo.
Fui o primeiro desenvolvedor contratado pela minha
empresa. Hoje eu lidero um time de desenvolvimento!
Minha meta é continuar estudando e praticando para ser um
Full-Stack Dev!
Economizei 3 meses para assinar a plataforma e sendo sincero valeu muito a pena, pois a plataforma é bem intuitiva e muuuuito didática a metodologia de ensino. Sinto que estou EVOLUINDO a cada dia. Muito obrigado!
Nossa! Plataforma maravilhosa. To amando o curso de desenvolvimento front-end, tinha coisas que eu ainda não tinha visto. A didática é do jeito que qualquer pessoa consegue aprender. Sério, to apaixonado, adorando demais.
Adquiri o curso de vocês e logo percebi que são os melhores do Brasil. É um passo a passo incrível. Só não aprende quem não quer. Foi o melhor investimento da minha vida!
Foi um dos melhores investimentos que já fiz na vida e tenho aprendido bastante com a plataforma. Vocês estão fazendo parte da minha jornada nesse mundo da programação, irei assinar meu contrato como programador graças a plataforma.
Wanderson Oliveira
Comprei a assinatura tem uma semana, aprendi mais do que 4 meses estudando outros cursos. Exercícios práticos que não tem como não aprender, estão de parabéns!
Obrigado DevMedia, nunca presenciei uma plataforma de ensino tão presente na vida acadêmica de seus alunos, parabéns!
Eduardo Dorneles
Aprendi React na plataforma da DevMedia há cerca de 1 ano e meio... Hoje estou há 1 ano empregado trabalhando 100% com React!
Adauto Junior
Já fiz alguns cursos na área e nenhum é tão bom quanto o de vocês. Estou aprendendo muito, muito obrigado por existirem. Estão de parabéns... Espero um dia conseguir um emprego na área.