Author: Michael Coles
Editor: Simon Robinson
Reviewer(s): Gavin Smyth, Sudheer Raveendran

Introduction

" ...the criminal element perceives names checks as an opportunity to perpetrate fraud. " - David R. Loesch, Assistant Director in Charge, Criminal Justice Information Services Division, FBI, (from his Testimony Before the Committee on the Judiciary, United States Senate on June 21, 2000).

In the late 1990's, the National Crime Information Center (NCIC) participated in an analysis of the technology used by the FBI's background check program. NCIC found that out of 6.9 million FBI background checks performed in 1997, exact name searches would have missed criminal records for 346,000 people (Winston).

Modern technology allows us an unprecedented ability to efficiently store and retrieve massive amounts of personal data, and it's also noteworthy in this context that names have always been the cornerstone of personal identification strategies. Without the exact spelling of a name, however, retrieving personal data can be very difficult. An increasing world population means that more people than ever share the same name, or variants of the same name. This makes personal identification more complex than ever.

Phonetic searching allows you to retrieve relevant data despite spelling variations and some typographical errors. With proper database design and pre-encoding, you can also experience a performance increase over regular string and pattern matching searches. Soundex has been adopted in many areas including genealogy, medical records management, customer relationship management, law enforcement and anti-terrorism as a way to save time and minimize potential security risks.

In this article I will discuss Microsoft SQL Server's built-in Soundex phonetic name search technology, a .NET-based Soundex solution for SQL Server 2005. In particular the article will cover:

• An introduction to phonetic search theory
• How to take advantage of SQL Server's built-in Soundex functionality
• A .NET-based Soundex alternative

The code accompanying this article demonstrates a NARA-compliant Soundex encoding function, a sample Web service and a Web application that demonstrate how to search a phonetic database. This UDF and sample database design can be used as the basis for Web applications, Web Services and Windows applications that require name-based search functionality.

Note about Languages

Soundex was invented to index surnames which were common in the U.S. in the late 19th and early 20th centuries. Most common U.S. surnames during this time period were Western European in origin. Because of its close ties to Western European surnames, Soundex is not particularly well-suited to surnames with origins of non-European origin. There are several improved phonetic matching algorithms based on Soundex that are designed to handle surnames of non-English/non-European surnames. These alternatives include Daitch-Mokotoff Soundex for Eastern European names and Double Metaphone for some French, Spanish and German surnames (including special characters); however, these are beyond the scope of this article.

System Requirements

To run the sample code for this article, you should have:

• Windows 2000 or later
• The .NET Framework 2.0
• SQL Server 2005
• Visual Studio 2005
• The AdventureWorks Sample Database (available with SQL Server 2005)

Installing and Compiling the Sample Code

The sample code for this article consists of SQL Server 2005 T-SQL Scripts, a NARA-compliant Soundex user-defined function for SQL Server 2005 and a sample Visual Studio 2005 Web Service application that demonstrates how to use the supplied user-defined function. For this purpose, the samples use the AdvertureWorks database supplied with SQL Server 2005. Instructions are given later in this article for registering the user-defined function.

Understanding Soundex

Robert C. Russell and Margaret K. Odell are credited with filing U.S. Patent #1,261,167 in 1918 to declare their invention of "certain new and useful improvements in indexes". In this patent, and later in U.S. Patent #1,435,663, Russell and Odell described a system by which names could be reduced to their phonetic equivalents. They labeled their system " Soundex ", short for " Sound Index ." The U.S. patent protections for the original Russell and Odell Soundex System expired several decades ago.

The Soundex system calls for grouping letters based on similarities in the way their sounds are produced. For instance, the letters " D " and " T " were grouped into a category known as dental mutes because the tongue is placed near the back of the teeth when the " D " and " T " sounds are vocalized. (Today the sounds made by these two particular letters are more accurately classified as " alveolar plosives" - a fancy way of saying that they are pronounced by placing the tip of tongue on the top of the mouth near the back of the teeth .)

How Soundex Works

In the Soundex system, names are reduced to their equivalent four character phonetic code, which always consists of a single letter and three numerals. The modern Soundex system is maintained by the U.S. National Archives and Records Administration ( NARA ). It is based on Russell and Odell's original system, but has undergone several changes since 1918. In NARA Soundex, letters are grouped together and each group is assigned a number according to the chart shown in Figure 1:

Figure 1. NARA Soundex Letter Groups

NARA Soundex eliminates vowels, and the " H ", and " W " characters from a name during encoding. It also reduces side-by-side letters with the same code (such as double letters) down to a single code. Per NARA's General Information Leaflet 55, the rules for NARA Soundex encoding are:

1. The first letter of the surname is always retained as the first letter of the encoding.
2. Numeric values are assigned to each letter according to Figure 1above.
3. Disregard all vowels, and " H " and " W " characters after the first letter.
4. Letters side-by-side with the same numeric code should be reduced to a single numeric code.
5. If a vowel separates two consonants that have the same code, both consonant codes are retained.
6. If " H " or " W " separates two consonants with the same code, the code to the right is not coded.
7. Only the first four characters of the encoding are considered significant. If the code is less than four characters in length, it is right-padded with " 0 " characters.

Some examples of NARA Soundex encoding include: LEE = L000, JOHNSON = J525, JONSEN = J525 and BRADY = B630.

You can think of Soundex as a form of truncated hash function - which produces a "fingerprint" of a piece of data (in this case the surname). Unlike collision-free hash functions , such as SHA-256, whose goal is to avoid " collisions " or duplicate hash values, the goal of Soundex is to manage a reasonable number of collisions. Since Soundex only has 6,735 possible encoded values, many of them occurring infrequently, collisions are unavoidable. Soundex attempts to manage these collisions to retrieve similar-sounding names.

Varieties of Soundex

Most modern implementations of Soundex, including the SQL Server SOUNDEX() function, are variants of the NARA standard. The differences among these implementations are particularly important if you are searching historical Census Bureau or military records. If all your Soundexing needs are strictly in-house, the differences are not as pronounced. As NARA also points out, early records were Soundex-coded by hand using many variants of the standard. If you are searching historical records, NARA recommends using multiple encoding methods in your searches to compensate for the many variants of the standard used when these historical records were Soundex-coded by hand.

Some of the differences between Russell and Odell's original Soundex and the NARA standard include:

• Russell and Odell originally eliminated " GH " from names during the encoding process; NARA does not
• Russell and Odell retained and encoded the first vowel encountered in a name, eliminating all others; NARA maintains the first vowel only if it is the first letter of the name, no vowels are encoded as a group
• Russell and Odell separated " M " and " N " into separate groups; NARA combines " M " and " N " into one group
• Russell and Odell dropped trailing " S " and " Z " characters from a name; NARA does not

Working through the Algorithm

Here I'll demonstrate how Soundex encoding is performed with the surname " TALLESHIM ".

In step one the first letter of the surname becomes the first letter of the Soundex code.

Figure 2. The First Step of Soundex Encoding

In the second step you encode the remaining letters using the chart above. Vowels are coded with temporary placeholders ("-" characters); they will be removed in the next step. " H " and " W " characters are removed immediately.

Figure 3. The Second Step of Soundex Encoding

Next you reduce side-by-side duplicate codes to a single code.

Figure 4. The Third Step of Soundex Encoding

In the fourth step you eliminate vowels.

Figure 5. The Third Step of Soundex Encoding

The order of the preceding steps is important: Eliminating vowels before reducing duplicate codes can result in incorrect encodings (because you end up merging letters that have the same code but were separated by a vowel).

The final step is to truncate the result to four characters; or right-pad it with zeroes if it is less than four characters in length. In this case the result is " T425 ", exactly four characters long.

SQL Server Soundex

A built-in SOUNDEX() function supplied by Microsoft is available in all versions of SQL Server, not just SQL Server 2005, although I'll be using SQL Server 2005 to demonstrate. You can use SOUNDEX() any place that a function is allowed, such as in T-SQL WHERE clauses and INSERT statements.

As mentioned previously, the SQL Server SOUNDEX() function is not NARA-compliant. I don't know why Microsoft chose not to follow the NARA standard, but they didn't. The differences are in the way NARA Soundex handles duplicate codes at the beginning of a name and the way it handles " H " and " W " characters in the name. The following table demonstrates the differences between the two methods:

Figure 6. Difference Between SQL SOUNDEX() and NARA SOUNDEX Codes

Take the surname " ASHCROFT " as an example. After the " H " separating the " S " and " C " is removed by NARA Soundex, the resulting " 22 " code is reduced to a single " 2 ". Microsoft's SOUNDEX() doesn't drop the second " 2 " after removing the " H " character.

For the surname " PFIZER ", the " P " and " F " characters both convert to the code " 1 ". Since they both convert to the same code, and are side-by-side, NARA Soundex drops the " F " character during the conversion. SQL SOUNDEX() does not drop the " F " in the second position.

The T-SQL SOUNDEX() function stops encoding when it encounters a punctuation mark, including spaces, hyphens and apostrophes. For T-SQL SOUNDEX() , ODELL = O340 but O'DELL = O000.

The NARA-Compliant Sample Code

Because the Microsoft Soundex implementation is not NARA-compliant, I've supplied a Soundex_NARA() SQL Server 2005 UDF with downloadable files for this article. Soundex_NARA() is a two-pass implementation of the NARA Soundex standard. It has the following distinct advantages over the T-SQL SOUNDEX() function:

1. The T-SQL SOUNDEX() function stops encoding a name when it encounters a punctuation mark, so SOUNDEX('O''MALLEY') is O000. Soundex_NARA() ignores punctuation marks, so that Soundex_NARA('O''MALLEY') is O540.

2. Soundex_NARA() function handles side-by-side duplicate codes at the beginning of a name, such as " PF " according to the NARA standard.

3. Soundex_NARA() properly handles the " H " and " W " characters according to the NARA standard.

Soundex Accuracy and Precision

Accuracy (also known as recall ) and precision are two closely related key concepts that can work for you, or against you, when querying a SQL Server database using Soundex, or any other method:

• Precision is a measure of how closely related your individual results are to one another.
• Accuracy is a measure of how relevant your results are to the correct, or desired, results.

The concepts of precision and accuracy are demonstrated in Figure 7 below. The Soundex code for each surname is given in parentheses following the name.

Figure 7. Demonstration of Accuracy and Precision

In Figure 7, " Smith, Jeff " is the target - the correct answer. Accuracy is measured by the distance of a result from the correct answer at the center of the target area. Precision is measured by the distance the results returned are from one another. " Smith, John " is fairly accurate relative to the target. We can achieve perfect accuracy and precision by using an exact name match query like the following:

USE AdventureWorks
GO
SELECT * 
FROM Person.Contact
WHERE LastName = 'Smith' AND FirstName = 'Jeff'
GO

The method of exact name match querying does not take spelling variations and typographical errors into account - which is of course the problem that Soundex seeks to solve. For instance, if you were told the name but weren't sure of the exact spelling, you could waste a lot of time guessing with queries like the following:

USE AdventureWorks
GO
SELECT * 
FROM Person.Contact
WHERE LastName = 'Smythe' AND FirstName = 'Jeff'
GO

Even the SQL LIKE operator with wildcards might not help. By using LIKE, you could still waste time and energy with wild guesses, such as this:

USE AdventureWorks
GO
SELECT * 
FROM Person.Contact
WHERE LastName LIKE 'Smit%' AND FirstName LIKE 'J%'
GO

And even then you might not get the correct answer: For example, LIKE 'ERIKSON%' will not return ERICSON , ERICKSON , ARICKSON or ERRIKSON .

Phonetic matching algorithms like Soundex make your searches less sensitive to variations in spelling, pronunciation and even some minor typographical errors. As an example of Soundex_NARA() function usage, we can re-write the query above:

USE AdventureWorks
GO
SELECT * 
FROM Person.Contact
WHERE dbo.Soundex_NARA(LastName) = dbo.Soundex_NARA('Smith') 
AND FirstName = 'Jeff'
GO

This query converts the surname of each row in the LastName column to its Soundex code and compares them to the Soundex code for " Smith " ( S530 ).

Maximizing Performance: The Patton Plan

The row-by-row convert-and-compare method just illustrated works fine for small tables, but in databases with a large number of rows the Soundex_NARA() computations on the LastName column cause SQL Server to abandon optimal Index Seeks in favor of less optimal Scans in the query plan. All of this affects the overall execution speed of your query. As can be seen in Figure 8 below, a Clustered Index Scan is used by SQL Server to execute the query. The estimated cost of the query (a SQL Server measure of efficiency) is 0.458157. The lower the cost, the more efficient our query is. Here is the query plan SQL Server shows you:

Figure 8. Query Plan for Sample SOUNDEX Query

Given this information, how can you maximize performance when using Soundex in SQL Server?

In order to increase Soundex_NARA() performance on SQL Server, we'll borrow a strategy from General George S. Patton's playbook by "not paying for the same real estate twice." Every time you run the sample query with Soundex_NARA(LastName) in the WHERE clause SQL Server re-computes the Soundex code for each and every row. Your primary optimization will come from pre-computing all of the Soundex codes and storing them in the database. I'll do this by adding a Person.LastName table to the AdventureWorks database using the following T-SQL query:

USE AdventureWorks
GO
CREATE TABLE Person.LastName
    (LastName NVARCHAR(50),
     Encoded NVARCHAR(4),
     PRIMARY KEY (Encoded, LastName))
GO
INSERT INTO Person.LastName (LastName, Encoded)
    SELECT DISTINCT LastName, dbo.Soundex_NARA(LastName)
    FROM Person.Contact
GO
CREATE NONCLUSTERED INDEX IX_ContactName
ON Person.Contact (LastName ASC, FirstName ASC, MiddleName ASC)
GO

Notice also that we modify the Person.Contact table by adding a non-clustered index on the (LastName, FirstName, MiddleName) columns. This gives us far greater efficiency when performing inner join queries using this table. Our optimized sample query from above looks like this:

SELECT *
FROM Person.LastName l, Person.Contact c
WHERE l.Encoded = dbo.Soundex_NARA('Smith')
AND l.LastName = c.LastName

This query optimizes the previous Clustered Index Scan down to Clustered Index Seeks and Inner Join operations. You can achieve much greater efficiency using this method, as can be seen in this newly optimized query plan. Note that the cost of the query has dropped considerably. The new total cost is now approximately 0.0859052. That's 1/5th the cost of the previous query! As you can see below, SQL Server regenerates a much more efficient query plan for this new query:

Figure 9. Query Plan for Optimized Pre-Computed SOUNDEX Codes

Keep these optimizations in mind when designing and optimizing databases for Soundex-enabled queries.

The Soundex_NARA() User-Defined Function

I designed Soundex_NARA() as a SQL Server 2005 .NET UDF that encodes a string in two passes. Although the code can be optimized further, I chose to use a simple two-pass method for demonstration purposes.

In the first pass I capitalize the string, eliminate all non-alphabetic characters, and convert the characters to their equivalent codes. Two temporary placeholders were added for the special case of vowels and the "H" and "W" characters: "-" and ".". Both are eliminated from the final Soundex code.

