Koppelingen (SQL Server)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

SQL Server maakt gebruik van joins om gegevens op te halen uit meerdere tabellen op basis van logische relaties tussen deze tabellen. Joins zijn essentieel voor relationele databasebewerkingen en stellen u in staat om gegevens uit twee of meer tabellen te combineren in één resultatenset.

SQL Server implementeert zowel logische join-bewerkingen (gedefinieerd door Transact-SQL syntaxis) als fysieke joinbewerkingen (de werkelijke algoritmen die worden gebruikt om de joins uit te voeren). Als u beide aspecten begrijpt, kunt u efficiënte query's schrijven en databaseprestaties optimaliseren.

Logische joinbewerkingen zijn onder andere:

• Inner joins
• Links, rechts en volledige outer joins
• Cross joins

Fysieke joinbewerkingen zijn onder andere:

• Geneste lus verbindingen
• Joins samenvoegen
• Hash-joins
• Adaptieve joins (van toepassing op: SQL Server 2017 (14.x) en latere versies)

In dit artikel wordt uitgelegd hoe joins werken, wanneer u verschillende jointypen gebruikt en hoe queryoptimalisatie het meest efficiënte join-algoritme selecteert op basis van factoren zoals tabelgrootte, beschikbare indexen en gegevensdistributie.

Basisprincipes van deelname

Met behulp van joins kunt u gegevens ophalen uit twee of meer tabellen op basis van logische relaties tussen de tabellen. Joins geven aan hoe SQL Server gegevens uit de ene tabel moet gebruiken om de rijen in een andere tabel te selecteren.

Een joinvoorwaarde definieert de manier waarop twee tabellen in een query zijn gerelateerd door:

• Hiermee geeft u de kolom op uit elke tabel die moet worden gebruikt voor de join. Een typische joinvoorwaarde specificeert een vreemde sleutel uit de ene tabel en de bijbehorende sleutel in de andere tabel.
• Een logische operator opgeven (bijvoorbeeld = of <>,) die moet worden gebruikt voor het vergelijken van waarden uit de kolommen.

Joins worden logisch uitgedrukt met behulp van de volgende Transact-SQL syntaxis:

• [ INNER ] JOIN
• LEFT [ OUTER ] JOIN
• RIGHT [ OUTER ] JOIN
• FULL [ OUTER ] JOIN
• CROSS JOIN

Inner joins kunnen worden opgegeven in de FROM of WHERE clausules. Outer joins en cross joins kunnen alleen in de FROM component worden opgegeven. De joinvoorwaarden worden gecombineerd met de WHERE voorwaarden en HAVING zoekvoorwaarden om de rijen te beheren die zijn geselecteerd in de basistabellen waarnaar in de FROM component wordt verwezen.

Als u de joinvoorwaarden in de FROM component opgeeft, kunt u deze scheiden van andere zoekvoorwaarden die in een WHERE component kunnen worden opgegeven. Dit is de aanbevolen methode voor het opgeven van joins. Een vereenvoudigde syntaxis voor ISO-componentdeelname FROM is:

FROM first_table < join_type > second_table [ ON ( join_condition ) ]

• De join_type geeft aan welk type join wordt uitgevoerd: een inner join, outer join, of cross join. Zie FROM-component voor uitleg over de verschillende typen joins.
• De join_condition definieert het predicaat dat moet worden geëvalueerd voor elk paar samengevoegde rijen.

De volgende code is een voorbeeld van een FROM clausule join-specificatie.

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

De volgende code is een eenvoudige SELECT statement met behulp van een join.

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

De SELECT verklaring retourneert de product- en leveranciersinformatie voor elke combinatie van onderdelen die door een bedrijf worden geleverd waarvoor de bedrijfsnaam begint met de letter F en de prijs van het product is meer dan $ 10.

Wanneer in één query naar meerdere tabellen wordt verwezen, moeten alle kolomverwijzingen ondubbelzinnig zijn. In het vorige voorbeeld hebben zowel de ProductVendor-tabel als de Vendor-tabel een kolom genaamd BusinessEntityID. Elke kolomnaam die wordt gedupliceerd tussen twee of meer tabellen waarnaar in de query wordt verwezen, moet worden gekwalificeerd met de tabelnaam. Alle verwijzingen naar de Vendor kolommen in het voorbeeld zijn gekwalificeerd.

Wanneer een kolomnaam niet wordt gedupliceerd in twee of meer tabellen die in de query worden gebruikt, hoeven verwijzingen ernaar niet te worden gekwalificeerd met de tabelnaam. Dit wordt weergegeven in het vorige voorbeeld. SELECT Een dergelijke component is soms moeilijk te begrijpen omdat er niets is om de tabel aan te geven die elke kolom heeft opgegeven. De leesbaarheid van de query wordt verbeterd als alle kolommen zijn gekwalificeerd met de tabelnamen. De leesbaarheid wordt verder verbeterd als er tabelaliassen worden gebruikt, met name wanneer de tabelnamen zelf moeten worden gekwalificeerd met de namen van de database en eigenaar. De volgende code is hetzelfde voorbeeld, behalve dat er tabelaliassen zijn toegewezen en dat de kolommen met tabelaliassen zijn gekwalificeerd om de leesbaarheid te verbeteren:

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

In de vorige voorbeelden zijn de joinvoorwaarden in de FROM component opgegeven. Dit is de voorkeursmethode. De volgende query bevat dezelfde joinvoorwaarde die is opgegeven in de WHERE component:

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%';

De SELECT lijst voor een join kan verwijzen naar alle kolommen in de gekoppelde tabellen of naar een subset van de kolommen. De SELECT lijst hoeft geen kolommen uit elke tabel in de join te bevatten. In een join met drie tabellen kan bijvoorbeeld slechts één tabel worden gebruikt om een brug te maken tussen een van de andere tabellen en de derde tabel en hoeft geen van de kolommen uit de middelste tabel te worden verwezen in de selectielijst. Dit wordt ook wel een anti semi-join genoemd.

Hoewel joinvoorwaarden meestal gelijkheidsvergelijkingen (=) hebben, kunnen andere vergelijkings- of relationele operatoren worden opgegeven, zoals andere predicaten. Zie Vergelijkingsoperatoren en WHERE voor meer informatie.

Wanneer SQL Server-processen worden samengevoegd, kiest de Query Optimizer de meest efficiënte methode (uit verschillende mogelijkheden) voor het verwerken van de join. Dit omvat het kiezen van het meest efficiënte type fysieke join, de volgorde waarin de tabellen worden samengevoegd en zelfs het gebruik van typen logische joinbewerkingen die niet rechtstreeks kunnen worden uitgedrukt met Transact-SQL syntaxis, zoals semi-joins en anti-semi-joins. De fysieke uitvoering van verschillende joins kan veel verschillende optimalisaties gebruiken en kan daarom niet betrouwbaar worden voorspeld. Zie De referentie voor logische en fysieke showplanoperatoren voor meer informatie over semi-joins en anti-semi-joins.

Kolommen die in een joinvoorwaarde worden gebruikt, hoeven niet dezelfde naam te hebben of hetzelfde gegevenstype te hebben. Als de gegevenstypen echter niet identiek zijn, moeten ze compatibel zijn of typen zijn die sql Server impliciet kan converteren. Als de gegevenstypen niet impliciet kunnen worden geconverteerd, moet de joinvoorwaarde het gegevenstype expliciet converteren met behulp van de CAST functie. Zie Conversie van gegevenstypen (Database Engine) voor meer informatie over impliciete en expliciete conversies.

De meeste query's die een join gebruiken, kunnen opnieuw worden geschreven met behulp van een subquery (een query die is genest binnen een andere query) en de meeste subquery's kunnen als joins worden herschreven. Zie Subquery's (SQL Server) voor meer informatie over subquery's.

Geneste lusverbindingen begrijpen

Als de ene invoer klein is (minder dan 10 rijen) en de andere invoer redelijk groot is en geïndexeerd op de joinkolommen, is een geneste lussen-join de snelste joinbewerking, omdat deze de minste I/O en de minste vergelijkingen vereisen.

De geneste lussen join, ook wel geneste iteratie genoemd, gebruikt één join-invoer als de buitenste invoertabel (weergegeven als de bovenste invoer in het grafische uitvoeringsplan) en één als de binnenste (onderste) invoertabel. De buitenste lus gebruikt de rij van de buitenste invoertabel per rij. De binnenste lus, uitgevoerd voor elke buitenste rij, zoekt naar overeenkomende rijen in de interne invoertabel.

In het eenvoudigste geval scant de zoekopdracht een hele tabel of index; dit wordt een naive nested loops join genoemd. Als de zoekopdracht misbruik maakt van een index, wordt dit een geneste lusdeelname van een index genoemd. Als de index is gebouwd als onderdeel van het queryplan (en vernietigd na voltooiing van de query), wordt deze een tijdelijke geneste lussen-join genoemd. Al deze varianten worden beschouwd door de queryoptimalisatie.

Een geneste lus-join is met name effectief als de buitenste gegevensset klein is en de binnenste gegevensset vooraf geïndexeerd en groot is. In veel kleine transacties, zoals transacties die alleen van invloed zijn op een kleine set rijen, zijn geneste lussen verbindingen superieur aan zowel samenvoeg-verbindingen als hash-verbindingen. In grote queries zijn geneste lusvervoegingen echter vaak niet de optimale keuze.

Wanneer het kenmerk OPTIMIZED van een Geneste Lussen join-operator is ingesteld op True, betekent dit dat een geoptimaliseerde Geneste Lussen (of batch sortering) wordt gebruikt om I/O te minimaliseren wanneer de tabel aan de binnenkant groot is, ongeacht of deze is geparallelliseerd of niet. De aanwezigheid van deze optimalisatie in een bepaald plan is mogelijk niet erg duidelijk bij het analyseren van een uitvoeringsplan, gezien het sorteren zelf een verborgen bewerking is. Maar door te zoeken in de plan-XML voor het kenmerk GEOPTIMALISEERD, geeft dit aan dat de join geneste lussen de volgorde van de invoerrijen kan wijzigen om de I/O-prestaties te verbeteren.

Joins samenvoegen

Als de twee join-invoer niet klein zijn, maar zijn gesorteerd op de joinkolom (bijvoorbeeld als ze zijn verkregen door gesorteerde indexen te scannen), is een samenvoegbewerking de snelste joinbewerking. Als beide join-invoer groot is en de twee invoer vergelijkbare grootten hebben, biedt een merge join met voorafgaande sortering en een hash join vergelijkbare prestaties. Hash join-bewerkingen zijn echter vaak veel sneller als de twee invoergrootten aanzienlijk van elkaar verschillen.

Voor de samenvoeging moeten beide invoerwaarden worden gesorteerd op de samenvoegkolommen, die worden gedefinieerd door de gelijkheidsclausulesON van het joinpredicaat. De queryoptimizer scant doorgaans een index, als deze bestaat op de juiste set kolommen, of plaatst een sorteeroperator onder de merge join. In zeldzame gevallen kunnen er meerdere gelijkheidsclausules zijn, maar de samenvoegkolommen zijn afkomstig van slechts enkele beschikbare gelijkheidsclausules.

Omdat elke invoer wordt gesorteerd, krijgt de operator Join samenvoegen een rij van elke invoer en worden deze vergeleken. Voor inner join-bewerkingen worden de rijen bijvoorbeeld geretourneerd als ze gelijk zijn. Als ze niet gelijk zijn, wordt de rij met lagere waarden verwijderd en wordt er een andere rij verkregen uit die invoer. Dit proces wordt herhaald totdat alle rijen zijn verwerkt.

De samenvoegbewerking is een reguliere bewerking of een veel-op-veel-bewerking. Een veel-op-veel-samenvoeging maakt gebruik van een tijdelijke tabel om rijen op te slaan. Als er dubbele waarden uit elke invoer zijn, moet een van de invoerwaarden terugspoelen naar het begin van de duplicaten, omdat elk duplicaat van de andere invoer wordt verwerkt.

Als er een restpredicaat aanwezig is, worden alle rijen die voldoen aan het samenvoegpredicaat geëvalueerd op het restpredicaat, en alleen die rijen die eraan voldoen worden geretourneerd.

Samenvoegen zelf is zeer snel, maar het kan een dure keuze zijn als sorteerbewerkingen vereist zijn. Als het gegevensvolume echter groot is en de gewenste gegevens kunnen worden verkregen uit bestaande B-tree-indexen, is merge join vaak het snelste beschikbare join-algoritme.

Hash-joins

Hash-joins kunnen efficiënt grote, niet-gesorteerde, niet-geïndexeerde invoer verwerken. Ze zijn handig voor tussenliggende resultaten in complexe query's, omdat:

• Tussenliggende resultaten worden niet geïndexeerd (tenzij expliciet opgeslagen op schijf en vervolgens geïndexeerd) en worden vaak niet geschikt gesorteerd voor de volgende bewerking in het queryplan.
• Queryoptimalisaties schatten alleen tussenliggende resultaatgrootten. Omdat schattingen zeer onnauwkeurig kunnen zijn voor complexe query's, moeten algoritmen voor het verwerken van tussenliggende resultaten niet alleen efficiënt zijn, maar moeten ze ook probleemloos afnemen als een tussenliggend resultaat veel groter blijkt te zijn dan verwacht.

De hash-join maakt verminderingen mogelijk bij het gebruik van denormalisatie. Denormalisatie wordt doorgaans gebruikt om betere prestaties te bereiken door samenvoegbewerkingen te verminderen, ondanks de gevaren van redundantie, zoals inconsistente updates. Hash-joins verminderen de noodzaak om te denormaliseren. Met hash-joins kunnen verticale partitionering (die groepen kolommen uit één tabel in afzonderlijke bestanden of indexen vertegenwoordigen) een bruikbare optie worden voor het ontwerpen van fysieke databases.

De hash-join heeft twee invoerwaarden: de build-invoer en testinvoer . De query optimizer wijst deze rollen toe, zodat de kleinere van de twee invoeren als de build-invoer fungeert.

Hash-joins worden gebruikt voor veel soorten setkoppelingsbewerkingen: inner join; left, right en full outer join; semi-join links en rechts; kruispunt; unie; en verschil. Bovendien kan een variant van de hash-join dubbele verwijdering en groepering uitvoeren, zoals SUM(salary) GROUP BY department. Deze wijzigingen gebruiken slechts één invoer voor zowel de build- als de probefuncties.

In de volgende secties worden verschillende typen hash-joins beschreven: hash-join in het geheugen, respijt-hash-join en recursieve hash-join.

Hash-join in het geheugen

De hash-join scant of berekent eerst de volledige build-invoer en bouwt vervolgens een hash-tabel in het geheugen. Elke rij wordt ingevoegd in een hash-bucket, afhankelijk van de hashwaarde die is berekend voor de hash-sleutel. Als de volledige build-invoer kleiner is dan het beschikbare geheugen, kunnen alle rijen worden ingevoegd in de hash-tabel. Deze buildfase wordt gevolgd door de verkenningsfase. De volledige testinvoer wordt één rij tegelijk gescand of berekend. Voor elke testrij wordt de waarde van de hash-sleutel berekend, wordt de bijbehorende hash-bucket gescand en worden de overeenkomsten geproduceerd.

Grace hash join

Als de build-invoer niet in het geheugen past, wordt een hash-join in verschillende stappen uitgevoerd. Dit wordt een grace hash join genoemd. Elke stap heeft een bouwfase en een proeffase. Aanvankelijk worden de volledige build- en probeerinput gebruikt en verdeeld (met behulp van een hash-functie op de hashsleutels) in meerdere bestanden. Als u de hash-functie op de hashsleutels gebruikt, zorgt u ervoor dat twee samenvoegingsrecords zich in hetzelfde paar bestanden moeten bevinden. Daarom is de taak van het samenvoegen van twee grote invoerwaarden beperkt tot meerdere, maar kleinere exemplaren van dezelfde taken. De hash-join wordt vervolgens toegepast op elk paar gepartitioneerde bestanden.

Recursieve hashjoin

Als de build-invoer zo groot is dat invoer voor een standaard externe samenvoeging meerdere samenvoegniveaus vereist, zijn er meerdere partitioneringsstappen en meerdere partitioneringsniveaus vereist. Als slechts enkele partities groot zijn, worden er extra partitioneringsstappen gebruikt voor alleen die specifieke partities. Om alle partitioneringsstappen zo snel mogelijk te laten verlopen, worden grote, asynchrone I/O-bewerkingen gebruikt, zodat één thread meerdere schijfstations bezet kan houden.

Het is niet altijd mogelijk tijdens optimalisatie om te bepalen welke hash-join wordt gebruikt. Daarom begint SQL Server met een in-memory hash join en schakelt geleidelijk over naar een grace hash join en recursieve hash join, afhankelijk van de grootte van de build-input.

Als de Query Optimizer verkeerd inschat welke van de twee invoerwaarden kleiner is en daarom de build-invoer zou moeten zijn, worden de build- en probe-rollen dynamisch omgekeerd. De hash-join zorgt ervoor dat het kleinere overloopbestand wordt gebruikt als build-invoer. Deze techniek wordt omkering van rollen genoemd. Rolomkering vindt plaats in de hash-join na ten minste één overloop naar de schijf.

Hash-borgtocht

De term hash-bailout wordt soms gebruikt om grace-hash-joins of recursieve-hash-joins te beschrijven.

Zie Gebeurtenisklasse hashwaarschuwing voor meer informatie over hash-bailout.

Adaptieve joins

Batchmodus Met adaptieve joins kunt u kiezen uit een hash-join - of geneste lussen-joinmethodetotdat de eerste invoer is gescand. De Adaptive Join-operator definieert een drempelwaarde die wordt gebruikt om te bepalen wanneer moet worden overgeschakeld naar een plan voor geneste lussen. Een queryplan kan daarom dynamisch overschakelen naar een betere joinstrategie tijdens de uitvoering zonder opnieuw te worden gecompileerd.

De runtime-beslissing is gebaseerd op de volgende stappen:

• Als het aantal rijen van de invoer van de build-join klein genoeg is dat een Geneste Lussen-join beter zou zijn dan een Hash-join, schakelt het plan over naar het Geneste Lussen-algoritme.
• Als de invoer van de build-join een specifieke drempelwaarde voor het aantal rijen overschrijdt, vindt er geen switch plaats en gaat uw plan verder met een Hash-join.

De volgende query wordt gebruikt om een voorbeeld van adaptieve join te illustreren:

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;

De query retourneert 336 rijen. Als u Live Query Statistics inschakelt, wordt het volgende plan weergegeven:

Let op het volgende in het plan:

1. Een columnstore-indexscan die wordt gebruikt om rijen te bieden voor de buildfase voor hash-joins.
2. De nieuwe adaptieve join-operator. Deze operator definieert een drempelwaarde die wordt gebruikt om te bepalen wanneer moet worden overgeschakeld naar een uitvoeringsplan met geneste lussen. In dit voorbeeld is de drempelwaarde 78 rijen. Alles met >= 78 rijen gebruikt een Hash-join. Als een waarde onder de drempel ligt, wordt een geneste-lussen-join gebruikt.
3. Omdat de query 336 rijen retourneert, is de drempelwaarde overschreden. De tweede vertakking vertegenwoordigt dus de testfase van een standaard-hash-joinbewerking. Live Query Statistics toont rijen die door de operators stromen, in dit geval '672 van 672'.
4. En de laatste vertakking is een geclusterd index zoeken voor gebruik door de geneste lussen join, indien de drempelwaarde niet is overschreden. Er worden '0 van 336'-rijen weergegeven (de vertakking is ongebruikt).

Vergelijk nu het plan met dezelfde query, maar als de Quantity waarde slechts één rij in de tabel heeft:

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;

De query retourneert één rij. Als u Live Query Statistics inschakelt, wordt het volgende plan weergegeven:

Let op het volgende in het plan:

• Als er één rij wordt geretourneerd, bevat het geclusterde indexzoeken nu rijen die er doorheen stromen.
• En omdat de buildfase hash-join niet is voortgezet, lopen er geen rijen door de tweede vertakking.

Opmerkingen bij adaptieve join

Adaptieve joins introduceren een hogere geheugenvereiste dan een geïndexeerd geneste loops Join-equivalent plan. Het extra geheugen wordt aangevraagd alsof de geneste lussen een hash-join waren. Er is ook overhead voor de buildfase als een stop-and-go-bewerking versus een geneste lussen die equivalente join streamen. Met die extra kosten komt flexibiliteit voor scenario's waarin het aantal rijen in de build-input fluctueert.

Adaptieve joins in batchmodus werken voor de eerste uitvoering van een instructie en zodra deze zijn gecompileerd, blijven opeenvolgende uitvoeringen adaptief op basis van de gecompileerde drempelwaarde voor Adaptieve join en de runtimerijen die door de buildfase van de buitenste invoer stromen.

Als een adaptieve join overschakelt naar een Geneste Lussen-operatie, gebruikt het de rijen die al zijn gelezen door de Hash Join-constructie. De operator leest de buitenste verwijzingsrijen niet opnieuw.

