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 WITH(NOLOCK)
WHERE [mySearchConditions]
ORDER BY [mySortExpression]

After:

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

SELECT recordID, title, description
FROM sortedResultsCTE
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 FirstSetCTE (recordID) AS
(
    SELECT recordID FROM ThisTableHere WITH(NOLOCK) WHERE SomeCondition = 'Some Value'
)
,
SecondSetCTE (recordID) AS
(
    SELECT tot.recordID
    FROM ThisOtherTable tot WITH(NOLOCK)
    INNER JOIN FirstSet fs ON tot.recordID = fs.recordID
    WHERE SomeOtherCondition = 'Some Other Value'
)

SELECT t1.recordID, t1.name
FROM SomeTable t1 WITH(NOLOCK)
INNER JOIN SecondSetCTE s2 ON t1.recordID = s2.recordID

Dynamic Pagination

Goal: Pass @page and @pageLen to a stored procedure and only return records within that range (starting at @page and limited to @pageLen number of records).

CREATE PROCEDURE [dbo].[PagingSP]
	@page int = null,
	@pageLen int = null
AS

DECLARE @firstRow INT, @lastRow INT

/* page controls */
SET @firstRow = ((@page-1)*@pageLen)+1
SET @lastRow = @page*@pageLen

WITH PagingCTE (
	id
	, name
	, col2
	, col3
	, rowNumber
) AS
(
	SELECT t.id
		, t.name
		, t.col2
		, t.col3
		, ROW_NUMBER() OVER (ORDER BY t.someColumn ASC)
	FROM [dbo].[MyTable] t WITH(NOLOCK) 
	WHERE (t.someColumn LIKE [CONDITIONS]) 
)
SELECT 
	id
	, name
FROM PagingCTE
WHERE ((@page IS NULL OR @pageLen IS NULL) OR (rowNumber BETWEEN @firstRow AND @lastRow))
ORDER BY rowNumber

Passing an array of integer values to a stored procedure

Goal: Convert a string containing comma-delineated values to an array of integer values. The string is passed to the stored procedure as an argument. The sproc converts the string to an array of integers which can then be used within the logic of the sproc.

CREATE PROCEDURE [dbo].[MyTableSelect]
	@idCSV varchar(500)=''
AS
SET ARITHABORT ON

DECLARE @xmlStr XML
SET @xmlStr = '<MyID>' + REPLACE(@idCSV, ',', '</MyID><MyID>') + '</MyID>'

SELECT
	t.id
	, t.name
	, t.col2
	, t.col3
	/* etc... */
FROM [dbo].MyTable t WITH(NOLOCK) 
WHERE t.myTargetID IN (
	SELECT x.MyID.value('.','INT') AS a
	FROM @xmlStr.nodes('//MyID') x(MyID)
)

SET ARITHABORT OFF

Dynamic sorting in a stored procedure

Goal: Pass an argument to a stored procedure that will control how the recordset is sorted.

SELECT 
	t.row1
	, t.row2
	, t.row3
	, ROW_NUMBER() OVER 
	(
		/* sort expressions */
		ORDER BY 
		CASE WHEN @vcSortType = 'row1' AND (@vcSortOrder IS NULL or @vcSortOrder = '' OR @vcSortOrder = 'ASC') THEN t.row1 END ASC,
		CASE WHEN @vcSortType = 'row1' AND @vcSortOrder = 'DESC' THEN t.row1 END DESC,
		CASE WHEN @vcSortType = 'row2' AND (@vcSortOrder IS NULL or @vcSortOrder = '' OR @vcSortOrder = 'ASC') THEN t.row2 END ASC,
		CASE WHEN @vcSortType = 'row2' AND @vcSortOrder = 'DESC' THEN t.row2 END DESC,
		CASE WHEN @vcSortType = 'row3' AND (@vcSortOrder IS NULL or @vcSortOrder = '' OR @vcSortOrder = 'ASC') THEN t.row3 END ASC,
		CASE WHEN @vcSortType = 'row3' AND @vcSortOrder = 'DESC' THEN t.row3 END DESC,

		/* etc... */

		t.row1 ASC, t.row2 ASC /* <<< this is the default sort order */
	) rowNumber
FROM [dbo].[MyTable] t WITH(NOLOCK)
ORDER BY rowNumber