Combinaciones (SQL Server)Joins (SQL Server)

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server realiza operaciones de ordenación, intersección, unión y diferencia mediante una tecnología de ordenación en memoria y combinación hash.performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Con este tipo de plan de consulta, SQL ServerSQL Server acepta la partición vertical de tablas, a veces llamada almacenamiento en columnas.Using this type of query plan, SQL ServerSQL Server supports vertical table partitioning, sometimes called columnar storage.

SQL ServerSQL Server emplea cuatro tipos de operaciones de combinación:employs four types of join operations:

  • Combinaciones de bucles anidadosNested Loops joins
  • Combinaciones de mezclaMerge joins
  • Combinaciones hashHash joins
  • Combinaciones adaptables (a partir de SQL Server 2017 (14.x)SQL Server 2017 (14.x))Adaptive joins (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))

Aspectos básicos de las combinacionesJoin Fundamentals

Las combinaciones permiten recuperar datos de dos o más tablas según las relaciones lógicas entre ellas.By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Las combinaciones indican cómo debe usar SQL ServerSQL Server los datos de una tabla para seleccionar las filas de otra tabla.Joins indicate how SQL ServerSQL Server should use data from one table to select the rows in another table.

Una condición de combinación define la forma en la que dos tablas se relacionan en una consulta al:A join condition defines the way two tables are related in a query by:

  • Especificar la columna de cada tabla que debe usarse para la combinación.Specifying the column from each table to be used for the join. Una condición de combinación típica especifica una clave externa de una tabla y su clave asociada en otra tabla.A typical join condition specifies a foreign key from one table and its associated key in the other table.
  • Especificar un operador lógico (por ejemplo, = o <>,) para usarlo en los valores de comparación de las columnas.Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Las combinaciones internas se pueden especificar en las cláusulas FROM o WHERE.Inner joins can be specified in either the FROM or WHERE clauses. Las combinaciones externas solo se pueden especificar en la cláusula FROM.Outer joins can be specified in the FROM clause only. Las condiciones de combinación se combinan con las condiciones de búsqueda de WHERE y HAVING para controlar cuáles son las filas seleccionadas de las tablas base a las que se hace referencia en la cláusula FROM.The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.

Especificar las condiciones de la combinación en la cláusula FROM ayuda a separarlas de cualquier otra condición de búsqueda que se pueda especificar en una cláusula WHERE; es el método recomendado para especificar combinaciones.Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. La sintaxis simplificada de la combinación de la cláusula FROM de ISO es:A simplified ISO FROM clause join syntax is:

FROM first_table join_type second_table [ON (join_condition)]

join_type especifica qué tipo de combinación se lleva a cabo: interior, exterior o cruzada.join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition define el predicado que se va a evaluar para cada par de filas combinadas.join_condition defines the predicate to be evaluated for each pair of joined rows. A continuación se muestra un ejemplo de la especificación de una combinación en la cláusula FROM:The following is an example of a FROM clause join specification:

FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
     ON (ProductVendor.BusinessEntityID = Vendor.BusinessEntityID)

A continuación se incluye un ejemplo de una instrucción SELECT sencilla con esta combinación:The following is a simple SELECT statement using this join:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

La selección devuelve la información de los productos y proveedores de cualquier combinación de partes suministrada por una empresa cuyo nombre empieza por la letra F y el precio del producto es superior a 10 $.The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.

Cuando en una consulta simple se hace referencia a varias tablas, ninguna de las referencias a las columnas debe ser ambigua.When multiple tables are referenced in a single query, all column references must be unambiguous. En el ejemplo anterior, las tablas ProductVendor y Vendor tienen una columna denominada BusinessEntityID.In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID. Cualquier nombre de columna que esté duplicado en varias tablas a las que se hace referencia en la consulta debe ser calificado con el nombre de la tabla.Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. Todas las referencias a las columnas de Vendor del ejemplo están calificadas.All references to the Vendor columns in the example are qualified.

Cuando el nombre de una columna no está duplicado en varias de las tablas usadas en la consulta, las referencias al mismo no tienen que calificarse con el nombre de la tabla.When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. Esto se muestra en el ejemplo anterior.This is shown in the previous example. Algunas veces, resulta difícil entender una instrucción SELECT porque no hay nada que indique la tabla que ha suministrado cada columna.Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. La legibilidad de la consulta puede mejorarse si todas las columnas se califican con sus nombres de tabla.The readability of the query is improved if all columns are qualified with their table names. Incluso puede mejorarse más si se usan alias de tablas, especialmente cuando los propios nombres de las tablas se deben calificar con los nombres de las bases de datos y de los propietarios.The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. A continuación se incluye el mismo ejemplo, con la salvedad de que se han asignado alias de tablas y las columnas se han calificado con dichos alias para facilitar su lectura:The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv 
JOIN Purchasing.Vendor AS v
    ON (pv.BusinessEntityID = v.BusinessEntityID)
WHERE StandardPrice > $10
    AND Name LIKE N'F%';

Los ejemplos anteriores han especificado las condiciones de combinación en la cláusula FROM, lo que constituye el método recomendado.The previous examples specified the join conditions in the FROM clause, which is the preferred method. La siguiente consulta contiene la misma condición de combinación especificada en la cláusula WHERE:The following query contains the same join condition specified in the WHERE clause:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
WHERE pv.BusinessEntityID=v.BusinessEntityID
    AND StandardPrice > $10
    AND Name LIKE N'F%';

La lista de selección de una combinación puede hacer referencia a todas las columnas de las tablas combinadas o a cualquier subconjunto de las columnas.The select list for a join can reference all the columns in the joined tables, or any subset of the columns. No es necesario que la lista de selección contenga columnas de cada tabla de la combinación.The select list is not required to contain columns from every table in the join. Por ejemplo, en una combinación de tres tablas, solo se puede usar una tabla para pasar de una de las dos tablas a la tercera y no es necesario que se haga referencia a ninguna columna de la tabla intermedia en la lista de selección.For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.

Aunque las condiciones de combinación suelen tener comparaciones de igualdad (=), también se pueden especificar otros operadores relacionales o de comparación, así como otros predicados.Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. Para obtener más información, vea Operadores de comparación (Transact-SQL) y WHERE (Transact-SQL).For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

Cuando SQL ServerSQL Server procesa combinaciones, el motor de consultas elige el método más eficaz entre varias posibilidades para procesar la combinación.When SQL ServerSQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. La ejecución física de varias combinaciones puede utilizar muchas optimizaciones diferentes y, por consiguiente. no se puede predecir de forma confiable.The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.

No es necesario que las columnas utilizadas en una condición de combinación tengan el mismo nombre o sean del mismo tipo de datos.Columns used in a join condition are not required to have the same name or be the same data type. Sin embargo, si los tipos de datos no son idénticos, deben ser compatibles o bien deben ser tipos que SQL Server pueda convertir implícitamente.However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. Si los tipos de datos no se pueden convertir implícitamente, la condición de combinación debe convertir explícitamente el tipo de datos mediante la función CAST.If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function. Para obtener más información sobre la conversión de datos, vea Conversión de tipos de datos (motor de base de datos).For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).

La mayor parte de las consultas que usan una combinación se pueden volver a escribir con una subconsulta (una consulta anidada dentro de otra consulta). La mayor parte de las subconsultas se pueden volver a escribir como combinaciones.Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins. Para obtener más información sobre las subconsultas, vea Subqueries (Subconsultas).For more information about subqueries, see Subqueries.

Nota

Las tablas no se pueden combinar directamente en columnas ntext, text o image.Tables cannot be joined directly on ntext, text, or image columns. Sin embargo, las tablas no se pueden combinar directamente en columnas ntext, text o image utilizando SUBSTRING.However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING.
Por ejemplo, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) realiza una combinación interna de dos tablas en los primeros 20 caracteres de cada columna de texto de las tablas t1 y t2.For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.
Además, otra posibilidad para comparar columnas ntext o text de dos tablas consiste en comparar las longitudes de las columnas con una cláusula WHERE, por ejemplo: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Descripción de las combinaciones de bucles anidadosUnderstanding Nested Loops joins

Si la entrada de una combinación es pequeña (menor de 10 filas), y la entrada de otra combinación es bastante grande y está indizada en las columnas de combinación, una combinación de bucles anidados de índices es la operación de combinación más rápida, debido a que requieren menos E/S y menos comparaciones.If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.

La combinación de bucles anidados, también denominada iteración anidada, utiliza una entrada de combinación como tabla de entrada externa (mostrada como la entrada superior en el plan de ejecución gráfico) y otra como tabla de entrada interna (inferior).The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. El bucle externo procesa la tabla de entrada externa fila a fila.The outer loop consumes the outer input table row by row. El bucle interno, que se ejecuta para cada fila externa, busca filas coincidentes en la tabla de entrada interna.The inner loop, executed for each outer row, searches for matching rows in the inner input table.

En el caso más sencillo, la búsqueda recorre una tabla o índice completo, lo cual recibe el nombre de combinación nativa de bucles anidados.In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. Si la búsqueda utiliza un índice, se denomina combinación de bucles anidados de índice.If the search exploits an index, it is called an index nested loops join. Si se genera el índice como parte del plan de consulta (y se destruyó al finalizar la consulta), se denomina combinación de bucles anidados de índice temporal.If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. El optimizador de consultas tiene en cuenta todas estas variantes.All these variants are considered by the Query Optimizer.

Una combinación de bucles anidados resulta particularmente eficaz si la entrada externa es pequeña y la entrada interna está indizada previamente y es grande.A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. En muchas transacciones pequeñas, como las que afectan solo a un pequeño conjunto de filas, las combinaciones de bucles anidados de índice son superiores a las combinaciones de mezcla y a las combinaciones hash.In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. Sin embargo, en las consultas grandes, las combinaciones de bucles anidados a menudo no son la opción óptima.In large queries, however, nested loops joins are often not the optimal choice.

Cuando el atributo OPTIMIZED de un operador de combinación de bucles anidados está establecido en True, significa que un bucle anidado optimizado (u ordenación por lotes) se usa para minimizar la E/S cuando la tabla interna es grande, independientemente de si está paralelizada o no.When the OPTIMIZED attribute of a Nested Loops join operator is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used to minimize I/O when the inner side table is large, regardless of it being parallelized or not. Es posible que la presencia de esta optimización en un plan determinado no sea demasiado obvia al analizar un plan de ejecución, debido a que la propia ordenación es una operación oculta.The presence of this optimization in a given plan may not be very obvious when analyzing an execution plan, given the sort itself is a hidden operation. Pero si se mira el XML del plan correspondiente al atributo OPTIMIZED, indica que la combinación de bucles anidados podría intentar reordenar las filas de entrada para mejorar el rendimiento de E/S.But by looking in the plan XML for the attribute OPTIMIZED, this indicates the Nested Loops join may try to reorder the input rows to improve I/O performance.

Comprender las combinaciones de mezclaUnderstanding Merge joins

Si las dos entradas de la combinación no son pequeñas pero están ordenadas por la columna de combinación (por ejemplo, si se obtuvieron al recorrer índices ordenados), una combinación de mezcla es la operación de combinación más rápida.If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. Si ambas entradas de combinación son grandes y tienen tamaños similares, una combinación de mezcla con una ordenación previa y una combinación hash ofrecen un rendimiento similar.If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. Sin embargo, las operaciones de combinación hash a menudo son más rápidas si los tamaños de las dos entradas difieren significativamente entre sí.However, hash join operations are often much faster if the two input sizes differ significantly from each other.

La combinación de mezcla requiere que ambas entradas estén ordenadas en las columnas de mezcla, que se han definido mediante las cláusulas de igualdad (ON) del predicado de combinación.The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. El optimizador de consultas normalmente recorre un índice, si existe uno en el conjunto de columnas apropiado, o bien coloca un operador de orden bajo la combinación de mezcla.The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. En algunos casos, puede haber varias cláusulas de igualdad, pero las columnas de mezcla se toman solo de algunas de las cláusulas de igualdad disponibles.In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

Dado que cada entrada está ordenada, el operador Merge Join obtendrá una fila de cada entrada y las comparará.Because each input is sorted, the Merge Join operator gets a row from each input and compares them. Por ejemplo, para operaciones de combinaciones internas, se devuelven las filas si son iguales.For example, for inner join operations, the rows are returned if they are equal. Si no son iguales, se descarta la fila que tenga el valor menor y se obtiene otra fila de esa entrada.If they are not equal, the lower-value row is discarded and another row is obtained from that input. Este proceso se repite hasta que se han procesado todas las filas.This process repeats until all rows have been processed.

La operación de combinación de mezcla puede ser una operación normal o de varios a varios.The merge join operation may be either a regular or a many-to-many operation. Una combinación de mezcla de varios a varios utiliza una tabla temporal para almacenar las filas.A many-to-many merge join uses a temporary table to store rows. Si hay valores duplicados de cada entrada, una de las entradas tendrá que retroceder al principio de los duplicados cuando se procesa cada duplicado de la otra entrada.If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.

Si hay un predicado residual, todas las filas que cumplan el predicado de mezcla evaluarán el predicado residual y solo se devolverán las filas que lo cumplan.If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.

La combinación de mezcla es muy rápida, pero puede ser una opción costosa si se requieren operaciones de ordenación.Merge join itself is very fast, but it can be an expensive choice if sort operations are required. Sin embargo, si el volumen de datos es grande y los datos deseados se pueden obtener con una ordenación previa de los índices existentes de árbol b, la combinación de mezcla es, a menudo, el algoritmo de combinación disponible más rápido.However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

Descripción de las combinaciones hashUnderstanding Hash joins

Las combinaciones hash pueden procesar eficazmente entradas grandes, sin ordenar y no indizadas.Hash joins can efficiently process large, unsorted, nonindexed inputs. Son útiles para obtener resultados intermedios en consultas complejas debido a que:They are useful for intermediate results in complex queries because:

  • Los resultados intermedios no están indizados (a menos que se hayan guardado explícitamente en disco y, después, se hayan indizado) y, a menudo, no tienen un orden adecuado para la siguiente operación del plan de consulta.Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
  • Los optimizadores de consultas solo calculan los tamaños de resultados intermedios.Query optimizers estimate only intermediate result sizes. Dado que las estimaciones pueden ser poco exactas en consultas complejas, los algoritmos utilizados para procesar los resultados intermedios no solo deben ser eficaces, sino que también deben rebajarse si un resultado intermedio es mayor de lo previsto.Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.

La combinación hash permite reducir el uso de la desnormalización.The hash join allows reductions in the use of denormalization. La desnormalización se suele utilizar para conseguir un rendimiento mejor mediante la reducción de las operaciones de combinación, a pesar del peligro de redundancia, como las actualizaciones incoherentes.Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Las combinaciones hash reducen la necesidad de desnormalización.Hash joins reduce the need to denormalize. Las combinaciones hash permiten que las particiones verticales (que representan grupos de columnas de una sola tabla en archivos o índices independientes) se conviertan en una opción viable para el diseño físico de bases de datos.Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.

