Performance Improvement went wrong in Production, worked fine in Test
Your rewrite does not have the same semantics as the original query, so different results are not surprising.

This is true without worrying about the NOLOCK hint, and even if it is (somehow) guaranteed that the highest ref_clnt value is associated with the row having the highest RefNo value. See this db&lt;>fiddle example.

Precision is important when dealing with computers, so you need to think carefully about data types and edge cases. The maximum RefNo calculation will use string sorting semantics, so '999' sorts higher than '1000'. There are several other important differences between the queries. I'm not going to list all of them, but NULL handling is another example.

There are also numerous bugs in both versions of the code. The original will fail if any ref_clnt value of -1000000000 or lower is returned, because that will not fit in varchar(10). The sign makes the length 11.

The easiest way to safely improve the original version of the code is to add an index on a computed column:

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);


db&lt;>fiddle demo

The execution plan can then seek directly to the highest ref_clnt row (sorted as integer) for the given S_Mode value:



The vendor's original SQL may still be of debatable quality, but at least it will run faster and produce the same results.



The vendor's new suggestion:

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


...is still problematic, at least in theory, because ISNULL uses the data type of the first parameter, so the integer literal 0 is implicitly cast to varchar(50).


  The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.


The MAX still operates on a string, which may produce unexpected results. In any case the expression is still not seekable without a (different) computed column index.