Inconsistent behaviour of sql code.

Does the master database have anything to do woth the manner in which a t-sql
statement/SP behaves (all other things being the same)?
Ex: We restore a database from a backup and run a SP. The SP
inserts/modifies/deletes data.
The database is backedup.
The database is now restored from the original dump and the SP re-run.
If a difference is done (record wise) of the tables affected - differences
exists.
Why should the same SP running on the same database in the same state result
in different records ?.
There is no date/time logic in the code, the SP will be the only process
running on the server.
One of the possibilities that was considered was the existence of a
parameter/setting in the master database that would have influenced this.
Any thoughts on this?
Cheers
SQLCatZ
SQLCatz
We cannot reproduce the problem. Can you post DDL+sample data + expected
result?
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:FB0B8E0C-67B4-4659-902E-C895A8AFAABF@.microsoft.com...
> Does the master database have anything to do woth the manner in which a
t-sql
> statement/SP behaves (all other things being the same)?
> Ex: We restore a database from a backup and run a SP. The SP
> inserts/modifies/deletes data.
> The database is backedup.
> The database is now restored from the original dump and the SP re-run.
> If a difference is done (record wise) of the tables affected - differences
> exists.
> Why should the same SP running on the same database in the same state
result
> in different records ?.
> There is no date/time logic in the code, the SP will be the only process
> running on the server.
> One of the possibilities that was considered was the existence of a
> parameter/setting in the master database that would have influenced this.
> Any thoughts on this?
> Cheers
> SQLCatZ
|||Certainly there are environment settings that can affect the results of
SQL statements, such as ANSI_NULLS and CONCAT_NULL_YIELDS_NULL. Most of
those are scoped to the database so if you've changed a database
setting somewhere along the way that may be a factor.
Transact SQL unfortunately also allows you to do lots of things that
are non-deterministic. Cursors, TOP, IDENTITY and the proprietary
version of the UPDATE statement all have pitfalls or "features" that
allow you to do things that may be non-deterministic and therefore give
inconsistent results in some circumstances. With good practices you can
avoid those traps but without seeing what statements you are running I
don't know whether any of these apply to you.
David Portas
SQL Server MVP