' Groupings are the NARA Soundex groups that are associated with each letter
' the "-" group is a temporary group I've added for the vowels (which are 
' removed later), and the "." is the "H" and "W" characters that are treated
' as special
Private Shared ReadOnly Groupings As String = "-123-12.-22455-12623-1.2-2"
' NARA_Soundex() is the function to Encode a string and return a NARA-
' compliant Soundex encoded string.  SQL 2005 requires that we declare this 
' as Shared.
<SqlFunction(IsDeterministic:=True)> _
Public Shared Function Encode(ByVal Input As SqlString) As String
    ' This holds our Result
    Dim Result As String = ""
    ' Check for a NULL
    If Not (Input.IsNull) Then
        ' Extract the Name from the Input SqlString
        Dim Name As String = Input.Value
        ' Encoded_String is the string variable which holds our final 
        ' encoded string which is returned
        Dim Temp_Encoded_String As New StringBuilder(32)
        ' First_Character holds the initial character of the name
        Dim First_Character As Char = "0"
        ' Loop through name, convert it character by character
        Temp_Encoded_String.Append("9")
        For i As Integer = 0 To Name.Length - 1
        ' Here we grab our string one character at a time and convert it to
        ' uppercase
        Dim c As Char = Char.ToUpper(Name.Chars(i))
        Dim j As Integer = Asc(c) - Asc("A")
        ' Make sure we have a valid NARA Soundex character
        If (j >= 0) And (j <= 25) Then
            ' If the First Character hasn't been assigned, we assign it here
            If (First_Character = "0") Then
                First_Character = c
            End If
            ' Append the code to the Encoded_String
            If Groupings.Chars(j) <> "." Then
                Temp_Encoded_String.Append(Groupings.Chars(j))
            End If
        End If
    Next
    Temp_Encoded_String.Append("9")

In the second pass I reduce all side-by-side duplicate codes to a single code and remove all of the temporary vowel placeholder codes ("-").

' The final encoded string to be returned
Dim Encoded_String As New StringBuilder(32)
' Here we perform the Summarization of the string, eliminating double
' consonants and vowels, per the NARA Rules as stated in the publication
' "NARA General Information Leaflet 55:  Using The Census Soundex"
Encoded_String.Append(First_Character)
For i As Integer = 2 To Temp_Encoded_String.Length - 2
    ' Here we grab the Current Character, Next Character and Last 
    ' Character
    Dim Current_Char As Char = Temp_Encoded_String.Chars(i)
    Dim Next_Char As Char = Temp_Encoded_String.Chars(i + 1)
    Dim Last_Char As Char = Temp_Encoded_String.Chars(i - 1)
    ' Now we shrink back-to-back double character groupings down to a 
    ' single character
    If (Current_Char <> "-" AndAlso Current_Char <> Last_Char) Then
        Encoded_String.Append(Current_Char)
    End If
Next

In the final steps I perform some clean-up before returning the final result:

' Pad with zeroes
Encoded_String = Encoded_String.Append("0", 4)
' Return the Result
Result = (Encoded_String.ToString).Substring(0, 4)

Registering the Soundex_NARA() Function

Use Visual Studio 2005 to compile Soundex_NARA.vb . After compilation, use this procedure to add Soundex_NARA to your SQL Server database:

1. Copy the file Soundex_NARA.dll to your SQL Server 2005 \binn directory. For default installations this will be at C :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\ .

2. Open Microsoft SQL Server Management Studio and run the following T-SQL commands. You might need to change the directory path in the CREATE ASSEMBLY statement to match your SQL Server directory:

USE AdventureWorks
GO
-- Turn on the SQL Server CLR
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
GO
-- Create the Assembly for the Soundex_NARA DLL
CREATE ASSEMBLY Soundex_NARA_Assembly
FROM 'C:\Program Files\Microsoft SQL 
   Server\MSSQL.1\MSSQL\Binn\Soundex_NARA.dll'
GO
-- Create the User-Defined Function Soundex_NARA
CREATE FUNCTION Soundex_NARA (@surname NVARCHAR(256))
RETURNS NCHAR(4)
AS
EXTERNAL NAME Soundex_NARA_Assembly.Soundex_NARA.Encode
GO

After installation using the above procedure, you can use the Soundex_NARA() UDF anywhere that you use the built-in T-SQL SOUNDEX() function.

Comparing the Microsoft and Nara Codes

The following sample T-SQL Code creates a small table and demonstrates the difference between results of the built-in T-SQL SOUNDEX() function and the Soundex_NARA() UDF:

CREATE TABLE #People
([ID] INT,
LastName VARCHAR(50))
GO
INSERT INTO #People ([ID], LastName)
SELECT 0, 'Ashcroft'
UNION SELECT 1, 'O''Malley'
UNION SELECT 2, 'Smith-Wesson'
UNION SELECT 3, 'Schneider'
UNION SELECT 4, 'Pfister'
GO
SELECT [ID], LastName, Soundex(LastName) AS [Soundex], 
   dbo.Soundex_NARA(LastName) AS [NARA]
FROM #People
GO
DROP TABLE #People
GO

This sample produces the result shown in Figure 10:

Figure 10. Typical results from Soundex_Nara()

This short T-SQL sample is provided to show how a proper NARA Soundex algorithm produces different results from the built-in SOUNDEX() function when double-letter codes are encountered in the first position (i.e., " P " and " F " are both code "1"), when punctuation is encountered in a name (as in "O'Malley") and when " H " separates letters with the same code (like the "SHC" in "Ashcroft").

The Soundex_NARA_WS Sample Web Service

To utilize the sample web service, you should install the Soundex_NARA() UDF in the AdventureWorks sample database as described in the section Registering the Soundex_NARA() Function above.

The Soundex_NARA() UDF can be used by any Windows application, Web Service or other Web-based application that accesses SQL Server 2005. The Soundex_NARA_WS web service demonstrates how to implement NARA Soundex searching via Web Service. The web service exposes a single method, NARA_Search() , which takes a last name as input and returns a .NET DataSet. The NARA_Search() method begins by connecting to the AdventureWorks database on the local SQL Server 2005 installation, using Integrated Security mode. The connection can be modified in the Dim sqlda As New SqlDataAdapter(...) line.

<WebMethod()> _
Public Function NARA_Search(ByVal lastname As String) As DataTable
    Dim sqlda As New SqlDataAdapter(_
        "SELECT DISTINCT lastname, firstname, middlename " & _
        "FROM Person.Contact " & _
        "WHERE dbo.Soundex_NARA(LastName) = dbo.Soundex_NARA(@lastname)" & _
        "ORDER BY lastname, firstname, middlename", _
        "SERVER=(local)\SQL2K5;INITIAL CATALOG=AdventureWorks; " & _
        "INTEGRATED SECURITY=SSPI;")
    Dim dt As New DataTable("Result")
    Try
        sqlda.SelectCommand.Parameters.Add("@lastname", SqlDbType.VarChar, _
            50).Value = lastname
        sqlda.Fill(dt)
    Catch ex As Exception
        Throw (ex)
    Finally
        If Not (sqlda Is Nothing) Then
            sqlda.Dispose()
        End If
    End Try
    Return dt
End Function

The code simple uses a DataAdapter to connect to the SQL Server, perform a SELECT query and fill a DataTable with the results. The DataTable is then returned as the result. To run the sample web service in test mode, load the Soundex_NARA_WS project in Visual Studio 2005 and run it. You will be presented with a web service screen like the following:

Figure 11. Running the Soundex_NARA_WS web service

Select the NARA_Search method and enter a lastname in the textbox, then press the Invoke button.

Figure 12. Invoking the NARA_Search() method

Matching NARA Soundex records are returned in your web browser as a DataSet, displayed in XML format.

Figure 13. Result of the NARA_Search() method

The Soundex_NARA_Sample Web Application

Like the sample Web Service, the sample Web application uses the AdventureWorks database. As with the Web Service, the Soundex_NARA() UDF must be installed using the method described in the "Installation and Use" section above. Soundex_NARA_Sample demonstrates use of the Soundex_NARA() UDF in a simple Web application. Like the sample Web Service, the sample application uses a DataAdapter to connect to the local SQL Server 2005 instance and execute a SQL query against the AdventureWorks sample database.

The RaiseCallbackEvent() subroutine implements the ICallbackEventHandler interface, and is the primary engine that runs this particular sample. The code for this subroutine is as follows:

Public Sub RaiseCallbackEvent(ByVal eventArgument As String) _
    Implements System.Web.UI.ICallbackEventHandler.RaiseCallbackEvent
    Dim s() As String = eventArgument.Split(":")
    If s(0) = "Search" Then
        Dim sqlda As New SqlDataAdapter("SELECT " & _
            "DISTINCT lastname, firstname, middlename " & _
            "FROM Person.Contact " & _
            "WHERE dbo.Soundex_NARA(lastname) = " & _
            "dbo.Soundex_NARA(@lastname) " & _
            "ORDER BY lastname, firstname, middlename", _
            "SERVER=(local)\SQL2K5;INITIAL CATALOG=AdventureWorks;" & _
            "INTEGRATED SECURITY=SSPI;")
        Try
            sqlda.SelectCommand.Parameters.Add("@lastname", _
                Data.SqlDbType.VarChar, 50).Value = s(1)
            sqlda.Fill(dt)
        Catch ex As Exception
            Throw (ex)
        Finally
            If Not (sqlda Is Nothing) Then
                sqlda.Dispose()
            End If
        End Try
        GridView1.DataSource = dt
        GridView1.DataBind()
        innerHTML = RenderHTML(Panel1)
    End If
End Sub

This routine simply connects to the database, performs a NARA Soundex-enabled SELECT query, and returns the results in a DataTable. The results are returned in a GridView using AJAX-style method calls, which requires some control re-rendering and support functions in both JavaScript and ASP.NET code.

To run the sample Web application, load the Soundex_NARA_Sample project into Visual Studio 2005 and run it. You will be presented with the following screen:

Figure 14. Sample Web Application Request

After entering a last name in the box and pressing Search, the application performs an AJAX-style callback, executes the SQL Query against the AdventureWorks sample database and re-renders the GridView presenting you with the final results in this format:

Figure 15. Sample Web Application Results

Conclusion

In this article I discussed how exact match SQL queries can waste your resources and miss relevant data. I discussed SQL Server's built-in SOUNDEX() function and presented a NARA-compliant Soundex_NARA() UDF with sample code demonstrating usage.