Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Thursday, July 24, 2008
More on SQL Server 2005 Full Text Index Service
In my previous post about How to work out which are valid full text languages on a SQL Server 2005 instance I referred to sys.syslanguages and sys.fulltext_languages in my queries, but didn't really say much more about them, so here goes
In the definition on MSDN it states
And one thing on the choice of U.S. English vs UK English. The SQL Server Full Text Search: Language Features says
As an English person, living in England and speaking English I find this a somewhat grating use of the phrase UK English. Bah!
In the definition on MSDN it states
The list in this table, doesn't match those in sys.syslanguages. These are purely the full-text-indexable languages. As I mentioned in my previous post 6 languages can be added by following these instructions. The line
SELECT *
FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
which indicates that the locale used for querying should be 1045, which equates to Polish. I have some sample SQL to post in the next few days which demonstrates the difference between indexing and querying language choices.
I've been doing quite a bit of work with trying to understand how the SQL Server 2005 full text index works, and how the language choice impacts it. My knowledge of full text indexing as a whole to this stage hasn't been great, so I've done quite a lot of background reading. Amongst the best resources I've found are: both by Hillary Cotter which provide a really simple, but yet pretty comprehensive introduction to the various features of indexing and querying using the Full Text Index service.
sys.syslanguages
In the definition on MSDN it states
"Contains one row for each language present in the instance of SQL Server 2005. Although U.S. English is not in syslanguages, it is always available to SQL Server."
And one thing on the choice of U.S. English vs UK English. The SQL Server Full Text Search: Language Features says
"In actual fact UK English does not refer to the Queen's English or the English used in the United Kingdom, but International English; the English that is used in all other English speaking countries other than US English."
As an English person, living in England and speaking English I find this a somewhat grating use of the phrase UK English. Bah!
sys.fulltext_languages
In the definition on MSDN it states
"This catalog view contains one row per language available for full-text indexing/querying operations. Each row provides an unambiguous representation of the available full-text linguistic resources that are registered with Microsoft SQL Server. The name or lcid can be specified in the full-text queries and full-text index DDL."
The list in this table, doesn't match those in sys.syslanguages. These are purely the full-text-indexable languages. As I mentioned in my previous post 6 languages can be added by following these instructions. The line
"The name or lcid can be specified in the full-text queries and full-text index DDL."refers to the ability to issue the following SQL:
SELECT *
FROM LanguageData
WHERE CONTAINS(*, 'the', language 1045 )
which indicates that the locale used for querying should be 1045, which equates to Polish. I have some sample SQL to post in the next few days which demonstrates the difference between indexing and querying language choices.
In General
I've been doing quite a bit of work with trying to understand how the SQL Server 2005 full text index works, and how the language choice impacts it. My knowledge of full text indexing as a whole to this stage hasn't been great, so I've done quite a lot of background reading. Amongst the best resources I've found are: both by Hillary Cotter which provide a really simple, but yet pretty comprehensive introduction to the various features of indexing and querying using the Full Text Index service.
Labels: full text index, localisation, localization, SQLServer2005
// posted by Jane @ 8:02 PM 
Comments:
