SQL Server 2017

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>