CONTAINS results different then LIKE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CONTAINS results different then LIKE

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2013-10-02 : 17:30:11

I have a resume table that has almost 8 million records. When I do a search with CONTAINS to find specific results I get different results then doing basically the same search but with LIKE statement.

Example:


select COUNT(*)
from Resume
where Resume_DateCreated > '2013-09-01'
and Resume_ID >= 7604075
and Resume_Detail like '%series 6 %'

--363 results


select COUNT(*)
from Resume
where Resume_DateCreated > '2013-09-01'
and Resume_ID >= 7604075
and CONTAINS(Resume_Detail, '"series 6 "')
--4970 results

What appears to be happening in the CONTAINS searches is it's finding 'series ' only. The '6' value isn't searched.

Someone mentioned something about stopwords / stoplists so here are my results:


select * from sys.fulltext_stopwords
--RETURNS nothing


select * from sys.fulltext_stoplists
--RETURNS nothing

Since it has no results I am wondering if it's my environment or something not setup properly?


select * from sys.databases where name = 'nameofdbhere'
--compatibility level = 80


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')
--RETURNS: 10.50.1600.1	RTM	Standard Edition (64-bit)
--aka: SQL Server 2008/2008 R2 RTM (12 Apr 2010)


exec master..xp_msver
/*
Index	Name	Internal_Value	Character_Value
1	ProductName	NULL	Microsoft SQL Server
2	ProductVersion	655410	10.50.1600.1
3	Language	1033	English (United States)
4	Platform	NULL	NT x64
5	Comments	NULL	SQL
6	CompanyName	NULL	Microsoft Corporation
7	FileDescription	NULL	SQL Server Windows NT - 64 Bit
8	FileVersion	NULL	2009.0100.1600.01 ((KJ_RTM).100402-1539 )
9	InternalName	NULL	SQLSERVR
10	LegalCopyright	NULL	Microsoft Corp. All rights reserved.
11	LegalTrademarks	NULL	Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12	OriginalFilename	NULL	SQLSERVR.EXE
13	PrivateBuild	NULL	NULL
14	SpecialBuild	104857601	NULL
15	WindowsVersion	498139398	6.1 (7601)
16	ProcessorCount	24	24
17	ProcessorActiveMask	NULL	          ffffff
18	ProcessorType	8664	NULL
19	PhysicalMemory	24563	24563 (25756262400)
20	Product ID	NULL	NULL


SELECT
	FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') as [IndexSizeMB],
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') as [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') as [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') as [PopulateCompletionAge_SEC],
    (SELECT CASE FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus,
    FULLTEXTCATALOGPROPERTY(cat.name,'UniqueKeyCount') as [UniqueKeyCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') as [ImportStatus],
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
FROM sys.fulltext_catalogs AS cat

/*
RESULTS:
IndexSizeMB	ItemCount	MergeStatus	PopulateCompletionAge_SEC	PopulateStatus	UniqueKeyCount	ImportStatus	LastPopulated
23228	7737338	0	0	Change Tracking	4985421	0	1990-01-01 00:00:00.000
*/

Based on this, wondering if the compatibility level is causing this.

Any ideas or suggestions would be helpful.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources