SQL - Implicit Transactions - AutoCommit Off

One of things I always want when testing some sql scripts is a way to do undo the operation.

Its like wanting to test everything is fine and also not having to change the underlying database which you are testing.

I have found the solution , Implicit transactions. Which are transactions which do not commit any changes to the database until appropriate commands are executed.


Pros
This is helpful for testing any sql scripts, which run in a batch and we can test the integrity of the data and fix any errors by just not commiting the transaction.

Cons
Transaction take longer to process, as a lot of information is logged and stored in temp , so as to allow to commit the transaction later on.But this is not noticeable in small transactions. However if the transactions are in the range of tens of thousands, then I would use explicit transactions.


How to set query editor to perform implicit transactions

• Open SQL Server Management Studio
• Select Tools menu , options
• Select Query Execution
• Select ANSI
• Select Implicit Transactions

When this is selected , any transaction you perform thereafter on the query editors , the transactions will not be commited automatically. Its like having autocommit off.

In SQL Scripts you can achive this by manually adding the opration.
SET IMPLICIT TRANSACTIONS ON

This is a useful tip for people working on sql scripts.

Database Collation - Facts I have known

Database collation is a property of the database which defined how data is stored , compared and retrieved.

this is pretty useful in cases where your application wants to perform case sensitive search or language specific search.

For one of my projects I had to install SQL Server , by default SQL Server chooses a case insensitive collation , However I chose a case sensitive collation.

I realised that a collation affects not only the data which is stored in the database but also any of the system tables , views etc.

Any queries which have been written so far will now work only if they have referenced the tables views columns in the correct case.

However there are ways you get around this problem:

• Reset the collation of the database server by re-installing the server. This is crude but if you are facing lots of issues then this is one good option, but be sure to take backups of any databases which might have already created.
• Rewrite the queries with the correct case. This can be a tedious task , but its the correct way of sql scripting as far as I think.
• Chose the correct collation for any new database you create. This will ensure that any scripts which reference this database will need not worry about the case etc.

Since i was referencing the system tables in a very few places I had less issues.

But yes that's what I have know about collation.