Intermittent incorrect results in complex procedure generating XML

Question

Hi everybody,

Issue happened in production in 3 runs out of 32 so far. The complex stored procedure that generates XML didn't create names portion. If we run the code of this stored procedure in production we can reproduce the issue in about 1 run out of 10 or more. We never had same problem in Stage. Stage is SQL 2019 and production is SQL 2016. May we assume a bug in SQL Server if the same code when running multiple times produces different results (the underlying tables are not changing during the execution)?

Thanks in advance.

Answer 1

No, I don't think we can assume this. This can also be a case of indeterministic code. That is, code that can produce different results, depending on the phase of the moon etc. Or simply because the code is relying on something which does not have a strict definition.

Of course, since we don't see the code, and we don't know the data, and we don't know what sort of XML it is supposed to produce, there is no way we can be very specific.