7 questions found
What is full text search in SQL Server, and how is it different from a regular LIKE search?
Beginner
Full text search is a specialized indexing feature designed for efficiently searching large amounts of text, letting you find whole words or phrases quickly even in very large tables, while a regular LIKE search with wildcards has to scan through the text character by character, becoming very slow on large amounts of data.
SELECT * FROM Articles
WHERE CONTAINS(Content, 'database');
Real-world example
A news website uses full text search to let visitors quickly search through thousands of articles for specific keywords, delivering fast results that a slow LIKE based search across the same amount of text could never achieve.
Common follow-ups: Do you need to set up anything special before using full text search?;What is the performance difference between full text search and LIKE on a large table?
Indexes;Data Types & Schema Design
How do you create a full text index on a table so you can use full text search functions?
Beginner
You first create a full text catalog to organize your full text indexes, then create a full text index on the specific table and columns you want to search, which requires the table to already have a unique, single column index defined, typically its primary key.
CREATE FULLTEXT CATALOG ArticleCatalog;
CREATE FULLTEXT INDEX ON Articles(Content)
KEY INDEX PK_Articles
ON ArticleCatalog;
Real-world example
A documentation website sets up a full text index on its articles table, enabling fast keyword searches across the entire body of every article stored in the system.
Common follow-ups: What requirement must a table meet before a full text index can be created on it?;How long does it typically take to build a full text index on a large table?
Indexes;Data Types & Schema Design
What is the difference between the CONTAINS and FREETEXT predicates when performing a full text search?
Intermediate
CONTAINS gives you precise control, letting you search for exact words, phrases, or use logical operators and proximity searches, while FREETEXT performs a more flexible, natural language style search that considers the meaning and different forms of the words you search for, similar to how a general search engine might interpret a query.
-- Precise search using CONTAINS
SELECT * FROM Articles WHERE CONTAINS(Content, '"database performance"');
-- Natural language search using FREETEXT
SELECT * FROM Articles WHERE FREETEXT(Content, 'improving database performance');
Real-world example
A search feature offers both an advanced search option using CONTAINS for users who want precise phrase matching, and a simple search box using FREETEXT for casual users typing natural sentences.
Common follow-ups: Which predicate is better for a typical search box aimed at everyday users?;Can CONTAINS search for words that start with a specific prefix?
Query Optimization & Plans;Data Types & Schema Design
How do you perform a proximity search using full text search, finding rows where two words appear near each other?
Intermediate
You use the CONTAINS predicate with the NEAR keyword, specifying the two words you want to find close together, optionally including a maximum distance between them, which is useful for finding relevant results where related terms appear together rather than scattered far apart throughout a large document.
SELECT * FROM Articles
WHERE CONTAINS(Content, 'NEAR(database, performance, 10)');
Real-world example
A technical documentation search finds articles where the words database and performance appear within ten words of each other, surfacing more relevant results than a search that simply looks for both words anywhere in the document.
Common follow-ups: How do you control the exact maximum distance allowed in a NEAR search?;Does word order matter when using the NEAR keyword?
Query Optimization & Plans;Indexes
How would you configure full text search to handle different languages correctly, including proper word breaking and stemming?
Advanced
You specify a language for your full text index using the LANGUAGE option, which tells SQL Server which word breaker and stemmer to use for that specific language, ensuring words are correctly split apart and different grammatical forms of the same word, like run and running, are properly recognized as related during a search.
CREATE FULLTEXT INDEX ON Articles(Content)
KEY INDEX PK_Articles
ON ArticleCatalog
WITH (Content LANGUAGE 1033); -- 1033 represents English
Real-world example
A multilingual publishing platform configures separate full text indexes with the correct language settings for its English and French content, ensuring searches correctly handle word variations specific to each language.
Common follow-ups: What happens if the wrong language is specified for a full text index?;How many languages does SQL Server support for full text search?
Internationalization;Data Types & Schema Design
How do you rank and sort full text search results by their relevance to the search query?
Advanced
You use the CONTAINSTABLE or FREETEXTTABLE table valued functions instead of the simple CONTAINS or FREETEXT predicates, which return a table including a RANK column representing how relevant each matching row is, letting you join this back to your original table and sort the results by relevance, showing the most relevant matches first.
SELECT a.*, ft.RANK
FROM Articles a
INNER JOIN CONTAINSTABLE(Articles, Content, 'database performance') ft
ON a.ArticleId = ft.[KEY]
ORDER BY ft.RANK DESC;
Real-world example
A search results page shows the most relevant articles first by joining the articles table with CONTAINSTABLE's ranking information, giving users a genuinely useful, relevance sorted search experience.
Common follow-ups: What factors influence how the RANK value is calculated?;What is the difference between CONTAINSTABLE and FREETEXTTABLE?
Query Optimization & Plans;Indexes
How often does a full text index need to be updated to reflect changes made to the underlying table's data?
Intermediate
By default, full text indexes update automatically shortly after the underlying data changes through a background population process, but for very high volume tables you can also configure manual population, giving you control over exactly when the index rebuild happens, such as during a scheduled maintenance window.
ALTER FULLTEXT INDEX ON Articles
SET CHANGE_TRACKING MANUAL;
ALTER FULLTEXT INDEX ON Articles
START UPDATE POPULATION;
Real-world example
A high traffic content platform switches its full text index to manual change tracking, scheduling index updates during off peak hours to avoid any performance impact on its busiest traffic periods.
Common follow-ups: What is the tradeoff between automatic and manual full text index updates?;How do you check how up to date a full text index currently is?
SQL Server Agent & Job Scheduling;Query Optimization & Plans