Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Case Sensitive Search

From Wiki

Jump to: navigation, search

This hack will not only show you how to do a case sensitive search but also how that case sensitive search will use an index seek.


First create this table

  1. CREATE TABLE #CaseSensitiveSearchTemp (Val CHAR(1))
  2.  
  3. INSERT #CaseSensitiveSearchTemp VALUES('A')
  4. INSERT #CaseSensitiveSearchTemp VALUES('B')
  5. INSERT #CaseSensitiveSearchTemp VALUES('C')
  6. INSERT #CaseSensitiveSearchTemp VALUES('D')
  7. INSERT #CaseSensitiveSearchTemp VALUES('E')
  8. INSERT #CaseSensitiveSearchTemp VALUES('F')
  9. INSERT #CaseSensitiveSearchTemp VALUES('G')
  10. INSERT #CaseSensitiveSearchTemp VALUES('H')


Now we will insert some lowercase characters

  1. INSERT #CaseSensitiveSearchTemp
  2. SELECT LOWER(Val) FROM #CaseSensitiveSearchTemp


Now we will create our real table which will have 65536 rows

  1. CREATE TABLE CaseSensitiveSearch (Val VARCHAR(50))


We will do a couple of cross joins to generate the data for our queries

  1. INSERT CaseSensitiveSearch
  2. SELECT t1.val + t2.val + t3.val + t4.val
  3. FROM #CaseSensitiveSearchTemp t1
  4. CROSS JOIN #CaseSensitiveSearchTemp t2
  5. CROSS JOIN #CaseSensitiveSearchTemp t3
  6. CROSS JOIN #CaseSensitiveSearchTemp t4


This should give you 65536 rows

  1. SELECT * FROM CaseSensitiveSearch


Create an index on the table

  1. CREATE INDEX IX_SearchVal ON CaseSensitiveSearch(Val)


This is how you do a case sensitive search

  1. SELECT * FROM CaseSensitiveSearch
  2. WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS


Now hit CRLK + K (SQL Server 2000) or CRLK + M(SQL Server 2005)


run these 2 queries in one batch by highlighting them both and hitting F5

  1. SELECT * FROM CaseSensitiveSearch
  2. WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
  3.  
  4. SELECT * FROM CaseSensitiveSearch
  5. WHERE Val = 'ABCD' COLLATE SQL_Latin1_General_CP1_CS_AS
  6. AND Val LIKE 'ABCD'


Look at the execution plan, I get 98.71% for the first query and 1.29% for the second query. Just by adding the AND condition SQL server is able to do an index seek and run the query many times faster

CaseSensitive.jpg 


Now try it with a lowercase a

  1. SELECT * FROM CaseSensitiveSearch
  2. WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
  3.  
  4. SELECT * FROM CaseSensitiveSearch
  5. WHERE Val = 'aBCD' COLLATE SQL_Latin1_General_CP1_CS_AS
  6. AND Val LIKE 'aBCD'


You see it all works without a problem, the correct result is returned


Contributed By: --SQLDenis 15:27, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks - 100+ List

Section Query Optimization

109 Rating: 2.9/5 (48 votes cast)