Adaptieve join-activiteit bijhouden

De Adaptive Join-operator heeft de volgende operator-eigenschappen voor plannen:

In het geschatte plan ziet u de planvorm van Adaptive Join, samen met een gedefinieerde drempelwaarde voor Adaptive Join en het geschatte jointype.

In aanmerking komende statements voor adaptieve joins

Een paar voorwaarden maken een logische join geschikt voor een batch mode Adaptive Join:

• Het compatibiliteitsniveau van de database is 140 of hoger.
• De query is een SELECT instructie (gegevenswijzigingsinstructies komen momenteel niet in aanmerking).
• De join kan worden uitgevoerd door een geïndexeerde geneste lussen-join of een fysiek hash-join-algoritme.
• De hash-join maakt gebruik van de Batch-modus, ingeschakeld via de aanwezigheid van een columnstore-index in de query in het algemeen, een columnstore geïndexeerde tabel waarnaar rechtstreeks wordt verwezen door de join of via het gebruik van de Batch-modus in rowstore.
• De gegenereerde alternatieve benaderingen van de Nested Loops join en Hash join moeten dezelfde eerste onderliggende (outer reference) hebben.

Adaptieve drempelwaarden per rij

In de volgende grafiek ziet u een voorbeeld van een snijpunt tussen de kosten van een Hash-join versus de kosten van een alternatief voor geneste lussen. Op dit snijpunt wordt de drempelwaarde bepaald die op zijn beurt het daadwerkelijke algoritme bepaalt dat wordt gebruikt voor de joinbewerking.

Adaptieve joins uitschakelen zonder het compatibiliteitsniveau te wijzigen

Adaptieve joins kunnen worden uitgeschakeld op het database- of verklaringsbereik terwijl het databasecompatibiliteitsniveau op niveau 140 en hoger blijft.

Als u adaptieve joins wilt uitschakelen voor alle queryuitvoeringen die afkomstig zijn van de database, voert u het volgende uit binnen de context van de toepasselijke database:

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

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

Wanneer deze instelling is ingeschakeld, wordt deze instelling weergegeven als ingeschakeld in sys.database_scoped_configurations.

Als u adaptieve joins opnieuw wilt inschakelen voor alle queryuitvoeringen die afkomstig zijn van de database, voert u het volgende uit binnen de context van de toepasselijke database:

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

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

Adaptieve joins kunnen ook worden uitgeschakeld voor een specifieke query door deze aan te wijzen DISABLE_BATCH_MODE_ADAPTIVE_JOINS als hint voor de USE HINT-query. Voorbeeld:

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'));

Null-waarden en joins

Wanneer er null-waarden zijn in de kolommen van de tabellen die worden samengevoegd, komen de null-waarden niet overeen met elkaar. De aanwezigheid van null-waarden in een kolom uit een van de tabellen die worden gekoppeld, kan alleen worden geretourneerd met behulp van een outer join (tenzij de WHERE component null-waarden uitsluit).

Hier volgen twee tabellen die elk een kolom hebben met NULL die deelnemen aan de join:

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

Een join die de waarden in de kolom a vergelijkt met de kolom c , krijgt geen overeenkomst voor de kolommen met waarden van NULL:

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

Er wordt slechts één rij met de waarde van 4 in de kolommen a en c geretourneerd.

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

(1 row(s) affected)

Null-waarden die worden geretourneerd uit een basistabel, zijn ook moeilijk te onderscheiden van de null-waarden die worden geretourneerd door een outer join. Met de volgende SELECT instructie wordt bijvoorbeeld een left outer join uitgevoerd op deze twee tabellen:

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

Hier is het resultatenoverzicht.

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

(3 row(s) affected)

De resultaten maken het niet eenvoudig om een NULL in de gegevens te onderscheiden van een NULL gegevens die een fout aangeeft bij het samenvoegen. Wanneer NULL waarden aanwezig zijn in gegevens die worden samengevoegd, is het meestal beter om ze weg te laten uit de resultaten met behulp van een gewone join.

Verwante inhoud

• Logische en fysieke referenties van showplan-operatoren
• Vergelijkingsoperatoren (Transact-SQL)
• Gegevenstypeconversie (database-engine)
• Subquery's (SQL Server)
• Adaptieve joins
• FROM-clausule plus JOIN, APPLY, PIVOT (Transact-SQL)