Help With Full Text Search. Performing AND across columns

I am trying to perform the following query on a table that has been
indexed using Full Text Search.

The table contains multiple columns than have been indexed. (Below,
all xml columns are indexed).
dbo.maintable(ProfileID int pk)
dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml)

I want to perform a query that will return any rows that contain ‘x’
and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
col3. (This is passed in by a user in a front end GUI)

The query to achieve this is:

SELECT ProfileID
FROM dbo.fts_table
WHERE CONTAINS (*,’”x” )
AND
CONTAINS (*,’”y”’)

Separating the search string in this way reduces performance and adds
complication in parsing the search string and extracting the AND
clauses from the OR, NOTs and parentheses (If a user has entered
them).

The query I want to perform:
SELECT *
FROM dbo.fts_table
WHERE CONTAINS (*,’”x” AND “y”’)

But this query is performed on a per column bases and will only return
the result if ‘x’ and ‘y’ are in the same column.

My first thought was to create a view, combining the data from each
column and use full text indexing on this.

CREATE VIEW fts_table_Joined with schemabinding as
SELECT mt.ProfileID as ProfileID,(SELECT col1, col2, col3 FROM
fts_table t WHERE t.ProfileID = mt.ProfileID)
FROM dbo.maintable mt

CREATE UNIQUE CLUSTERED INDEX fts_table_Joined_Index
ON fts_table_Joined (ProfileID);

But you cannot create an index on a view that has a nested query.
(The index is required for Full Text Search and the nested query is
needed to obtain a row for each record.)

The option I am left with is to create a table that replicates the
data in all the columns into one xml column and maintain this data
with triggers. This adds obvious overhead and data replication.

My question is am I missing something, is there a better way to solve
this problem?

re: Help With Full Text Search. Performing AND across columns

On Jul 10, 7:00*am, Sham <shamil.s...@gmail.comwrote:The subquery when used in the select part of the query should be
single column (in fact that's the goal, combining xml columns). Like
below:

CREATE VIEW fts_table_Joined with schemabinding as
SELECT mt.ProfileID as ProfileID,(SELECT col1+ col2+ col3 FROM
fts_table t WHERE t.ProfileID = mt.ProfileID)
FROM dbo.maintable mt



But since add operator can not be used on columns with xml data type,
first converting the columns to character data type and reconverting
back to xml may help:

SELECT mt.ProfileID,
(SELECT convert(xml,convert(nvarchar(MAX),col1)+
convert(nvarchar(MAX),col2)+ convert(nvarchar(MAX),col3))
FROM fts_table
WHERE (ProfileID = mt.ProfileID)) AS
ConsolidatedXMLColumns
FROM maintable AS mt


Finally, a more convenient solution can be just creating a persisted
computed column in fts_table and using the formula in the above
subquery:

convert(xml,convert(nvarchar(MAX),col1)+ convert(nvarchar(MAX),col2)+
convert(nvarchar(MAX),col3)).

re: Help With Full Text Search. Performing AND across columns

Thank you for you comment nidaar.

I had forgot to add FOR XML AUTO on the sub query, which would achieve
combining the columns into one.

This still does not solve my issue. As I would like to perform a full
text index (MS Full Text Search) on the data, to do this I need to
create a view with a unique index on it. This is not possible if a
view has a nested query in it?

Thanks,

Shamil

On 12 Jul, 05:29, nidaar <nid...@gmail.comwrote:

re: Help With Full Text Search. Performing AND across columns

On Jul 14, 5:07*am, Sham <shamil.s...@gmail.comwrote:
Since indexed views do not support subqueries, the subquery needs to
be converted to a join and a unique index has to be defined on the
indexed view (ProfileID), then a Full-text index on the consolidated
column can be created. Like below:

View:
CREATE VIEW [dbo].[View_1]
WITH SCHEMABINDING
AS
SELECT mt.ProfileID, CONVERT(xml, CONVERT(nvarchar(MAX),
dbo.fts_table.col1) + CONVERT(nvarchar(MAX), dbo.fts_table.col2) +
CONVERT(nvarchar(MAX),
dbo.fts_table.col3)) AS ConsolidatedXMLColumns
FROM dbo.maintable AS mt INNER JOIN
dbo.fts_table ON mt.ProfileID =
dbo.fts_table.ProfileID


Unique Index:
CREATE UNIQUE CLUSTERED INDEX [uix_view1] ON [dbo].[View_1]
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Full-Text Index:
CREATE FULLTEXT INDEX ON [dbo].[View_1] KEY INDEX [uix_view1] ON [aaa]
WITH CHANGE_TRACKING AUTO
GO
USE [ApptitudeSQL]
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ADD ([ConsolidatedXMLColumns])
GO
USE [ApptitudeSQL]
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ENABLE


Full-text query:
SELECT ProfileID
FROM View_1
WHERE CONTAINS(ConsolidatedXMLColumns,'SearchCriteria')

Of course a computed column with a full text index would be easier to
implement.

re: Help With Full Text Search. Performing AND across columns

On Jul 14, 5:07*am, Sham <shamil.s...@gmail.comwrote:Since indexed views do not support subqueries, the subquery needs to
be converted to a join and a unique index has to be defined on the
indexed view (ProfileID), then a Full-text index on the consolidated
column can be created. Like below:

View:
CREATE VIEW [dbo].[View_1]
WITH SCHEMABINDING
AS
SELECT mt.ProfileID, CONVERT(xml, CONVERT(nvarchar(MAX),
dbo.fts_table.col1) + CONVERT(nvarchar(MAX), dbo.fts_table.col2) +
CONVERT(nvarchar(MAX),
dbo.fts_table.col3)) AS ConsolidatedXMLColumns
FROM dbo.maintable AS mt INNER JOIN
dbo.fts_table ON mt.ProfileID =
dbo.fts_table.ProfileID


Unique Index:
CREATE UNIQUE CLUSTERED INDEX [uix_view1] ON [dbo].[View_1]
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Full-Text Index:
CREATE FULLTEXT INDEX ON [dbo].[View_1] KEY INDEX [uix_view1] ON
[aaa]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ADD ([ConsolidatedXMLColumns])
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ENABLE


Full-text query:
SELECT ProfileID
FROM View_1
WHERE CONTAINS(ConsolidatedXMLColumns,'SearchCriteria')


Of course a computed column with a full text index would be easier to
implement.

re: Help With Full Text Search. Performing AND across columns

On Jul 14, 5:07*am, Sham <shamil.s...@gmail.comwrote:View:
CREATE VIEW [dbo].[View_1]
WITH SCHEMABINDING
AS
SELECT mt.ProfileID, CONVERT(xml, CONVERT(nvarchar(MAX),
dbo.fts_table.col1) + CONVERT(nvarchar(MAX), dbo.fts_table.col2) +
CONVERT(nvarchar(MAX),
dbo.fts_table.col3)) AS ConsolidatedXMLColumns
FROM dbo.maintable AS mt INNER JOIN
dbo.fts_table ON mt.ProfileID =
dbo.fts_table.ProfileID


Unique Index:
CREATE UNIQUE CLUSTERED INDEX [uix_view1] ON [dbo].[View_1]
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Full-Text Index:
CREATE FULLTEXT INDEX ON [dbo].[View_1] KEY INDEX [uix_view1] ON
[MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ADD ([ConsolidatedXMLColumns])
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ENABLE


Full-text query:
SELECT ProfileID
FROM View_1
WHERE CONTAINS(ConsolidatedXMLColumns,'SearchCriteria')

re: Help With Full Text Search. Performing AND across columns

On Jul 14, 5:07*am, Sham <shamil.s...@gmail.comwrote:

Since subqueries are not supported by indexed views, the subquery
needs to be converted to a join, before defining a unique index (on
ProfileID). Then a full-text index can be created on the consolidated
column. Like below:


View:
CREATE VIEW [dbo].[View_1]
WITH SCHEMABINDING
AS
SELECT mt.ProfileID, CONVERT(xml, CONVERT(nvarchar(MAX),
dbo.fts_table.col1) + CONVERT(nvarchar(MAX), dbo.fts_table.col2) +
CONVERT(nvarchar(MAX),
dbo.fts_table.col3)) AS ConsolidatedXMLColumns
FROM dbo.maintable AS mt INNER JOIN
dbo.fts_table ON mt.ProfileID =
dbo.fts_table.ProfileID


Unique Index:
CREATE UNIQUE CLUSTERED INDEX [uix_view1] ON [dbo].[View_1]
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Full-Text Index:
CREATE FULLTEXT INDEX ON [dbo].[View_1] KEY INDEX [uix_view1] ON
[MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ADD ([ConsolidatedXMLColumns])
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ENABLE


Full-text query:
SELECT ProfileID
FROM View_1
WHERE CONTAINS(ConsolidatedXMLColumns,'SearchCriteria')


Of course, a persisted computed column on fts_table with a full text
index can be implemented in fewer steps.

re: Help With Full Text Search. Performing AND across columns

On Jul 14, 5:07*am, Sham <shamil.s...@gmail.comwrote:Since subqueries are not supported by indexed views, the subquery
needs to be converted to a join, before defining a unique index (on
ProfileID). Then a full-text index can be created on the consolidated
column. Like below:

View:
CREATE VIEW [dbo].[View_1]
WITH SCHEMABINDING
AS
SELECT mt.ProfileID, CONVERT(xml, CONVERT(nvarchar(MAX),
dbo.fts_table.col1) + CONVERT(nvarchar(MAX), dbo.fts_table.col2) +
CONVERT(nvarchar(MAX),
dbo.fts_table.col3)) AS ConsolidatedXMLColumns
FROM dbo.maintable AS mt INNER JOIN
dbo.fts_table ON mt.ProfileID =
dbo.fts_table.ProfileID


Unique Index:
CREATE UNIQUE CLUSTERED INDEX [uix_view1] ON [dbo].[View_1]
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Full-Text Index:
CREATE FULLTEXT INDEX ON [dbo].[View_1] KEY INDEX [uix_view1] ON
[MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ADD ([ConsolidatedXMLColumns])
GO
ALTER FULLTEXT INDEX ON [dbo].[View_1] ENABLE


Full-text query:
SELECT ProfileID
FROM View_1
WHERE CONTAINS(ConsolidatedXMLColumns,'SearchCriteria')


Actually FOR XML AUTO is not required. And of course, a persisted
computed column on fts_table with a full text
index can be implemented in fewer steps.

re: Help With Full Text Search. Performing AND across columns

On 14 Jul, 20:20, nidaar <nid...@gmail.comwrote:Thanks a lot, that works well.

The database I am going to be performing this on has 80 columns across
6 tables that need to be joined.

Would you know if there is a performance implication when performing
the conversion on all of these fields?

Thanks

Sham