Truncate Within A Transaction

In the previous post, we have seen, what is the difference between in “Truncate vs. Delete”?


Actually if we use truncate then we can’t rollback the data, but the question is … What will happen if we use truncate within a transaction, Whether we can rollback or not?.


The following example reveals the answer for our question.


I created a table and inserted a rows, as shown below

Then I executed the below Query:

It Returns:

Conclusion:

Truncated Data can be roll backed if it is executed within a transaction

Truncate Vs Delete

Truncate	Delete
Truncate Table <Table Name>	Delete from <Table Name> Where <Condition>
Cannot use Where Condition	It is possible
It Removes all the data	It deletes specified data if where condition exists.
Faster in performance wise, because it doesn't keep any logs	Slower than truncate because, it keeps logs
Rollback is not possible	Rollback is possible

PL/SQL vs. T-SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database.

 

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

SQL Server ERROR Messages Severity Level

In My Previous Post, I explained how to list all the errors from Database. Every error is mapped with severity code, This article Explains what is severity code.

When you or system executes any SQL Scripts or related tasks, SQL Server DB Engine raises the error as well as it indicates the severity of error.

 

Severity code helps to understand the type of error. 

The following table lists the severity code and its meaning:

 

Severity Code	Description
0-10	It is Informational messages not actual error, actually 0 means No Error, No Information, before invoke the Programs, DB Engine converts to 0 then start performing operations
11-16	Error can be corrected by user, this may be syntax error
11	Object Doesn't Exists
12	Don't allow to do lock on Any Object
13	Transaction Dead Lock Errors
14	Security related Error, access denied
15	Syntax Error
16	General Error like invalid arguments, string value not quoted properly etc.,
17-19	Software Error, not corrected by User
17	Out of memory exception, disk usage, lock, write protected, no access to resource etc.,
18	DB Engine related error
19	Non-Configurable limit exceeded with DB Engine
19-25	Note: 19-25 error will be updated in SQL Error Log
20-25	Fatal Error occurred based on single or batch process running currently
20	Problem with current Task only
21	problem affects all other process
22	Table or Index Damaged by software or hardware. It occurs rarely. Run DBCC CHECKDB to determine error
23	Problem with integrity of Database, corrupted
24	Need to restore database, database may be corrupted, may be hardware issue
25	System Error

 

You can create or define new error message using system procedure sp_addmessage, when you add, you can use any one of the severity type (1-25) listed above

List SQL Error Codes

The following Script helps to list All Error Codes and description of SQL Server

Run this Following Queries in Master Database
Code:
Use Master

SELECT * FROM sysmessages

Results: