How can I know when SQL Full Text Index Population is finished?

image

When you execute some queries on your SQL Server, are you sure the catalog is being imported?

With this simple script you can know which is the Index status.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'TEST_FullIndex'

SELECT FULLTEXTCATALOGPROPERTY(@CatalogName,'ItemCount') as NumberOfItems, 
	   FULLTEXTCATALOGPROPERTY(@CatalogName,'ImportStatus') as ImportStatus,
	   DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,
                     'PopulateCompletionAge'), '1/1/1990') AS LastPopulated,
    (SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS Status
FROM sys.fulltext_catalogs AS cat

 

More information about the status or other property on Microsoft https://technet.microsoft.com/en-us/library/ms190370.aspx

LINQ to SQL - Enabling Fulltext searching

LINQ to SQL allows you write your data access statements in your .Net code in LINQ which is then translated into SQL. LINQ supports many of the same constructs, predicates of SQL however some aren't supported.



One set of predicates that aren't supported are those for full text, i.e. CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE That means you can't write something like,

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.Contains("Description,Title","simon")

                 select s;

You can however do

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.description.Contains("simon")

                 select s;

So you might think that full text is supported. Unfortunately Contains is translated into a LIKE predicate with two wildcards, i.e.

select *

  from sessions

 where description like '%enrico%'


So how do you get it working. Well the magic is in Inline Table Valued Functions. I've talked about them before when discussing performance of scalar functions. An inline table valued function is essentially a prameterised SQL statement, and importantly only one statement. This means the optimiser can merge the SQL Statement into the query that uses it.

To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,

create function udf_sessionSearch

      (@keywords nvarchar(4000))

returns table

as

  return (select [SessionId],[rank]

            from containstable(Session,(description,title),@keywords))

 

You then add this function to your LINQ 2 SQL model and he presto you can now write queries like.

var sessList = from s   in DB.Sessions

               join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId

             select s;


If you want you can extend this to limit the number of results from containstable. If you want to search different columns then you will need different functions as the colum list used by containstable cannot take a parameter.


Advertsing

125X125_06

TagCloud

MonthList

CommentList