SQL Server 2008 ContainsTable, CTE, and Paging

Posted by David Murdoch on Stack Overflow See other posts from Stack Overflow or by David Murdoch
Published on 2010-03-16T14:14:56Z Indexed on 2010/03/16 14:16 UTC
Read the original article Hit count: 589

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

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about common-table-expression