Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages .
Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended
SELECT *
FROM dbo.vw_recipe_search r
INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
ON r.search_id = kt.[KEY]
ORDER BY RANK DESC;
The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column .
We used Levenstein string comparision function to order for better results set
You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:
USE master
GO
SET NOCOUNT ON
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE fullTextDemo3
END
GO
IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO
ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO
USE fullTextDemo3
GO
CREATE TABLE dbo.nearnessTest (
rowId INT IDENTITY,
keywords VARCHAR(MAX) NOT NULL,
notes VARCHAR(100) NOT NULL,
CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO
INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
-- CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
--AND m2.language_id = 1033
GO
-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' )
CREATE FULLTEXT CATALOG ftc_List3
GO
-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO
SELECT 'before' ft, * FROM sys.fulltext_indexes
GO
DECLARE @i INT
SET @i = 0
WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN
SELECT outstanding_batch_count, *
FROM sys.dm_fts_index_population
WHERE database_id = DB_ID()
--SELECT *
--FROM sys.dm_fts_outstanding_batches
--WHERE database_id = DB_ID()
WAITFOR DELAY '00:00:05'
SET @i = @i + 1
IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END
END
SELECT 'after' ft, * FROM sys.fulltext_indexes
GO
SELECT *
FROM dbo.nearnessTest
WHERE CONTAINS( keywords, 'error' )
What happens when you issue an explicit start for the index population, eg
ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;
Run these summary functions
SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus
Best Answer
I think you need to be having a look at the Full Text DMVs here: http://msdn.microsoft.com/en-us/library/ms174971%28v=sql.105%29.aspx
Specifiably I think you want something like:
or