Certain Formatted Text field (HTML) search conditions can be optimised in the Query builder. You can tell when this has happened because a lightning bolt will appear in the Query when you execute it.

Here is the message you get when you hover over the information icon having executed the Query. Note the lack of a lightning bolt in the green boxes.

Ideally SQL Server should have been installed with Full Text Indexing switched on, in which case all repositories created will have HTML columns optimised by default. You can follow these steps to retrospectively add it.

Checking if your server has full text indexing enabled

Right click on your server in SQL Server Management Studio and select Facets.

Below you can see the option ‘IsFullTextInstalled’.  If this is True then you can skip to the last stage.

Installing full text indexing on your server

Step 1: Open the Installation Center for the version of SQL Server you have installed.

Step 2:

Select “Add features to an existing installation” (the top option).

Step 3:

Locate your installation media.

Step 4:

Select Add Features to an existing install.

Step 5:

Continue until the Feature Selection and select “Full-Text and Semantic Extractions for Search”.  Then ‘Next’ until installation is complete.

Adding full text indexing to your Repository

Once you have installed full text indexing on your server you need to enable it on your MooD database. (This is the part which is usually done by Repository Manager when you create a Repository)

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AND CAST(LEFT(CAST( SERVERPROPERTY('productversion') AS CHAR(50) ), PATINDEX('%.%', CAST( SERVERPROPERTY('productversion') AS CHAR(50) )) - 1) AS INT) >= 10)

  BEGIN

     CREATE FULLTEXT CATALOG HTMLFTCatalog

       declare @sql nvarchar(4000)

        set @sql = 'CREATE FULLTEXT INDEX ON MooDElementHTML ( HTMLValue TYPE COLUMN FileExtension LANGUAGE 2057 )  KEY INDEX ElementHTMLPrimaryKey  ON HTMLFTCatalog WITH STOPLIST = OFF'

     exec sp_executesql @sql

  END

Checking your Queries are now optimised

This is the result – the Query is now optimised and will execute significantly quicker than before.