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
))
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>
SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0032894
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'
Warnings
Type conversion in expression (CONVERT_IMPLICIT(int,[Expr1002],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.0032894
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size11 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.0000037 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000023
Estimated Subtree Cost0.0032893
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1
Estimated Row Size36 B
Actual Rebinds0
Actual Rewinds0
Node ID1
Output List
Expr1002
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.0000003 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000003
Estimated Subtree Cost0.0032856
Estimated Number of Executions1
Estimated Number of Rows3
Estimated Row Size43 B
Node ID2
Output List
Expr1004
Clustered Index Scan (Clustered)
[table_name].[PK_table_name]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
Physical OperationClustered Index Scan
Logical OperationClustered Index Scan
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read3
Actual Number of Rows3
Actual Number of Batches0
Estimated Operator Cost0.0032853 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001603
Estimated Subtree Cost0.0032853
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read3
Estimated Number of Rows3
Estimated Row Size43 B
Actual Rebinds0
Actual Rewinds0
OrderedFalse
Node ID3
Output List
[fiddle_c81edccdad78447096ddd6e9ac0a7a51].[dbo].[table_name].ref_clnt
Object
[fiddle_c81edccdad78447096ddd6e9ac0a7a51].[dbo].[table_name].[PK_table_name]
Predicate
[fiddle_c81edccdad78447096ddd6e9ac0a7a51].[dbo].[table_name].[S_Mode]='value'