Does SQL2005 still require all FTI keywords to match in the same column?

Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
Hi Thomas,
I am afraid SQL 2005 shows the same behavior. FTS is designed to search for text/documents, which normally are stored in the same column. Can you please explain a bit more why you need to have queries that involve several word matches for several columns?
Thanks!
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:33 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Does SQL2005 still require all FTI keywords to match in the same column?
Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
|||Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Hi Thomas,
I see your problem. Let me think about the best solutions.
Most of FTS users are focus in get great functionality to efficiently search inside a document, rather than to search parts in different documents stored in different columns. Anyway, for these cases like yours, we support multiple CONTAINS. Is true that
the performance is not as good as with one single CONTAINS but if we would allow from the beginning to have several columns look ups in a single CONTAINS, we would probably finish with similar performance even if you are just writing one clause.
The good news is the following.
-For next FTS release we have several architecture improvements that will improve dramatically the joined queries. This means that mix relational (date for instance) with FTS search will be efficient. Following your example, before look the FTS side, the
optimizer will get the few ones that pass the date filter and then these ones will be FT searched.
This improvement also will improve multiple CONTAINS queries, so you will not longer experiment pain.
-For now, the best you can do is to use computed columns. These columns will contain virtually the same data than the original columns and you can create a FT index on that column. The indexing time will take longer as you are merging 2 or more columns bu
t at query time you will be able to query efficiently and find what you look for.
Does this help?
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:34 PM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas
|||Hi Thomas,
When I say next FTS release I mean the next release, not any upgrade. And don’t worry, the next version should be no longer than 2007
In a following post I will let you know the steps to work with computed columns in SQL 2005.
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas [mailto:tomwinzig@.gmail.com]
Posted At: Friday, February 17, 2006 10:50 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas

Does RS use ORDER BY for grouping?

It seems like you don't need to specify SQL's ORDER BY to match Page and Group breaks in RS. I.e., RS does
this handling by itself, with the added work and memory requirements to do it.
My question is whether RS will understand that I do have ORDER BY in the query that matches my Page and Group
breaks, and just read off the input stream without doing the sorting etc by itself. If it doesn't, I wouldn't
want to burden the DBMS with the sorting as it would be redundant...
I tried a report, which was indeed alphabetically grouped by RS without a SQL ORDER BY. Even when adding ORDER
BY NEWID() (which returns rows in a different "random ordering for each execution), the report was still
grouped alphabetically.
(I am not referring to the ordering of the details info, I understand that ORDER BY is needed for that.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/You don't ever need to use ORDER BY in your query, even to order the
details. RS supports many different types of data sources (and you can
easily add your own by writing a custom data processing extension), not just
SQL, and some these data sources may not have the ability to sort. Also, RS
does not examine your query and cannot detect that you have an ORDER BY.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPN1N6ifEHA.1644@.tk2msftngp13.phx.gbl...
> It seems like you don't need to specify SQL's ORDER BY to match Page and
> Group breaks in RS. I.e., RS does
> this handling by itself, with the added work and memory requirements to do
> it.
> My question is whether RS will understand that I do have ORDER BY in the
> query that matches my Page and Group
> breaks, and just read off the input stream without doing the sorting etc
> by itself. If it doesn't, I wouldn't
> want to burden the DBMS with the sorting as it would be redundant...
> I tried a report, which was indeed alphabetically grouped by RS without a
> SQL ORDER BY. Even when adding ORDER
> BY NEWID() (which returns rows in a different "random ordering for each
> execution), the report was still
> grouped alphabetically.
> (I am not referring to the ordering of the details info, I understand that
> ORDER BY is needed for that.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>

does not match with a table name or alias name used in the query

I am trying to create a trigger on a table. The idea is
that the trigger will stop duplicates being entered but
will allow null values. I am trying to use the inserted
table but am receiving the following error.
The column prefix 'T2' does not match with a table name
or alias name used in the query.
The table and trigger creation script follows.
Thanks in advance.
========= Table
=========
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Symbol] [T_STD_SYMBOL] NULL ,
[ClassTypeID] [int] NOT NULL ,
[Description] [varchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
========= Trigger
=========
if exists (select 1
from sysobjects
where id = object_id('uqSymbol_class')
and type = 'TR')
drop trigger uqSymbol_class
go
/* Trigger to stop duplicate Symbols being entered in
the Symbol column in the class table. */
create trigger uqSymbol_class on Class for insert, update
as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
if @.numrows = 0
return
/* Check to see if the inserted updated symbol
already exists */
if update(symbol)
begin
-- Check weather the Symbol value is Null.
Ignore if so.
-- if ((select symbol from inserted) != null)
begin -- IF((SELECT COUNT(*) FROM INSERTED WHERE
Symbol IS NOT NULL) > 0)
if (select count(*)
from Class t1, inserted t2
where t1.symbol = t2.symbol) >1 AND
T2.Symbol IS NOT NULL
begin
select @.errno = 30003,
@.errmsg = 'The Symbol you have
entered or just updated already exists.'
goto error
end
end
-- end
return
/* Error handling */
error:
raiserror @.errno @.errmsg
rollback transaction
end
ThanX :-)You are not giving us any code which uses t2 as an alias... please repost
complete code...
also if there are ever multiple triggers, and one of the other triggers does
a select insert update or delete, then @.@.rowcount in this trigger will not
be correct. It is probably safer to count records in inserted and deleted...
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||You missed out the relevant bit of code.
In fact you don't need a trigger to do this. You can use an indexed view to
enforce uniqueness only for non-NULL values:
CREATE VIEW Symbols
WITH SCHEMABINDING
AS
SELECT symbol
FROM dbo.class
WHERE symbol IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclsymbol ON Symbols (symbol)
--
David Portas
--
Please reply only to the newsgroup
--|||Hi Jamie,
Posting messages with attachments usually isn't very useful because most
people do not trust them, won't open them and so don't have enough
information to answer your question. Post everything in plain text instead.
If you are on SQL Server 2000 you don't have to use a trigger to achieve
this, but you can instead create an indexed view to check for duplicates:
CREATE VIEW Class_Symbol_check
AS
SELECT Symbol FROM Class
WHERE Symbol IS NOT NULL
GO
CREATE UNIQUE INDEX ON Class_Symbol_check (Symbol )
GO
Read the topic "Indexed views" in Books online for more information.
The fact that a UNIQUE index can only have one NULL (otherwise you could
just create a unique constraint on the Symbol column in the class table) is
a problem in SQL Server and one I sincerly hope will be addressed in the
next version.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||> a problem in SQL Server and one I sincerly hope will be addressed in the
> next version.
Full SQL92 constraints would solve this problem and more. I hope Yukon will
support the ANSI-style constraints.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah,
In the all the info I have read and heard about Yukon there is a lot of talk
about CLR support, and only very general remarks about improvements in
T-SQL. I guess the marketing people at Microsoft think that (full ANSI
constraints etc) is "hard core SQL" and too difficult for 95% of the
developers, and they are probably right. Euan Garden (Program manager for
SQL Server) said during a presentation "that people think that Microsoft
will drop support for T-SQL now that there's CLR support, but that's not the
case", so that tells you the level they are targeting. </rant>
Of the new SQL features in Yukon that I know of I like the idea in Yukon
that you can write your own datatypes, I just don't like the idea that you
have to write them in a CLR language instead of being able to declare them
in SQL, although I expect that there will soon be a cottage industry in
datatypes for SQL Server (postcodes, telephone numbers, credit card numbers,
ISBN etc), just like there was with ActiveX controls for VB 6.
But there is little or no information on structural improvement to T-SQL and
further compliance with ANSI standards. Do we get ANSI style constraints?
row constructors? ...?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:#ZqfnQLYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > a problem in SQL Server and one I sincerly hope will be addressed in the
> > next version.
> Full SQL92 constraints would solve this problem and more. I hope Yukon
will
> support the ANSI-style constraints.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
>|||Thanks for all your help. I have implemented the view as
the trigger.
Jacco, what did you mean by CLR.
Thanks again.