Editing MSSQL Cookbook

==Full-Text Searches==

*MSDN: [http://msdn.microsoft.com/en-us/library/ms142571.aspx Full-Text Search (SQL Server)] Starting point for MSDN full-text search articles.
*MSDN: [http://msdn.microsoft.com/en-us/library/ms142583.aspx Full-Text Predicates and Functions Overview]<br />Basic differences between <code>FREETEXT/CONTAINS/FREETEXTTABLE/CONTAINSTABLE</code>.
*MSDN: [http://msdn.microsoft.com/en-us/library/ms142559.aspx Querying SQL Server Using Full-Text Search]
*MSDN: [http://msdn.microsoft.com/en-us/library/ms142524.aspx How Search Query Results Are Ranked (Full-Text Search)]


<code>CONTAINS</code> and <code>CONTAINSTABLE</code> vs <code>FREETEXT</code> and <code>FREETEXTTABLE</code>:

<code>CONTAINS</code> 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 <code>LIKE</code> predicate works, eg <code>WHERE text LIKE '%Mountain%'</code>.

<code>FREETEXT</code> matches more on the meaning of the search term rather than the literal characters in the search term.

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

==Common Table Expressions==

*MSDN: [http://msdn.microsoft.com/en-us/library/ms175972.aspx WITH common_table_expression (T-SQL)]
*MSDN: [http://msdn.microsoft.com/en-us/library/ms190766.aspx Using Common Table Expressions]<br />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:

<syntaxhighlight lang="sql">
DECLARE @tmpTable TABLE (rowNum IDENTITY(1,1), recordID INT)

INSERT INTO @tmpTable
SELECT recordID
FROM myTable WITH(NOLOCK)
WHERE [mySearchConditions]
ORDER BY [mySortExpression]
</syntaxhighlight>


After:

<syntaxhighlight lang="sql">
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
</syntaxhighlight>


The [http://msdn.microsoft.com/en-us/library/ms186734.aspx <code>ROW_NUMBER()</code> 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.

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

==Dynamic Pagination==

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

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

== Retrieving total row count in paginated rowsets ==
Combine `ROW_NUMBER()` with `COUNT(1) OVER (PARTITION BY 1)`
<syntaxhighlight lang="sql">
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
</syntaxhighlight>

==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.

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

==Dynamic sorting in a stored procedure==

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

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

==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 <code>PARTITION BY</code> (SQL Server 2005 or greater).

<syntaxhighlight lang="sql">
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 
</syntaxhighlight>

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

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

'''Note''' that while <code>PARTITION BY</code> makes for very clear code, it is not super-fast for execution.
[[Category:Web Development]]
[[Category:MSSQL]]