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. 

Difference between Char, Varchar, and nVarchar data type in SQL Server

In real world everyone has different need and requirement, why we use the same data type to store different types of value. What is the actual difference between Char, Varchar and nVarchar data type.

Char DataType

Char datatype which is used to store the fixed length, non-unicode characters data. Suppose if we declared char (50) it will allocates memory for 50 characters. Once we declare char (50) and if we enter fewer than the number of characters defined (i.e. 6), the remaining length will be space filled to the right. it means other 44 characters of memory will be wasted.

Now we look in the query, How will it effect.

 

Output of this Select Statement:

Use this data type when the column data is to be of fixed length, which tends to be the case

for customer IDs and bank account IDs.

Nchar DataType

Nchar type is exactly similar to char. But hold character in Unicode format rather than ANSI. The Unicode format has a larger character set range than ANSI. Unicode datatype take exact double memory space in sql server. So use the nchar when you want to store the Unicode value.

varchar DataType

Varchar datatype, which is used to store the alphanumeric values, just like char datatype. If both are similar than what is the difference between char and Varchar? What is the need to create the new datatype? Later in this article we will discuss this. The maximum size of a Varchar column is 8,000 characters. However, if you define the column with no size—that is, Varchar () — then the length will default to 1.

Difference between the char and Varchar datatype

Char belongs to fixed length characters datatype, Varchar belongs to variable length characters datatype. If you define a column to be 20 characters long, but If you enter fewer than the number of characters defined like 10 characters then it will consume only 10 characters memory. It will not consume the defined memory.

Now we look in the query:

Output of the select statement:

We can see in above image the output of the select statement, the ZipCodelength value are varying according to data in zipCode field.

So we should use the Char for the fixed length data field and Varchar for the variable length data field.

Nvarchar DataType

Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Difference between Varchar and Nvarchar DataType

Varchar(n)

Nvarchar(n)

1.    Non-Unicode Variable Length character data type. Example: Declare @FirstName As Varchar(20) = ‘Developer’ Select @FirstName 2.    It takes 1 byte per character. Example: Declare @FirstName As Varchar(20) = ‘Developer’ Select @FirstName AS FirstName, DATALENGTH(@FirstName) As Length Result: FirstName Length Developer 9 3.    Can store maximum 8000 Non-Unicode characters. 4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1. Example: Declare @FirstName As Varchar =’Developer’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length D                1 5.     If n is not specified in while using Cast/Convert   functions, then it is considered as 30.      Example:      Declare @firstName Varchar(50)= ‘Unicode character sets hold up to 65,536’     Select CAST(@firstName As Varchar) FirstName, DATALENGTH(CAST(@firstName As varchar)) Length        Result: FirstName                                Length Unicode character sets hold up  30

1.    Unicode Variable Length character data type. Nvarchar  can store both the Unicode and non-Unicode (i.e) Chinese, Japanese, Korean etc) character. Example: Declare @FirstName As Nvarchar(20) = ‘Developer’ Select @FirstName 2.    It takes exactly double bytes per character as compare to Varchar. It takes 2 bytes per Unicode/Non-Unicode character. Example: Declare @FirstName As Nvarchar(20)= ‘Developer Select @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length Developer 18 3.    Can store maximum 4000 Unicode/Non-Unicode characters. 4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1. Example: Declare @FirstName As Nvarchar =’Developer’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length D                2 5.    If n is not specified in while using Cast/Convert     functions, then it is considered as 30.       Example:        Declare @firstName Nvarchar(50)=  ‘Unicode character sets hold up to 65,536’      Select CAST(@firstName As Nvarchar) FirstName,  DATALENGTH(CAST(@firstName As Nvarchar)) Length    Result:  FirstName                                 Length  Unicode character sets hold up   60

So if you are not using other languages then it’s better to use varchar datatype instead of nvarchar.