Sql-server – SQL Server 2008 R2 Most Efficient Full Text Search Field Type

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

Please provide Full-text index definition, rowcount and total size of that column. Having VARCHAR(max) gives 32 megabytes per row of storage. If someone inserts huge text in that column many times, your db and index will grow very fast.

You can create index on a different FILEGROUP, which in turn can map to db file on another drive. ALTER DATABASE to add file(s) to new file group. FG is created and file(s) added to it in single statement:

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

For further reading (and source of this code): ALTER DATABASE File and Filegroup Options

Sorry, cannot add a comment for some reason. wil update /delete this answer later.

UPDATE

Although possible via a mapped drive, storing an index on the network has big performance penalty. If you are using all records in the table frequently and FT index is new functionality to explore, then this is ok: table on local storage and index on the network.

Alternatively I would suggest considering table usage and partition table into current-frequently used - and archive - almost unused - data. You can have several archive tables on different shares. Table and indexes will be stored on the same partition.

RE: Compression. It is a trade off between storage and CPU. If you have plenty of CPU resource, do consider this. If index stored on the network and compression rate is good, then it would also help.

UPDATE on Compression: Compression would requires row size to be less than 8060 bytes. So if you are storing text, then compression would not be possible for the table (= clustered index or heap). But if FTI row size is less than that, then you can compress FTI index.