Category Archives: SQL Server

Enabling Danish for SQL Server FullText

SQL Server FullText enables you to search large amount of strings fast, and it is easy to use. It hasn’t changed much since SQL Server 2000.
A simple getting started tutorial can be found on Code Project.

SQL Server FullText is easy to use in applications requiring string searching.

The Danish, Polish and Turkish wordbreaker and stemmer implementations for SQL Server FullText is not developed by Microsoft and therefore not enabled by default. The libraries are however part of the installation process and are therefore present on disk.

To make use of the Danish language capabilities in SQL Server 2008, register the libraries in registry and reload the FullText languages:

  1. Download & run the DanishFulltext.reg file on the server. It will register wordbreaker, stemmer and default location of the thesaurus xml file.
  2. Run the exec sp_fulltext_service ‘update_languages’ in a Management Studio.

Now verify that Danish is enabled with this query: SELECT name FROM sys.fulltext_languages

Note: The DanishFullText.reg assumes that SQL Server is a default instance (not a named instance). If not, modify the file by changing the MSSQL10.MSSQLSERVER to the instance name.

It is the same case with Polish and Turkish – they are not registered by default. See more in the MSDN article How to: Load Licensed Third-Party Word Breakers.

List of out of the box SQL Server 2008 FullText supported languages: Arabic, Bengali (India), Brazilian, British English, Bulgarian, Catalan, Chinese (Hong Kong SAR, PRC), Chinese (Macau SAR), Chinese (Singapore), Croatian, Danish, Dutch, English, French, German, Gujarati, Hebrew, Hindi, Icelandic, Indonesian ,Italian, Japanese, Kannada, Korean, Latvian, Lithuanian, Malay – Malaysia, Malayalam, Marathi, Neutral, Norwegian (Bokmål), Polish, Portuguese, Punjabi, Romanian, Russian, Serbian (Cyrillic), Serbian (Latin), Simplified Chinese, Slovak, Slovenian, Spanish, Swedish, Tamil, Telugu, Thai, Traditional Chinese, Turkish, Ukrainian, Urdu, Vietnamese.

Meeting the SQL Azure Development Team

Last week I was at Microsoft HQ in Redmond, WA, USA. I was invited by the SQL Azure Development Team to look at some of the new unreleased features and comment on features in their roadmap.

Unfortunately most of the content was confidential, meaning that I was under NDA, so I may not disclose any details. Sorry :-/

During the week with the SQL Azure Development Team I was fortunate to be engaged in technical detailed discussion about some of the upcoming feature releases – mainly discussing the SQL Server features not currently available in SQL Azure. It was interesting and enlightening at the same time to discuss their technical challenges and why they have build SQL Azure the way they have.

All in all, my conclusion after this event is that Microsoft takes SQL Azure seriously and it will become a major player in the RDBMS world. It will not just be a SQL Server in the cloud, but a separate product with different market segments and different features. I am looking forward to a bright future with SQL Azure 🙂

Finding Missing Indexes with SQL Server DMVs

Finding Missing Indexes with DMVsSome time ago I wrote written about easy index wins for SQL Server 2005.

SQL server maintains statistics about indexes you should consider creating. This time I’ll show you a DMV (Dynamic Management View) that lists index candidates. This method works for SQL Server 2005 SP2 and later versions.

The query is based on three DMVs and returns index candidates where the calculated improvement is more than 10%:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure_pct,
  QUOTENAME(db_name(mid.database_id)) AS [database],
  QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id)) AS [schema],
  QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id)) AS [table],
  'CREATE INDEX [missing_index_' + CONVERT(varchar(64), NEWID()) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns, '')
  + CASE
      WHEN mid.equality_columns IS NOT NULL
	    AND mid.inequality_columns IS NOT NULL THEN ','
      ELSE ''
    END
  + ISNULL(mid.inequality_columns, '')
  + ')'
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
	  AS create_index_statement,
  migs.*,
  mid.database_id,
  mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
  INNER JOIN sys.dm_db_missing_index_group_stats migs
	ON migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details mid
	ON mig.index_handle = mid.index_handle
WHERE
	migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
		(migs.user_seeks + migs.user_scans) > 10
ORDER BY
	migs.avg_total_user_cost * migs.avg_user_impact *
		(migs.user_seeks + migs.user_scans) DESC

It is important to note, that these are index candidates are only candidates and the improvements are based on estimates. The estimated improvement does not take extra disk space requirements and the maintenance of the indexes during updates, inserts and deletes. Furthermore it does not make recommendation about usage of clustered or non-clustered indexes.

This blog post is inspired by Bart Duncan’s Are you using SQL’s missing index DMVs?

Miracle Open World 2010 Lucene Presentation

The conference is over and it was a great success. I meet a lot of new people and had lots of technical discussions about .Net, graph databases, freetext search, SQL Server, Oracle Service Bus, debugging with WinDbg and extensions.

The slides and demo code for my Lucene session is available here:

My session “Making freetext search with Lucene.Net work for you” abstract:

Lucene is an open source full-featured text search engine library, making searching in large amounts of text lightning fast. Lucene are in use by many large sites like Wikipedia, LinkedIn, MySpace etc.

It is easy to get started with Lucene, but there are many pitfalls… In this session you will learn about the do’s and don’t’s for indexing and searching, tools, scaling, new features in version 2.9 and some of the more advanced features.

This presentation will use the Microsoft .Net implementation of Lucene named Lucene.Net, but the content of this presentation applies for ported versions of Lucene.