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:

DECLARE @firstRow int, @lastRow int

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

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 @lastRow = @page*@pageLen
SET @firstRow = @lastRow - @pageLen + 1

WITH PagingCTE AS
(
	SELECT t.id
		, t.name
		, t.col2
		, t.col3
		, ROW_NUMBER() OVER (ORDER BY t.someColumn ASC) as rowNumber
	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

Retrieving total row count in paginated rowsets

Combine ROW_NUMBER() with COUNT(1) OVER (PARTITION BY 1)

WITH PagingCTE AS
(
	SELECT t.id
		, t.name
		, ROW_NUMBER() OVER (ORDER BY t.someColumn ASC) as rowNumber
		, COUNT(1) OVER (PARTITION BY 1) as totalRows 
	FROM [dbo].[MyTable] t WITH(NOLOCK) 
	WHERE (t.someColumn LIKE [CONDITIONS]) 
)
SELECT 
	id
	, name
	, totalRows
FROM PagingCTE
WHERE 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 /* <<< note that if "value" appears capitalized here it's because of the syntaxhighlighter. It should be lowercase. */
	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 (NULLIF(@vcSortOrder,'') IS NULL 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 (NULLIF(@vcSortOrder,'') IS NULL 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 (NULLIF(@vcSortOrder, '') IS NULL 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

Selecting the earliest records grouped by some other criteria

Goal: You want to get the records with the earliest date for each group.

Solution: Use PARTITION BY (SQL Server 2005 or greater).

WITH groupedCTE AS
(
	SELECT 
		id
		, groupName
		, createDate
		/* defining the group and creating a way to target by date value */
 		, ROW_NUMBER() OVER (PARTITION BY groupName ORDER BY createDate) AS row 
	FROM myTable
)
SELECT 
	id
	, groupName
	, createDate
FROM groupedCTE
/* filter down to the earliest record */
WHERE row = 1 
/* ORDER BY clause here isn't contributing to the solution; just demonstrates sorting the results */
ORDER BY groupName

Solution: Another approach using PARTITION BY The first solution seems more clear, and maybe it's also more efficient?

SELECT
	id
	, groupName
	, createDate
FROM 
(
	SELECT
		id
		, groupName
		, createDate
		MAX(createDate) OVER (PARTITION BY groupName) MaxCreateDate
	FROM myTable
) a1
/* filter down to the earliest record */
WHERE groupName = groupName
AND dtView = MaxCreateDate
/* ORDER BY clause here isn't contributing to the solution; just demonstrates sorting the results */
ORDER BY groupName

Note that while PARTITION BY makes for very clear code, it is not super-fast for execution.