MS SQL 2000 Query Analyzer and Full-Text Indexing Query?

Question

Post reply

MS SQL 2000 Query Analyzer and Full-Text Indexing Query?

Kevin Snyder

Ten Centuries

Points: 1082
More actions
July 6, 2005 at 12:45 pm

#89939

I can only view the column headers in MS Query Analyzer when I run the following code. I have correctly setup the index catalog in MS SQL 2000 Enterprise Manager.
SELECT ArticleTitle
FROM Article
WHERE CONTAINS(ArticleTitle, 'magazine')
SELECT *
FROM Article
WHERE CONTAINS(ArticleDescription, 'magazine')
Now, I can run the follow conventional code and get result everytime.
SELECT * FROM Article
WHERE ArticleTitle LIKE '%magazine%'
SELECT ArticleUrl FROM Article
WHERE ArticleTitle LIKE '%magazine%'
Working with the MS SQL Full-Text Catalog is new to me. I'm surprised that MS Query has these limitations.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

CONTAINS(ArticleDescription, 'magazine')

is not the same as

LIKE '%magazine%'

it's more like

LIKE 'magazine%'.

I can't explain it but I can assure you that those queries are not the same.

Kevin Snyder Ten Centuries Points: 1082 More actions · Answer 2

" I can only view the column headers in MS Query Analyzer when I run the following code. I have correctly setup the index catalog in MS SQL 2000 Enterprise Manager."

SELECT ArticleTitle

FROM Article

WHERE CONTAINS(ArticleTitle, 'magazine')

SELECT *

FROM Article

WHERE CONTAINS(ArticleDescription, 'magazine')

Full-Text syntax does not show data in results of MS SQL Query Analyzer....

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I have to disagree on this one... a query still is a query even with full-text :

Select * from dbo.Problemes where contains (*, 'tester*')

PkProbleme TitreProbleme DateProbleme FkADP FkUser FkObjADP FkProblemeType FkProblemeStatus FkProblemePriorite DescriptionProbleme CauseProbleme TS_ForFullIndexes

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

47 test 2005-07-06 14:45:21.000 1060 3512 37505 1 1 4 testers tester 0x000000000014FBF6

(1 ligne(s) affectée(s))

Kevin Snyder Ten Centuries Points: 1082 More actions · Answer 4

Kevin Snyder

Ten Centuries

Points: 1082

July 7, 2005 at 7:15 am

#571745

I did'nt have the 'Catalog' populated...........

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

July 7, 2005 at 7:22 am

#571756

Ya that can be a problem .

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Remi is right in that the queries are not the same

CONTAINS(ArticleDescription, 'magazine')

is the same as

LIKE '% magazine %' (yes there is a space either side of the word magazine)

CONTAINS(ArticleDescription, 'magazine*')

it is not more like

LIKE 'magazine%'.

Far away is close at hand in the images of elsewhere.
Anon.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

As I said, not the expert in this matter . Thanx for correcting my answer David.

John Kane SSCrazy Points: 2810 More actions · Answer 8

Well, I am the expert on SQL Server Full-text Search (FTS) as I have worked with it while at Microsoft since its inclusion in SQL Server 7.0 Beta3 in 1998. I maintain a blog on this subject as well as have extensively research this topic for a book on the wider topic of Search and I've made thousands (well, maybe hundreds ) of replies in the sql server public newsgroup (microsoft.public.sqlserver.fulltext).

SQL FTS is a language-specifc "word" based search algorithm, while LIKE is a "character" based algorithm. Both methods have their advantages and disadvantages, and SQL FTS was designed to provide different text search methods and the two method can and do return different results based upon the search string as well as the search text and OS platform.

From a performance perspective, when you have table that has 100,000+ to over 1 million rows and if you continuously run the below LIKE query using LIKE '%word%', each query will do a table or index scan (an expensive operation) for each query regardless of the indexes on the table or column. FTS queries with on a FT enable table with the same number of rows will perform better on average and depending upon the exact query and data volume

than a similar T-SQL LIKE query.

SQL Server 2000 Full-Text Search Resources and Links

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

SQL Server 2000 FTS on Windows 2000 vs. Windows Server 2003...

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158.entry

See the SQL FTS blog link for more information on SQL Server 2000 as well as SQL Server 2005 Full Text Search.

Thanks,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

You cannot use ANSI SQL %LIKE% with FULL TEXT Index because your data is not table space based SQL Server creates an Arithmetic pointer to your data in the file system. FULL TEXT is an add on to SQL Server dependent on Microsoft Search so you have to populate the Microsoft Search Catalog to get expected results. You do your search with CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE all are Microsoft Proprietry. Run a search for all in the BOL(books online) Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie