Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql