I have an SQL view on SQL 2016 SP2 that joins on 14 tables with no predicate.
WHEN dsp.DimSourceSystemID=2 THEN edsp.CyberExposureGroup
WHEN dsp.DimSourceSystemID=4 THEN vdsp.CyberExposureGroup
ELSE 'Unknown' END AS CyberExposureGroup
FROM dwh.DimPolicy dsp
INNER JOIN Eclipse.DimPolicyExt edsp
ON dsp.DimEclipsePolicyExtID = edsp.DimEclipsePolicyExtID
INNER JOIN Velocity.DimPolicyExt vdsp
ON dsp.DimVelocityPolicyExtID = vdsp.DimVelocityPolicyExtID
INNER JOIN DWH.DimProgramme dp
ON dsp.DimProgrammeID = dp.DimProgrammeID
INNER JOIN DWH.DimPricingLeaderStatus dpls
ON edsp.DimPricingLeaderStatusID = dpls.DimPricingLeaderStatusID
INNER JOIN DWH.DimPlacingBasis dpb
ON dsp.DimPlacingBasisID = dpb.DimPlacingBasisID
INNER JOIN DWH.DimCoverageBasis dcb
ON dsp.DimCoverageBasisID = dcb.DimCoverageBasisID
--INNER JOIN DWH.DimNewRenewed dnr
-- ON dsp.DimNewRenewedID = dnr.DimNewRenewedID
INNER JOIN DWH.DimSubClass dsc
ON edsp.DimSubClassID = dsc.DimSubClassID
INNER JOIN DWH.DimStatsMinorClass dsminc
ON edsp.DimStatsMinorClassID = dsminc.DimStatsMinorClassID
INNER JOIN DWH.DimStatsMajorClass dsmajc
ON edsp.DimStatsMajorClassID = dsmajc.DimStatsMajorClassID
----------------------R5--------------------------
INNER JOIN DWH.DimCoverholder cv
ON edsp.DimCoverholderID = cv.DimCoverholderID
----------------------R7--------------------------
LEFT JOIN dwh.DimPlacingBasis MDPB
ON MDPB.MasterDataPlacingBasis = edsp.MasterPlacingBasis
INNER JOIN DWH.DimAssured da
ON dsp.DimAssuredID = da.DimAssuredID
--Underwriter Information R15
INNER JOIN DWH.DimUnderwriter duw
ON dsp.DimUnderwriterID = duw.DimUnderwriterID
I want to use the loop join hint so that the query will have less parallelism overhead.
WHEN dsp.DimSourceSystemID=2 THEN edsp.CyberExposureGroup
WHEN dsp.DimSourceSystemID=4 THEN vdsp.CyberExposureGroup
ELSE 'Unknown' END AS CyberExposureGroup
FROM dwh.DimPolicy dsp
INNER LOOP JOIN Eclipse.DimPolicyExt edsp
ON dsp.DimEclipsePolicyExtID = edsp.DimEclipsePolicyExtID
INNER LOOP JOIN Velocity.DimPolicyExt vdsp
ON dsp.DimVelocityPolicyExtID = vdsp.DimVelocityPolicyExtID
INNER LOOP JOIN DWH.DimProgramme dp
ON dsp.DimProgrammeID = dp.DimProgrammeID
INNER LOOP JOIN DWH.DimPricingLeaderStatus dpls
ON edsp.DimPricingLeaderStatusID = dpls.DimPricingLeaderStatusID
INNER LOOP JOIN DWH.DimPlacingBasis dpb
ON dsp.DimPlacingBasisID = dpb.DimPlacingBasisID
INNER LOOP JOIN DWH.DimCoverageBasis dcb
ON dsp.DimCoverageBasisID = dcb.DimCoverageBasisID
--INNER JOIN DWH.DimNewRenewed dnr
-- ON dsp.DimNewRenewedID = dnr.DimNewRenewedID
INNER LOOP JOIN DWH.DimSubClass dsc
ON edsp.DimSubClassID = dsc.DimSubClassID
INNER LOOP JOIN DWH.DimStatsMinorClass dsminc
ON edsp.DimStatsMinorClassID = dsminc.DimStatsMinorClassID
INNER LOOP JOIN DWH.DimStatsMajorClass dsmajc
ON edsp.DimStatsMajorClassID = dsmajc.DimStatsMajorClassID
----------------------R5--------------------------
INNER LOOP JOIN DWH.DimCoverholder cv
ON edsp.DimCoverholderID = cv.DimCoverholderID
----------------------R7--------------------------
LEFT LOOP JOIN dwh.DimPlacingBasis MDPB
ON MDPB.MasterDataPlacingBasis = edsp.MasterPlacingBasis
INNER LOOP JOIN DWH.DimAssured da
ON dsp.DimAssuredID = da.DimAssuredID
--Underwriter Information R15
INNER LOOP JOIN DWH.DimUnderwriter duw
ON dsp.DimUnderwriterID = duw.DimUnderwriterID
This works and the query runs faster at the expense of more IO, however to my surprise I get slightly different row counts for the two different versions of the view. 7,877,287 rows compared to 7,877,285 rows. This is a data warehouse but there are no inserts going on. So do join hints affect the overall number of rows or could this be a bug?
