Some questions about timestamps, noise words, ...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter van Wilrijk - 24 Jul 2006 10:26 GMT

Hi,

1) I've read timestamp isn't really convertible to datetime, but can a
timestampfield tell me when a specific row was indexed, just to be sure
adaptions where reflected in the index ... or does it only store whe a row
was updated in order to let the system know it has to be indexed again?

2) Can I query a list of all words that have been indexed and if so can I
see for each word how many times they appear in the table?

3) Is there a command that tells me which noise word file my FTI is using?
I've got 2 FT indexed databases with same tablestructure, one storing dutch
and one storing french content, but I guess they both use the same (english)
noise word file :-)

Thanks a lot,
Kind regards,
Peter Van Wilrijk.

Reply to this Message

Hilary Cotter - 24 Jul 2006 13:28 GMT

answers inline

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

> Hi,
>
> 1) I've read timestamp isn't really convertible to datetime, but can a
> timestampfield tell me when a specific row was indexed, just to be sure
> adaptions where reflected in the index ... or does it only store whe a row
> was updated in order to let the system know it has to be indexed again?

A timestamp column is incremented when a row is updated or inserted. When
you run an incremental population and your table you are full-text indexing
has a time stamp column on it an incremental population will be done if a
full population has been done previously. The incremental population reads
the latest timestamp from the table and compares it with the last timestamp
value retrieved when the last population was done. It then only re-indexes
columns with high timestamp values than the previous one. It still has to
suck each row out of the table to determine what rows have been deleted, and
what rows have a higher time stamp value and need to be reindexed. It does
not differentiate between a row whose columns you are fulltext indexing have
been modified and another columnn (one which is not full-text indexed) has
been modified.

> 2) Can I query a list of all words that have been indexed and if so can I
> see for each word how many times they appear in the table?

No, you have to write utilities to do this yourself.

> 3) Is there a command that tells me which noise word file my FTI is using?
> I've got 2 FT indexed databases with same tablestructure, one storing
> dutch
> and one storing french content, but I guess they both use the same
> (english)
> noise word file :-)

No, you can issue a SP_HELP_FULLTEXT_COLUMNS 'MyTableName' to tell you which
word breaker is applied.

You then have to read the registry to determine the noise word list applied.
For SQL 2000 its in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override\SQLServer$SQL2000

For SQL 2005 its in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\MSSearch\Language

> Thanks a lot,
> Kind regards,
> Peter Van Wilrijk.

Reply to this Message

Peter van Wilrijk - 24 Jul 2006 14:34 GMT

Muchas Gracias Hilary!!

All very clear to me now ...

except one little thing ...
can the timestamp (after cast or convert) tell me when the row was inserted
(or last updated) or should I use datetime fields to store that
information??

Btw, somebody working with .NET???
I've 2 stored procedures. The first to select data, the second to update
data ... but only when in the mean time ... nobody else updated the specific
row.

I know it can be done using ADO.NET instructions but I want/have to use
stored procedures here.

So I'm looking for a piece of code showing how to handle the timestamp field
in .NET. I want to be able to put the timestamp into a classproperty to
pass it from a dataset in my datalayer to my client (I pass an object, not a
dataset to my client) ... and afterwards I want to pass it back from my
client to the datalayerfunction that calls the update stored procedure.
I've some problems here since .NET 2002 doesn't seem to support timestamp
data type, requiring to convert it to an array of 8 bytes???

Thanks,
Peter Van Wilrijk.

> answers inline
>
[quoted text clipped - 49 lines]
> You then have to read the registry to determine the noise word list applied.
> For SQL 2000 its in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\O
verride\SQLServer$SQL2000

> For SQL 2005 its in
>
[quoted text clipped - 3 lines]
> > Kind regards,
> > Peter Van Wilrijk.

Reply to this Message

Hilary Cotter - 24 Jul 2006 18:27 GMT

Use datetime for that.

Regarding timestamps and ado.net - have a look at this.

http://www.codeproject.com/cs/database/Multi-Tier.asp

Signature

> Muchas Gracias Hilary!!
>
[quoted text clipped - 105 lines]
>> > Kind regards,
>> > Peter Van Wilrijk.

Reply to this Message

Peter van Wilrijk - 25 Jul 2006 10:08 GMT

Thanks once more!!!

> Use datetime for that.
>
[quoted text clipped - 114 lines]
> > applied.
> >> For SQL 2000 its in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\O

> > verride\SQLServer$SQL2000
> >>
[quoted text clipped - 5 lines]
> >> > Kind regards,
> >> > Peter Van Wilrijk.

Reply to this Message