SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION dbo.fn_ParseNickName
    (@CaseCaption nvarchar(255))
RETURNS nvarchar(10)
WITH SCHEMABINDING
AS
BEGIN
    RETURN LEFT(@CaseCaption, 10)
END;
CREATE TABLE dbo.Cases 
(
    CaseCaption nvarchar(255),
    NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)),
    NickNamePersisted AS (dbo.fn_ParseNickName(CaseCaption)) PERSISTED NOT NULL,
);

CREATE INDEX i 
ON dbo.Cases (CaseCaption) 
INCLUDE (NickNamePersisted);
SET STATISTICS XML ON;
SELECT C.NickNamePersisted 
FROM dbo.Cases AS C
WHERE C.CaseCaption > '';
SET STATISTICS XML OFF;


NickNamePersisted




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 [C].[NickNamePersisted] FROM [dbo].[Cases] [C] WHERE [C].[CaseCaption]&gt;@1" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090074CE0DFAC4A72D439991638B4FD99BDA0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032833" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x1A2AD6FF975442E4" QueryPlanHash="0x83B569848D355D3A" 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="TSQLUserDefinedFunctionsNotParallelizable" CachedPlanSize="24" CompileTime="31" CompileCPU="31" CompileMemory="176"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="778464"></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="21" EstimatedTotalSubtreeCost="0.0032833" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Table="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference><ScalarOperator ScalarString="[fiddle_80f3edff75b44081a2ef81f295572c2e].[dbo].[Cases].[NickNameComputed] as [C].[NickNameComputed]"><Identifier><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNameComputed" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="21" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNameComputed" ComputedColumn="1"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNameComputed" ComputedColumn="1"></ColumnReference><ScalarOperator ScalarString="[fiddle_80f3edff75b44081a2ef81f295572c2e].[dbo].[fn_ParseNickName]([fiddle_80f3edff75b44081a2ef81f295572c2e].[dbo].[Cases].[CaseCaption] as [C].[CaseCaption])"><UserDefinedFunction FunctionName="[fiddle_80f3edff75b44081a2ef81f295572c2e].[dbo].[fn_ParseNickName]"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="CaseCaption"></ColumnReference></Identifier></ScalarOperator></UserDefinedFunction></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="266" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="CaseCaption"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" 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_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="CaseCaption"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Index="[i]" Alias="[C]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="CaseCaption"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(4000),[@1],0)"><Identifier><ColumnReference Column="ConstExpr1002"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;&apos;" ParameterRuntimeValue="&apos;&apos;"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


DROP TABLE dbo.Cases;

CREATE TABLE dbo.Cases (
    CaseCaption nvarchar(255),
    NickNamePersisted AS (dbo.fn_ParseNickName(CaseCaption)) PERSISTED NOT NULL,
    NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)),
);

CREATE INDEX i 
ON dbo.Cases (CaseCaption) 
INCLUDE (NickNamePersisted);
SET STATISTICS XML ON;
SELECT C.NickNamePersisted 
FROM dbo.Cases AS C
WHERE C.CaseCaption > '';
SET STATISTICS XML OFF;


NickNamePersisted




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 [C].[NickNamePersisted] FROM [dbo].[Cases] [C] WHERE [C].[CaseCaption]&gt;@1" StatementId="1" StatementCompId="0" StatementType="SELECT" StatementSqlHandle="0x090074CE0DFAC4A72D439991638B4FD99BDA0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032832" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x1A2AD6FF975442E4" QueryPlanHash="0x76E7DDCA4CA60FBC" 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="TSQLUserDefinedFunctionsNotParallelizable" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="168"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="778464"></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="21" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Table="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference><ScalarOperator ScalarString="[fiddle_80f3edff75b44081a2ef81f295572c2e].[dbo].[Cases].[NickNamePersisted] as [C].[NickNamePersisted]"><Identifier><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="21" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" 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_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="NickNamePersisted" ComputedColumn="1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Index="[i]" Alias="[C]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_80f3edff75b44081a2ef81f295572c2e]" Schema="[dbo]" Table="[Cases]" Alias="[C]" Column="CaseCaption"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(4000),[@1],0)"><Identifier><ColumnReference Column="ConstExpr1002"><ScalarOperator><Convert DataType="nvarchar" Length="8000" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;&apos;" ParameterRuntimeValue="&apos;&apos;"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>