May 1, 2007 at 9:43 pm
SQL Server 2005
We have a problem with 'Full-Text Search' using 'NEAR' in a contains select. It does not appear to be working as it should. Please see below for the scenerio.
Below is a set of scripts I used to test the full-text search feature of MS SQL Server 2005. This feature is failing to function as expected when we are using CONTAINS predicate + NEAR proximity term.
The SQL Server documentation says:
NEAR | ~ - Indicates that the word or phrase on the left side of the NEAR or ~ operator should be approximately close (eight words) to the word or phrase on the right side of the NEAR or ~ operator.
~~~~~~~~~~~~~~~~~~~~~~~~~
USE [FTS_TEST_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Creating the test table
CREATE TABLE [dbo].[FTS_TEST_TABLE](
[FTS_TEST_ID] [int] NOT NULL,
[FTS_TEST_TEXT] [ntext] NOT NULL
)
GO
-- Creating table index
CREATE UNIQUE CLUSTERED INDEX FTS_TEST_ID_IDX ON FTS_TEST_TABLE(FTS_TEST_ID)
GO
-- Creating the Full Text catalog
CREATE FULLTEXT CATALOG CAT_FTS_TEST WITH ACCENT_SENSITIVITY=OFF AS DEFAULT
GO
-- Creating the Full Text index
CREATE FULLTEXT INDEX ON FTS_TEST_TABLE(FTS_TEST_TEXT)
KEY INDEX FTS_TEST_ID_IDX ON CAT_FTS_TEST
GO
-- Inserting a sample record
INSERT INTO FTS_TEST_TABLE VALUES (1, 'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.
Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.')
-- Trying out the Full Text Search
SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');
Result: The record is being selected, despite the distance between the words FTS_STRING_SAMPLE1 and FTS_STRING_SAMPLE2
========================
Any assistance would be greatly appreciated.
It appears to be doing and AND search and not a NEAR search
Glyn
May 2, 2007 at 2:15 pm
I have read your post and the BOL and I am assuming Microsoft did not run their test with NText, I think their test was run with NVarChar(Max) which keeps all data under 4000 in a row which could give you the results you expect. But I really think you should use Containstable with NVarChar(max) because it is easier to filter with Near when you have the table in memory. Run a search for Containstable and NVarChar(Max) in the BOL. Hope this helps.
Kind regards,
Gift Peddie
May 2, 2007 at 7:37 pm
Thanks Peddie I will check it out
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply