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
))
INSERT dbo.table_name
    (RefNo, S_Mode, ref_clnt)
VALUES
    ('998', 'value', NULL),
    ('999', 'value', -1),
    ('1000', 'value', -2);
3 rows affected

set statistics xml on;

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'


(No column name)


-1




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode=&apos;value&apos;" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00328944" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x1618E43B988B3C8B" QueryPlanHash="0x67F0F2FFF57A0100" 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="0" CompileCPU="0" CompileMemory="232"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[Expr1002],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1119920"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-03-21T22:59:27.26" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_34C8D9D1]" Table="[table_name]" Schema="[dbo]" Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]"></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="11" EstimatedTotalSubtreeCost="0.00328944" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1002],0)+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="2.3e-006" AvgRowSize="36" EstimatedTotalSubtreeCost="0.00328934" 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="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-007" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0032856" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="isnull([fiddle_c81edccdad78447096ddd6e9ac0a7a51].[dbo].[table_name].[ref_clnt],&apos;0&apos;)"><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]" Schema="[dbo]" Table="[table_name]" Column="ref_clnt"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;0&apos;"></Const></ScalarOperator></Intrinsic></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="43" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]" Schema="[dbo]" Table="[table_name]" Column="ref_clnt"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]" Schema="[dbo]" Table="[table_name]" Column="ref_clnt"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]" Schema="[dbo]" Table="[table_name]" Index="[PK_table_name]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_c81edccdad78447096ddd6e9ac0a7a51].[dbo].[table_name].[S_Mode]=&apos;value&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c81edccdad78447096ddd6e9ac0a7a51]" Schema="[dbo]" Table="[table_name]" Column="S_Mode"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;value&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>