Microsoft® SQL Server® 2012 Bible

205

Chapter 8: Data Types, Expressions, and Scalar Functions

8

Result:

LeBlanc LaBlanc LeBlank

-------- ------- --------

L145 L145 L145

Other, more refi ned, Soundex methods exist. Ken Henderson, in his book The Guru’s Guide to Transact-SQL (Addison-

Wesley, 2000), provides an improved Soundex algorithm and stored procedure. If you implement Soundex in a pro-

duction application, explore his version. Alternatively, you can research one of the other refi ned Soundex methods on

the websites listed previously and write your own custom stored procedure.

You can add Soundex searches to a database in two ways. The simplest method is to add the

SOUNDEX() function within the WHERE clause, as follows:

USE AdventureWorks

GO

SELECT LastName, FirstName

FROM Person.Person

WHERE

SOUNDEX('Andersen') = SOUNDEX(LastName)

Scrolling through the result reveals three distinct last names: Andersen, Anderson, and

Atrim. This is because they each have a SOUNDEX value of A536.

Although this implementation has the smallest impact on the data schema, it can cause

performance issues as the data size grows because the SOUNDEX() function must execute

for every row in the database, and an index on the name column (if any) cannot be used

with an effi cient seek operation, but only with a much more expensive scan. A faster varia-

tion of this fi rst implementation method pretests for names with the same fi rst letter, thus

enabling SQL Server to use any indexes to narrow the search, so fewer rows must be read,

and the SOUNDEX() function must be performed only for rows selected by the index:

USE AdventureWorks

GO

SELECT LastName, FirstName

FROM Person.Person

WHERE

SOUNDEX('Andersen') = SOUNDEX(LastName) and LastName LIKE 'A%'

The second implementation method is to write the Soundex value in a column and index

it with a nonclustered index. Because the Soundex value for each row is calculated during

the write, the SOUNDEX() function does not need to be called for every row read by the

SELECT statement. This is a recommended method for a database application that heavily

depends on Soundex for contact searches. With that in place, search for a row, or all the

c08.indd 205c08.indd 205 7/30/2012 4:21:17 PM7/30/2012 4:21:17 PM

http://www.it-ebooks.info