Search this blog

Wednesday, December 29, 2010

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

Wednesday, December 15, 2010

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.

Friday, December 3, 2010

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

Thursday, December 2, 2010

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: