SQL Server Full-Text Search - Incredibly Limited. Is Yukon any better?

Author

Incredibly Limited. Is Yukon any better?

Arthur Dent

2005-04-04, 8:04 pm

Hello, i am fairly new to the world of SQL Server. ive spent many years
working on ORACLE, but am now getting my feet into SS.

I have a project right now, an online catalog, for a client. One of the
features of the site is, as usual, a product search.
I made a PROD_SEARCH table, consisting of PRODID, SEARCHTEXT columns, with a
fulltext index on searchtext.
searchtext is then populated nightly with a number of relational columns all
brought together (MFGPN, desc, notes, marketing, etc.)

Now im to the point of coding the actual search queries, and i cannot
beLIEVE how limited SS's CONTAINS clause is. As near as i can tell from
Books online, and from searching the newsgroups, the only funciontality it
has is:
wildcarding the end of a string
ANDing ORing and NOTing
NEARing and
inflectionals.

ORACLE's CONTAINS is VASTly more flexible and poweful than this. It has so
many operators and keywords that i always kept a cheat sheet nearby, and you
could query almost anything imaginable. Granted it was a LOT more complex to
set up, but the trade off was worth it.

Im wondering, is SS's freetext going to be getting any boost in what it can
do in Yukon? The most glaring deficiency is the lack of real wildcarding
(searching the end of a string is nice, but really, incredibly limited).

Cheers,
- Arthur Dent.

Hilary Cotter

2005-04-04, 8:04 pm

The search syntax does not improve much in SQL 2005. You can now

1) specify the language you are querying in
2) search by one column, all columns or a sub set of columns
3) search on properties - I am not sure if this feature has been yanked or
not.
4) search on near proximity where near is defined as a separation distance
of 1326 words, anything else is deemed far and is does not show up in
searches
5) search on more languages
6) use the thesaurus option in contains

For more advanced querying functionality you will need to look at Indexing
Services or Sharepoint Portal Search. Note that Sharepoint Team Services
uses the SQL FTS querying engine and its syntax.

--
Hilary Cotter
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

"Arthur Dent" <hitchhikersguideto-news@yahoo.com> wrote in message
news:%23ENisxSOFHA.604@TK2MSFTNGP10.phx.gbl...
> Hello, i am fairly new to the world of SQL Server. ive spent many years
> working on ORACLE, but am now getting my feet into SS.
>
> I have a project right now, an online catalog, for a client. One of the
> features of the site is, as usual, a product search.
> I made a PROD_SEARCH table, consisting of PRODID, SEARCHTEXT columns, with
a
> fulltext index on searchtext.
> searchtext is then populated nightly with a number of relational columns
all
> brought together (MFGPN, desc, notes, marketing, etc.)
>
> Now im to the point of coding the actual search queries, and i cannot
> beLIEVE how limited SS's CONTAINS clause is. As near as i can tell from
> Books online, and from searching the newsgroups, the only funciontality it
> has is:
> wildcarding the end of a string
> ANDing ORing and NOTing
> NEARing and
> inflectionals.
>
> ORACLE's CONTAINS is VASTly more flexible and poweful than this. It has so
> many operators and keywords that i always kept a cheat sheet nearby, and
you
> could query almost anything imaginable. Granted it was a LOT more complex
to
> set up, but the trade off was worth it.
>
> Im wondering, is SS's freetext going to be getting any boost in what it
can
> do in Yukon? The most glaring deficiency is the lack of real wildcarding
> (searching the end of a string is nice, but really, incredibly limited).
>
> Cheers,
> - Arthur Dent.
>
>

Arthur Dent

2005-04-04, 8:04 pm

Thanks for the fast response... though im disappointed to hear that.

Indexing services, can this be readily integrated into an ASP.NET
application?
Is it something which is implemented then at the database level, or at the
application level?

Thanks in advance.

"Hilary Cotter" <hilary.cotter@gmail.com> wrote
in message news:%23aRbPFTOFHA.3336@TK2MSFTNGP10.phx.gbl...
> The search syntax does not improve much in SQL 2005. You can now
>
> 1) specify the language you are querying in
> 2) search by one column, all columns or a sub set of columns
> 3) search on properties - I am not sure if this feature has been yanked or
> not.
> 4) search on near proximity where near is defined as a separation distance
> of 1326 words, anything else is deemed far and is does not show up in
> searches
> 5) search on more languages
> 6) use the thesaurus option in contains
>
> For more advanced querying functionality you will need to look at Indexing
> Services or Sharepoint Portal Search. Note that Sharepoint Team Services
> uses the SQL FTS querying engine and its syntax.

Hilary Cotter

2005-04-04, 8:04 pm

sure can - create a linked server to msidxs. You will have to store your
content in the file system to query it though. So its both.

To create a linked server issue the following command:

Sp_addlinkedServer 'LinkedServerName','
Indexing
Services','MSIDXS','
YourIndexingServices
CatalogName'

Then issue your queries like this:

SELECT * FROM OPENQUERY(LinkedServ
erName,'SELECT PATH, SIZE, CREATE FROM
SCOPE() WHERE CONTAINS(''Microsoft
'')')

--
Hilary Cotter
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

"Arthur Dent" <hitchhikersguideto-news@yahoo.com> wrote in message
news:uSIczITOFHA.3880@TK2MSFTNGP15.phx.gbl...
> Thanks for the fast response... though im disappointed to hear that.
>
> Indexing services, can this be readily integrated into an ASP.NET
> application?
> Is it something which is implemented then at the database level, or at the
> application level?
>
> Thanks in advance.
>
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote
> in message news:%23aRbPFTOFHA.3336@TK2MSFTNGP10.phx.gbl...
or[color=darkred]
distance[color=darkr
ed]
Indexing[color=darkr
ed]
>
>

Arthur Dent

2005-04-04, 8:04 pm

Thanks for the info. Bit cumbersome to have to store the search table in the
fs, ... i guess each row would then basically need to be a separate file?
But if thats what it takes, thats what it takes. Ill have to look at that
when i get a chance.

Thanks!

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:uQ$0oPTOFHA.904@tk2msftngp13.phx.gbl...
> sure can - create a linked server to msidxs. You will have to store your
> content in the file system to query it though. So its both.
>
>
> To create a linked server issue the following command:
>
> Sp_addlinkedServer 'LinkedServerName','
Indexing
> Services','MSIDXS','
YourIndexingServices
CatalogName'
>
> Then issue your queries like this:
>
> SELECT * FROM OPENQUERY(LinkedServ
erName,'SELECT PATH, SIZE, CREATE FROM
> SCOPE() WHERE CONTAINS(''Microsoft
'')')
>
>
> --
> Hilary Cotter
> 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
>
> "Arthur Dent" <hitchhikersguideto-news@yahoo.com> wrote in message
> news:uSIczITOFHA.3880@TK2MSFTNGP15.phx.gbl...
> or
> distance
> Indexing
>
>

Hilary Cotter

2005-04-04, 8:04 pm

That is correct - each row in the file system with a name that can point
back to the pk.

--
Hilary Cotter
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

"Arthur Dent" <hitchhikersguideto-news@yahoo.com> wrote in message
news:eXSHlxTOFHA.3512@TK2MSFTNGP15.phx.gbl...
> Thanks for the info. Bit cumbersome to have to store the search table in
the
> fs, ... i guess each row would then basically need to be a separate file?
> But if thats what it takes, thats what it takes. Ill have to look at that
> when i get a chance.
>
> Thanks!
>
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
> news:uQ$0oPTOFHA.904@tk2msftngp13.phx.gbl...
yanked[color=darkred
]
>
>

Sponsored Links