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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CONTAINS results different then LIKE
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

24 Posts

Posted - 10/02/2013 :  17:30:11  Show Profile  Send lleemon a Yahoo! Message  Reply with Quote
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.


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

    FULLTEXTCATALOGPROPERTY(,'ItemCount') as [ItemCount],
    FULLTEXTCATALOGPROPERTY(,'MergeStatus') as [MergeStatus],
    FULLTEXTCATALOGPROPERTY(,'PopulateCompletionAge') as [PopulateCompletionAge_SEC],
        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(,'UniqueKeyCount') as [UniqueKeyCount],
    FULLTEXTCATALOGPROPERTY(,'ImportStatus') as [ImportStatus],
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
FROM sys.fulltext_catalogs AS cat

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.


Edited by - lleemon on 10/02/2013 17:48:48
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000