By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo.TEST2 (id int primary key, name nvarchar(128))
create function [dbo].[f_TEST]
(
@test bit
)
returns table
as
return (
select id, name from TEST1 where @test = 1
union all
select id, name from TEST2 where @test = 0
)
set statistics xml on;
declare @test bit = 1
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);
| id | name |
|---|
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.518" Build="13.0.6404.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * from dbo.f_TEST(@test) OPTION (RECOMPILE)" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0032832" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x07E803C0DC68F156" QueryPlanHash="0x6842CABAB828EA37" CardinalityEstimationModelVersion="130"><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="0" CompileCPU="0" CompileMemory="152"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="666592"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><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-007" AvgRowSize="143" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference><ColumnReference Column="Union1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Union1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST1].[id]"><Identifier><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="id"></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST1].[name]"><Identifier><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="name"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="143" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST1]" Index="[PK__TEST1__3213E83F73BB35B9]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)
SELECT
SELECT
| Cached plan size | 16 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032832 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)
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 | 143 B |
| Node ID | 0 |
Output List
Union1004
Union1005
Clustered Index Scan (Clustered)
[TEST1].[PK__TEST1__3213E83F73BB35B…
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 |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (100%) |
| 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 | 143 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 1 |
Output List
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST1].id
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST1].name
Object
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST1].[PK__TEST1__3213E83F73BB35B9]
set statistics xml on;
declare @test bit = 0
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);
| id | name |
|---|
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.518" Build="13.0.6404.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * from dbo.f_TEST(@test) OPTION (RECOMPILE)" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0032832" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x07E803C0DC68F156" QueryPlanHash="0xF419AAB4C7D82A1F" CardinalityEstimationModelVersion="130"><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="0" CompileCPU="0" CompileMemory="152"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="666592"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><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-007" AvgRowSize="143" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1004"></ColumnReference><ColumnReference Column="Union1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Union1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST2].[id]"><Identifier><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="id"></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST2].[name]"><Identifier><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="name"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="143" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_5ee8169ed9404eb6bc2182065d858314]" Schema="[dbo]" Table="[TEST2]" Index="[PK__TEST2__3213E83F6AAD8532]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)
SELECT
SELECT
| Cached plan size | 16 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032832 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)
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 | 143 B |
| Node ID | 0 |
Output List
Union1004
Union1005
Clustered Index Scan (Clustered)
[TEST2].[PK__TEST2__3213E83F6AAD853…
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 |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (100%) |
| 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 | 143 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 1 |
Output List
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST2].id
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST2].name
Object
[fiddle_5ee8169ed9404eb6bc2182065d858314].[dbo].[TEST2].[PK__TEST2__3213E83F6AAD8532]