Document properties

I am using FTI as a search engine in a document library that we are
developing. How can we search on the properties of the document such as size
or date created as well as preforming a full text search?
The iFilters used in SQL FTS do not emit the document properties like size,
creation date, modified date. You will have to store them in the database in
seperate columns.
Ie
Create Table Text
(pk int not null primary key identity(1,1),
CreateDate datetime,
Size int,
TextCol text
)
Querying is difficult, as you typically want to limit your results set to
the top 100 for performance reasons. So your query would look like this:
select * from text join containstable(text, *, 'Microsoft',100) as a
on a.[key]=text.pk
where createdate> getdate()-20
order by rank desc
The problem is lets suppose the top 100 ranked hits on Microsoft are all
more recent than getdate()-20. In this case you will get no hits to such a
query.
There is a performance penalty to pay for returning more than 2000 hits from
MSSearch in a query. The workaround is to try an expensive query like this
and hope you get a small results set returned from MSSearch:
select * from text join containstable(text, *, 'Microsoft') as a
on a.[key]=text.pk
where createdate> getdate()-20
order by rank desc
or to partition your text table into date ranges which match your search
criteria.
The same sort of logic applies for size.
For small tables this won't be a problem for you. For large tables it is a
problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rob 19" <Rob19@.discussions.microsoft.com> wrote in message
news:77698456-55A4-44DC-A67A-844A64F66685@.microsoft.com...
> I am using FTI as a search engine in a document library that we are
> developing. How can we search on the properties of the document such as
size
> or date created as well as preforming a full text search?
|||Thanks Hilary. Do we know if this problem will be addressed in SQL Server 2005?
"Hilary Cotter" wrote:

> The iFilters used in SQL FTS do not emit the document properties like size,
> creation date, modified date. You will have to store them in the database in
> seperate columns.
> Ie
> Create Table Text
> (pk int not null primary key identity(1,1),
> CreateDate datetime,
> Size int,
> TextCol text
> )
> Querying is difficult, as you typically want to limit your results set to
> the top 100 for performance reasons. So your query would look like this:
> select * from text join containstable(text, *, 'Microsoft',100) as a
> on a.[key]=text.pk
> where createdate> getdate()-20
> order by rank desc
> The problem is lets suppose the top 100 ranked hits on Microsoft are all
> more recent than getdate()-20. In this case you will get no hits to such a
> query.
> There is a performance penalty to pay for returning more than 2000 hits from
> MSSearch in a query. The workaround is to try an expensive query like this
> and hope you get a small results set returned from MSSearch:
> select * from text join containstable(text, *, 'Microsoft') as a
> on a.[key]=text.pk
> where createdate> getdate()-20
> order by rank desc
> or to partition your text table into date ranges which match your search
> criteria.
> The same sort of logic applies for size.
> For small tables this won't be a problem for you. For large tables it is a
> problem.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Rob 19" <Rob19@.discussions.microsoft.com> wrote in message
> news:77698456-55A4-44DC-A67A-844A64F66685@.microsoft.com...
> size
>
>
|||No, its still a problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rob 19" <Rob19@.discussions.microsoft.com> wrote in message
news:BA69E42E-7999-4165-9547-5D4BECC3997E@.microsoft.com...
> Thanks Hilary. Do we know if this problem will be addressed in SQL Server
2005?[vbcol=seagreen]
> "Hilary Cotter" wrote:
size,[vbcol=seagreen]
database in[vbcol=seagreen]
to[vbcol=seagreen]
a[vbcol=seagreen]
from[vbcol=seagreen]
this[vbcol=seagreen]
a[vbcol=seagreen]
as[vbcol=seagreen]