SQL Server Loop through Table Rows without Cursor

By: Daniel Farina | Updated: 2019-09-12 | Comments (2) | Related: More > T-SQL

Problem

siempre ha escuchado que debe evitar cursores en su código T-SQL como una mejor práctica de SQL Server, porque los cursores son perjudiciales para rendimiento y a veces causa problemas. Pero a veces hay una necesidad de recorrer losdatos una fila a la vez, por lo que en este consejo veremos una comparación de cómo hacer un bucle sin usar el cursor.,

solución

todos sabemos que SQL Server, al igual que cualquier base de datos relacional, permite al usuario realizar operaciones basadas en conjuntos. Además, como hacen muchos proveedores de bases de datos, SQL Server incluye una extensión de procedimiento que es el lenguaje T-SQL. Añade construcciones que se encuentran en lenguajes de procedimientos permitiendo una codificación más sencilla para los desarrolladores. Estas construcciones se añadieron por una razón y, a veces, este es el único enfoque de la tarea en cuestión.,

usando un bucle While en lugar de cursores en SQL Server

si alguna vez ha trabajado con cursores, puede encontrar este título un poco confuso porque, después de todo, los cursores usan construcciones while para iterar entre filas. Pero además de eso, quiero mostrarles que en algunas circunstancias cuando usamos un cursor para iterar un conjunto de filas podemos cambiarlo a un bucle while. En tales casos, el único desafío será elegir una condición de salida adecuada.,

Pros y contras de usar cursores para iterar a través de filas de tablas en SQL Server

no todo está mal con los cursores, también tienen algunas ventajas sobre otras técnicas de bucle.

• Los cursores son actualizables: cuando crea un cursor, utiliza una consulta para defineit utilizando la instrucción DECLARE CURSOR. Al usar la opción UPDATE en la instrucción cursorcreation, puede actualizar las columnas dentro del cursor.,
• Puede moverse hacia adelante y hacia atrás en un cursor: mediante el uso de la opción de desplazamiento en la declaración declarar CURSOR puede navegar a través de los registros del cursor en ambas direcciones con las opciones de búsqueda Primero, Último, anterior, siguiente, relativo yabsoluto. Tenga en cuenta que la opción SCROLL es incompatible con las opciones FORWARD_ONLYand FAST_FORWARD.
• Los cursores se pueden pasar a los procedimientos almacenados: si utiliza la opción GLOBAL para crear un cursor, se puede utilizar en cualquier procedimiento almacenado o por lotes ejecutados en la misma conexión. Esto le permite usar cursores en procedimientos almacenados anidados.,
• Los cursores tienen muchas opciones diferentes: con los cursores tienes la oportunidad de usar diferentes opciones que afectan cómo se comportarán en lo que respecta al bloqueo.
• Los cursores no necesitan una condición: al usar cursores, está manejando un conjunto de filas como un registro. Esto le permite moverse a través del cursor sin necesidad de tener una condición booleana. Por ejemplo, puede crear un cursor con el nombre de las bases de datos que residen en una instancia de SQL Server sin la necesidad de una clave sustituta para funcionar como una condición de prueba como en un bucle WHILE.,

también hay algunos aspectos negativos que debe tener en cuenta al usar cursoren lugar de otras opciones de bucle.

• Si usa cursores globales en su código, corre el riesgo de errores de facing debido a que un cursor se cierra por algún procedimiento almacenado anidado en su código.
• normalmente los cursores tienen menos rendimiento que un bucle equivalente usando un WHILEloop o CTE.

Pros y contras de usar un bucle While para iterar a través de filas de tablas en SQL Server

También hay beneficios de usar un bucle WHILE en comparación con un cursor.,

• Mientras que los bucles son más rápidos que los cursores.
• Mientras que los bucles usan menos bloqueos que los cursores.
• menos uso de Tempdb: los bucles While no crean una copia de datos intempdb como lo hace un cursor. Recuerde que los cursores, dependiendo de las opciones que utilice para crearlos, pueden hacer que se creen las tablas temporales.

la siguiente lista detalla los aspectos negativos de los bucles WHILE.

• avanzar o retroceder es complejo: para avanzar o retroceder en aloop, debe cambiar dinámicamente la condición de iteración dentro del bucle.,Esto requiere un cuidado adicional; de lo contrario, puede terminar en un bucle infinito.
• El riesgo de un bucle infinito: en comparación con un cursor, no tiene un conjunto fijo de datos para el bucle (es decir, los datos devueltos por el estado SELECT en la declaración del cursor), en su lugar, cuando se utiliza un bucle WHILE, tiene que definir un boundarywith una expresión que se evalúa como verdadero o falso.

construyendo el entorno de prueba para Cursores y bucles

para probar esto, usaré una tabla con una columna de identidad (CursorTestID),una columna varchar (Filler) y una columna bigint (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO 

La idea es recorrer las filas de la tabla ordenadas por la columna CursorTestID y actualizar la columna RunningTotal con la suma del valor de la columna CursorTestID y el valor de la columna RunningTotal de la fila anterior.

pero antes de comenzar, primero necesitamos generar algunas filas de prueba con el siguiente script.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

en el script anterior, notará que solo usé un solo estado insert y aproveché el separador de lotes (el comando GO 500000) como un cortocircuito para ejecutar esta instrucción insert 500000 veces. Puede leer más sobre este método para repetir la ejecución por lotes en este consejo: ejecutar un lote de T-SQL varias veces usando GO.

ejemplo de un Cursor básico para recorrer filas de tablas en SQL Server

vamos a crear un cursor para llenar la columna RunningTotal. Observe en el nextscript que declaré el cursor con la opción FAST_FORWARD., Esto se hace con el fin de mejorar el rendimiento del cursor porque de acuerdo con Microsoft thefast_forward argumento «especifica un FORWARD_ONLY, READ_ONLY cursor con performanceoptimizations habilitado». En otras palabras, estamos instruyendo a SQL Server para que use un cursor de solo lectura que solo puede avanzar y desplazarse desde la primera hasta la última fila.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

la siguiente imagen es una captura de pantalla que muestra la ejecución del script above.As como pueden ver, tomó tres minutos y cinco segundos actualizar las 500.000 filas de nuestra tabla de pruebas.,

ejemplo de un bucle While básico para recorrer filas de tablas en SQL Server

ahora reescribiré el script anterior evitando el uso de un cursor. Notarás que contiene un bucle While que es casi idéntico al del script del servidor. Esto es, como he dicho anteriormente, porque incluso cuando se trabaja con cursorsusted necesita utilizar una estructura de control iterativa.

la siguiente imagen es una captura de pantalla de la ejecución del script anterior. Es tiempo tookless para ejecutar el bucle while que el cursor.,

otro ejemplo de Cursor de SQL Server

tomemos por ejemplo el cursor en la función tipStandardize SQL Server data con búsqueda de texto y reemplazo. Una palabra de consejo, para ejecutar este código, debe seguir los pasos en la sugerencia para crear el entorno de prueba.

y aquí está el código del cursor:

si analizamos este código, podemos ver que hay un cursor que pasa por los productos de la tabla que copié a continuación.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products 

ejemplo de Cursor de SQL Server convertido a un bucle While

para reemplazar este cursor con un bucle WHILE, necesitamos crear una tabla temporal para implementar una tabla de recuento. Para todos los que no saben lo que es una tabla de tallytable, podemos definirla como una tabla que contiene un par de columnas que consisten en una clave y su valor. En nuestro caso particular vamos a utilizar una integerkey secuencial a partir de 1, por lo que podemos utilizarlo como un iterador. Esta clave se asociará a un ProductID de la tabla Products.,

Al principio, dado que la tabla Products tiene la clave ProductID definida como identityusted puede verse tentado a omitir este paso, pero debe tener en cuenta que en un caso real una fila podría haber sido eliminada, por lo tanto, no podrá utilizar la identitycolumn como un iterador. Además, se puede eliminar una fila mientras ejecutamos ourcode y podría provocar errores de ejecución. Para evitar esto vamos a añadir aTRY-CATCHblock. Voy a entrar en esto más adelante.

antes de iniciar el bucle WHILE, necesitamos establecer su condición de inicio y parada., Para este asunto, agregué dos nuevas variables enteras llamadas @ Iterator y @MaxIterator.La variable @MaxIterator se utiliza para mantener el número de elementos en la # TallyTabletable y establecemos su valor solo una vez antes de iniciar el bucle. La variable @ Iterator se inicializa a 1, como lo definimos como el número inicial en la secuencia y vamos a incrementar su valor en cada iteración.

próximos pasos

• ¿eres nuevo en Cursores y necesitas algo de práctica? En la siguiente sugerencia encontrará una explicación, un ejemplo de cursor fácil de entender y más lecturas recomendadas: ejemplo de Cursor de SQL Server.,
• Si desea convertir los cursores existentes en su código para establecer consultas basadas, eche un vistazo a este chapterSQL Server Convert Cursorto basado en theSQL Server Database Design Best Practices Tutorial.
• ¿necesita otro ejemplo sobre el uso de un bucle While? Eche un vistazo a este tipque le mostrará cómo dividir las sentencias DML en lotes:optimice los procesos de inserción, actualización y eliminación de servidores SQL grandes mediante el uso de lotes.
• En caso de que no sepa cómo usar TRY…Capture exceptionhandling, eche un vistazo a este consejo:SQL Server Pruebe y Capture el manejo de excepciones.,
• Manténgase atento a la categoría de tips SQL de servidor SQL para obtener más ideas de codificación.

Última actualización: 2019-09-12

Sobre el autor

Daniel Farina nació en Buenos Aires, Argentina. Autodidacta, desde niño mostró una pasión por el aprendizaje.ver todos mis consejos

recursos relacionados

• Más consejos para desarrolladores de bases de datos…