¿Es ORDEN POR y ROW_NUMBER () determinista?

He usado SQL en motores de bases de datos pares de vez en cuando durante varios años, pero tengo poco conocimiento teórico, por lo que mi pregunta podría ser muy "noobish" para algunos de ustedes. Pero se volvió importante para mí ahora, así que tengo que preguntar.

Imagine URL de tabla con un status columna no exclusivo. Y para la pregunta supongamos que tenemos una gran cantidad de filas y el estado tiene el mismo valor en cada logging.

E imagina que ejecutamos muchas veces la consulta:

 SELECT * FROM Urls ORDER BY status 
  1. ¿Recibimos cada vez el mismo order de fila o no? Si hacemos lo que sucederá si agregamos algunas filas nuevas? ¿Cambia el order o se añaden nuevos loggings al final de los resultados? Y si no recibimos el mismo pedido, ¿en qué condiciones depende esta order?

  2. ¿ ROW_NUMBER() OVER (ORDER BY status) devolverá el mismo order que la consulta anterior o se basa en un mecanismo de orderamiento diferente?

Es muy sencillo. Si desea un pedido en el que pueda confiar, debe include suficientes columnas en su cláusula ORDER BY manera que la combinación de todas esas columnas sea única para cada fila. Nada más está garantizado.

Para una sola tabla, generalmente puede get lo que desea al listr las columnas que son "interesantes" para orderarlas y luego include las columnas de la key principal después. Dado que el PK, por sí mismo, garantiza la exclusividad, la combinación completa también garantiza una definición única del pedido, por ejemplo, si la tabla Urls tiene una key principal de {Site, Page, Ordinal} , lo siguiente le daría un resultado confiable:

 SELECT * FROM Urls ORDER BY status, Site, Page, Ordinal 

ORDER BY no es estable en SQL Server (ni en ninguna otra database, hasta donde yo sé). Un tipo estable es aquel que devuelve loggings en el mismo order en que se encuentran en la tabla.

La razón de alto nivel es bastante simple. Las tablas son sets. No tienen order. Entonces un tipo "estable" simplemente no tiene sentido.

Las razones de menor nivel son probablemente más importantes. La database podría estar implementando un algorithm de orderación en paralelo. Tales algorithms no son, por defecto, estables.

Si desea una sorting estable, incluya una columna key en la sorting.

Esto se alude en la documentation :

Para lograr resultados estables entre requestes de consulta que utilizan DESPLAZAMIENTO y FETCH, se deben cumplir las siguientes condiciones:

Los datos subyacentes que utiliza la consulta no deben cambiar. Es decir, las filas tocadas por la consulta no se actualizan o todas las requestes de páginas de la consulta se ejecutan en una sola transacción utilizando una instantánea o un aislamiento de transacción serializable. Para get más información sobre estos niveles de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

La cláusula ORDER BY contiene una columna o combinación de columnas que se garantiza que son únicas.

Realmente me encantan este tipo de preguntas, ya que puedes hacer análisis de performance.

Primero, creemos una database de muestra [testing] con una tabla [urls] con un millón de loggings aleatorios.

Vea el código a continuación.

 -- Switch databases USE [master]; go -- Create simple database CREATE DATABASE [test]; go -- Switch databases USE [test]; go -- Create simple table CREATE TABLE [urls] ( my_id INT IDENTITY(1, 1) PRIMARY KEY , my_link VARCHAR(255) , my_status VARCHAR(15) ); go -- http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table -- Load table with 1M rows of data ; WITH PASS0 AS ( SELECT 1 AS C UNION ALL SELECT 1 ), --2 rows PASS1 AS ( SELECT 1 AS C FROM PASS0 AS A , PASS0 AS B ), --4 rows PASS2 AS ( SELECT 1 AS C FROM PASS1 AS A , PASS1 AS B ), --16 rows PASS3 AS ( SELECT 1 AS C FROM PASS2 AS A , PASS2 AS B ), --256 rows PASS4 AS ( SELECT 1 AS C FROM PASS3 AS A , PASS3 AS B ), --65536 rows PASS5 AS ( SELECT 1 AS C FROM PASS4 AS A , PASS4 AS B ), --4,294,967,296 rows TALLY AS ( SELECT ROW_NUMBER() OVER ( ORDER BY C ) AS Number FROM PASS5 ) INSERT INTO urls ( my_link , my_status ) SELECT -- top 10 search engines + me CASE ( Number % 11 ) WHEN 0 THEN 'www.ask.com' WHEN 1 THEN 'www.bing.com' WHEN 2 THEN 'www.duckduckgo.com' WHEN 3 THEN 'www.dogpile.com' WHEN 4 THEN 'www.webopedia.com' WHEN 5 THEN 'www.clusty.com' WHEN 6 THEN 'www.archive.org' WHEN 7 THEN 'www.mahalo.com' WHEN 8 THEN 'www.google.com' WHEN 9 THEN 'www.yahoo.com' ELSE 'www.craftydba.com' END AS my_link , -- ratings scale CASE ( Number % 5 ) WHEN 0 THEN 'poor' WHEN 1 THEN 'fair' WHEN 2 THEN 'good' WHEN 3 THEN 'very good' ELSE 'excellent' END AS my_status FROM TALLY AS T WHERE Number <= 1000000 go 

En segundo lugar, siempre queremos borrar los búferes y la memory caching al realizar análisis de performance en nuestro entorno de testing. Además, queremos activar las statistics de E / S y el time para comparar los resultados.

Vea el código a continuación.

 -- Show time & i/o SET STATISTICS TIME ON SET STATISTICS IO ON GO -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO 

En tercer lugar, queremos probar la primera statement de TSQL. Mire el plan de ejecución y capture las statistics.

 -- Try 1 SELECT * FROM urls ORDER BY my_status /* Table 'urls'. Scan count 5, logical reads 4987, physical reads 1, read-ahead reads 4918, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3166 ms, elapsed time = 8130 ms. */ 

enter image description here

En cuarto lugar, queremos probar la segunda statement de TSQL. No olvide borrar la memory caching y los búferes del plan de consulta. Si no lo hace, la consulta tarda less de 1 segundo, ya que la mayoría de la información está en la memory. Mire el plan de ejecución y capture las statistics.

 -- Try 2 SELECT ROW_NUMBER() OVER (ORDER BY my_status) as my_rownum, * FROM urls /* Table 'urls'. Scan count 5, logical reads 4987, physical reads 1, read-ahead reads 4918, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3276 ms, elapsed time = 8414 ms. */ 

enter image description here

Por último, pero no less importante, aquí está la parte divertida, el análisis de performance.

1 – Podemos ver que el segundo plan es un súper set del primero. Entonces, ambos planes escanean el índice agrupado y clasifican los datos. El paralelismo se usa para poner juntos los resultados.

2 – El segundo plan / consulta necesita calcular el número de fila. Segmenta los datos y calcula este escalar. Por lo tanto, terminamos con dos operadores más en el plan.

No es sorprendente que el primer plan se ejecute en 8130 ms y el segundo plan se ejecute en 8414 ms.

Siempre mire el plan de consulta. Tanto estimado como real. Te dicen que quieres que el motor planee hacer y lo que realmente hace.

En este ejemplo, dos declaraciones diferentes de TSQL presentan planes casi idénticos.

Sinceramente

John

http://www.craftydba.com

La respuesta general a cualquier pregunta sql "en qué order aparece este resultado" es "lo que el server siente, y puede no ser el mismo desde la consulta hasta la consulta" a less que haya pedido específicamente un pedido.

Incluso algo simple como 'seleccionar top 1000 myColumn from myTable' puede regresar con cualquier fila en cualquier order; por ejemplo, el server puede usar subprocesss paralelos y el primer subprocess para comenzar a devolver los resultados comenzó a leer en el medio de la tabla, o se utilizó un índice que incluía myColumn, por lo que obtuvo las filas con el primer nombre del producto alfabéticamente (esta vez; el índice tenía diferentes statistics, por lo que seleccionó un índice diferente y le dio las 1000 transactions más antiguas) …

Incluso teóricamente es posible que el server diga "tenía estas 10 páginas en mi caching de memory que coinciden con su consulta, le pasaré estas mientras espero a que el disco devuelva el rest …