BEGIN TRAN and testing

Also its probably much easier to test the transaction in query analyser rather than the webpage.

Do not "play" with isolation levels, it is not recommended at all. This is very bad practice.
If you need to see your data during/after execution, use table variable or global table.

thanks for the nice advice.

>>table variable to do some logging into
>>test tables (or global temporary tables)

Do you mean to hold the data, so if need be, can revert back?

No. Holding the data could be achieved by doing a backup, and restore after test. Keeping old values somewhere is only appropriate if you have a static DB for test, that is noone else is fiddling around with it.

The more sophisticated way is to use some other user, owning it's own copy of the tables to change, and only change those. Since a schema owner's tables have precedence over the general available (assigned to dbo), that works great - if a table in the own schema exists, it will overlay the dbo one, without the application knowing of it.

You can store any data into the table variable (either just some log entries like "reaching point X at hh:mm:ss" or store data in the middle of the transaction). Again, table variables are not the part of the transaction scope, so use them as you want. And this data will not revert back after ROLLBACK. Tell me what exactly you are trying to achieve.

>>The more sophisticated way is to use some other user

So you mean if the app is using dbo.tblTracker, then I could create testSchema.tblTracker, and do make the app to point to the testSchema data, instead of the default dbo used for the app?

>>table variables are not the part of the transaction scope

so we lose the data at the end of the session; but no big deal if the table variables are lost. But I wanted to test the data change, and see if the app behaves correctly, and then commit it after the users take a look at it and are happy. this way, i do not have to make the database single user mode, and can still let other users in while i am making the change, and test and then commit...

The "different schema" thingy only works smooth if you do *not* explicitely refer the owner of the objects. Else it does not make much sense, as you need to adapt the SQLs each time.

man2002ua, only i will be interested in the data consistency.

Qlemo, how will your idea work in sql 2000 vs sql 2005? (with schema's meaning being different)

The concept of "schema" is not applying to MSSQL 2000. But the owner of tables still exists. Schemas are "only" an additional layer you can put between the db and the objects. So instead of server.db.schema.object you can still use server.db.owner.object. As long as schema and owner are named the same, it's not different (in respect to what I suggested).

Qlemo, to make sure i get your point, consider this:

create schema schematest
create table schematest.TestTable (TestCol_schema int)
create table TestTable (TestCol_dbo int)
select * from TestTable
--select * from schematest.TestTable
--drop table TestTable
--drop table schematest.TestTable
--drop schema schematest

In the above scenario, did you anticipate my main 'live' data to be in dbo.TestTable, and that my test data should be in schematest.TestTable?

OK. So the above code in 33466291 will work both in 2000 and 2005, but in diff context, right? but both will be OK?
(in sql 2000, i have to create user instead of schema)

then check the result in schematest.TestTable, and then if i am happy with it, then do the regular stuff in dbo tables.?

Absolutely correct!

great help! thanks expert!