Conditional full-text indexing possible in 2000?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Smedegaard Buus - 01 Nov 2006 13:25 GMT

Hey there :)

Sorry if I'm asking dumb questions, but I'm not really a MSSQL man myself,
I've always used MySQL, but this particular project that I'm involved with
right now uses an MSSQL Server. So, I have one question.

Is it possible to have fulltext indexing on a table with a condition? As
in "do indexing on all items in this table except those where
field 'isDeleted' equals '1'"? As it is right now, most data from deleted
items are moved to a different table to avoid indexing "deleted" data, and
thus speed up searches.

If it's not possible in 2000 (the wizard to create fulltext thingies doesn't
supply such an option itself, but I was thinking maybe there's some manual
editing that could be done?), then is it possible in 2003?

Thanks in advance,
Daniel

Reply to this Message

Hilary Cotter - 01 Nov 2006 14:00 GMT

You do full-text index views in SQL 2005 which could incorporate this
condition. It is not possible to do this in SQL 2000 unless you partition
the data which matches your condition into a separate table.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hey there :)
>
[quoted text clipped - 15 lines]
> Thanks in advance,
> Daniel

Reply to this Message

Daniel Smedegaard Buus - 01 Nov 2006 15:34 GMT

> You do full-text index views in SQL 2005 which could incorporate this
> condition. It is not possible to do this in SQL 2000 unless you partition
> the data which matches your condition into a separate table.

Hello Hillary, thank you very much for your reply :)

The partition scheme you describe seems to be exactly the approach used at
the moment. The server will, among other tasks, be backing a TYPO3
installation, and at the moment it seems this actually won't run on MS SQL
2000. So now we have two good arguments for upgrading.

We have a 2005 Standard license, so we're trying that out to see if it'll
fit the bill.

Thanks again for your help :)

Daniel

Reply to this Message

Simon Sabin - 01 Nov 2006 22:07 GMT

Hello Daniel,

Indexed views will store a complete copy of the data.

Standard edition doesn't do partitioning you will need to use a partitioned
view which adds a level of complexity because of the restrictions on their
design. You could add the extra column as a token so that you don't get those
rows back from the full text search i.e.

select *
from containstable (yourtable, yourcolumn,'(IWantThisValue AND ThisValue)
AND NOT ISDELETED'

so when you update your isdeleted flag to 1 you add ISDELETED to the column
being full text indexed

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

>> You do full-text index views in SQL 2005 which could incorporate this
>> condition. It is not possible to do this in SQL 2000 unless you
[quoted text clipped - 14 lines]
>
> Daniel

Reply to this Message

Daniel Smedegaard Buus - 02 Nov 2006 11:04 GMT

> Hello Daniel,

Hi Simon, thanks for writing :)

> Indexed views will store a complete copy of the data.

Is this different from "regular" full-text indexes?

> Standard edition doesn't do partitioning you will need to use a
> partitioned view which adds a level of complexity because of the
[quoted text clipped - 7 lines]
> so when you update your isdeleted flag to 1 you add ISDELETED to the
> column being full text indexed

Argh... You'll have to excuse me, I'm not really a DB designer myself in
general, and definitely not MS SQL savvy in particular, I'm merely going to
use this DB in PHP, so my knowledge is somewhat reduced... I'm not quite
sure what you're telling me? Sorry :(

> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons

Reply to this Message

Simon Sabin - 02 Nov 2006 23:31 GMT

Hello Daniel,

Data is stored in a table

A view is a way of representing the joining of multiple tables i.e. a piece
of SQL using a name, a shorthand. The engine effectivley puts the SQL from
your view into the SQL it isbeing used. in.

An indexed view actually stores the data that the view would return, this
way the data can be returned from this set rather than all the base tables.
That means your data that is returned by an indexed view will be stored in
the indexed view AND the original table.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

>> Hello Daniel,
>>
[quoted text clipped - 25 lines]
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/simons

Reply to this Message

Daniel Smedegaard Buus - 23 Nov 2006 13:14 GMT

> Hello Daniel,
>
[quoted text clipped - 8 lines]
> tables. That means your data that is returned by an indexed view will be
> stored in the indexed view AND the original table.

Hi Simon, so so sorry for not replying earlier!

Thanks for the explanation. It sounds like a great way to seriously optimize
your DB for specific uses at the expense of storage space (which is
plentiful anyway ;). We will do this most definitely!

Thanks :)

Daniel

> Simon Sabin
> SQL Server MVP
[quoted text clipped - 29 lines]
>>> SQL Server MVP
>>> http://sqlblogcasts.com/blogs/simons

Signature

http://www.rhesusb.dk

Reply to this Message