I have a full-text index setup on a filestream enabled column in a table in SQL Server 2008 database. I anticipate the filestream to end up holding terabytes of information over the next year.
I needed to be able to paginate the result set of the full-text search (ie - starting at row 50, return 10 results), so I used the common hack for t-sql (since it doesn't support LIMIT) to allow me to do this. The hack goes something like
select * from ( select top(@take) * from ( select top (@n) * from myTable where contains(FSData, @srchTerms) order by myID asc ) as T1 order by myID desc ) as T2 order by myID asc
My question: can I expect the full-text search to get slower and slower since 1) I anticipate it to eventually index terabytes of information and 2) I'm using the cludge above to easily paginate the result set
Should I be handling the pagination in the business logic?
Thanks
