Sunday, February 19, 2012

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

No comments:

Post a Comment