Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In SQL Server 2008 R2, what is the most efficient column type to store data enabling full text search?

I can currently see two options, nvarchar and varbinary.

Which one performs better and why?

EDIT

If, for example, at the moment all I need is text, but in the future it might change, will there be a performance hit if i use varbinary?

share|improve this question

migrated from stackoverflow.com Jan 31 '12 at 15:04

This question came from our site for professional and enthusiast programmers.

varbinary is for binary data.

nvarchar is for character data.

Regardless of what the full text index is capable of indexing, use a data type suited to the domain.

Also, you will be able to see the contents of a nvarchar column in directly SSMS.

Performance issues are more likely to do with the frequency of maintenence of indexes, and hardware such as where your data is stored (as in a separate Filegroup), and memory usage, and indexing type (full population, incremental, manual, and auto change tracking population).

Note that SQL Server 2008 full-text search is more aggressive in memory usage and may use more I/O; 64‑bit architectures are better than 32‑bit in doing crawl tasks in general.

Sometimes crawl can be too aggressive in committing memory and this could cause sqlserver/system to have fewer resources than needed. It is highly recommended that you do the following:

  • Limit the memory used by the crawl (full population) by setting max server memory to an appropriate value to prevent the crawl task from taking all available memory.

  • Increase the page file size if applicable.

Indexing performance is generally superior to SQL Server 2005. However, in certain situations there may be some performance degradation as follows:

  • Crawl on large documents (~>8KB). In non 64‑bit platforms, this might be slower in some scenarios.

  • varchar type data. This is because SQL Server 2005 does a better job with the varchar type than with a text type, whereas in the new full-text architecture there is no difference in theory between varchar data and text data.

  • Noninteger keys, especially GUID columns used as a full-text key

  • Too many unique keywords. It has been shown in random document crawl modes that performance can be slower than SQL Server 2005 (when uniquekeycount > 80 million).

Ref: SQL Server 2008 Full-Text Search: Internals and Enhancements

share|improve this answer
    
if, for example, at the moment the domain is only text, but in the future it might change, will there be a performance hit if i use varbinary? – Simon Jan 31 '12 at 4:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.