Full Text Indexing - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Full Text Indexing

Author

Topic

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-17 : 10:47:36

Hi guys.

For one of our search pages we would like to use Full Text Searches. However, I haven't done it before and I have some questions.

Here is the ddl for the table that is stored in a full text catalog.


CREATE TABLE [dbo].[Content] (
	[ContentID] [int] IDENTITY (1, 1) NOT NULL ,
	[ParentID] [int] NOT NULL ,
	[SectionID] [int] NOT NULL ,
	[TypeID] [int] NOT NULL ,
	[Order] [int] NOT NULL ,
	[Depth] [int] NOT NULL ,
	[Lineage] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OtherMenus] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Summary] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Content] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostDate] [datetime] NOT NULL ,
	[ExpireDate] [datetime] NOT NULL ,
	[LockDate] [datetime] NOT NULL ,
	[IsApproved] [bit] NOT NULL ,
	[LastSavedUserID] [int] NULL ,
	[LastSavedDate] [datetime] NULL ,
	[ViewRestricted] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

This table is used to store html content. I included the columns: Title, Summary, and Content into a full text catalog.

Here are my questions

1)Is it possible to put a weight on the columns so matches in the Title column would weight more than those in the Summary and Content columns. I looked for a while on BOL and all I found were examples of putting weights on individual words.


USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8), 
   sauces weight (.4), relishes weight (.2) )' )
GO

Is it possible to do something like this?


SELECT ContentID, Title
FROM Content
WHERE CONTAINS(Title WEIGHT(.5), Summary WEIGHT(.3), 
      Content WEIGHT(.2), @SearchInput)

2. The Content column is of text data type and it stores html fragments. From BOL it says...

quote:

Formatted text strings, such as Microsoft word document files or HTML files, cannot be stored in character string or Unicode columns because many of the bytes in these files contain data structures that do not form valid characters.

It then goes on to explain that you need to store the these columns in Image columns and apply a filter.

Does anyone know if it applies this filter when it populates the full text catalog, or if it performs it each time during a search. If it does it each time during a search I'm wondering if I should create another column which contains a "text only" version with all the html tags stripped out. This way I could use populate the text only column so searches would be faster.

Any help would be appreciated on these two issues.

Dustin Michaels

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-17 : 12:02:51

I have another question to if you guys don't mind answering.

I tried typing in "and" in a text box that is used with full text search and I received an sql error

quote:

Execution of a full-text operation failed. A clause of the query contained only ignored words

Is there any listing of all the ignored words that full-text search uses or is there an option that I can use so if the query contains only ignored words that an empty result set is returned?

Dustin Michaels

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources