Can we rollback the Truncate, Drop, and Delete command deleted data?

Here we talk about the concept of truncate, drop and delete commands and whether they can be rollback, if so, how and when !!

Yesterday I was working on this and see if we use the truncate command in transaction, we can rollback the data. One more thing if we perform delete command outside the transaction, we can’t rollback the data.

Let’s try this…

Suppose I have a table dbo.Employee with three column- Id, Name, and Salary.

CREATE TABLE [dbo].Employee(
      Id [int] NULL,
      Name [varchar](50) NULL,
      Salary [Int] NULL
)

Insert the values into the db.Employee table

INSERT INTO dbo.Employee VALUES(1,'Employee1',10000)
INSERT INTO dbo.Employee VALUES (2,'Employee2',20000)
INSERT INTO dbo.Employee VALUES (3,'Employee3',30000)
INSERT INTO dbo.Employee VALUES (4,'Employee4',10000)
INSERT INTO dbo.Employee VALUES (5,'Employee5',30000)

Now I have a five rows in table

SELECT * FROM dbo.Employee

Now we perform the delete command in Transaction and then select command

BEGIN TRAN
DELETE FROM dbo.Employee WHERE ID = 2
ROLLBACK

SELECT * FROM dbo.Employee

Output:

But again I run the select command then I get the same result as previous select statement (i.e 5).
Now I perform the Truncate command in Transaction, Let see what happen.

BEGIN TRAN
TRUNCATE TABLE dbo.Employee
ROLLBACK

Now again I run the select command and got the same rows and same result, if same thing we do with Drop command that will also give same result.
It means If we use the Delete, Truncate, and Drop command with Transaction, we can rollback the data and if we perform all three command outside the Transaction we can’t rollback.

One more thing I have to add in this article, we can’t rollback the data but we can restore the data from the Transaction_log, if we use the delete command. We can’t restore the data if we use the truncate or drop command. How and Why read in next article.

In summary, 
We can rollback the DDL and DML commands, if all these commands are in transaction. We can only restore the DML commands data. 

Welcoming Microsoft world onto my blog...

For most of my IT life, I have been thoroughly disconnected with the Microsoft world of enterprise products. One of the reasons could be the fact that I was introduced into the IT through the corridors of Java & then Unix.. Subsequently more things came on the way, but I never got around to diving deep in the MS world.

Sometime back, I did try and explored SQL server 2008 R2 and associated ETL/Analytics pieces, but it didnt excite me as much.

Now, I have an opportunity to learn and share the MS wisdom through a dear friend Sachin Dubey.  He's going to be putting up content on to this blog. He would be contributing content geared towards Microsoft products, e.g. SQL Server, MS-BI, Dot-Net etc.  With significant experience working around MS BI space, Sachin is a self built knowledge repository. I hope we all learn with him.

Making rails talk to SQL Server Express Edition

I had zero exposure to sqlserver before today. With the help of a dba and google :) I finally achieved the communication.  Here's what I did to achieve this -

Make an odbc connection - pointing to the db server, preferably make a sql server authentication user

Install the following gems

1. activerecord-odbc-adapter (2.0)
2. activerecord-sqlserver-adapter (2.3.8)
3. dbd-odbc (0.2.5)
4. dbi (0.4.5)
5. deprecated (2.0.1)

Further, I copied odbc.so and odbc_utf8.so files from http://www.ch-werner.de/rubyodbc/i386-msvcrt-ruby-odbc.zip to .../ruby/1.8/i386-msvcrt even though exact directory was not found in the path. So, the files were eventually copied to ...\Ruby187\lib\ruby\1.8\i386-mingw32

My database.yml file looks like this -

development:
  adapter: sqlserver
  mode: ODBC
  dsn:
  username: myUserName
  password: myPassword