La combinación hash tiene dos entradas: la entrada de compilación y la entrada de sondeo.The hash join has two inputs: the build input and probe input. El optimizador de consultas asigna estos roles de forma que la entrada más pequeña sea la entrada de generación.The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

Las combinaciones hash se utilizan para muchos tipos de operaciones de coincidencia de conjuntos: combinación interna; combinación externa completa, izquierda y derecha; semicombinación izquierda y derecha; intersección; unión y diferencia.Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Además, una variante de la combinación hash puede realizar la eliminación duplicada y la agrupación, como SUM(salary) GROUP BY department.Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department. Estas modificaciones solo utilizan una entrada para los roles de generación y sondeo.These modifications use only one input for both the build and probe roles.

En las secciones siguientes se describen los distintos tipos de combinaciones hash: combinación hash en memoria, combinación hash aplazada y combinación hash recursiva.The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.

Combinación hash en memoriaIn-Memory Hash Join

La combinación hash primero recorre o calcula la entrada de generación completa y, a continuación, genera una tabla hash en memoria.The hash join first scans or computes the entire build input and then builds a hash table in memory. Cada fila se inserta en un cubo hash según el valor hash calculado para la clave hash.Each row is inserted into a hash bucket depending on the hash value computed for the hash key. Si la entrada de generación completa es menor que la memoria disponible, se pueden insertar todas las filas en la tabla hash.If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. Después de la fase de generación se produce la fase de sondeo.This build phase is followed by the probe phase. La entrada de sondeo completa se recorre o se calcula fila a fila y, por cada fila de sondeo, se calcula el valor de la clave hash, se recorre el cubo hash correspondiente y se obtienen las coincidencias.The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

Combinación hash aplazadaGrace Hash Join

Si la entrada de generación no cabe en la memoria, la combinación hash se realiza en varios pasos.If the build input does not fit in memory, a hash join proceeds in several steps. Esto se denomina combinación hash aplazada.This is known as a grace hash join. Cada paso contiene una fase de generación y una fase de sondeo.Each step has a build phase and probe phase. Inicialmente, se procesan todas las entradas de generación y sondeo, y se crean particiones (con una función hash sobre las claves hash) en varios archivos.Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. La utilización de la función hash sobre las claves hash garantiza que todos los pares de registros combinados estén en la misma pareja de archivos.Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Por tanto, la tarea de combinar dos grandes entradas se ha reducido a varias instancias más pequeñas de las mismas tareas.Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. A continuación se aplica la combinación hash a cada pareja de archivos de la partición.The hash join is then applied to each pair of partitioned files.

Combinación hash recursivaRecursive Hash Join

Si la entrada de generación es tan grande que las entradas para una mezcla externa estándar requerirían varios niveles de mezcla, son necesarios varios pasos de particiones y varios niveles de particiones.If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. Si solo algunas de las particiones son grandes, los pasos de particiones adicionales solo se utilizan para estas particiones específicas.If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. Para que todos los pasos de las particiones sean lo más rápidos posible, se utilizan grandes operaciones asincrónicas de E/S de forma que un solo subproceso pueda mantener ocupadas varias unidades de disco.In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.

Nota

Si la entrada de generación solo es un poco más grande que la memoria disponible, se combinan elementos de una combinación hash en memoria y una combinación hash aplazada en un solo paso, para producir una combinación hash híbrida.If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.

Durante la optimización, no siempre se puede determinar qué combinación hash se utiliza.It is not always possible during optimization to determine which hash join is used. Por tanto, SQL Server comienza utilizando una combinación hash en memoria y, gradualmente, pasa a una combinación hash aplazada y a una combinación hash recursiva, según el tamaño de la entrada de generación.Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.

Si el optimizador de consultas prevé erróneamente cuál de las dos entradas será más pequeña y, por tanto, debería haber sido la entrada de generación, se invierten dinámicamente los roles de generación y sondeo.If the Query Optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. La combinación hash se asegura de usar el archivo de desbordamiento menor como entrada de generación.The hash join makes sure that it uses the smaller overflow file as build input. Esta técnica se denomina “inversión de roles”.This technique is called role reversal. La inversión de roles tiene lugar en una combinación hash después de al menos un volcado en el disco.Role reversal occurs inside the hash join after at least one spill to the disk.

Nota

La inversión de roles funciona independientemente de cualquier estructura o sugerencia de consulta.Role reversal occurs independent of any query hints or structure. La inversión de roles no se muestra en el plan de consulta; cuando se lleva a cabo, resulta transparente para el usuario.Role reversal does not display in your query plan; when it occurs, it is transparent to the user.

Salida hashHash Bailout

El término salida hash algunas veces se utiliza para describir combinaciones hash aplazadas o recursivas.The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.

Nota

Las combinaciones hash recursivas o las salidas hash reducen el rendimiento del servidor.Recursive hash joins or hash bailouts cause reduced performance in your server. Si ve muchos eventos Hash Warning en un seguimiento, actualice las estadísticas en las columnas que se están combinando.If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.

Para obtener más información sobre el salida hash, vea Hash Warning [clase de eventos].For more information about hash bailout, see Hash Warning Event Class.

Descripción de las combinaciones adaptablesUnderstanding Adaptive joins

Las combinaciones adaptables en modo por lotes permiten elegir un método Combinación hash o Combinación de bucles anidados que se aplace hasta después de que se haya examinado la primera entrada.Batch mode Adaptive Joins enable the choice of a Hash Join or Nested Loops join method to be deferred until after the first input has been scanned. El operador de combinaciones adaptables define un umbral que se usa para decidir cuándo cambiar a un plan de bucles anidados.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Por lo tanto, un plan de consulta puede cambiar dinámicamente a una mejor estrategia de combinación durante la ejecución sin tener que sea necesaria una nueva compilación.A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.

Sugerencia

La cargas de trabajo con oscilaciones frecuentes entre análisis de entrada de combinación pequeños y grandes son las que más se benefician de esta característica.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

La decisión de runtime se basa en estos pasos:The runtime decision is based on the following steps:

  • Si el recuento de filas de la entrada de combinación de compilación es lo suficientemente pequeño como para que una combinación de bucles anidados sea una opción más óptima que una combinación hash, el plan cambia a un algoritmo de bucles anidados.If the row count of the build join input is small enough that a Nested Loops join would be more optimal than a Hash join, the plan switches to a Nested Loops algorithm.
  • Si la entrada de combinación de compilación supera un umbral de recuento de filas determinado, no se produce ningún cambio y el plan continúa con una combinación hash.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.

La siguiente consulta se usa para mostrar un ejemplo de combinación adaptable:The following query is used to illustrate an Adaptive Join example:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

La consulta devuelve 336 filas.The query returns 336 rows. Al habilitar las estadísticas de consultas activas se ve el siguiente plan:Enabling Live Query Statistics displays the following plan:

La consulta da lugar a 336 filas

En el plan, tenga en cuenta lo siguiente:In the plan, note the following:

  1. Una exploración de índice de almacén de columnas que se usa para proporcionar filas para la fase de compilación de combinación hash.A columnstore index scan used to provide rows for the Hash join build phase.
  2. El nuevo operador de combinación adaptable.The new Adaptive Join operator. Este operador define un umbral que se usa para decidir cuándo cambiar a un plan de bucle anidado.This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. En este ejemplo, el umbral es 78 filas.For this example, the threshold is 78 rows. Todo lo que tenga > = 78 filas usará una combinación hash.Anything with >= 78 rows will use a Hash join. Si es inferior al umbral, se usará una combinación de bucles anidados.If less than the threshold, a Nested Loops join will be used.
  3. Puesto que la consulta devuelve 336 filas, se supera el umbral y la segunda rama representa la fase de sondeo de una operación de combinación hash estándar.Since the query returns 336 rows, this exceeded the threshold and so the second branch represents the probe phase of a standard Hash join operation. Observe que las estadísticas de consultas dinámicas muestran las filas que pasan por los operadores, en este caso "672 de 672".Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. La última rama es la búsqueda en índice agrupado que usa la combinación de bucles anidados si no se ha superado el umbral.And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. Observe que se ve "0 de 336" filas mostradas (la rama no se usa).Notice that we see "0 of 336" rows displayed (the branch is unused).

Ahora vamos a comparar el plan con la misma consulta, pero cuando el valor Cantidad solo tiene una fila en la tabla:Now contrast the plan with the same query, but when the Quantity value only has one row in the table:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

La consulta devuelve una fila.The query returns one row. Al habilitar las estadísticas de consultas activas se ve el siguiente plan:Enabling Live Query Statistics displays the following plan:

La consulta da lugar a una fila

En el plan, tenga en cuenta lo siguiente:In the plan, note the following:

  • Con una fila devuelta, ahora pasan filas por la búsqueda en índice clúster.With one row returned, the Clustered Index Seek now has rows flowing through it.
  • Y puesto que no se ha continuado con la fase de compilación de combinación hash, no pasan filas por la segunda rama.And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

Comentarios sobre las combinaciones adaptablesAdaptive Join remarks

Las combinaciones adaptables tienen unos requisitos de memoria superiores a un plan equivalente de combinación de bucle anidado de índice.Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. La memoria adicional se solicita como si los bucles anidados fuesen una combinación hash.The additional memory is requested as if the Nested Loops was a Hash join. También hay sobrecarga para la fase de compilación como una operación de detención e inicio frente a una combinación equivalente de transmisión de bucle anidado.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. Ese costo adicional va acompañado de flexibilidad en escenarios donde los recuentos de filas pueden fluctuar en la entrada de compilación.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

Las combinaciones adaptables en modo por lotes funcionan para la ejecución inicial de una instrucción y, una vez compiladas, las ejecuciones consecutivas seguirán siendo adaptables según el umbral de combinación adaptable compilado y las filas de runtime que fluyen a través de la fase de compilación de la entrada externa.Batch mode Adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.

Si una combinación adaptable cambia a una operación de bucles anidados, usa las filas ya leídas por la compilación de combinación hash.If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. El operador no vuelve a leer las filas de la referencia externa.The operator does not re-read the outer reference rows again.

Seguimiento de la actividad de combinación adaptableTracking Adaptive join activity

El operador de combinación adaptable tiene los siguientes atributos de operador de plan:The Adaptive Join operator has the following plan operator attributes:

Atributo de planPlan attribute DescripciónDescription
AdaptiveThresholdRowsAdaptiveThresholdRows Muestra el uso de umbral para cambiar de una combinación hash a una combinación de bucle anidado.Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType El tipo de combinación probable.What the join type is likely to be.
ActualJoinTypeActualJoinType En un plan real, se muestra qué algoritmo de combinación se ha elegido finalmente según el umbral.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

El plan estimado muestra la forma del plan de combinación adaptable, junto con un umbral de combinación adaptable definido y un tipo de combinación estimado.The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

Sugerencia

El Almacén de consultas captura y puede aplicar un plan de combinación adaptable de modo de proceso por lotes.Query Store captures and is able to force a batch mode Adaptive Join plan.

Instrucciones aptas de combinación adaptableAdaptive join eligible statements

