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

our c# application using sql database match tables eachother. have 1 table contains around 1 million rows. of datatypes varchar(900) , below. though there columns imported varchar(max) type. user able see whole table of 1 million records via c# application. reduce amount of memory used on local system use pagination algorithm. e.g. load 2 datatables of +- 15.000 rows each in memory. when user scrolling past these pages, furthest page updated new data database table, way can have large tables without running memory problems.

we specific data database table use of row numbers. query retrieving data looks this:

;with selectrows as(select *, row=row_number() over(order mycolumn) mytable) select * selectrows row between 0 , 15000; 

on small tables isn't of problem regarding performance. large tables, when sort on column has no index (for example varchar(max) columns), executing slow. sorting on column index executing blazing fast, expected of course. in way possible sort large table on varchar(max) column. if there solution problem, should able run on either sql server 2005, 2008, 2012 versions.

use coarse filter varchar max, example taken here. reduced version of varchar max column. since using ordering, should enough purposes.

create schema [20090501_max] create table t_bigdata ( id int not null primary key, value nvarchar(max), value_index cast(value nvarchar(450)) ) go create index ix_bigdata_value on [20090501_max].t_bigdata(value_index)