Tenemos una gran base de datos en la que tenemos paginación lateral de base de datos. Esto es rápido, devolviendo una página de 50 filas de millones de registros en una pequeña fracción de segundo.

Los usuarios pueden definir su propio orden, básicamente eligiendo por qué columna ordenar. Las columnas son dinámicas: algunas tienen valores numéricos, algunas fechas y texto.

Si bien la mayoría se clasifica como se espera, el texto se ordena de una manera tonta. Bueno, digo tonto, tiene sentido para las computadoras, pero frustra a los usuarios.

Por ejemplo, ordenar por una identificación de registro de cadena da algo como:

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...y así.

Quiero que esto tenga en cuenta el número, así que:

rec1
rec2
rec3
rec4
rec10
rec14
rec20

No puedo controlar la entrada (de lo contrario, solo formatearía en los 000 principales) y no puedo confiar en un solo formato; algunos son cosas como "{código alfa} - {código de departamento} - {id de rec.}".

Conozco algunas formas de hacer esto en C #, pero no puedo extraer todos los registros para ordenarlos, ya que eso sería lento.

¿Alguien sabe una manera de aplicar rápidamente una ordenación natural en el servidor SQL?

Estamos usando:

ROW_NUMBER() over (order by {field name} asc)

Y luego estamos buscando por eso.

Podemos agregar desencadenantes, aunque no lo haríamos. Toda su entrada está parametrizada y similares, pero no puedo cambiar el formato: si ponen "rec2" y "rec10" esperan que se devuelvan así, y en orden natural.

Tenemos una entrada de usuario válida que sigue diferentes formatos para diferentes clientes.

Uno podría ir rec1, rec2, rec3, ... rec100, rec101

Mientras que otro podría ir: grp1rec1, grp1rec2, ... grp20rec300, grp20rec301

Cuando digo que no podemos controlar la entrada, quiero decir que no podemos obligar a los usuarios a cambiar estos estándares: tienen un valor como grp1rec1 y no puedo volver a formatearlo como grp01rec001, ya que eso estaría cambiando algo utilizado para búsquedas y vinculación a sistemas externos.

Estos formatos varían mucho, pero a menudo son mezclas de letras y números.

Ordenarlos en C # es fácil: solo divídalos en{ "grp", 20, "rec", 301 } y luego compare los valores de secuencia a su vez.

Sin embargo, puede haber millones de registros y la información está paginada, necesito que la clasificación se realice en el servidor SQL.

El servidor SQL ordena por valor, no por comparación: en C # puedo dividir los valores para comparar, pero en SQL necesito cierta lógica que (muy rápidamente) obtenga un valor único que se clasifique de manera consistente.

@moebius: su respuesta podría funcionar, pero se siente como un compromiso feo agregar una clave de clasificación para todos estos valores de texto.