Algunas condiciones convierten a una combinación lógica en apta como combinación adaptable de modo de proceso por lotes:A few conditions make a logical join eligible for a batch mode Adaptive Join:

  • El nivel de compatibilidad de la base de datos es 140 o superior.The database compatibility level is 140 or higher.
  • La consulta es una instrucción SELECT (las instrucciones de modificación de datos no son aptas actualmente).The query is a SELECT statement (data modification statements are currently ineligible).
  • La combinación puede ser ejecutada tanto por una combinación de bucles anidados indexada como por un algoritmo físico de combinación hash.The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
  • La combinación hash usa el modo por lotes, ya sea mediante la presencia de un índice de almacén de columnas en la consulta global o una referencia directa a la tabla con índice de almacén de columnas por parte de la combinación.The Hash join uses Batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  • Las soluciones alternativas generadas de la combinación de bucles anidados y la combinación hash deben tener el mismo primer elemento secundario (referencia externa).The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).

Filas de umbral adaptableAdaptive threshold rows

El gráfico siguiente muestra una intersección de ejemplo entre el costo de una combinación hash y el de una alternativa de combinación de bucles anidados.The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative. En este punto de intersección, se determina el umbral que a su vez determina el algoritmo real usado para la operación de combinación.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

Umbral de combinación

Deshabilitar las combinaciones adaptables sin cambiar el nivel de compatibilidadDisabling Adaptive joins without changing the compatibility level

Las combinaciones adaptables se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 140 o posterior.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
Para deshabilitar las combinaciones adaptables para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Cuando se habilita, esta opción aparecerá como habilitada en sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Para volver a habilitar las combinaciones adaptables para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

Las combinaciones adaptables también se pueden deshabilitar para una consulta específica si se designa DISABLE_BATCH_MODE_ADAPTIVE_JOINS como una sugerencia de consulta USE HINT.Adaptive joins can also be disabled for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. Por ejemplo:For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

Nota

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de marca de seguimiento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Combinaciones y valores NULLNull Values and Joins

Cuando hay valores NULL en las columnas de las tablas que se están combinando, los valores NULL no coinciden unos con otros.When there are null values in the columns of the tables being joined, the null values do not match each other. La presencia de valores NULL en una columna de una de las tablas que se está combinando solo se puede indicar si se usa una combinación externa (a menos que la cláusula WHERE excluya los valores NULL).The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

A continuación se muestran dos tablas que tienen, cada una, un valor NULL en la columna que participa en la combinación:Here are two tables that each have NULL in the column that will participate in the join:

table1                          table2
a           b                   c            d
-------     ------              -------      ------
      1        one                 NULL         two
   NULL      three                    4        four
      4      join4

Una combinación que compara los valores de la columna con los de la columna c no obtiene una coincidencia en las columnas que tienen valores NULL:A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:

SELECT *
FROM table1 t1 JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Solo se devuelve una fila con el valor 4 en las columnas a y c:Only one row with 4 in column a and c is returned:

a           b      c           d      
----------- ------ ----------- ------ 
4           join4  4           four   

(1 row(s) affected)

Los valores NULL devueltos de una tabla base también son difíciles de distinguir de los valores NULL devueltos en una combinación externa.Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. Por ejemplo, la siguiente instrucción SELECT realiza una combinación externa izquierda en las dos tablas:For example, the following SELECT statement does a left outer join on these two tables:

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

El conjunto de resultados es el siguiente.Here is the result set.

a           b      c           d      
----------- ------ ----------- ------ 
NULL        three  NULL        NULL 
1           one    NULL        NULL 
4           join4  4           four   

(3 row(s) affected)

Los resultados no facilitan la distinción de NULL en los datos de NULL que representen la imposibilidad de combinar.The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. Cuando haya valores NULL en los datos que se estén combinando, normalmente será preferible omitirlos de los resultados al utilizar una combinación normal.When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.

Consulte tambiénSee Also

Referencia de operadores lógicos y físicos del plan de presentación Showplan Logical and Physical Operators Reference
Operadores de comparación (Transact-SQL) Comparison Operators (Transact-SQL)
Conversión de tipo de datos (motor de base de datos) Data Type Conversion (Database Engine)
Subqueries (Subconsultas)Subqueries
Combinaciones adaptablesAdaptive Joins