Binaryworld - How to perform case-sensitive searches on a case-insensitive server/ta ... [ SQL Server -> User Defined Function ]

This shows some of the cool, powerful techniques of SQL2000 viz. COLLATION, BINARY_CHECKSUM functions.

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