{"id":296,"date":"2014-10-17T09:30:32","date_gmt":"2014-10-17T08:30:32","guid":{"rendered":"http:\/\/www.codereview.co\/?p=296"},"modified":"2014-10-15T14:47:55","modified_gmt":"2014-10-15T13:47:55","slug":"complex-search-queries-in-sql-server","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/complex-search-queries-in-sql-server\/","title":{"rendered":"Complex search queries in SQL Server"},"content":{"rendered":"

Scenario<\/strong><\/span><\/p>\n

Just like every other company, we need to have our queries run as fast as possible for our users to have the best experience possible with our application.\u00a0 One of the core components of our application is to search across a number of columns in a table.\u00a0 We have been trying to accomplish this task with a variety of T-SQL options and nothing has been very quick.\u00a0 To add fuel to the fire, we need to rank the data so that the most relevant data is sorted from top to bottom.\u00a0 I have seen some of your recent tips related to Full Text Search.\u00a0 Can this technology help me achieve high performance searching while ranking the data?<\/span><\/p>\n

\u00a0<\/span><\/p>\n

Solution<\/strong><\/span><\/p>\n

As a matter of fact, the Full Text Search implementation in SQL Server 2005 has the ability to query across a number of columns in a single table then return the results in a sorted manner.\u00a0 This is achieved by using the CONTAINSTABLE command where a table of results are returned with the associated rank for each row.\u00a0 The table that is returned via the CONTAINSTABLE command has a column named KEY that contains the Full Text index key values corresponding to the relational table unique key value.\u00a0 In addition, the resulting table has a column named RANK which is a value from 0 to 1000 for each row indicating how well a row matched the selection criteria.\u00a0 From that point forward, ORDER BY and WHERE clauses can be added to the original SELECT statement to fine tune the result set.\u00a0 Let’s walk through some examples to see if these will meet your needs.<\/span><\/p>\n

CONTAINSTABLE Syntax<\/em><\/span><\/p>\n

CONTAINSTABLE ( table , { column_name | (column_list ) | * }<\/em><\/span>
\n , ‘ < contains_search_condition > ‘ <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0[ , LANGUAGE language_term] <\/em><\/span>
\n \u00a0\u00a0[ ,top_n_by_rank ] <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0) <\/em><\/span>
\n < contains_search_condition > ::= <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0{ < simple_term > <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0| < prefix_term > <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0| < generation_term > <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0| < proximity_term > <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0|\u00a0 < weighted_term > <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0} <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0| { ( < contains_search_condition > ) <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0{ { AND | & } | { AND NOT | &! } | { OR | | } } <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0< contains_search_condition > [ …n ] <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0}<\/em><\/span>
\n < simple_term > ::= <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0word | ” phrase “<\/em><\/span>
\n < prefix term > ::= <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0{ “word * ” | “phrase *” } <\/em><\/span>
\n < generation_term > ::= <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,…n ] ) <\/em><\/span>
\n < proximity_term > ::=<\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0{ < simple_term > | < prefix_term > } <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0{ { NEAR | ~ } { < simple_term > | < prefix_term > } } [ …n ] <\/em><\/span>
\n < weighted_term > ::= <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0ISABOUT<\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0( { { <\/em><\/span>
\n \u00a0\u00a0< simple_term > <\/em><\/span>
\n \u00a0\u00a0| < prefix_term > <\/em><\/span>
\n \u00a0\u00a0| < generation_term > <\/em><\/span>
\n \u00a0\u00a0| < proximity_term > <\/em><\/span>
\n \u00a0\u00a0} <\/em><\/span>
\n \u00a0\u00a0\u00a0[ WEIGHT ( weight_value ) ] <\/em><\/span>
\n \u00a0\u00a0\u00a0} [ ,…n ] <\/em><\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0)<\/em><\/span><\/p>\n

Example 1 – Sorted result set with OR logic<\/span><\/p>\n

In this example, all of the columns in the Production.Product table configured for Full Text Search are queried and only those columns with a rank over 100 are returned in descending order.\u00a0 As you can see, the relational table and full text table are joined via the unique key in the relational table (FT_TBL.ProductID) and in the full text catalog (KEY_TBL.[KEY]).<\/span><\/p>\n

USE AdventureWorks;<\/em><\/span>
\n GO<\/em><\/span>
\n SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK<\/em><\/span>
\n FROM Production.Product AS FT_TBL<\/em><\/span>
\n INNER JOIN CONTAINSTABLE(Production.Product, *,<\/em><\/span>
\n ‘”*washer*” OR “*ball*”‘) AS KEY_TBL<\/em><\/span>
\n ON FT_TBL.ProductID = KEY_TBL.[KEY]<\/em><\/span>
\n WHERE KEY_TBL.RANK > 100<\/em><\/span>
\n ORDER BY KEY_TBL.RANK DESC<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

Example 2 – Sorted result set based on the most relevant values<\/span><\/p>\n

The change from example 1 is a literal phrase ‘flat washer’ is queried with only the 10 most relevant results are returned, which is the parameter specified after the literal phrase in the CONTAINSTABLE command.<\/span><\/p>\n

USE AdventureWorks;<\/em><\/span>
\n GO<\/em><\/span>
\n SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK<\/em><\/span>
\n FROM Production.Product AS FT_TBL<\/em><\/span>
\n INNER JOIN CONTAINSTABLE(Production.Product, *,<\/em><\/span>
\n ‘”flat washer”‘, 10) AS KEY_TBL<\/em><\/span>
\n ON FT_TBL.ProductID = KEY_TBL.[KEY]<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

Example 3 – Proximity search<\/span><\/p>\n

Search the records where the terms ‘XL’, ‘men’ and ‘shorts’ are all in close proximity.<\/span><\/p>\n

USE AdventureWorks;<\/em><\/span>
\n GO<\/em><\/span>
\n SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK<\/em><\/span>
\n FROM Production.Product AS FT_TBL<\/em><\/span>
\n INNER JOIN CONTAINSTABLE(Production.Product, *,<\/em><\/span>
\n ‘XL NEAR men NEAR shorts’, 10) AS KEY_TBL<\/em><\/span>
\n ON FT_TBL.ProductID = KEY_TBL.[KEY]<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

Example 4 – Prefix search<\/span><\/p>\n

The only difference in this query from the previous example is to search for records with the ‘chain’ prefix.<\/span><\/p>\n

USE AdventureWorks;<\/em><\/span>
\n GO<\/em><\/span>
\n SELECT FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK<\/em><\/span>
\n FROM Production.Product AS FT_TBL<\/em><\/span>
\n INNER JOIN CONTAINSTABLE(Production.Product, *,<\/em><\/span>
\n ‘”chain*”‘, 10) AS KEY_TBL<\/em><\/span>
\n ON FT_TBL.ProductID = KEY_TBL.[KEY]<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

Example 5 – Pass in parameters to the Full Text query<\/span><\/p>\n

Below is an example where the query parameters and the percentage are returned based on the parameters passed into the code.<\/span><\/p>\n

USE AdventureWorks;<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

DECLARE @Parm1 varchar(50)<\/em><\/span>
\n DECLARE @Parm2 int<\/em><\/span><\/p>\n

SET<\/em><\/span><\/p>\n

@Parm1 = ‘”XL” OR “men” OR “shorts”‘<\/em><\/span>
\n SET @Parm2 = 10<\/em><\/span><\/p>\n

SELECT<\/em><\/span><\/p>\n

FT_TBL.ProductID, FT_TBL.[Name], KEY_TBL.RANK<\/em><\/span>
\n FROM Production.Product AS FT_TBL<\/em><\/span>
\n INNER JOIN CONTAINSTABLE(Production.Product, *,<\/em><\/span>
\n @Parm1, @Parm2) AS KEY_TBL<\/em><\/span>
\n ON FT_TBL.ProductID = KEY_TBL.[KEY]<\/em><\/span>
\n GO<\/em><\/span><\/p>\n

 <\/p>\n

Thanks for reading this article,<\/strong><\/span><\/p>\n

Next steps :<\/strong><\/span><\/p>\n

    \n
  1. Add this script to your database toolkit<\/strong><\/span><\/li>\n
  2. Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/span><\/li>\n
  3. Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Scenario Just like every other company, we need to have our queries run as fast as possible for our users to have the best experience possible with our application.\u00a0 One of the core components of our application is to search across a number of columns in a table.\u00a0 We have been trying to accomplish this […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[154,63,21,13,12],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/296"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=296"}],"version-history":[{"count":2,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/296\/revisions"}],"predecessor-version":[{"id":298,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/296\/revisions\/298"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=296"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}