By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DataToSearch (
data_id INT IDENTITY(1,1)
CONSTRAINT PK_TableToSearch PRIMARY KEY,
col1 INT,
col2 INT,
col3 INT,
)
-- Mix of index with different column orders.
-- We do not attempt to generate all N-factorial permutations, so some lookups may
-- have less than optimal index usage (but will still be better than a table scan).
CREATE INDEX IX_DataToSearch_col1_col2_col3 ON DataToSearch(col1, col2, col3)
CREATE INDEX IX_DataToSearch_col2_col3_col1 ON DataToSearch(col2, col3, col1)
CREATE INDEX IX_DataToSearch_col3_col1_col2 ON DataToSearch(col3, col1, col2)
-- 3 x combinations of {null, 1..9). 1000 rows total
INSERT INTO DataToSearch
SELECT NULLIF(S1.value, 0), NULLIF(S2.value, 0), NULLIF(S3.value, 0)
FROM GENERATE_SERIES(0, 9) S1
CROSS APPLY GENERATE_SERIES(0, 9) S2
CROSS APPLY GENERATE_SERIES(0, 9) S3
CREATE TABLE SearchCriteria (
search_id INT IDENTITY(1,1)
CONSTRAINT PK_ValuesToFind PRIMARY KEY,
col1 INT,
col2 INT,
col3 INT
)
INSERT INTO SearchCriteria
VALUES
(1, 2, 3), -- 1 match
(4, 5, null), -- 10 matches
(null, 6, 7) -- 10 matches
| search_id | col1 | col2 | col3 |
|---|---|---|---|
| 1 | 1 | 2 | 3 |
| 2 | 4 | 5 | null |
| 3 | null | 6 | 7 |
SET STATISTICS XML ON
-- Dynamic SQL approach using a single query with embedded search values.
-- This will perform index seeks for nearly all cases, depending on index availability.
-- Even suboptimal cases are much better than table scans.
DECLARE @NL CHAR = CHAR(10) -- newline
DECLARE @UnionAll NVARCHAR(100) = @NL + 'UNION ALL' + @NL
DECLARE @sql NVARCHAR(MAX) = (
SELECT STRING_AGG(Q.Sql, @UnionAll) WITHIN GROUP(ORDER BY S.search_id)
--SELECT *
FROM SearchCriteria S
CROSS APPLY (
SELECT CONCAT(
CAST('' AS NVARCHAR(MAX)),
'SELECT ', S.search_id, ' AS search_id, D.data_id, D.col1, D.col2, D.col3',
@NL, 'FROM DataToSearch D',
@NL, 'WHERE 1 = 1',
-- The following lines will either generate a "AND condition" line
-- or null (no condition) for cases where the search value is null.
-- Use the following for exact numeric values (excluding real/float)
-- If used for text values, the following is open to SQL Injection
(@NL + 'AND D.col1 = ' + CAST(S.col1 AS VARCHAR(30))),
(@NL + 'AND D.col2 = ' + CAST(S.col2 AS VARCHAR(30))),
(@NL + 'AND D.col3 = ' + CAST(S.col3 AS VARCHAR(30))),
-- Use the following for text values (limit 128 characters)
--(@NL + 'AND D.col1 = N' + QUOTENAME(S.col1, '''')),
--(@NL + 'AND D.col2 = N' + QUOTENAME(S.col2, '''')),
--(@NL + 'AND D.col3 = N' + QUOTENAME(S.col3, '''')),
-- Use the following for text values (possibly longer than 128 characters)
--(@NL + 'AND D.col1 = N''' + REPLACE(S.col1, '''', '''''') + ''''),
--(@NL + 'AND D.col2 = N''' + REPLACE(S.col2, '''', '''''') + ''''),
--(@NL + 'AND D.col3 = N''' + REPLACE(S.col3, '''', '''''') + ''''),
-- Use an appropriate mix of the above, if the columns have mixed types
''
) AS Sql
) Q
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @sql NVARCHAR(MAX) = (
 SELECT STRING_AGG(Q.Sql, @UnionAll) WITHIN GROUP(ORDER BY S.search_id)
 --SELECT *
 FROM SearchCriteria S
 CROSS APPLY (
 SELECT CONCAT(
 CAST('' AS NVARCHAR(MAX)),
 'SELECT ', S.search_id, ' AS search_id, D.data_id, D.col1, D.col2, D.col3',
 @NL, 'FROM DataToSearch D',
 @NL, 'WHERE 1 = 1',
 -- The following lines will either generate a "AND condition" line
 -- or null (no condition) for cases where the search value is null.
 -- Use the following for exact numeric values (excluding real/float)
 -- If used for text values, the following is open to SQL Injection
 (@NL + 'AND D.col1 = ' + CAST(S.col1 AS VARCHAR(30))),
 (@NL + 'AND D.col2 = ' + CAST(S.col2 AS VARCHAR(30))),
 (@NL + 'AND D.col3 = ' + CAST(S.col3 AS VARCHAR(30))),
 -- Use the following for text values (limit 128 characters)
 --(@NL + 'AND D.col1 = N' + QUOTENAME(S.col1, '''')),
 --(@NL + 'AND D.col2 = N' + QUOTENAME(S.col2, '''')),
 --(@NL + 'AND D.col3 = N' + QUOTENAME(S.col3, '''')),
 -- Use the following for text values (possibly longer than 128 characters)
 --(@NL + 'AND D.col1 = N''' + REPLACE(S.col1, '''', '''''') + ''''),
 --(@NL + 'AND D.col2 = N''' + REPLACE(S.col2, '''', '''''') + ''''),
 --(@NL + 'AND D.col3 = N''' + REPLACE(S.col3, '''', '''''') + ''''),
 -- Use an appropriate mix of the above, if the columns have mixed types
 ''
 ) AS Sql
 ) Q
)" StatementId="1" StatementCompId="3" StatementType="SELECT" StatementSqlHandle="0x0900796701352098E6BA35BF4D791CB6DEEC0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.003288" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x1C4DFD1807665139" QueryPlanHash="0x18F2683C04AA55B9" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="376"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[S].[col3],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[S].[col2],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[S].[col1],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(max),[S].[search_id],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.003288" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="[Expr1002]"><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="2.3e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0032879" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="STRING_AGG(concat(CONVERT(nvarchar(max),'',0),CONVERT_IMPLICIT(nvarchar(max),'SELECT ',0),[Expr1019],CONVERT_IMPLICIT(nvarchar(max),' AS search_id, D.data_id, D.col1, D.col2, D.col3',0),CONVERT_IMPLICIT(nvarchar(max),[@NL],0),CONVERT_IMPLICIT(nvarchar(max),'FROM DataToSearch D',0),CONVERT_IMPLICIT(nvarchar(max),[@NL],0),CONVERT_IMPLICIT(nvarchar(max),'WHERE 1 = 1',0),CONVERT_IMPLICIT(nvarchar(max),[@NL]+'AND D.col1 = '+[Expr1018],0),CONVERT_IMPLICIT(nvarchar(max),[@NL]+'AND D.col2 = '+[Expr1017],0),CONVERT_IMPLICIT(nvarchar(max),[@NL]+'AND D.col3 = '+[Expr1016],0),CONVERT_IMPLICIT(nvarchar(max),'',0)),[@UnionAll])WITHIN GROUP (ORDER BY [S].[search_id])"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Intrinsic FunctionName="concat"><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1015"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="0"><ScalarOperator><Const ConstValue="''"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1014"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="'SELECT '"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1019"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1013"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="' AS search_id, D.data_id, D.col1, D.col2, D.col3'"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1012"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@NL"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1011"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="'FROM DataToSearch D'"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1010"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@NL"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1009"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="'WHERE 1 = 1'"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1008"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="@NL"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'AND D.col1 = '"></Const></ScalarOperator></Arithmetic></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1018"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1007"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="@NL"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'AND D.col2 = '"></Const></ScalarOperator></Arithmetic></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1017"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1006"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="@NL"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'AND D.col3 = '"></Const></ScalarOperator></Arithmetic></ScalarOperator></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1016"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1005"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="''"></Const></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@UnionAll"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-07" AvgRowSize="4090" EstimatedTotalSubtreeCost="0.0032856" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="search_id"></ColumnReference><ColumnReference Column="Expr1016"></ColumnReference><ColumnReference Column="Expr1017"></ColumnReference><ColumnReference Column="Expr1018"></ColumnReference><ColumnReference Column="Expr1019"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1016"></ColumnReference><ScalarOperator ScalarString="CONVERT(varchar(30),[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [S].[col3],0)"><Convert DataType="varchar" Length="30" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1017"></ColumnReference><ScalarOperator ScalarString="CONVERT(varchar(30),[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [S].[col2],0)"><Convert DataType="varchar" Length="30" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1018"></ColumnReference><ScalarOperator ScalarString="CONVERT(varchar(30),[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [S].[col1],0)"><Convert DataType="varchar" Length="30" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1019"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(max),[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[search_id] as [S].[search_id],0)"><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="search_id"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[S]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" Alias="[S]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@sql" ParameterDataType="nvarchar(max)" ParameterRuntimeValue="N'SELECT 1 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 1
AND D.col2 = 2
AND D.col3 = 3
UNION ALL
SELECT 2 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 4
AND D.co'"></ColumnReference><ColumnReference Column="@UnionAll" ParameterDataType="nvarchar(100)" ParameterRuntimeValue="N'
UNION ALL
'"></ColumnReference><ColumnReference Column="@NL" ParameterDataType="char(1)" ParameterRuntimeValue="'
'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
DECLARE @sql NVARCHAR(MAX) = (
SELECT STRING_AGG(Q.Sql, @UnionAll) WITHIN GROUP(ORDER BY S.search_id)
--SELECT *
FROM SearchCriteria S
CROSS APPLY (
SELECT CONCAT(
CAST('' AS NVARCHAR(MAX)),
'SELECT ', S.search_id, ' AS search_id, D.data_id, D.col1, D.col2, D.col3',
@NL, 'FROM DataToSearch D',
@NL, 'WHERE 1 = 1',
-- The following lines will either generate a "AND condition" line
-- or null (no condition) for cases where the search value is null.
-- Use the following for exact numeric values (excluding real/float)
-- If used for text values, the following is open to SQL Injection
(@NL + 'AND D.col1 = ' + CAST(S.col1 AS VARCHAR(30))),
(@NL + 'AND D.col2 = ' + CAST(S.col2 AS VARCHAR(30))),
(@NL + 'AND D.col3 = ' + CAST(S.col3 AS VARCHAR(30))),
-- Use the following for text values (limit 128 characters)
--(@NL + 'AND D.col1 = N' + QUOTENAME(S.col1, '''')),
--(@NL + 'AND D.col2 = N' + QUOTENAME(S.col2, '''')),
--(@NL + 'AND D.col3 = N' + QUOTENAME(S.col3, '''')),
-- Use the following for text values (possibly longer than 128 characters)
--(@NL + 'AND D.col1 = N''' + REPLACE(S.col1, '''', '''''') + ''''),
--(@NL + 'AND D.col2 = N''' + REPLACE(S.col2, '''', '''''') + ''''),
--(@NL + 'AND D.col3 = N''' + REPLACE(S.col3, '''', '''''') + ''''),
-- Use an appropriate mix of the above, if the columns have mixed types
''
) AS Sql
) Q
)
SELECT
SELECT
| Cached plan size | 40 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.003288 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
DECLARE @sql NVARCHAR(MAX) = (
SELECT STRING_AGG(Q.Sql, @UnionAll) WITHIN GROUP(ORDER BY S.search_id)
--SELECT *
FROM SearchCriteria S
CROSS APPLY (
SELECT CONCAT(
CAST('' AS NVARCHAR(MAX)),
'SELECT ', S.search_id, ' AS search_id, D.data_id, D.col1, D.col2, D.col3',
@NL, 'FROM DataToSearch D',
@NL, 'WHERE 1 = 1',
-- The following lines will either generate a "AND condition" line
-- or null (no condition) for cases where the search value is null.
-- Use the following for exact numeric values (excluding real/float)
-- If used for text values, the following is open to SQL Injection
(@NL + 'AND D.col1 = ' + CAST(S.col1 AS VARCHAR(30))),
(@NL + 'AND D.col2 = ' + CAST(S.col2 AS VARCHAR(30))),
(@NL + 'AND D.col3 = ' + CAST(S.col3 AS VARCHAR(30))),
-- Use the following for text values (limit 128 characters)
--(@NL + 'AND D.col1 = N' + QUOTENAME(S.col1, '''')),
--(@NL + 'AND D.col2 = N' + QUOTENAME(S.col2, '''')),
--(@NL + 'AND D.col3 = N' + QUOTENAME(S.col3, '''')),
-- Use the following for text values (possibly longer than 128 characters)
--(@NL + 'AND D.col1 = N''' + REPLACE(S.col1, '''', '''''') + ''''),
--(@NL + 'AND D.col2 = N''' + REPLACE(S.col2, '''', '''''') + ''''),
--(@NL + 'AND D.col3 = N''' + REPLACE(S.col3, '''', '''''') + ''''),
-- Use an appropriate mix of the above, if the columns have mixed types
''
) AS Sql
) Q
)
Warnings
Type conversion in expression (CONVERT(varchar(30),[S].[col3],0)) may affect "Cardinality Estimate" in query plan choice.
Type conversion in expression (CONVERT(varchar(30),[S].[col2],0)) may affect "Cardinality Estimate" in query plan choice.
Type conversion in expression (CONVERT(varchar(30),[S].[col1],0)) may affect "Cardinality Estimate" in query plan choice.
Type conversion in expression (CONVERT_IMPLICIT(nvarchar(max),[S].[search_id],0)) may affect "Cardinality Estimate" in query plan choice.
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.003288 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4035 B |
| Node ID | 0 |
Output List
Expr1004
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
| Physical Operation | Stream Aggregate |
|---|---|
| Logical Operation | Aggregate |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000023 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000023 |
| Estimated Subtree Cost | 0.0032879 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4035 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
Expr1002
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000003 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000003 |
| Estimated Subtree Cost | 0.0032856 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 4090 B |
| Node ID | 2 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[S].search_id
Expr1016
Expr1017
Expr1018
Expr1019
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind].…
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 3 |
| Actual Number of Rows | 3 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 3 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[S].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[S].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[S].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[S].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind].[S]
| search_id | data_id | col1 | col2 | col3 |
|---|---|---|---|---|
| 1 | 313 | 1 | 2 | 3 |
| 2 | 46 | 4 | 5 | null |
| 2 | 146 | 4 | 5 | 1 |
| 2 | 246 | 4 | 5 | 2 |
| 2 | 346 | 4 | 5 | 3 |
| 2 | 446 | 4 | 5 | 4 |
| 2 | 546 | 4 | 5 | 5 |
| 2 | 646 | 4 | 5 | 6 |
| 2 | 746 | 4 | 5 | 7 |
| 2 | 846 | 4 | 5 | 8 |
| 2 | 946 | 4 | 5 | 9 |
| 3 | 707 | null | 6 | 7 |
| 3 | 717 | 1 | 6 | 7 |
| 3 | 727 | 2 | 6 | 7 |
| 3 | 737 | 3 | 6 | 7 |
| 3 | 747 | 4 | 6 | 7 |
| 3 | 757 | 5 | 6 | 7 |
| 3 | 767 | 6 | 6 | 7 |
| 3 | 777 | 7 | 6 | 7 |
| 3 | 787 | 8 | 6 | 7 |
| 3 | 797 | 9 | 6 | 7 |
SELECT 1 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 1
AND D.col2 = 2
AND D.col3 = 3
UNION ALL
SELECT 2 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 4
AND D.col2 = 5
UNION ALL
SELECT 3 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = 6
AND D.col3 = 7
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT 1 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 1
AND D.col2 = 2
AND D.col3 = 3
UNION ALL
SELECT 2 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 4
AND D.col2 = 5
UNION ALL
SELECT 3 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = 6
AND D.col3 = 7" StatementId="2" StatementCompId="6" StatementType="SELECT" StatementSqlHandle="0x09001C34BCDAC2E2A9F707589BCC80992B910000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="true" StatementSubTreeCost="0.0098733" StatementEstRows="21" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2A9AC60E61B5799A" QueryPlanHash="0x424190A957BEEDA0" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="368"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col1_col2_col3]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col2_col3_col1]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col3_col1_col2]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="21" EstimateIO="0" EstimateCPU="2.1e-06" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0098733" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="21" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1009"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1001"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col3_col1_col2]" Alias="[D]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-06" AvgRowSize="27" EstimatedTotalSubtreeCost="0.003294" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003293" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col1_col2_col3]" Alias="[D]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(4)"><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator ScalarString="(5)"><Const ConstValue="(5)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-06" AvgRowSize="27" EstimatedTotalSubtreeCost="0.003294" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003293" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col2_col3_col1]" Alias="[D]" TableReferenceId="3" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(6)"><Const ConstValue="(6)"></Const></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Concat></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT 1 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 1
AND D.col2 = 2
AND D.col3 = 3
UNION ALL
SELECT 2 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 4
AND D.col2 = 5
UNION ALL
SELECT 3 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = 6
AND D.col3 = 7
SELECT
SELECT
| Cached plan size | 48 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0098733 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 21 |
Statement
SELECT 1 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 1
AND D.col2 = 2
AND D.col3 = 3
UNION ALL
SELECT 2 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = 4
AND D.col2 = 5
UNION ALL
SELECT 3 AS search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = 6
AND D.col3 = 7
Concatenation
Cost: 0%
Concatenation
| Physical Operation | Concatenation |
|---|---|
| Logical Operation | Concatenation |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 21 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000021 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000021 |
| Estimated Subtree Cost | 0.0098733 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 21 |
| Estimated Row Size | 27 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Output List
Union1006
Union1007
Union1008
Union1009
Union1010
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0032832 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 27 B |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Expr1001
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 33%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 1 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (33%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 2 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col3_col1_col2].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col3, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col1, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2 = Scalar Operator((3)), Scalar Operator((1)), Scalar Operator((2))
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.003294 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 27 B |
| Node ID | 3 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Expr1003
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 33%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 10 |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.003293 (33%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.000168 |
| Estimated Subtree Cost | 0.003293 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 10 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 4 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col1_col2_col3].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col1, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2 = Scalar Operator((4)), Scalar Operator((5))
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.003294 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 27 B |
| Node ID | 5 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Expr1005
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 33%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 10 |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.003293 (33%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.000168 |
| Estimated Subtree Cost | 0.003293 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 10 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 6 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col2_col3_col1].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col3 = Scalar Operator((6)), Scalar Operator((7))
-- Dynamic SQL approach using multiple parameterized queries that feed a
-- temp #Results table.
-- This will perform index seeks for nearly all cases, depending on index availability.
-- Even suboptimal cases are much better than table scans.
CREATE TABLE #Results (
search_id INT,
data_id INT,
col1 INT,
col2 INT,
col3 INT,
)
DECLARE CSR CURSOR FAST_FORWARD FOR
SELECT *
FROM SearchCriteria
ORDER BY search_id
OPEN CSR
DECLARE @search_id INT, @col1 INT, @col2 INT, @col3 INT
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX) = CONCAT(
CAST('' AS NVARCHAR(MAX))
, 'INSERT INTO #Results'
, CHAR(10) + 'SELECT @search_id, D.data_id, D.col1, D.col2, D.col3'
, CHAR(10) + 'FROM DataToSearch D'
, CHAR(10) + 'WHERE 1 = 1'
, CASE WHEN @col1 IS NOT NULL THEN CHAR(10) + 'AND D.col1 = @col1' END
, CASE WHEN @col2 IS NOT NULL THEN CHAR(10) + 'AND D.col2 = @col2' END
, CASE WHEN @col3 IS NOT NULL THEN CHAR(10) + 'AND D.col3 = @col3' END
)
DECLARE @params NVARCHAR(MAX) = '@search_id INT, @col1 INT, @col2 INT, @col3 INT'
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
" StatementId="1" StatementCompId="4" StatementType="FETCH CURSOR" RetrievedFromCache="true" StatementSubTreeCost="0.0032853" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xC543B672F3CF75A9" QueryPlanHash="0xD25D26D8D738D070" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelFastForwardCursor" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
FETCH CURSOR
FETCH CURSOR
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032853 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 3 |
Statement
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 1 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind]
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
AND D.col3 = @col3" StatementId="2" StatementCompId="17" StatementType="INSERT" StatementSqlHandle="0x090089BDD0E0CEAAFCDDAE79D05D9CE934C30000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" BatchSqlHandle="0x0200000057E5BC3346EB2F7CDE7457F38EDD6F400043724E0000000000000000000000000000000000000000" StatementParameterizationType="1" RetrievedFromCache="true" StatementSubTreeCost="0.0132841" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x24CACBF885254268" QueryPlanHash="0x3975CE44C9EF1F7B" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col1_col2_col3]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col2_col3_col1]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col3_col1_col2]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0132841" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#Results]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#Results]" Column="search_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="@search_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="data_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col1"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col2"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col3"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col3_col1_col2]" Alias="[D]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@col3]"><Identifier><ColumnReference Column="@col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[@col1]"><Identifier><ColumnReference Column="@col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[@col2]"><Identifier><ColumnReference Column="@col2"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@search_id" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference><ColumnReference Column="@col3" ParameterDataType="int" ParameterCompiledValue="(3)" ParameterRuntimeValue="(3)"></ColumnReference><ColumnReference Column="@col2" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"></ColumnReference><ColumnReference Column="@col1" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
AND D.col3 = @col3
INSERT
INSERT
| Cached plan size | 32 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0132841 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
AND D.col3 = @col3
Table Insert
(Insert)
Cost: 75%
Table Insert
Insert input rows into the table specified in Argument field.
| Physical Operation | Table Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (75%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0132841 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[tempdb].[dbo].[#Results]
Predicate
[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 25%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 1 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (25%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col3_col1_col2].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col3, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col1, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2 = Scalar Operator([@col3]), Scalar Operator([@col1]), Scalar Operator([@col2])
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
AND D.col3 = @col3
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
" StatementId="3" StatementCompId="10" StatementType="FETCH CURSOR" RetrievedFromCache="true" StatementSubTreeCost="0.0032853" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xC543B672F3CF75A9" QueryPlanHash="0xD25D26D8D738D070" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelFastForwardCursor" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
FETCH CURSOR
FETCH CURSOR
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032853 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 3 |
Statement
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 2 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind]
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2" StatementId="4" StatementCompId="18" StatementType="INSERT" StatementSqlHandle="0x0900BE6FB20E5BF39A75CAAF219226A2A9E80000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" BatchSqlHandle="0x020000001C7DEC27DDA73C049AD146A0E90D0DF3D1FE86250000000000000000000000000000000000000000" StatementParameterizationType="1" RetrievedFromCache="true" StatementSubTreeCost="0.013303" StatementEstRows="10" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x8766538381B41555" QueryPlanHash="0x6746A89DED8BB64B" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col1_col2_col3]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col2_col3_col1]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col3_col1_col2]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="10" EstimateIO="0.01" EstimateCPU="1e-05" AvgRowSize="9" EstimatedTotalSubtreeCost="0.013303" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="10" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#Results]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#Results]" Column="search_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="@search_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="data_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col1"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col2"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col3"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003293" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col1_col2_col3]" Alias="[D]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@col1]"><Identifier><ColumnReference Column="@col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[@col2]"><Identifier><ColumnReference Column="@col2"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@search_id" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"></ColumnReference><ColumnReference Column="@col2" ParameterDataType="int" ParameterCompiledValue="(5)" ParameterRuntimeValue="(5)"></ColumnReference><ColumnReference Column="@col1" ParameterDataType="int" ParameterCompiledValue="(4)" ParameterRuntimeValue="(4)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
INSERT
INSERT
| Cached plan size | 32 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.013303 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 10 |
Statement
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
Table Insert
(Insert)
Cost: 75%
Table Insert
Insert input rows into the table specified in Argument field.
| Physical Operation | Table Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.01001 (75%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.00001 |
| Estimated Subtree Cost | 0.013303 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[tempdb].[dbo].[#Results]
Predicate
[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 25%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 10 |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.003293 (25%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.000168 |
| Estimated Subtree Cost | 0.003293 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 10 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col1_col2_col3].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col1, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2 = Scalar Operator([@col1]), Scalar Operator([@col2])
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col1 = @col1
AND D.col2 = @col2
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
" StatementId="5" StatementCompId="10" StatementType="FETCH CURSOR" RetrievedFromCache="true" StatementSubTreeCost="0.0032853" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xC543B672F3CF75A9" QueryPlanHash="0xD25D26D8D738D070" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelFastForwardCursor" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
FETCH CURSOR
FETCH CURSOR
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032853 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 3 |
Statement
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 2 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind]
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = @col2
AND D.col3 = @col3" StatementId="6" StatementCompId="19" StatementType="INSERT" StatementSqlHandle="0x090043B2A27B115D5BA3BE581B0228E7D0390000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" BatchSqlHandle="0x02000000E740EB02753F555DF56BEC980D369EE0BC78549E0000000000000000000000000000000000000000" StatementParameterizationType="1" RetrievedFromCache="true" StatementSubTreeCost="0.013303" StatementEstRows="10" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xA2C8DFB001DCB228" QueryPlanHash="0x805D599B98C78C01" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col1_col2_col3]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col2_col3_col1]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col3_col1_col2]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="10" EstimateIO="0.01" EstimateCPU="1e-05" AvgRowSize="9" EstimatedTotalSubtreeCost="0.013303" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="10" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#Results]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#Results]" Column="search_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="@search_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="data_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col1"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col2"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#Results]" Column="col3"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003293" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[IX_DataToSearch_col2_col3_col1]" Alias="[D]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[D]" Column="col3"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@col2]"><Identifier><ColumnReference Column="@col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[@col3]"><Identifier><ColumnReference Column="@col3"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@search_id" ParameterDataType="int" ParameterCompiledValue="(3)" ParameterRuntimeValue="(3)"></ColumnReference><ColumnReference Column="@col3" ParameterDataType="int" ParameterCompiledValue="(7)" ParameterRuntimeValue="(7)"></ColumnReference><ColumnReference Column="@col2" ParameterDataType="int" ParameterCompiledValue="(6)" ParameterRuntimeValue="(6)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = @col2
AND D.col3 = @col3
INSERT
INSERT
| Cached plan size | 32 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.013303 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 10 |
Statement
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = @col2
AND D.col3 = @col3
Table Insert
(Insert)
Cost: 75%
Table Insert
Insert input rows into the table specified in Argument field.
| Physical Operation | Table Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.01001 (75%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.00001 |
| Estimated Subtree Cost | 0.013303 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[tempdb].[dbo].[#Results]
Predicate
[#Results].[search_id] = [@search_id],[#Results].[data_id] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[data_id] as [D].[data_id],[#Results].[col1] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [D].[col1],[#Results].[col2] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [D].[col2],[#Results].[col3] = [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [D].[col3]
Index Seek (NonClustered)
[DataToSearch].[IX_DataToSearch_col…
Cost: 25%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 10 |
| Actual Number of Rows | 10 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.003293 (25%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.000168 |
| Estimated Subtree Cost | 0.003293 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 10 |
| Estimated Number of Rows | 10 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[D].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[IX_DataToSearch_col2_col3_col1].[D]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col2, [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].col3 = Scalar Operator([@col2]), Scalar Operator([@col3])
INSERT INTO #Results
SELECT @search_id, D.data_id, D.col1, D.col2, D.col3
FROM DataToSearch D
WHERE 1 = 1
AND D.col2 = @col2
AND D.col3 = @col3
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
" StatementId="7" StatementCompId="10" StatementType="FETCH CURSOR" RetrievedFromCache="true" StatementSubTreeCost="0.0032853" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xC543B672F3CF75A9" QueryPlanHash="0xD25D26D8D738D070" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelFastForwardCursor" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
FETCH CURSOR
FETCH CURSOR
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032853 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 3 |
Statement
FETCH NEXT FROM CSR INTO @search_id, @col1, @col2, @col3
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 1 |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind]
| search_id | data_id | col1 | col2 | col3 |
|---|---|---|---|---|
| 1 | 313 | 1 | 2 | 3 |
| 2 | 46 | 4 | 5 | null |
| 2 | 146 | 4 | 5 | 1 |
| 2 | 246 | 4 | 5 | 2 |
| 2 | 346 | 4 | 5 | 3 |
| 2 | 446 | 4 | 5 | 4 |
| 2 | 546 | 4 | 5 | 5 |
| 2 | 646 | 4 | 5 | 6 |
| 2 | 746 | 4 | 5 | 7 |
| 2 | 846 | 4 | 5 | 8 |
| 2 | 946 | 4 | 5 | 9 |
| 3 | 707 | null | 6 | 7 |
| 3 | 717 | 1 | 6 | 7 |
| 3 | 727 | 2 | 6 | 7 |
| 3 | 737 | 3 | 6 | 7 |
| 3 | 747 | 4 | 6 | 7 |
| 3 | 757 | 5 | 6 | 7 |
| 3 | 767 | 6 | 6 | 7 |
| 3 | 777 | 7 | 6 | 7 |
| 3 | 787 | 8 | 6 | 7 |
| 3 | 797 | 9 | 6 | 7 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT *
FROM #Results" StatementId="8" StatementCompId="15" StatementType="SELECT" StatementSqlHandle="0x09000DAB3E2335B38D34A321A339014B31760000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" BatchSqlHandle="0x02000000A2F5E7077F418C44D5472DFC151EA3B239AA70B30000000000000000000000000000000000000000" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0033051" StatementEstRows="21" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x7F0427D0F266A9EC" QueryPlanHash="0xF2B60A184A14117A" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="15" CompileCPU="0" CompileMemory="80"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="21" EstimatedRowsRead="21" EstimateIO="0.0032035" EstimateCPU="0.0001016" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0033051" TableCardinality="21" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#Results]" Column="search_id"></ColumnReference><ColumnReference Table="[#Results]" Column="data_id"></ColumnReference><ColumnReference Table="[#Results]" Column="col1"></ColumnReference><ColumnReference Table="[#Results]" Column="col2"></ColumnReference><ColumnReference Table="[#Results]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="21" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="21" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[#Results]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#Results]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#Results]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#Results]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#Results]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#Results]" Storage="RowStore"></Object></TableScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT *
FROM #Results
SELECT
SELECT
| Cached plan size | 16 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0033051 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 21 |
Statement
SELECT *
FROM #Results
Table Scan
[#Results]
Cost: 100%
Table Scan
Scan rows from a table.
| Physical Operation | Table Scan |
|---|---|
| Logical Operation | Table Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 21 |
| Actual Number of Rows | 21 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0033051 (100%) |
| Estimated I/O Cost | 0.0032035 |
| Estimated CPU Cost | 0.0001016 |
| Estimated Subtree Cost | 0.0033051 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 21 |
| Estimated Number of Rows | 21 |
| Estimated Row Size | 27 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Output List
[#Results].search_id
[#Results].data_id
[#Results].col1
[#Results].col2
[#Results].col3
Object
[tempdb].[dbo].[#Results]
-- This performs table scans
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON (a.col1 = b.col1 OR a.col1 IS NULL)
AND (a.col2 = b.col2 OR a.col2 IS NULL)
AND (a.col3 = b.col3 OR a.col3 IS NULL)
| search_id | col1 | col2 | col3 | data_id | col1 | col2 | col3 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 313 | 1 | 2 | 3 |
| 2 | 4 | 5 | null | 46 | 4 | 5 | null |
| 2 | 4 | 5 | null | 146 | 4 | 5 | 1 |
| 2 | 4 | 5 | null | 246 | 4 | 5 | 2 |
| 2 | 4 | 5 | null | 346 | 4 | 5 | 3 |
| 2 | 4 | 5 | null | 446 | 4 | 5 | 4 |
| 2 | 4 | 5 | null | 546 | 4 | 5 | 5 |
| 2 | 4 | 5 | null | 646 | 4 | 5 | 6 |
| 2 | 4 | 5 | null | 746 | 4 | 5 | 7 |
| 2 | 4 | 5 | null | 846 | 4 | 5 | 8 |
| 2 | 4 | 5 | null | 946 | 4 | 5 | 9 |
| 3 | null | 6 | 7 | 707 | null | 6 | 7 |
| 3 | null | 6 | 7 | 717 | 1 | 6 | 7 |
| 3 | null | 6 | 7 | 727 | 2 | 6 | 7 |
| 3 | null | 6 | 7 | 737 | 3 | 6 | 7 |
| 3 | null | 6 | 7 | 747 | 4 | 6 | 7 |
| 3 | null | 6 | 7 | 757 | 5 | 6 | 7 |
| 3 | null | 6 | 7 | 767 | 6 | 6 | 7 |
| 3 | null | 6 | 7 | 777 | 7 | 6 | 7 |
| 3 | null | 6 | 7 | 787 | 8 | 6 | 7 |
| 3 | null | 6 | 7 | 797 | 9 | 6 | 7 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON (a.col1 = b.col1 OR a.col1 IS NULL)
AND (a.col2 = b.col2 OR a.col2 IS NULL)
AND (a.col3 = b.col3 OR a.col3 IS NULL)" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x09003A07A00AB8B3DBEF7FBD082B720B762B0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0321273" StatementEstRows="300" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x183FBEFCFED30B80" QueryPlanHash="0x7509ADE64071A9F9" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="224"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.97" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_37A5467C]" Table="[SearchCriteria]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col1_col2_col3]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col2_col3_col1]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.94" ModificationCount="0" SamplingPercent="100" Statistics="[IX_DataToSearch_col3_col1_col2]" Table="[DataToSearch]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="300" EstimateIO="0" EstimateCPU="0.01254" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0321273" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="21" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] IS NULL) AND ([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] IS NULL) AND ([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] IS NULL)"><Logical Operation="AND"><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" Alias="[a]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1000" EstimatedRowsRead="1000" EstimateIO="0.00616646" EstimateCPU="0.0011785" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00970196" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3000" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="21" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3000" ActualEndOfScans="3" ActualExecutions="3" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[PK_TableToSearch]" Alias="[b]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON (a.col1 = b.col1 OR a.col1 IS NULL)
AND (a.col2 = b.col2 OR a.col2 IS NULL)
AND (a.col3 = b.col3 OR a.col3 IS NULL)
SELECT
SELECT
| Cached plan size | 32 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0321273 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 300 |
Statement
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON (a.col1 = b.col1 OR a.col1 IS NULL)
AND (a.col2 = b.col2 OR a.col2 IS NULL)
AND (a.col3 = b.col3 OR a.col3 IS NULL)
Nested Loops
(Left Outer Join)
Cost: 60%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Left Outer Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 21 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.01914 (60%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.01254 |
| Estimated Subtree Cost | 0.0321273 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 300 |
| Estimated Row Size | 39 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col3
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col3
Predicate
([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] IS NULL) AND ([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] IS NULL) AND ([fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3]=[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] OR [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] IS NULL)
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind].…
Cost: 10%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 3 |
| Actual Number of Rows | 3 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (10%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind].[a]
Clustered Index Scan (Clustered)
[DataToSearch].[PK_TableToSearch].[…
Cost: 30%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 3000 |
| Actual Number of Rows | 3000 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.009702 (30%) |
| Estimated I/O Cost | 0.0061665 |
| Estimated CPU Cost | 0.0011785 |
| Estimated Subtree Cost | 0.009702 |
| Estimated Number of Executions | 1 |
| Number of Executions | 3 |
| Estimated Number of Rows to be Read | 1000 |
| Estimated Number of Rows | 1000 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 2 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[PK_TableToSearch].[b]
-- This performs table scans. (The plan visual may look better, but it still performeds
-- N x full table scans before filtering.)
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON COALESCE(a.col1, b.col1, -1) = COALESCE(b.col1, -1)
AND COALESCE(a.col2, b.col2, -1) = COALESCE(b.col2, -1)
AND COALESCE(a.col3, b.col3, -1) = COALESCE(b.col3, -1)
| search_id | col1 | col2 | col3 | data_id | col1 | col2 | col3 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 313 | 1 | 2 | 3 |
| 2 | 4 | 5 | null | 46 | 4 | 5 | null |
| 2 | 4 | 5 | null | 146 | 4 | 5 | 1 |
| 2 | 4 | 5 | null | 246 | 4 | 5 | 2 |
| 2 | 4 | 5 | null | 346 | 4 | 5 | 3 |
| 2 | 4 | 5 | null | 446 | 4 | 5 | 4 |
| 2 | 4 | 5 | null | 546 | 4 | 5 | 5 |
| 2 | 4 | 5 | null | 646 | 4 | 5 | 6 |
| 2 | 4 | 5 | null | 746 | 4 | 5 | 7 |
| 2 | 4 | 5 | null | 846 | 4 | 5 | 8 |
| 2 | 4 | 5 | null | 946 | 4 | 5 | 9 |
| 3 | null | 6 | 7 | 707 | null | 6 | 7 |
| 3 | null | 6 | 7 | 717 | 1 | 6 | 7 |
| 3 | null | 6 | 7 | 727 | 2 | 6 | 7 |
| 3 | null | 6 | 7 | 737 | 3 | 6 | 7 |
| 3 | null | 6 | 7 | 747 | 4 | 6 | 7 |
| 3 | null | 6 | 7 | 757 | 5 | 6 | 7 |
| 3 | null | 6 | 7 | 767 | 6 | 6 | 7 |
| 3 | null | 6 | 7 | 777 | 7 | 6 | 7 |
| 3 | null | 6 | 7 | 787 | 8 | 6 | 7 |
| 3 | null | 6 | 7 | 797 | 9 | 6 | 7 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON COALESCE(a.col1, b.col1, -1) = COALESCE(b.col1, -1)
AND COALESCE(a.col2, b.col2, -1) = COALESCE(b.col2, -1)
AND COALESCE(a.col3, b.col3, -1) = COALESCE(b.col3, -1)" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x09003A6F93E6D735B0AC415E02BAABF74E790000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0302503" StatementEstRows="1000" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xA2FB9309ABE8B924" QueryPlanHash="0x7509ADE64071A9F9" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="360"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2169600"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-01-11T02:47:30.97" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_37A5467C]" Table="[SearchCriteria]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.98" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_37A5467C]" Table="[SearchCriteria]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-01-11T02:47:30.98" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_37A5467C]" Table="[SearchCriteria]" Schema="[dbo]" Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.000222996" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0302503" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="21" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="search_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Index="[PK_ValuesToFind]" Alias="[a]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="17.7828" EstimatedRowsRead="1000" EstimateIO="0.00616646" EstimateCPU="0.0011785" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00970196" TableCardinality="1000" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="21" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="21" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3000" ActualEndOfScans="3" ActualExecutions="3" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="data_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Index="[PK_TableToSearch]" Alias="[b]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] ELSE (-1) END AND CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] ELSE (-1) END AND CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] ELSE (-1) END"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col1"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col2"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[SearchCriteria]" Alias="[a]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5]" Schema="[dbo]" Table="[DataToSearch]" Alias="[b]" Column="col3"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(-1)"></Const></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON COALESCE(a.col1, b.col1, -1) = COALESCE(b.col1, -1)
AND COALESCE(a.col2, b.col2, -1) = COALESCE(b.col2, -1)
AND COALESCE(a.col3, b.col3, -1) = COALESCE(b.col3, -1)
SELECT
SELECT
| Cached plan size | 40 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0302503 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1000 |
Statement
SELECT *
FROM SearchCriteria AS a
LEFT JOIN DataToSearch AS b
ON COALESCE(a.col1, b.col1, -1) = COALESCE(b.col1, -1)
AND COALESCE(a.col2, b.col2, -1) = COALESCE(b.col2, -1)
AND COALESCE(a.col3, b.col3, -1) = COALESCE(b.col3, -1)
Nested Loops
(Left Outer Join)
Cost: 57%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Left Outer Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 21 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.017263 (57%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000223 |
| Estimated Subtree Cost | 0.0302503 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1000 |
| Estimated Row Size | 39 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col3
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col3
Outer References
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col3
Clustered Index Scan (Clustered)
[SearchCriteria].[PK_ValuesToFind].…
Cost: 11%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 3 |
| Actual Number of Rows | 3 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032853 (11%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001603 |
| Estimated Subtree Cost | 0.0032853 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 1 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].search_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[a].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[PK_ValuesToFind].[a]
Clustered Index Scan (Clustered)
[DataToSearch].[PK_TableToSearch].[…
Cost: 32%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 3000 |
| Actual Number of Rows | 21 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.009702 (32%) |
| Estimated I/O Cost | 0.0061665 |
| Estimated CPU Cost | 0.0011785 |
| Estimated Subtree Cost | 0.009702 |
| Estimated Number of Executions | 1 |
| Number of Executions | 3 |
| Estimated Number of Rows to be Read | 1000 |
| Estimated Number of Rows | 17.7828 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 2 |
Output List
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].data_id
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col1
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col2
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[b].col3
Object
[fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[PK_TableToSearch].[b]
Predicate
CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col1] as [a].[col1] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col1] as [b].[col1] ELSE (-1) END AND CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col2] as [a].[col2] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col2] as [b].[col2] ELSE (-1) END AND CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[SearchCriteria].[col3] as [a].[col3] ELSE CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] ELSE (-1) END END=CASE WHEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] IS NOT NULL THEN [fiddle_92e8e9b37e7646a9a8ed88d694e7d4c5].[dbo].[DataToSearch].[col3] as [b].[col3] ELSE (-1) END