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
SQLCatZSQLCatz
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
--

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

Inconsistent behaviour of sql code.

Does the master database have anything to do woth the manner in which a t-sq
l
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
SQLCatZSQLCatz
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
--