SQL Server Full-Text Search (FTS) Blog

New word breakers may return different results in SQL Server 2012 Full-Text Search

Douglas Laudenschlager [MS] — Fri, 18 Nov 2011 18:01:00 GMT

If you rely on SQL Server Full-Text Search, then this is important information, even if you can't recite the definition of word breaking and stemming.

Word breakers identify word boundaries and break words into their components. Stemmers identify alternate forms of a word, such as "run," "ran," and "running." Word breakers and stemmers are applied by Full-Text Search both to the text that you index at indexing time, and to the terms in your full-text queries at query time.

1. New word breakers are installed with SQL Server 2012

All the word breakers and stemmers used by Full-Text Search and Semantic Search (with the exception Korean) are updated in SQL Server 2012 "Denali." Please note the following changes in particular:

The third-party word breakers for English that were included with previous releases of SQL Server have been replaced with Microsoft components.
The third-party word breakers for Danish, Polish, and Turkish that were included with previous releases of SQL Server have been replaced with Microsoft components. The new components are enabled by default.
There are new word breakers for Czech and Greek. Previous releases of SQL Server Full-Text Search did not include support for these two languages.
The word breaker and stemmer for the Korean language are not upgraded in this release.

For consistency between the contents of indexes and the results of queries, we recommend that you repopulate existing full-text indexes after upgrading.

The information above is included in Books Online in the topic Programmability Enhancements (Database Engine).

2. Behavior changes in the new word breakers

The new components might return different results than the older components when you populate and query full-text indexes. In some cases, the word breaking returns similar results, but in other cases the new components return more results or fewer results than the older components.

Please consult the tables in the topic, Behavior Changes to Full-Text Search, for examples of some of the differences that can be expected in English results.

3. Reverting to the previous behavior

If you have to retain the previous behavior of the word breakers and stemmers, then we describe the steps for reverting in the topic, Revert the Word Breakers Used by Search to the Previous Version.

Reverting may require you to replace files and to change registry entries manually. These steps are described in detail in the linked topic.

We hope this information helps you to understand the behavior of the new word breakers in SQL Server 2012 successfully!

Subscribe to our blog feed for ongoing updates about search in SQL Server.

Custom proximity search with NEAR in SQL Server 2012

Douglas Laudenschlager [MS] — Tue, 15 Nov 2011 20:55:02 GMT

When you perform a proximity search with NEAR in versions of SQL Server prior to SQL Server 2012 "Denali," we decide what's NEAR for you. In SQL Server 2012, however, you're in control! With the new customizable NEAR operator, your full-text query can now specify 2 optional conditions that you couldn't specify before:

How near the search terms must be to one another.
Whether Term A must come before or after Term B.

Here's a quick example of the new NEAR operator:

… CONTAINSTABLE (Documents, Content, 'NEAR((John, Smith), 4, TRUE)')

This is a new operator, not an enhancement to the existing NEAR operator. The generic NEAR operator is deprecated in SQL Server 2012. It continues to work, but should not be used in new development.

1. Syntax of the custom proximity operator

1.1 Search terms

You can specify 2 or more search terms. As with generic NEAR, all search terms must be found in the target text to qualify as a hit.

Separate multiple terms with commas.
Terms can be single words or phrases.
Terms can be simple terms ('A') or prefix terms ('A*').
The query can use certain other query operators with NEAR. (See later in this post.)
A query for a single term returns an error.
The maximum number of terms is 64. A larger number returns an error.
Use double quotes to delimit phrases.

What about noise words among the query terms?

With the default setting – Transform noise words = 0 – the query raises a warning and does not return results if one or more of the search terms is a noise word.
With the setting Transform noise words = 1, the noise word among the query terms is ignored. If only 1 of the search terms is not a noise word, then the query effectively becomes a search for that term. For example:
```
CONTAINS(NEAR(('the', 'dog'), 3))  =>  CONTAINS('dog')
```

What about overlapping query terms? We only return non-overlapping occurrences. For example, if you performed the following query against the text 'AA' -

NEAR((A, AA), 2, TRUE)

- we will not return a hit, because the occurrences overlap. In fact, a query for overlapping terms can fail due to its complexity.

1.2 Distance between terms – optional max_gap argument

For detailed information about the optional max_gap argument, see my blog post, How near is NEAR in SQL Server 2012 Full-Text Search?

1.3 Order of terms – optional order argument

The optional Boolean order argument enforces order among the search terms. The default value is FALSE. If you want to specify order, then you also have to specify max_gap.

1.4 Right-to-left (RTL) languages are respected

In Arabic, Hebrew, and other RTL languages, the NEAR semantics apply in reverse. For example, NEAR((John, Smith), 3, TRUE) will match documents that contains "Smith xx xx John."

2. Using other operators in a NEAR query

You cannot use ISABOUT or FORMSOF with the custom proximity operator.

You cannot combine the new NEAR operator in a query with the generic ("old") NEAR operator.

However, you can use AND and OR, as shown in the following examples:

CONTAINS('NEAR((A, B), 5) AND C')
CONTAINS('NEAR((A, B), 5) OR C')
CONTAINS('NEAR((A, B), 5) AND/OR NEAR((C, D), 3)')

I hope this helps you to use the new NEAR operator in SQL Server 2012 successfully! Subscribe to our blog feed for ongoing updates about search capabilities in SQL Server.

-Doug

Looping over document similarity details in Semantic Search

Douglas Laudenschlager [MS] — Mon, 31 Oct 2011 18:16:58 GMT

Statistical Semantic Search, which is new in SQL Server 2012 “Denali,” indexes documents and text columns to support queries that go beyond the simple keyword search of LIKE or of Full-Text Search. Semantic Search lets you query for 3 types of information about your documents:

Key phrases. Return the statistically significant phrases in each document.
Similar documents. Return documents or rows that are similar or related, based on the key phrases in each document.
Why documents are similar. Return the key phrases that explain why 2 documents were identified as similar.

Semantic Search provides a rowset function for each of these 3 types of queries. Before we dive into the subject of this post – which describes how to do something that you can’t do with the built-in functions – let’s take a quick look at each of these capabilities.

The examples in this post use a collection of SQL Server white papers published on MSDN and TechNet – mostly in Microsoft Word format – saved into a SQL Server database by using a SQL Server 2012 FileTable. With this approach, I can easily save BLOBs into the database simply by dragging and dropping the files in Windows Explorer. The database table is named WhitePapers, and it has the fixed schema common to all FileTables.

Key phrases

To return the key phrases in a document, call the rowset function semantickeyphrasetable and provide the following:

The name of the indexed table that you want to query.
The indexed column or columns that you want to query.
Optionally, the ID of a single document or row.

In the following example, we use the title of a sample document to get its document ID from the FileTable. We also use a wildcard to query all the columns that are indexed for semantic search.

DECLARE @Title nvarchar(255)
DECLARE @DocumentID hierarchyid

-- We'll use the published white paper "We Loaded 1TB in 30 Minutes with SSIS."
SET @Title = '1TBin30MwithSSIS.docx'

-- Get the ID of the document from its title.
SELECT @DocumentID = path_locator
FROM WhitePapers
WHERE name = @Title

-- Get the top 5 key phrases in the document.
SELECT TOP (5) keyphrase AS 'Key Phrase', score AS 'Score'
FROM semantickeyphrasetable(WhitePapers, *, @DocumentID)
ORDER BY score DESC
GO

This query returns the following statistically significant key phrases for the selected white paper. (In version 1 of semantic search, only one-word terms are supported.)

Key Phrase       Score
---------------- -------------
sql              0.6475008
tcp              0.6415842
etl              0.6152592
numa             0.5434772
tpc              0.5183253

(5 row(s) affected)

Why documents are similar

To return the common key phrases that explain why 2 documents are identified as similar, call the rowset function semanticsimilaritydetailstable and provide the following:

The name of the indexed table that you want to query.
The IDs of the source document and of the matched (similar) document.
The indexed columns in the source row and in the matched row whose values you want to compare (often the same column).

In the following example, we ask for the key phrases which explain why the document “ScaleUpDWinSQL2008.docx” was identified in the preceding query as the document that’s most closely related to our source document about SSIS performance. The indexed content of the documents is stored in the file_stream column of our FileTable.

DECLARE @Title1 nvarchar(255)
DECLARE @Document1ID hierarchyid
DECLARE @Document2ID hierarchyid

-- Source white paper - "We Loaded 1TB in 30 Minutes with SSIS, and So Can You."
SET @Title1 = '1TBin30MwithSSIS.docx'

-- Get the ID of the source document from its title.
SELECT @Document1ID = path_locator
FROM WhitePapers
WHERE name = @Title1

-- Get the ID of the top matching document.
SELECT TOP (1) @Document2ID = matched_document_key
FROM semanticsimilaritytable(WhitePapers, *, @Document1ID)
ORDER BY score DESC

-- Now get the reasons for the match.
SELECT TOP (5) keyphrase AS 'Key Phrase', score as 'Score'
FROM semanticsimilaritydetailstable
(WhitePapers, file_stream, @Document1ID, file_stream, @Document2ID)
ORDER by score DESC
GO

This query identifies the top key phrases that are common to both documents:

Key Phrase       Score
---------------- -------------
sql              0.5141976
etl              0.3497615
server           0.241514
cpus             0.2336491
cpu              0.2166376

(5 row(s) affected)

For each similar document, tell me why it’s similar

Now we have enough background to get to the subject of this post!

It’s natural to want to ask this question: “For each document that’s similar to my source document, tell me why it’s similar.” We don’t provide a built-in function to support this set-based query. But you can achieve this iterative functionality concisely by using CROSS APPLY.

First, let’s create a table-valued function that returns semantic similarity details for a single pair of documents. This function will serve as the target of the CROSS APPLY.

CREATE FUNCTION semanticsimilaritydetails_foreach
    (
    @Document1ID hierarchyid,
    @Document2ID hierarchyid,
    @PhraseCount smallint
    )
RETURNS TABLE
AS
RETURN 
(
    SELECT TOP (@PhraseCount) * FROM
       semanticsimilaritydetailstable
       (
       WhitePapers,
       file_stream, @Document1ID,
       file_stream, @Document2ID
       )
       ORDER BY score DESC
)
GO

Now let’s use this new function with CROSS APPLY. @Document1ID contains the path_locator value of the source document that we’ve used in the preceding examples. In the WHERE clause, we make sure that we're not comparing the source document to itself.

DECLARE @DetailsCount smallint = 3;

SELECT WP.name AS ‘Document’,
              SSD.keyphrase AS ‘Key Phrase’,
              SSD.score AS ‘Score’
FROM WhitePapers as WP
CROSS APPLY semanticsimilaritydetails_foreach
       (@Document1ID, WP.path_locator, @DetailsCount) as SSD
WHERE WP.path_locator <> @Document1ID
GO

Here’s a portion of the results. They show not only each document that’s similar or related to our source document, but also the top 3 key phrases that explain why the 2 documents are similar.

Document                                                         Key Phrase       score
---------------------------------------------------------------- ---------------- -------------
ApplicationAndMulti-ServerManagement.docx                        sql              0.6475008
ApplicationAndMulti-ServerManagement.docx                        server           0.4185843
ApplicationAndMulti-ServerManagement.docx                        cpu              0.2633764
AuditingSQLServer2008.docx                                       sql              0.6475008
AuditingSQLServer2008.docx                                       server           0.3523027
AuditingSQLServer2008.docx                                       sqlserver        0.2144429
Automation of Data Mining Using Integration Services.docx        sql              0.3925959
Automation of Data Mining Using Integration Services.docx        msdn             0.1967144
Automation of Data Mining Using Integration Services.docx        aspx             0.191946

That’s it! Now you know how to iterate over a set of documents that are similar to a source document, and learn why each matching document is listed. You can probably find additional ways to parameterize or enhance these simple examples.

Be sure to check out the killer demo of FileTable and Semantic Search from SQL Server evangelist Roger Doherty and his team. This example includes a tag cloud to visualize the relative importance of key phrases and to find similar documents at a glance.

Introducing Fulltext Statistical Semantic Search in SQL Server code name 'Denali' Release!

Naveen Garg [MSFT] — Thu, 21 Jul 2011 17:56:00 GMT

We are very excited to introduce a new Statistical Semantic Search feature with Fulltext Search in Denali. With an optional new parameter ‘statistical_semantics’ you can now augment your Fulltext keywords index with two new indexes – one that captures top 100 statistically relevant words from a document and another that based on these relevant words, captures the top 10 similar documents in the same table. All together now Fulltext search not only allows you to search for a keyword efficiently, but also discover statistically relevant insight through prominent words and similar content.

We are providing you a compressed zip folder SQLSearchDemo that contains following documents and samples that should help you get started with all the new great features in Fulltext Search for SQL Server ‘Denali’ Release.

SQL Server Denali Fulltext and Semantic Search Features - Deep Dive Presentation
Fulltext and Semantic Search Features Demo Script for AdventureWorks Denali CTP1 Sample Database
Fulltext and Semantic Search Features Demo Script using Denali’s new FileTable feature
SQL Server Technical Articles Dataset for integrated Fulltext, Semantic and FileTable Features demo
Demo Instructions

We thank you for your active involvement during the development cycle of these features and are very much looking forward to your feedback from your test runs and live workload deployments!

PS: blog updated with new attachment for zip file.

Getting a custom IFilter working with SQL Server 2008/R2 (IFilterSample)

Sudarshan Narasimhan [MSFT] — Wed, 13 Jul 2011 20:04:22 GMT

In this post I will cover how to get a custom IFilter working on SQL Server 2008/R2 (64-bit or 32-bit). For the purpose of this demo, I am using the IFilterSample that is available with Windows Server 2008 SDK. Once you have installed the SDK you can find the sample here -> C:\Program Files\Microsoft SDKs\Windows\v6.1\Samples\winui\wsearch\IFilterSample

The IFilterSample implements a sample IFilter for a fictitious file format called “.filtersample” which is actually an XML file. This sample will work on Windows Vista and Windows 7.

Developers out there might already be familiar with using the IFilter interface to develop the filters. For those who aren’t you can start from here,

Windows Search Interfaces
http://msdn.microsoft.com/en-us/library/ee872063(v=VS.85).aspx

Data Add-in Interfaces
http://msdn.microsoft.com/en-us/library/ee872090(v=VS.85).aspx

IFilter Interface
http://msdn.microsoft.com/en-us/library/bb266451(v=vs.85).aspx

I am going to use IFilterSample as reference and must warn you this post is NOT a how-to-write-custom-IFilter lesson :).
The IFilterSample is a native C++ COM based DLL and as part of its registration provides a CLSID that can be registered on the system. Here are the step-by-step instructions you came here for:-

Download and install the Windows Server 2008 SDK on your machine.
Copy the sample from C:\Program Files\Microsoft SDKs\Windows\v6.1\Samples\winui\wsearch\FilterSample, onto your desktop which is the copy we will use to build the project.
Open the FilterSample.sln file and in Additional Dependencies in Linker Properties, add xmllite.lib
Build the project for x64 or Win32 as appropriate (this would be dependent on the architecture of your SQL Server this IFilter will be used on).
Once the project is built you will have FilterSample.DLL in your Debug or Release folder. Make sure that the SQL Server service account has FULL CONTROL permissions on this folder.
From an elevated command prompt browse to the location of the DLL and register it using regsvr32 FilterSample.dll
Now we need to create some registry keys in SQL Server to make it use this filter DLL.

1. The keys I created below are for a default instance of SQL Server 2008, so change the InstanceID as appropriate.
2. The CLSID given below is specific to this IFilterSample only.
3. Notice that the file extension I provided is called “.filtersample”.
4. The path provided has to match the path from where it was registered in step #6.
  
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters\.filtersample]
  @="{6C337B26-3E38-4F98-813B-FBA18BAB64F5}"
  
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\CLSID\{6C337B26-3E38-4F98-813B-FBA18BAB64F5}]
  @="C:\\Users\\sudarn\\Desktop\\FilterSample\\Release\\FilterSample.dll"
  "ThreadingModel"="Both"
  "Flags"=dword:00000001

8. Now, connect to the SQL instance as a sysadmin and run the following sequence of commands

 EXEC sp_fulltext_service @action='load_os_resources', @value=1;
 EXEC sp_fulltext_service 'verify_signature', 0
 EXEC sp_fulltext_service 'update_languages'; 
 EXEC sp_fulltext_service 'restart_all_fdhosts';

9. To verify the FilterSample.DLL has been successfully loaded and recognised by SQL Server, you can check the DMV sys.fulltext_document_types or use sp_help_fulltext_system_components 'filter'

That was all, folks! You can now do a test to verify that the search is working as expected. There are 2 ways of testing the IFilter.

1. Using SQL Server

1. Create a test table and enable it for full-text indexing.

