About full-text indexes (SQL FTS)

See http://technet.microsoft.com/en-us/library/ms142571.aspx for some background.

srv4 creates full-indexes on a full-text catalog (see http://technet.microsoft.com/en-us/library/bb326035.aspx) called 'Main'. If this catalog does not exists it is automatically created using the default SQL server settings.

srv4 creates full-text indexes in the 'FullText' filegroup if it is present. If there is no filegroup with that name the full-text indexes are created in the same filegroup of the associated table.

srv4 create full-text indexes with the following properties:

•Without specifying a language - and so using the SQL server default language

•Without enabling semantics functions (http://msdn.microsoft.com/en-us/library/gg492075.aspx)

•With automatic change tracking - i.e. the index is automatically updated as data in inserted or modified from the associated table

•Using the system default stoplist

•Without an associated search property list (see http://technet.microsoft.com/en-us/library/ee677637.aspx)

srv4 creates full-text indexes only if it cannot find an existing index with the same column, so if the default settings used by srv4 for the index creation are not appropriate, simply drop the index and create it manually with the required settings - as long as it contain the same column specified in the schema the 'srv4 dbupdate' / 'srv4 dbschame' won't touch it.

The 'srv4 dbupdate', 'srv4 dbcreate' and 'srv4 dbschema -in XXXX' commands automatically create the necessary SQL full-text indexes if executed when SQL full-text search is enabled by:

  <add key="Db.FullTextEngine" value="Sql" />

The full-text indexes to create and which attribute to index are computed automatically based on the definition of the full-text indexed attributes inside the schema, e.g.:

  <xs:element name="summary" type="tText" minOccurs="0">

    <xs:annotation>

      <xs:appinfo>

        <gs:fullTextAttr exaMetaName="summary"></gs:fullTextAttr>

      </xs:appinfo>

    </xs:annotation>

  </xs:element>

and

  <xs:element name="image">

    <xs:complexType gs:key="folderRef name">

      <xs:annotation>

        <xs:appinfo>

          <gs:_objectType mediaType="Image"/>

          <gs:fullTextObj exaSourceName="image">

              <gs:meta name="gn4Title" field="true" isUsedForSorting="true" />

              <gs:meta name="modifiedDate" field="true" isUsedForSorting="true"/>

              <gs:meta name="creationDate" field="true" isUsedForSorting="true"/>

              <gs:meta name="authors" field="true" />

              <gs:meta name="keywords" field="true" />

              <gs:meta name="location" field="true" />

              <gs:meta name="cats" field="true"/>

              <gs:meta name="keywords" field="true" />

              <gs:meta name="publicationDate" field="true" isUsedForSorting="true"/>

              <gs:meta name="publicationRef" field="true" isUsedForSorting="true"/>

              <gs:meta name="sourceRef" field="true" isUsedForSorting="true"/>

              <gs:meta name="folderRef" field="true" isUsedForSorting="true"/>

              <gs:meta name="folderKind" field="true" />

          </gs:fullTextObj>

    . . . .

The Exalead field name ('exaMetaName') is ignored - SQL full-text does not have separate names for full-text indexed columns. The 'isUsedForSorting' option is ignored as well.

The system creates full-text indexes only for string and XML attributes (searches for other kind of attributes is done using normal SQL) that have 'field="true"'. The content of full-text searchable string and XML attributes that have 'field="false"' - and so are not searchable independently - is just copied in the (full-text indexed) gn_FullText column.

'srv4 dbupdate' and 'srv4 dbschema -in XXXXX' compare the existing full-text indexes (if any) with the ones specified in the schema and update them if necessary - so if for example a new full-text indexed attribute is added in the schema re-importing the schema using 'srv4 dbschema -in XXXXX' automatically updates the full-text indexes accordingly.

•List all tables that have an associate full-text index - with some info about the index itself:

 select

   so.name 'table', 

   sftcat.name 'catalog', 

   sftstop.name 'stoplist', 

   dataspaces.name 'filegroup'

 from

   sysobjects so

   join sys.fulltext_indexes sfti on so.id=sfti.object_id

   join sys.fulltext_catalogs sftcat on sfti.fulltext_catalog_id=sftcat.fulltext_catalog_id 

   join sys.data_spaces dataspaces on dataspaces.data_space_id=sfti.data_space_id 

   left outer join sys.fulltext_stoplists sftstop on sftstop.stoplist_id=sfti.stoplist_id

 order by

   so.name

•List all table and columns that are full-text indexed:

 select

   so.name, sc.name

 from

   sysobjects so

   join sys.fulltext_index_columns sftc on so.id=sftc.object_id

   join syscolumns sc on so.id=sc.id and sc.colid=sftc.column_id

 order by

   so.name

•List all tables that are full-text indexed with the current status of the full-text indexing:

 select

   so.name 'table',

   case OBJECTPROPERTYEX(so.id,'TableFulltextPopulateStatus')

         when 0 then 'Idle'

         when 1 then 'Full population in progress' 

         when 2 then 'Incremental population in progress'

         when 3 then 'Propagation of changes in progress'

         when 4 then 'Background update in progress'

         when 5 then 'Throttled or paused' 

           end 'status',

   OBJECTPROPERTYEX(so.id,'TableFulltextPendingChanges') 'pending changes'

 from

   sysobjects so

   join sys.fulltext_indexes sfti on so.id=sfti.object_id

 order by

   so.name