USE tempdb;
Go
-- Create a table with case-insenstive collation
CREATE TABLE CaseTest ( 
        Col1 varchar( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AI
);
go
-- Create an index for searches
CREATE CLUSTERED INDEX IX_CaseTest_Col1 ON CaseTest( col1 ) ;
go
INSERT INTO CaseTest VALUES( 'Test String.' );
Go
/*
        How to perform case-sensitive searches on the column?
*/
/*
        Method #1: ANSI SQL compliant solution. The COLLATE clause can be used
        in the comparison / WHERE clause. But this precludes the use of the 
        index & hence the search needs to be optimized. This can be done by 
        providing redundant SARGs in the WHERE clause that will use the index &
        also perform the case-sensitive matching.
                   This is a very powerful & flexible solution that allows you to
        change the collation dynamically & perform searches.
*/
SET SHOWPLAN_TEXT ON;
GO
DECLARE @SearchStr varchar( 30 );
SET @SearchStr = 'test string.';

-- First, show the unoptimized method using the COLLATE clause alone:
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 COLLATE SQL_Latin1_General_CP1_CS_AS = @SearchStr;
/*
  |--Index Scan(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1_BinaryCheckSum] AS [t1]),
     WHERE:(Convert([t1].[Col1])=Convert([@SearchStr])))
*/

-- Now, use the redundant SARG in the WHERE clause to force an index seek
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr And
      t1.Col1 COLLATE SQL_Latin1_General_CP1_CS_AS = @SearchStr;
/*
  |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1] AS [t1]),
     SEEK:([t1].[Col1]=Convert([@SearchStr])),  WHERE:(Convert([t1].[Col1])=Convert([@SearchStr])) ORDERED FORWARD)
*/

SET @SearchStr = 'Test String.';
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr And
      t1.Col1 COLLATE SQL_Latin1_General_CP1_CS_AS = @SearchStr;
GO

/*
        Method #2: T-SQL specific solution. This method uses the SQL2000 specific
        BINARY_CHECKSUM function to perform case-sensitive searches. The binary checksum
        value will differ based on the string value & hence is different for each string.
*/       
DECLARE @SearchStr varchar( 30 );
SET @SearchStr = 'test string.';

-- First, show the unoptimized method using the COLLATE clause alone:
SELECT * FROM CaseTest AS t1
WHERE BINARY_CHECKSUM( t1.Col1 ) = BINARY_CHECKSUM( @SearchStr );

-- Now, use the redundant SARG in the WHERE clause to force an index seek
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr And
      BINARY_CHECKSUM( t1.Col1 ) = BINARY_CHECKSUM( @SearchStr );

SET @SearchStr = 'Test String.';
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr And
      BINARY_CHECKSUM( t1.Col1 ) = BINARY_CHECKSUM( @SearchStr );
GO
SET SHOWPLAN_TEXT OFF;
GO

/*
        Now let's take this approach a step further & use the SQL2000 feature of
        defining indexes on computed columns to optimize these searches. Modify
        the table 'CastTest'
*/
ALTER TABLE CaseTest ADD Col1BinChkSum AS ( BINARY_CHECKSUM( Col1 ) );
CREATE NONCLUSTERED INDEX IX_CaseTest_Col1_BinaryCheckSum ON CaseTest( Col1BinChkSum );
GO
-- Add one more row
INSERT INTO CaseTest VALUES( 'test string.' );
-- View the rows from 'CaseTest' with the new computed column
SELECT * FROM CaseTest;
/*
Col1                           Col1BinChkSum 
------------------------------ ------------- 
Test String.                      1412553240 
test string.                      1949555224 
*/
GO

SET SHOWPLAN_TEXT ON;
GO
DECLARE @SearchStr varchar( 30 );
SET @SearchStr = 'test string.';

-- The usual case-insensitive search:
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr;
/*
  |--Compute Scalar(DEFINE:([t1].[Col1BinChkSum]=binary_checksum([t1].[Col1])))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1] AS [t1]), 
          SEEK:([t1].[Col1]=Convert([@SearchStr])) ORDERED FORWARD)
*/

-- Search using the computed binary checksum value
SELECT * FROM CaseTest AS t1
WHERE t1.Col1BinChkSum = BINARY_CHECKSUM( @SearchStr );
/*
  |--Index Seek(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1_BinaryCheckSum] AS [t1]),
     SEEK:([t1].[Col1BinChkSum]=binary_checksum([@SearchStr])) ORDERED FORWARD)
*/

SET @SearchStr = 'Test String.';
SELECT * FROM CaseTest AS t1
WHERE t1.Col1BinChkSum = BINARY_CHECKSUM( @SearchStr );
/*
  |--Index Seek(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1_BinaryCheckSum] AS [t1]),
     SEEK:([t1].[Col1BinChkSum]=binary_checksum([@SearchStr])) ORDERED FORWARD)
*/
GO

/*
        Method #3: Another ANSI compliant solution.
*/
DECLARE @SearchStr varchar( 30 );
SET @SearchStr = 'test string.';

-- Convert to a binary pattern & perform the case-sensitive search.
-- The CAST function precludes the use of the index as revealed in the SHOWPLAN output.
SELECT * FROM CaseTest AS t1
WHERE CAST( t1.Col1 AS varbinary( 10 ) ) = CAST( @SearchStr AS varbinary( 10 ) );
/*
  |--Index Scan(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1_BinaryCheckSum] AS [t1]),
     WHERE:(Convert([t1].[Col1])=Convert([@SearchStr])))
*/

SET @SearchStr = 'Test String.';
-- Optimized version of the search:
SELECT * FROM CaseTest AS t1
WHERE t1.Col1 = @SearchStr And
      CAST( t1.Col1 AS varbinary( 10 ) ) = CAST( @SearchStr AS varbinary( 10 ) );
/*
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[CaseTest].[IX_CaseTest_Col1] AS [t1]),
          SEEK:([t1].[Col1]=Convert([@SearchStr])),  WHERE:(Convert([t1].[Col1])=Convert([@SearchStr])) ORDERED FORWARD)
*/
GO
SET SHOWPLAN_TEXT OFF;
GO

DROP TABLE CaseTest;
GO

This page was last updated on May 01, 2006 04:28 PM.