CODEWITHSUNDEEP
sqlsql-serversearchindexing
user1715925
user1715925

Reputation: 607

how to perform efficient search on non-index field of a very large table in sql server

I have a very large table with only 6 fields on it. Number of rows is roughly 20 million. I have a clustered index including one integer field. However, there is an nvarchar(max) field (not necessarily unique, but never null) that I will frequently need to search on and the problem is I can't index on it since SQL has a maximum limit for size of index. What are my options for solving this? I'm not too familiar with SQL so please avoid technical terms.

Upvotes: 2

Views: 1870

Answers (2)

Stoleg
Stoleg

Reputation: 9300

Search without an index cannot be efficient.

Full text search can be done on char, varchar, nvarcahr columns. However it will always work better if you create Full-Text Index.

The reason why it works with out index on some columns - is to allow extended functionality of full-text search: semantics, word distance, stop words etc.

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Sounds like you'll need to use Full-Text Search:

Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables.

...

These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max)

Not only will you need a full-text index in order to be able to add the index the large text column... but if you will be searching this large block of text for partial matches, such as searching for a specific word or a phrase in the middle of a large document, a normal index would be useless, since a leading wildcard (i.e. WHERE TextField LIKE '%Specific word or phrase%') in your search would prevent the use of a non-full-text index.

Upvotes: 2

Related Questions