I'm trying to figure out a way to simplify implementing paged searching in sql server.
My restrictions are:
- has to be in a proc.
- can't use dynamic sql.
Here's my sample query:
DECLARE @TenantId int DECLARE @MemberStatusId int DECLARE @SearchTerm varchar(50) DECLARE @ColumnName varchar(30) DECLARE @PageNum int DECLARE @RecCount int ; WITH RowPages AS ( SELECT TOP( @PageNum * @RecCount ) RowNumber = ROW_NUMBER() OVER( ORDER BY CASE WHEN @ColumnName = 'Name' THEN P.LastName WHEN @ColumnName = 'PhoneNumber' THEN M.PhoneNumber WHEN @ColumnName = '' THEN P.LastName END ASC, CASE WHEN @ColumnName = 'DateAdded' THEN P.DateAdded END DESC ), TotalCount = COUNT(M.MemberId) OVER ( PARTITION BY NULL), M.MemberId, M.ExternalId, M.PhoneNumber, P.FirstName, P.LastName, P.EmailAddress FROM membership.Members M (nolock) INNER JOIN core.Persons P (nolock) on (P.PersonId = M.MemberId) WHERE (M.TenantId = @TenantId) AND ((@MemberStatusId = 0) or (M.MemberStatusId = @MemberStatusId)) AND ( (@SearchTerm = '""') OR (CONTAINS( (P.FirstName, P.LastName, P.EmailAddress), @SearchTerm)) ) ) SELECT TotalCount, MemberId, ExternalId, PhoneNumber, FirstName, LastName, EmailAddress FROM RowPages WHERE RowNumber > ((@PageNum - 1) * @RecCount) ORDER BY RowNumber
What this does is perform a paged search of a couple tables. You can pass in the page number and # records per page as well as the column to sort by and it has a full text search component. This is one of the simpler queries of this style we have.
What I'm trying to figure out is how to optimize this. The biggest costs in the execution plan are in regards to the full text search. There is a table value function [FullTextMatch] which has a cost of 100%.