CREATE TABLE [dbo].[testTBL](
 [doctype] [nvarchar](50) NOT NULL,
 [document] [varbinary](max) NULL,
 [docname] [varchar](50) NOT NULL,
 CONSTRAINT [PK_testTBL] PRIMARY KEY CLUSTERED 
 (
 [docname] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
 GO
 CREATE FULLTEXT CATALOG [CatalogTEST] WITH ACCENT_SENSITIVITY = OFF
 AUTHORIZATION [dbo]
 
 GO
 CREATE FULLTEXT INDEX ON [dbo].[testTBL](
 [docname] LANGUAGE [English], 
 [doctype] LANGUAGE [English], 
 [document] TYPE COLUMN [doctype] LANGUAGE [English])
 KEY INDEX [PK_testTBL]ON ([CatalogTEST], FILEGROUP [PRIMARY])
 WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
 GO

b. Insert a sample document into the table. The IFilterSample comes with a file called “SampleFile.filtersample”, which can be used for test purposes.

INSERT INTO dbo.testTBL(doctype,docname,document) 
 SELECT '.filtersample' AS doctype, 'SampleFile.filtersample' AS docname, 
 * FROM OPENROWSET(BULK N'C:\Users\sudarn\Desktop\FilterSample\SampleFile.filtersample', SINGLE_BLOB) AS Document

c. Perform a test search

select * from dbo.testTBL where contains(document, 'emergency')

d. You can also check to see the document was indexed by querying DMV,

SELECT * FROM sys.dm_fts_index_keywords(db_id('test'), object_id('dbo.testTBL));

2. Using FILTDUMP.exe

Filtdump.exe is a sample tool that is available again in WinSDK and can be used to test a particular filter by loading it and then getting the text chunk out of any sample document. It is available in both 32-bit and 64-bit versions. It can be found here once you install WinSDK,

C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64\filtdump.exe
C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\filtdump.exe

For example,
C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64>filtdump.exe -b "C:\Users\sudarn\Desktop\FilterSample\SampleFile.filtersample"
C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64>filtdump.exe "C:\Users\sudarn\Desktop\FilterSample\SampleFile.filtersample"

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

Type = 31 (0x1f): AuthorXYZ

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

Attribute = B725F130-47EF-101A-A5F102608C9EEBAC\19

idChunk = 4

BreakType = 0 (No Break)

Flags(chunkstate) = 0x1 (Text)

Locale = 1033 (0x409)

IdChunkSource = 0

cwcStartSource = 0

cwcLenSource = 0

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

this is a test of the emergency broadcasting system.

This is the content of the document.

I'm most pleased that it is the content of the document.

</snippet>

If you see the above then the IFilter is working fine as you are able to read it and get the text chunks out.

Possible Issue(s)

You might receive this error message in the SQLFT logs when trying to index a newly inserted row. This indicates that the IFilter was not recognised and there was an issue in loading it.

“Warning: No appropriate filter was found during full-text index population for table or indexed view '[test].[dbo].[Documents]' (table or indexed view ID '1003150619', database ID '6'), full-text key value 'SampleFile.filtersample'. Some columns of the row were not indexed.”

(OR)

Error '0x8004170c: The document format is not recognized by the filter.' occurred during full-text index population for table or indexed view '[test].[dbo].[Documents]' (table or indexed view ID '1003150619', database ID '6'), full-text key value 'SampleFile.filtersample'. Failed to index the row.

The possible things to check for above error are:-

Ensure that the registration and other steps given above are done in the same sequence as indicated.
If you are using the Debug build ensure that the VCRuntime Debug dependency DLL is present (msvcr90d.dll or msvcr100d.dll).
Use Process Monitor to ensure that your DLL is getting loaded
Use Dependency Walker to ensure there are no other missing dependencies for your DLL.
Verify that the DLL architecture (x64/x86) matches your SQL Server architecture. You cannot mix & match this.

I must point out one important thing here if you are going to use this DLL on other machines. You will need to have the Visual Studio Redistributable package installed on the target machine where you plan to deploy this. You will need the corresponding redistributable package for the VS version you used to build the DLL.

Have fun IFiltering and stay tuned for more on this blog.

Fulltext Search Improvements in SQL Server code-named 'Denali' CTP1

Naveen Garg [MSFT] — Thu, 02 Jun 2011 23:06:00 GMT

Fulltext Search codebase has been significantly revamped to address both query performance and throughput on large scale (millions of documents) with concurrent updates. With SQL2008 we moved all the index storage in the database file and majority of population logic in to the core engine to make fulltext search an integral and fully manageable engine component. However there was work to be done to make it perform and scale against best of the fulltext engines out there in the industry. With Denali CTP1, we are pleased to deliver this improvement.

We looked at the entire code base from how queries block while waiting an ongoing index update to release a shared schema lock, from how much memory is allocated during index fragment population, to how we could reorganize the query code base as a streaming Table Value Function to optimize for TOP N search queries, how we could maintain key distribution histograms to execute search on parallel threads, all the way to how we could take better advantage of the processor compute instructions (scoring ranks for example)… End result is that we are able to significantly boost performance (10X in many cases when it comes to concurrent index updates with large query workloads) and scale without having to change any storage structures or existing API surface. All our customers going from SQL 2008 / R2 to Denali will benefit with this improvement.

Besides performance and scale improvement, we also added support for property scoped searches over documents with file system properties stored with-in a fulltext enabled table. One can now issue a CONTAINS query looking for all documents containing a particular term and authored by a particular author without having to maintain a separate column for the Author name in the database.

We also improved NEAR operator in the CONTAINS predicate to allow users to specify distance between two terms and if the order of the term matters. It is important to note that distances between two words in a single sentence are much smaller compared to the same across two sentences (even if words are placed next to each other with a period in between), or across paragraphs, or across bullet points or across spreadsheet columns or worksheets.

We invite you to give SQL Server Code-named Denali Release a try and let us know what you think of the Fulltext Search improvements. Also enclosed here is a presentation that summarizes these feature improvements in CTP1 release.

SQL Server 2008/R2 Fulltext Search Fix for improving queries performance during concurrent index updates

Naveen Garg [MSFT] — Tue, 12 Apr 2011 18:41:00 GMT

SQL Server 2008/R2 Fulltext Search Fix for improving queries performance during concurrent index updates - http://support.microsoft.com/kb/958947

As you may already know, Microsoft has made significant improvements since SQL Server 2005 to fully integrate full-text search components inside the SQL Server execution engine and storage (http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx). Full-text index population and storage happen within SQL Server (not by a separate process and not in separate files in the file system anymore!). This provided a tightly integrated and optimized query execution and resource governance infrastructure for mixed queries (containing both tabular data and full-text search keywords in a single query – for example search for all documents that contain “SQL Server” and “Improvement” and that were created between October 2008 and April 2011) as well as tremendous manageability benefits. Now DBAs do not have to deal with a separate set of files, but simply the database itself. (Note: Filters and word breakers continue to run in a separate much leaner process – FDHost - to ensure that third-party components do not pose any security or reliability risk to the SQL engine).

That said, some of our customers who deployed SQL Server 2008 ran into a situation where one long running query created a backlog of many more pending queries that would have otherwise finished fairly quickly. This problem occurred under the following conditions:

A heavy transactional load that was also indexed for full-text searches. For example, end-users entering notes or comments that are also being continually searched upon at web scale.
A background index update started while there is a pending query.

Our engineering team promptly took on the issue and worked closely with customers to get to the bottom of it. The problem was caused by the following sequence of operations.

A particular query lasts long enough in its execution cycle with a Schema Stability (Sch-S) lock on metadata for index fragments (see Schema Locks in SQL Server at http://msdn.microsoft.com/en-us/library/ms175519.aspx).
A background index-merge kicks in (to reduce number of full-text index fragments) by the full-text engine and tries to acquire a Schema Modification (Sch-M) lock on the same metadata.
New queries are issued while the background index merge is waiting on the original query to finish.

Now all new queries, irrespective of their individual duration (when run in isolation) get queued behind the background merge and the first query. Therefore all queries start to take a very long time, reducing the full-text query performance of the entire system.

As soon as the problem was discovered and reported, our dev team got to work tirelessly. They addressed customer concerns by suggesting query changes to ensure that live systems continued to work. In parallel they investigated code improvements and made a number of changes that were thoroughly reviewed, tested and deployed in customer environments before releasing them in this publicly downloadable QFE.

The fix entails following code enhancements:

Release locks on index fragment metadata as soon as it is read by queries so that background index merge can proceed.
Reduce memory blocks and associated computations in query preparation, compilation and execution code blocks to improve parallelism.
1. Reduce size of histograms to ensure:
  1. They can be efficiently evaluated on parallel threads.
  2. They can be efficiently copied across different components (from compilation to optimization) and freed-up to improve throughput.
2. Calculate cardinality separately and discard swiftly after use.
3. Use significantly smaller allocation increments (by a factor of 64!).

Thus by releasing locks as soon as possible, coupled with reducing the amount of memory consumed during the query execution cycle we improved the throughput of the entire query subsystem and eliminated the problem with queued pending queries and an associated background index merge.

Now with the SQL Server Denali release, our development team has made significant further improvements:

We have improved parallelism in query execution (rewriting CONTAINS and FREETEXT internally as a Streaming TVF, optimizing execution tree with predicate folding whenever possible and slicing execution on parallel threads for document ranges based on histograms,…).
We have improved background index merges (moving away from locking the index fragments to a timestamp model where only timestamp update gets locked and therefore both query execution and index updates continue to happen more concurrently)

We invite our customers to download the latest CTP of the Denali release here and give the new and much improved fully integrated Full-text Search engine a run on their production systems. We have some very happy TAP customers who are already reporting significant performance boost.

Getting the LRSAMPLE custom word-breaker to work on 64-bit SQL Server 2008

Sudarshan Narasimhan [MSFT] — Sat, 11 Dec 2010 07:00:00 GMT

In this post I will cover how to get a custom word-breaker working on SQL Server 2008 64-bit instances. Many of you out-there might already be familiar with an old sample that shipped with Windows Platform SDK called LRSAMPLE. I am going to use this as a sample reference and must warn you this post is not a how-to on going about coding a custom word-breaker.

Let’s start off with the assumption that you have a requirement for a custom word-breaker, since the kind of word-breaking that you require is not possible with the default Word-breaker DLL that ships with SQL Server 2008.

To give you an idea of what I mean by “custom requirements”, have a look at this scenario.

Sample Rows
Brick 4 Sided, Other (See Remarks)
Stucco Unspecified, Other (See Remarks)
Aluminum/Vinyl, Brick/Frame, Concrete Siding
Brick 4 Sided, Brick/Frame, Concrete Siding

Let’s say you want to differentiate between “Brick” and “Brick/Frame” in such a way that a search for the keyword “Brick” return results for only entries which contain Brick alone and not ones that contain Brick/Frame. This of course is not possible using LANGWRBK.dll (SQL 2005) or NaturalLanguage6.dll (SQL 2008).

Okay, now you followed MSDN and compiled LRSAMPLE, got the DLL, registered it following the instructions in http://msdn.microsoft.com/en-us/library/ms345188.aspx , but when you actually tried to use the new word-breaker in a CONTAINS query, you are faced with this error

Msg 30053, Level 16, State 102, Line 1
Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.

The LRSAMPLE is a C++ COM based DLL (I did warn you it was an old sampleJ) and as part of its registration it puts in the CLSID under HKLM\System\currentcontrolset\control\contentindex\language

Even after following the instructions to register it to the letter, it does not work! Even a sample query to test the word-breaker using sys.dm_fts_parser gives the same error.

SELECT * FROM sys.dm_fts_parser (' "Hello" ', 19465, 0, 0)
-- 19465 is the language LCID is registered it as

Digging a little further in the SQLFT logs to see the reason for the word-breaking failure, I saw this error.

2010-10-20 14:19:32.59 spid23s Error '0x8007007f' occurred during full-text index population for table or indexed view '[GAMLS_NEW].[dbo].[AllClassesCommonData]' (table or indexed view ID '1182067397', database ID '10'), full-text key value '578004'. Attempt will be made to reindex it.

0x8007007f ---> The specified procedure could not be found.

Okay, this clears points to the DLL having some problems. I tried to register this DLL using regsvr32 and got these errors.
regsvr32 "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\lrsample.dll"

DLLEntryPoint could not be found.
Specified procedure could not be found

One thing I should point out is that the sample is based on VC6 and by default builds a DEBUG version of the DLL. So if your SQL Server is on the same machine as your Visual Studio, it might actually work. The moment you move this to a machine that does not have VS you will get these errors.

Here are the changes required to be done for importing this sample to a VS project and getting it to work on x64 architecture :-
(Note: This was done on VS 2010 on Windows 7)

1. Create an empty VS2010 project and pull in all the files in LRSAMPLE folder.

2. Change the configuration to Release and platform to x64

3. The following code change has to be made in lrsample.cxx to specify the path for DLL registration. (The registration code is hardcoded).

SLangRegistry const English_Sample_LangRes =
{
    L"English_Sample", MAKELANGID( LANG_ENGLISH, SUBLANG_ENGLISH_SAMPLE ),
    { L"{d225281a-7ca9-4a46-ae7d-c63a9d4815d4}",
      L"English_Sample Word Breaker",
      //L"lrsample.dll",
     L"C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLSERVER2008\\MSSQL\\Binn\\lrsample.dll",      -->  Change required here to specify location where DLL will be place
      L"both" },
    { L"{0a275611-aa4d-4b39-8290-4baf77703f55}",
      L"English_Sample Stemmer",
      //L"lrsample.dll",
     L"C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLSERVER2008\\MSSQL\\Binn\\lrsample.dll",      -->  Change required here to specify location where DLL will be place
      L"both" }
};

You need to specify the path of your SQL Server Instance Binn directory.

4. Make the following changes in the VS Project Properties.

a. General: Set the configuration type to Dynamic Library (.dll)

b. Linker\Input: Set module definition file to .\lrsample.def

c. Linker\System: Set the subsystem to WINDOWS

5. Build the Solution and copy the DLL from the Release folder to your SQL Binn directory.

6. Register the DLL from here using regsvr32 from command prompt.

Regsvr32 “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008\MSSQL\Binn\lrsample.dll”

After we did the above steps, the regular procedure of using custom word-breaker from http://msdn.microsoft.com/en-us/library/ms345188.aspx was enough to get this working on SQL Server 2008 X64.

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
exec sp_fulltext_service 'verify_signature' , 0;
exec sp_fulltext_service 'update_languages';
exec sp_fulltext_service 'restart_all_fdhosts'

To confirm if the word-breaker was loaded you can check this DMV select * from sys.fulltext_languages

To test the word-breaker we used this query and viola, no more errors!

SELECT * FROM sys.dm_fts_parser (' "Breaking Word" ', 19465, 0, 0)

I must point out one important thing here if you are going to use this DLL on other machines. You will need to have the Visual Studio Redistributable package installed on the target machine where you plan to deploy this. You will need the corresponding redist package for the VS version you used to build the DLL.

If you want to use this on multiple instances on the same machine you still need to have it copied over to each instance Binn folder and do the registration steps as per above article to create the registry keys.

Some References

Word Breaker and Stemmer Sample
http://msdn.microsoft.com/en-us/library/ms693176(v=VS.85).aspx

How to: Load Licensed Third-Party Word Breakers
http://msdn.microsoft.com/en-us/library/ms345188.aspx

IStemmer'ed the tide (or, Language-specific processing #2)
http://blogs.msdn.com/b/michkap/archive/2005/03/13/394822.aspx

You toucha my letters, IWordBreaker you face (or, Language-specific processing, #3)
http://blogs.msdn.com/b/michkap/archive/0001/01/01/395199.aspx

Part2 - SQL Full text Search and International Characters: Interesting Scenarios, solutions and deep-dive using DMV’s

Sudarshan Narasimhan [MSFT] — Sat, 11 Dec 2010 05:28:00 GMT

Continuing on my last post which dealt with Chinese characters, I will cover some user scenarios on Germanic characters like German/Swedish/Norwegian with SQL FTS. Read on...

Scenario1 – German language

Mr Customer: Hey, we have some full text search catalogs for German words, and we noticed that FREETEXTTABLE fails to find text clearly defined in the catalog!
Me: Hmm, can you give me more details on this behaviour.
Mr Customer: Sure. Here is what I see happening,

E.g. in the German catalog, define a column to be searchable for the table and set the word breaker language to German and let’s assume that the column has a product name = ‘d.3’
Now, when I do a search for ‘d.3’ I got 0 rows returned back. What’s up with that?

Me: Ah, gotcha! Here is what is happening…

I setup a sample repro query and as expected this did not return any rows.

SELECT FT_TBL.id,FT_TBL.data,KEY_TBL.RANK

FROM dbo.German AS FT_TBL 

INNER JOIN FREETEXTTABLE(dbo.German, data,'d.3') AS KEY_TBL

ON FT_TBL.id = KEY_TBL.[KEY]

The word ‘d.3’ is a noise word (and Noise words are not stored in a full-text index). A perfect example would be words like “an”, “the”, “but” etc. To prove this, I again go back to the trusted full-text DMV’s.

SELECT * FROM sys.dm_fts_parser ('d.3', 1031, 0, 0) --1031 is the LCID for GERMAN

keyword	occurrence	special_term	display_term	expansion_type	source_term
0x0064	1	Noise Word	d	0	d.3
0x0033	2	Noise Word	3	0	d.3
0x006E006E0033	2	Noise Word	nn3	0	d.3

As you can see all the words when broken down are NOISE WORDS. This means that they will not be stored in the full-text index, which is why your queries do not return any results.

In fact, if you run this query from Management Studio and in the results pane, switch to the Messages tab and you will see this warning,

Informational: The full-text search condition contained noise word(s).

Most people miss out on this indicator, since it’s a little tucked away. The above message means that “some string” in the search query was a noise word which was ignore by SQL Server when doing the search lookup.
Okay, so what do you do if you really really want to search for some keywords like “d.3” or “AT&T”?

Solution1
For the above scenario#2, FREETEXTTABLE is the incorrect function to use. FREETEXT predicate is to search for values that match the meaning, but not the exact wording. If you want to find the exact match, you should use either CONTAINS or CONTAINSTABLE.

Solution2
With SQL Server 2008, by default the system Stoplist is used for a catalog. So I created an empty stoplist and associated it with the above full-text index. This time when I run the query it did return 1 row for the string ‘d.3’

id data RANK
--- ----- --------
1 d.3 186

Note: Stoplists/Stopwords are available starting from SQL Server 2008 onwards. Please check this link for more information http://msdn.microsoft.com/en-us/library/ms142551.aspx.

If you are using SQL 2005, you can manually edit the noiseDEU.txt in the FTDATA folder and remove the alphabets and numbers. This will require a restart of the full-text search service + full population for the changes to show up. The disadvantage of doing this is that you full-text index gets bloated and becomes larger in size.

Scenario2 – Swedish/Norwegian characters

Mr Customer: I have a Full-text index created on a table that contains data in both Swedish and Norwegian. Certain queries produce incorrect results and the behaviour is inconsistent.
Me: I am all ears!
Mr Customer: My Swedish users raised a point that they aren’t able to search using some special chars like Ø. Here’s my complete situation.

I have lots of rows in my table which contains the word OPPGJØR. Now when I run the following query using a prefix search, it gives me 0 rows as output. (FYI - OPPGJØR translates to SETTLEMENT in Norwegian).

select * from TBL1 where contains(*,'"OPPGJØ*"')

But if I remove full-text and do a LIKE search I see the rows containing OPPGJØR being returned.

select * from TBL1 where datacolumn like 'OPPGJØ*'

Even more strange, if I place the prefix search like this I see the rows being returned.

select * from TBL1 where contains(*,'"OPPGJØR*"')

What is the difference in search between OPPGJØ* vs. OPPGJØR?

Me: Hmm! Let us breakdown the problem and see why this is happening.

First off I see that the word Ø is being used in the search condition. This is an accented word. So words such as café and cafe are treated differently. I checked the ACCENT SENSITIVITY property of the catalog and it was ON.
I turned it off using the following command,

alter fulltext catalog CATALOGNAME
REBUILD WITH ACCENT_SENSITIVITY=OFF;

After this, the above query started giving me the rows for 'OPPGJØ*’. The disadvantage of doing this is that, it can start to give you more result rows. In fact it gave me results for OPPGJO since this the treated the same as OPPGJØ. In fact this customer of mine had other special Norwegian words like Æ and Å which are again accented.

Here is the breakdown using the same DMV as before.

Select  document_id, occurrence_count, display_term from sys.dm_fts_index_keywords_by_document ( DB_ID('DBNAME'), OBJECT_ID('TBL1') ) Order by Keyword

This gave me the following output,

1	oppgj
1	oppgj
1	oppgj
1	oppgj
1	oppgjor
1	oppgjor
1	oppgjor
1	oppgjor

Next, I tried to pass in the LCID parameter to the search condition to see if that helped.

select * from Item it
where contains(*,'"OPPGJØ*"', LANGUAGE 1053)

select * from Item it
where contains(*,'"OPPGJØ*"', LANGUAGE 1044)

1053 --> Swedish

1044 --> Norwegian

You can look up this information in sys.fulltext_languages. But this also gave me 0 rows when accent_sensitivity was turned ON. Like I mentioned earlier these word-breakers are shipped out the box with SQL Server and NaturalLanguage6.dll is the one responsible for the above. More information on this here.

I tested this same on a SQL Server 2005 SP3 instance since the word-breakers were different back then (nls400.dll).

select * from dbo.swede where contains (text, '"OPPGJØ*"')

select * from dbo.swede where contains (text, '"åä*"')

And to my surprise, I got the expected output. So this looks like an issue with the new SQL 2008 word-breaker for Swedish/Norwegian. To be more specific this appears to be an issue in SQL 2008 specific to umlaut & other Germanic alphabets. You can read about umlaut here.

I did prefix searches (word*) for the following characters and they all returned me incorrect rows with sensitivity ON.

ä
ö
ü
Ä
Ö
Ü
Ø
Æ

Now, we are really getting somewhere close to nailing this issue. Since we know the problem has been isolated to prefix search on accented characters, I did some searching and found this KB article that talks about the exact problem with “extended characters or accents in a prefix search”!

FIX: Prefix search with extended characters or accents returns incorrect result in SQL Server 2008
http://support.microsoft.com/default.aspx?scid=kb;EN-US;973090

Solution
The solution here was to apply SQL 2008 SP1 Cumulative Update 4 which is described in the above article. After I tested this fix, the prefix searches started returned me expected results similar to SQL 2005.

Also to get better localized results, I changed the word-breaking language for the column datacolumn in TBL1 to Norwegian.

Other tips & considerations for international languages

Windows LCID Information
http://msdn.microsoft.com/en-us/library/ms788149.aspx

International Considerations for Full-Text Search
http://msdn.microsoft.com/en-us/library/ms142507(SQL.90).aspx

I hope this was useful in demonstrating some interesting case scenarios on international characters and its usage with SQL Full-text search.

The ultimate search engine would basically understand everything in the world and it would always give you the right thing.

Part1 - SQL Full text Search and International Characters: Interesting Scenarios, solutions and deep-dive using DMV’s

Sudarshan Narasimhan [MSFT] — Fri, 03 Sep 2010 00:29:00 GMT

As part of my work I come across varied scenarios that customers encounter with SQL Server. I recently had the opportunity to work on some interesting scenarios using SQL Full-text search and international characters (non-English). To most people, the concept of word-breaking especially with non-English characters is still a mystery. In this blog series, I will cover 3 scenarios where I was able to use some of the cool new features in SQL 2008/R2 to troubleshoot a user scenario. You might have run into something similar, so I thought I’d post it out here to clear any questions you had/have and hopefully impart some wisdom along the way. J

So here goes …

Scenario1 – Chinese words

Mr Customer: Hey, I have this problem with Chinese words not getting properly broken down in SQL Server 2008.
Me: Hmm, that’s sounds interesting. Chinese, did you say?
Mr Customer: Yes, Here is what I see happening

1. The Chinese word 网络 is being treated by full-text indexer as a single word. When I perform a full text search for the characters '网' and '络' individually, it does not return the above entry, but searching for '网络' together does.

Here is the XML where this is stored,
<LocalizedText xml:lang="zh-cn">网络</LocalizedText>

2. However, this word 子网seems to be treated by the full-text indexer as two separate words. So when I do a search for either of the following I get rows returned.
网
子
子网

Here is the XML where this is stored,
<LocalizedText xml:lang="zh-cn">子网</LocalizedText>

I went one step ahead and consulted with a Chinese speaking friend of mine and he says this is a bug and of the opinion that the phrase 子网 should also be treated as a single word, not separate words.

Me: Still more interesting. I did some poking around and here are the steps I took to figure it out.

I am a stranger to Chinese, so I translated these Chinese words into English with the help of Bing Translator! to understand what they mean.

网络 à Network
子网 à Subnet

Okay, now I’m a little better off knowing what these words mean. To test if the word-breaking & indexing are happening correctly, I setup a repro.

-- Step1. Table Creation. The column must be unicode and I am using NVARCHAR

CREATE TABLE [dbo].[chinese](

[id] [int] NOT NULL,

[data] [nvarchar](50) NULL,

CONSTRAINT [PK_chinese] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE FULLTEXT CATALOG [chinese]

IN PATH N'D:\'

WITH ACCENT_SENSITIVITY = ON

AUTHORIZATION [dbo]

CREATE FULLTEXT INDEX ON [dbo].[chinese](

[data] LANGUAGE [Traditional Chinese])

KEY INDEX [PK_chinese] ON [chinese]

WITH CHANGE_TRACKING AUTO

-- Step2. Insert 2 rows. You must use the N (unicode identifier) when inserting data.

insert into dbo.chinese (id,data) values (1, N'网络')

insert into dbo.chinese (id,data) values (2, N'子网')

-- Step3. Run Full-text queries using the CONTAINS predicate

-- 1st query to search for the word 网 (Net)

select * from chinese where contains (data, '"网"')

-- 2 rows returned

-- 2nd query to search for the word 网 (Work)

select * from chinese where contains (data, '"络"')

-- 1 row returned

-- 3rd query to search for the word 子 (Sub)

select * from chinese where contains (data, '"子"')

-- 1 row returned

So it appears that the Chinese word-breaker is indeed working properly. I was testing this on SQL Server 2008 SP1 and my language for word-breaking was Chinese Traditional.

Next, I tested this using Chinese Simplified as word-breaking language, since I see in the XML zh-cn was specified, and bingo the word-breaking issue does indeed reproduce.

Okay, what next? Fear not SQL Server 2008 has very good DMV support for full-text search! To isolate this further, I checked the Full-text Index to see how these characters were being stored.

Simplified Chinese:
SELECT * FROM sys.dm_fts_index_keywords(db_id('PUBS'), object_id('dbo.Chinese'))

keyword	display_term	column_id	document_count
0x5B50	子	2	1
0x7F51	网	2	1
0x7F517EDC	网络	2	1
0xFF	END OF FILE	2	2

Traditional Chinese:

SELECT * FROM sys.dm_fts_index_keywords(db_id('PUBS'), object_id('dbo.Chinese'))

keyword	display_term	column_id	document_count
0x5B50	子	2	1
0x7EDC	络	2	1
0x7F51	网	2	2
0xFF	END OF FILE	2	2

If you look closely at the above two outputs, the word-breaking is definitely different in Simplified vs. Traditional. Notice how the display_term has broken the 2nd into two separate tokens whereas the 1st is displayed as a single token.

But this definitely looks like a word-breaker issue with ChsBrkr.dll specific to Traditional Chinese. This is the way the words are supposed to be broken (by design!). Why do I say this?
Because for the word 子网, it does make sense that it should be a single word as individually it cannot be broken down separately.

Word-Breaking
Keep in mind, word-breaking is very much a language specific behaviour. The way each language has its own grammar, punctuation etc. are key factors used in word-breaking. The difference in behavior observed here, is due to the difference in grammar between Chinese Traditional vs. Chinese Simplified. After all, SQL Server uses the same natural language word-breakers that are used by Windows & Office.

While I’m on the topic of word-breaking, here is a sample query that you can use to identify the word-breaking language for each column in each table in a particular database. (You can use sp_msforeachdb to get this working for all databases in one go).

SELECT tbl.object_id as [ObjectID],

tbl.name as [TableName],

col.name AS [ColumnName],

sl.name AS [WordBreaker_Language],

sl.lcid AS [LCID]

FROM

sys.tables AS tbl

INNER JOIN sys.fulltext_indexes AS fti ON fti.object_id=tbl.object_id

INNER JOIN sys.fulltext_index_columns AS icol ON icol.object_id=fti.object_id

INNER JOIN sys.columns AS col ON col.object_id = icol.object_id and col.column_id = icol.column_id

INNER JOIN sys.fulltext_languages AS sl ON sl.lcid=icol.language_id

To Conclude - Using the DMV's for word-breaking we were able to get a much more clear picture of "why" the full-text engine was behaving the way it was and we were able to see the very smallest of differences in word-breaking when the language for word-breaker changed between Simplified vs. Traditional.

In my next post I will talk about a similar user scenario that I came across, only this was to be on Germanic characters like German/Swedish/Norwegian. Stay tuned for more....

Escaping special characters in FTS. i.e:How to use double quotes within a phrase itself?

Fernando Azpeitia — Mon, 21 Dec 2009 19:58:00 GMT

Recently, we got the following valid question:

I have something like :

SELECT line

FROM text1

WHERE CONTAINS(line, '"Good" Day"')

I get a Msg 7630 error because of the apostrophy in the middle of the phrase, but that is a part of my search phrase. So how can have a workaround to escape special characters in between phrase?

The solution is to use double quotes again to escape the double quotes within a phrase. This is an example:

SELECT line

FROM text1

WHERE CONTAINS(line, '" ""Good"" Day"')

Any other special character will not raise any error as our parser won't take it as phrase diferentiator, etc..

I hope this helps!

Presentation on SQL Server 2008 Full-Text Search: iFTS (integrated FTS)

Fernando Azpeitia — Tue, 15 Dec 2009 17:44:00 GMT

Hi All,

Several weeks ago I had the privilege to present SQL Server Full-Text Search at PASS 2009, here in Seattle. This presentation was part of a larger session targeting how to manage unstructured and semi-structured data in SQL Server 2008.

The session was an overall success, aiming the main pain points and best practices our customers are asking for. The original slide deck is under PASS branding, but I thought that posting in this blog another deck covering similar content would be beneficial. Find attached here this slide deck, which hopefully you find helpful and interesting!

'Gotchas' on searching for 'AT&T'? 'M&M'? etc..

Fernando Azpeitia — Wed, 09 Dec 2009 22:02:00 GMT

Generally, WBs will parse the '&' character as a separator and therefore emit the terms on both sides of this character. We already posted on this blog how to avoid this default behavior by using custom dictionaries.

Note the following though, SQL Server Full-Text Search Parser will see this character as an abreviation of an AND, therefore when you search for:

...CONTAINS('AT&T',.....)

Whether you have included this word in your custom dictionary or not, the query will be internally translated to:

...CONTAINS('AT AND T',......)

Thus, not applying the entry for AT&T in the custom dictionary.

In order to avoid this transformation, you need to use double quotes indicating you are searching for a phrase, thus such transformation should not take place. i.e: ....CONTAINS ("AT&T",....).

This will work correctly and if you added this word to your custom dictionary, it won't be tokenized but stored as-is into the FTIndex.

Hope this helps!.

Creating Custom Dictionaries for special terms to be indexed 'as-is' in SQL Server 2008 Full-Text Indexes

Fernando Azpeitia — Thu, 03 Dec 2009 23:59:00 GMT

Did You Know?: That SQL Server 2008 provides the ability to create a Custom Dictionary of special terms to be indexed 'as-is' (i.e: 'AT&T') into the FTIndex?...

Many times you find yourself in a situation where you would like to index a given word/term ‘as-is’ in your Full-Text Index. However, due the specific tokenization behavior from a given WB, it gets splitted in several individual terms. This might cause at query time recall issues.

For instance, your data contains the following term “ISBN-7”. Internally, most (if not all) WBs will tokenize this as ‘ISBN’ and ‘7’ separately. These two terms are going to be persisted in the FTIndex instead of the single and original “ISBN-7”. Note that because ‘7’ might be a default noise word for most languages (single digit numbers typically are considered as noise words), what happens is that the FTIndex will only index the term ‘ISBN’.

Later, at query time when you search for ‘ISBN-7’ using any of our available predicates (i.e: CONTAINS) or tvfs (i.e:CONTAINSTABLE), the same process will occur and the query will be internally translated to a query for ‘ISBN’ only. You will then receive all rows/documents containing ‘ISBN’, not only the one you cared about initially: ‘ISBN-7’.

By disabling STOPLIST for a given FTIndex, you are going to solve the missing results part of the problem, but you are going to have larger FTIndexes leading to performance implications and still not solving the problem of false positives, as you will still find documents containing 'ISBN' and '7' somewhere in the corpus of the document, but not necessarily together (next to each other) as you wanted originally.

This is a common problem when querying for email addresses for example, where the '@' character serves as separator . In this case, you are going to find document containing the exact email address as you desired, but as well documents containing both parts of the email separated for other special character different than '@' , leading to false positives again. There are other different variants of this behavior that can lead to false positives as well, etc.. While this behavior is expected, as you see, it can lead to undesired results.

In order to prevent this, SQL Server 2008 new WB family is introducing the ability to list a set of words/terms that should not be tokenized by a given WB, thus forcing them to be indexed as they are. These lists (called custom dictionaries) are simple text files and associated to each language supported by SQL Server Full-Text Search.

Here are the details in how to accomplish this:

(Reference: http://technet.microsoft.com/en-us/library/cc263242.aspx)

A custom dictionary is a Unicode-encoded file that can be used to specify words that you want the word breaker of the same language to consider as complete words. Custom dictionaries are not provided, by default. To modify the word breaker behavior for more than one language you must create a separate custom dictionary for each language for which you want to modify the word breaker’s behavior. You cannot create a custom dictionary for the language-neutral word breaker.

-Example created by Venkat, one of our team testers:

Consider the term “34590-97-8”. When this is passed to the German wordbreaker (for instance), the output terms are many:

select [display_term] from sys.dm_fts_parser('34590-97-8', 1031,0,0)

display_term

34590

nn34590

nn97

nn8

This output can be seen using:

select * from sys.dm_fts_parser('34590-97-8', 1031,0,0)

If you need to keep these numbers together for business reasons (I.e: this is a chemical code, account, phone number, etc) you then need to create a custom dictionary for German language in this case. Here are the steps:

1. Log on to the machine (on which sql server is installed) as a member of the Administrators group.

2. Start Notepad and type the words you want in your custom dictionary. Be sure to avoid invalid entries as described in the Reference link previously included. Remember that each word must be on a separate line and separated by a carriage return (CR) and line feed (LF).

3. On the File menu, click Save As.

4. In the Save as type list, select All Files.

5. In the Encoding list, select Unicode.

6. In the File name box, type the file name in the following format: CustomNNNN.lex, where NNNN is the language hex code of the language for which you are creating the custom dictionary (i.e: English= 1033). See Table 1, in the document referenced before in this post for a list of valid file names for supported languages and dialects.

7. In the Save in list, navigate to the folder that contains the word breakers. By default, this is the sql Binn directory. Example C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn.

8. Click Save.

9. Restart fdhost by executing “exec sp_fulltext_service 'restart_all_fdhosts'” on the sql server instance.

Now the custom dictionary will treat the term differently as a single word:

select [display_term] from sys.dm_fts_parser('34590-97-8', 1031,0,0)

display_term

34590-97-8

Currently, these text files only support a list of words and not more complex expressions, as for instance regular ones, etc.. (i.e: ISBN-*).

Important Note: This will only work with newly shipped SQL Server 2008 Word Breakers and not with older word breakers unchanged in our 2008 release. These new wordbreakers are also shipped with the OS in Vista+ operating systems. These wordbreaker dlls have names as NaturalLanguage6, NLS*.dll etc. As a notorious exception, the english wordbreaker shipped with SQL Server 2008 is identical to our 2005 version, thus it does not belong to this new category of Word Breakers capable to support a custom dictionary. In order to use a custom dictionary for English in SQL Server 2008, you will need to change the registry registration and indicate that the English WB existing in you OS (only if it is Vista+) is the one that should be used for your SQL intance from that moment on. Please follow this article in how to change this registration for a given WB or iFilter:

http://msdn.microsoft.com/en-us/library/ms345188.aspx

The above article shows how to do it for Danish, Polish etc. To do this for the new English word breaker, you need the corresponding values for the new English Word Breaker and stemmer. The wordbreaker files to copy to your sql Binn directory are below. These should be in your windows\system32 folder on Vista+ operating systems.

1. NlsData0009.dll

2. NlsLexicons0009.dll

3. NlsGrammars0009.dlll

Other settings needed:

String value	TsaurusFile	tsEnu.xml
DWORD value	Locale	00000409
String value	WBreakerClass	{9DAA54E8-CD95-4107-8E7F-BA3F24732D95}
string value	StemmerClass	{61A48126-EF74-4d4a-9DDA-43FD542CAD1E}

We hope this information is useful to you. We are going to include this information in our official Books Online (BOL) release in upcoming SQL Server 2008 SPs as well as in our next major release.

Regards!

Welcome!

Fernando Azpeitia — Mon, 30 Nov 2009 19:41:00 GMT

Welcome to our official SQL Server Full-Text Search (FTS) Blog!

This blog was just born recently J. We, the SQL Server FTS team, are planning to start posting here tons of useful information related with our Full-Text Search (FTS) feature in SQL Server.

This blog's goal is to cover the vast majority of themes of interest within our customers and other interested parties. The posts and articles published here will cover various areas of this feature, from deep dives in some of our architecture/behaviors to overall summaries from recent SQL conferences and main customer interactions. Announcing news, new content, best practices, known issues, etc.. ; as well as to gather suggestions, questions and answers will be common practices on this blog.

We hope this blog grows steadily and provides critical insight and best practices to our community out there! Please feel free to comment our posts and let us know your questions or topics you need help with.

Thanks!

SQL Server Full-Text Search (FTS) Blog

New word breakers may return different results in SQL Server 2012 Full-Text Search

1. New word breakers are installed with SQL Server 2012

2. Behavior changes in the new word breakers

3. Reverting to the previous behavior

Custom proximity search with NEAR in SQL Server 2012

1. Syntax of the custom proximity operator

1.1 Search terms

1.2 Distance between terms – optional max_gap argument

1.3 Order of terms – optional order argument

1.4 Right-to-left (RTL) languages are respected

2. Using other operators in a NEAR query

Looping over document similarity details in Semantic Search

Key phrases

Similar documents

Why documents are similar

For each similar document, tell me why it’s similar

Introducing Fulltext Statistical Semantic Search in SQL Server code name 'Denali' Release!

Getting a custom IFilter working with SQL Server 2008/R2 (IFilterSample)

Fulltext Search Improvements in SQL Server code-named 'Denali' CTP1

SQL Server 2008/R2 Fulltext Search Fix for improving queries performance during concurrent index updates

Getting the LRSAMPLE custom word-breaker to work on 64-bit SQL Server 2008

Part2 - SQL Full text Search and International Characters: Interesting Scenarios, solutions and deep-dive using DMV’s

Part1 - SQL Full text Search and International Characters: Interesting Scenarios, solutions and deep-dive using DMV’s

Escaping special characters in FTS. i.e:How to use double quotes within a phrase itself?

Presentation on SQL Server 2008 Full-Text Search: iFTS (integrated FTS)

'Gotchas' on searching for 'AT&T'? 'M&M'? etc..

Creating Custom Dictionaries for special terms to be indexed 'as-is' in SQL Server 2008 Full-Text Indexes

Welcome!