add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
CREATE TABLE [dbo].[table_name](
[RefNo] [dbo].[udt_RefNo] NOT NULL,
[S_Mode] [varchar](10) NOT NULL,
[ref_clnt] [varchar](50) NULL
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED
(
[RefNo] ASC
))
ALTER TABLE dbo.table_name
ADD ref_cc AS
ISNULL(CAST(ref_clnt AS integer), 0);

CREATE NONCLUSTERED INDEX i
ON dbo.table_name (S_Mode, ref_cc);
INSERT dbo.table_name
(RefNo, S_Mode, ref_clnt)
VALUES
('998', 'value', NULL),
('999', 'value', 2),
('1000', 'value', 3);
3 rows affected
set statistics xml on;

SELECT Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10))
FROM table_name WITH(NOLOCK)
WHERE s_mode='value'
Ref
4
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) &#xa;FROM table_name WITH(NOLOCK) &#xa;WHERE s_mode=&apos;value&apos;" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032845" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7D86E7107C28FC2F" QueryPlanHash="0x45DA0D40D2FF9ECD" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="24" CompileTime="15" CompileCPU="15" CompileMemory="264"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(int,[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].[ref_clnt],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[Expr1002]+(1),0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1972704"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-04-04T10:50:37.78" ModificationCount="0" SamplingPercent="100" Statistics="[i]" Table="[table_name]" Schema="[dbo]" Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]"></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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032845" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT(varchar(10),[Expr1002]+(1),0)"><Convert DataType="varchar" Length="10" Style="0" Implicit="0"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032844" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1004])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032833" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateRowsWithoutRowGoal="3" EstimateIO="0" EstimateCPU="3e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Column="ref_cc" ComputedColumn="1"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].[ref_cc]"><Identifier><ColumnReference Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Column="ref_cc" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Column="ref_cc" ComputedColumn="1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Column="ref_cc" ComputedColumn="1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Index="[i]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b517c876e82648f88a9c3adc98f88e85]" Schema="[dbo]" Table="[table_name]" Column="S_Mode"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;value&apos;"><Const ConstValue="&apos;value&apos;"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Top></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SELECT Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) FROM table_name WITH(NOLOCK) WHERE s_mode='value'
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0032845
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) FROM table_name WITH(NOLOCK) WHERE s_mode='value'
Warnings
Type conversion in expression (CONVERT(int,[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].[ref_clnt],0)) may affect "Cardinality Estimate" in query plan choice.
Type conversion in expression (CONVERT(varchar(10),[Expr1002]+(1),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 OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0000001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000001
Estimated Subtree Cost0.0032845
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size16 B
Node ID0
Output List
Expr1003
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
Physical OperationStream Aggregate
Logical OperationAggregate
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0000011 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000011
Estimated Subtree Cost0.0032844
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1
Estimated Row Size11 B
Actual Rebinds0
Actual Rewinds0
Node ID1
Output List
Expr1002
Top
Cost: 0%
Top
Select the first few rows based on a sort order.
Physical OperationTop
Logical OperationTop
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0000001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000001
Estimated Subtree Cost0.0032833
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1
Estimated Row Size11 B
Actual Rebinds0
Actual Rewinds0
Node ID3
Output List
Expr1004
Top Expression
(1)
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0000001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000003
Estimated Subtree Cost0.0032832
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size15 B
Node ID4
Output List
[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].ref_cc
Expr1004
Index Seek (NonClustered)
[table_name].[i]
Cost: 100%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read1
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032831 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001603
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read3
Estimated Number of Rows1
Estimated Row Size11 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID5
Output List
[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].ref_cc
Object
[fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].[i]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_b517c876e82648f88a9c3adc98f88e85].[dbo].[table_name].S_Mode = Scalar Operator('value')