Return Page of Full-Text Search Results without Resorting?

Question

I have a query similar to the following to return articles that match a full-text search. In production, the text [FULL TEXT SEARCH CRITERIA] is replaced with the actual full-text search expression.

The query is written to return only one page of results.

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY ArtViews DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  WHERE [FULL TEXT SEARCH CRITERIA] AND Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID 

This works fine except that full-text search returns the results in order of relevance, but ROW_NUMBER() OVER (ORDER BY ArtViews DESC) resorts the results. Is there any way to write the same query without resorting the results?

Answer 1

It looks like the correct answer to this is to use CONTAINSTABLE, which works similar to CONTAINS but returns a table instead of a Boolean WHERE condition.

So I end up with something like this instead.

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  INNER JOIN CONTAINSTABLE(Article, *, 'FORMSOF(INFLECTIONAL, abc)') AS FTS ON Article.ArtID = FTS.[KEY]
  WHERE Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID