MSSQL Cookbook

Full-Text Searches

• MSDN: Full-Text Search (SQL Server) Starting point for MSDN full-text search articles.
• MSDN: Full-Text Predicates and Functions Overview
  Basic differences between FREETEXT/CONTAINS/FREETEXTTABLE/CONTAINSTABLE.
• MSDN: Querying SQL Server Using Full-Text Search
• MSDN: How Search Query Results Are Ranked (Full-Text Search)

CONTAINS and CONTAINSTABLE vs FREETEXT and FREETEXTTABLE:

CONTAINS is a literal boolean match. If the search term contains multiple words, they must be surrounded by double quotes, or separated with "and", "or", "near", etc. If you search for "Mountain", then the literal word "Mountain" must be somewhere in the text. Which is similar to the way that the LIKE predicate works, eg WHERE text LIKE '%Mountain%'.

FREETEXT matches more on the meaning of the search term rather than the literal characters in the search term.

FREETEXT/CONTAINS vs. FREETEXTTABLE/CONTAINSTABLE: FREETEXTTABLE and CONTAINSTABLE return a RANK for the matches.

Common Table Expressions

• MSDN: WITH common_table_expression (T-SQL)
• MSDN: Using Common Table Expressions
  The comments on this page contain some useful information.

Before SQL Server 2005 I would create a temp table in order to organize a recordset to return a single page of results. CTE's are more efficient and easier to understand when editing the T-SQL.

Before:

DECLARE @tmpTable TABLE (rowNum IDENTITY(1,1), recordID INT)

INSERT INTO @tmpTable
SELECT recordID
FROM myTable
WHERE [mySearchConditions]
ORDER BY [mySortExpression]

After:

WITH sortedResults (recordID, title, description) AS
(
    SELECT recordID, title, description,
    ROW_NUMBER() OVER(ORDER BY [mySortExpression]) AS rowNumber
    FROM myTable
    WHERE [mySearchConditions]
)

SELECT recordID, title, description
FROM sortedResults
WHERE rowNumber BETWEEN @firstRow AND @lastRow

The ROW_NUMBER() function returns just that, the sequential value of the row in the resultset.

One great feature of CTE's is that you can use the limited recordsets in subsequent statements, you can refer to the subsets multiple times within the statement, you can use subsets to create other subsets and/or join to other tables.

WITH FirstSet (recordID) AS
(
    SELECT recordID FROM ThisTableHere WHERE SomeCondition = 'Some Value'
)
,
SecondSet (recordID) AS
(
    SELECT tot.recordID
    FROM ThisOtherTable tot
    INNER JOIN FirstSet fs ON tot.recordID = fs.recordID
    WHERE SomeOtherCondition = 'Some Other Value'
)

SELECT t1.recordID, t1.name
FROM SomeTable t1
INNER JOIN SecondSet s2 ON t1.recordID = s2.recordID