SQL optimization on ORDER BY clause on varchar(max) column

Question

Our C# application is using a SQL database to match tables with eachother. We have one table that contains around 1 million rows. Most of the datatypes are varchar(900) and below. Though there are some columns that are imported as a varchar(max) type. The user is able to see the whole table of 1 million records via the C# application. To reduce the amount of memory that is used on the local system we use a pagination algorithm. E.g. we load two datatables of +- 15.000 rows each in the memory. When the user is scrolling past these pages, the furthest page will be updated with new data from the database table, this way you can have very large tables without running into memory problems.

We get the specific data from the database table with the use of row numbers. The query that is retrieving the data looks like this:

;WITH selectRows AS(SELECT *, row=ROW_NUMBER() OVER(ORDER BY myColumn) FROM myTable)
SELECT * FROM selectRows WHERE row BETWEEN 0 AND 15000;

On small tables this isn't much of a problem regarding the performance. However with large tables, when we sort on a column that has no index (for example the varchar(max) columns), it will be executing very slow. Sorting on a column with an index is executing blazing fast, as expected of course. Is it in any way possible to sort a large table on a varchar(max) column. If there is any solution for my problem, it should be able to run on either SQL server 2005, 2008, 2012 versions.

Answer 1

Use coarse filter for varchar max, example taken from here. This is actually a reduced version of your varchar max column. Since you are only using it for ordering, it should be enough for your purposes.

CREATE SCHEMA [20090501_max]
CREATE TABLE t_bigdata (
id INT NOT NULL PRIMARY KEY,
value NVARCHAR(MAX),
value_index AS CAST(value AS NVARCHAR(450))
)
GO
CREATE INDEX IX_bigdata_value ON [20090501_max].t_bigdata(value_index)