SQL Server 2008 ContainsTable, CTE, and Paging
- by David Murdoch
I'd like to perform efficient paging using containstable.
The following query selects the top 10 ranked results from my database using containstable when searching for a name (first or last) that begins with "Joh".
DECLARE @Limit int;
SET @Limit = 10;
SELECT TOP @Limit
c.ChildID, c.PersonID, c.DOB, c.Gender
FROM
[Person].[vFullName] AS v
INNER JOIN CONTAINSTABLE(
[Person].[vFullName],
(FullName),
IS ABOUT ( "Joh*" WEIGHT (.4), "Joh" WEIGHT (.6))
) AS k3
ON
v.PersonID = k3.[KEY]
JOIN
[Child].[Details] c
ON
c.PersonID = v.PersonID
JOIN
[Person].[Details] p
ON
p.PersonID = c.PersonID
ORDER BY
k3.RANK DESC,
FullName ASC,
p.Active DESC,
c.ChildID ASC
I'd like to combine it with the following CTE which returns the 10th-20th results ordered by ChildID (the primary key):
DECLARE @Start int;
DECLARE @Limit int;
SET @Start = 10;
SET @Limit = 10;
WITH ChildEntities AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ChildID) AS Row, ChildID
FROM Child.Details
)
SELECT
c.ChildID, c.PersonID, c.DOB, c.Gender
FROM
ChildEntities cte
INNER JOIN
Child.Details c
ON
cte.ChildID = c.ChildID
WHERE
cte.Row
BETWEEN
@Start+1 AND @Start+@Limit
ORDER BY
cte.Row ASC
