Difference between Delete and Truncate

ifference between TRUNCATE, DELETE and DROP commands

Submitted by admin on Sat, 2006-02-11 02:07 

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        10

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
         0

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Flashback complete.

PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

‹ What are the difference between DDL, DML and DCL commands?upHow does one escape special characters when writing SQL queries? ›

» 

• Login to post comments

With dropping a table, also bear in mind that you lose any associated grants / constraints etc. and if you want to recreate the table, you would need to recreate these - and it may be that the user dropping the table may not have the rights to re-grant etc. - so dropping is always an extreme measure! If you're constantly dropping / recreating you should probably be using temporary tables for one-off jobs or global temporary tables for frequently running processes..

TRUNCATE V/s DELETE

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock,                                             each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.	Slower than truncate because, it maintain logs for every record
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.

Try catch block in sql

SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling

April 11, 2007 by pinaldave

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur. In SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

• ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
• ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
• ERROR_STATE: returns the state number of the error.
• ERROR_LINE: returns the line number where the error occurred.
• ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
• ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Syntax:
BEGIN TRY
{ sql_statement |statement_block }END TRYBEGIN CATCH
{ sql_statement |statement_block }END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:
BEGIN TRYDECLARE @X INT---- Divide by zero to generate ErrorSET @X = 1/0PRINT 'Command after error in TRY block'END TRYBEGIN CATCHPRINT 'Error Detected'END CATCHPRINT 'Command after TRY/CATCH blocks'
Above code will return following result:

Error Detected
Command after TRY/CATCH blocks
If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.

Limitation of TRY…CATCH:

• Compiled errors are not caught.
• Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
• Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.

For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;GOBEGIN TRY-- Generate a divide-by-zero error.SELECT 1/0;END TRYBEGIN CATCHSELECTERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;END CATCH;GO

Temptables,Tablevariables,Ctes comparison?

Temp tables

Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table. 

Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

Table Variables
These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

CTE

In my experience, CTEs are more like temporary views than anything else. When you look at the execution plan, you'll see that they are inlined into the query, not materialised and stored. I find, with the exception of recursion, they're more to make queries simpler to write than faster to run.

How to find second highest salary using cte?

second highest salary
with cte as
(
select salary,ROW_NUMBER() over (order by salary desc) as 'rownum'
from dbo.employee
)
select salary from cte where rownum=2

SQLSERVER BEGINNER SAMPLE DATABASE

select * from employee

select MAX(salary) from employee

select * from employee where salary in (select MAX(salary) from employee1daffa)

create database sqlserverbeginner

use sqlserverbeginner

--sqlserverbeginner sampledatabase

create table Department(

                       dept_no varchar(20),

                       dept_name varchar(50),

                       location varchar(50)

                       )

insert into Department values('d1','Research','Dallas')

insert into Department values('d2','Accounting','Seattle')

insert into Department values('d3','Marketing','Dallas')

select * from Department

create table Employee(

                     emp_no int,

                     emp_fname varchar(50),

                     emp_lname varchar(50),

                     dept_no varchar(20)

                     )

insert into Employee values(25348,'Matthew','Smith','d3')

insert into Employee values(10102,'Ann','Jones','d3')

insert into Employee values(18316,'John','Barrimore','d1')

insert into Employee values(29346,'James','James','d2')

insert into Employee values(9031,'Elsa','Bertoni','d2')

insert into Employee values(2581,'Elke','Hansel','d2')

insert into Employee values(28559,'Sybill','Moser','d1')

--project table

create table project(

                     project_no varchar(20),

                     project_name varchar(20),

                     budget int

                     )

insert into project values('p1','Apollo',120000)                    

insert into project values('p2','Gemini',95000)                    

insert into project values('p3','Mercury',185600)

select * from project

--the workson table

create table TheWorks_onTable(

                             emp_no int,

                             project_no varchar(50),

                             job varchar(50),

                             enter_date datetime

                             )

insert into TheWorks_onTable values(10102,'p1','Analyst',2006/10/1)

insert into TheWorks_onTable values(10102,'p3','Manager',2008/01/01),(25348,'p2','Clerk',2007/02/15),(18316,'p2',null,2007/06/01)

                            

insert into TheWorks_onTable values(29346,'p2',null,2006/12/15)

insert into TheWorks_onTable values(2581,'p3','Analyst',2007/10/15)

insert into TheWorks_onTable values(9031,'p1','Manager',2007/04/15)

insert into TheWorks_onTable values(28559,'p1','Analyst',2006/10/01)                                   

insert into TheWorks_onTable values(28559,'p2','Clerk',2008/02/01)

insert into TheWorks_onTable values(9031,'p3','Clerk',2006/11/15)

insert into TheWorks_onTable values(29346,'p1','Clerk',2007/01/04)

select * from TheWorks_onTable

                    

                    

Differences between stored procedures and functions?

IntroductionThis article explains about the basic differences between the sql user defined functions and stored procedures. Let us see the differences between them. Differences between Stored procedures and User defined functions

 Difference 1:

Stored procedure will be used for perform specific tasks 

The stored procedure normally used to perform a speck task. The bulk of sql statement that that will be complied and it uses the cached execution plans. It can be return more than one result set.

Normally functions will be used for computing value

The functions are used to do the calculations instead of doing in the query. It can be used for many places if we want the same operation.     

Difference 2: Stored procedures may or may not return values

The stored procedure based on query type it will do the operation. If we write any select query then it will return the results. If we do only update, insert or delete then it wont return any results. However if you want to check the confirmation of the transaction then we can return the result. It is not compulsory to return the result set.

But function should return value

The function must return the value. Based on the function type it will return the results.

If we have written scalar function then it returns single value. If we have written table valued function then it returns multiple rows. We cannot write the function without return any value to the calling program.

Difference 3:

Stored procedure cannot be used in the select/where/having clause

The stored procedure cannot be called like the following.  SELECT * FROM Pr_RetrieveEmployees -- It will throws an error

It will throw an error. Similarly the stored procedure cannot be part the sql query any where.

But function can be called from select/where/having clause 

The function can be called using the select query.

It can be called from the select/where/having clause. 

For instance SELECT [dbo].fn_EmployeeSalary (5) Ãƒ  it is scalar UDF. It returns single value.

                         SELECT * FROM fn_EmployeeHistory (3) Ãƒ  its will return multi value.

Difference 4:

Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

The stored procedure can run independently. Once the stored procedure is compiled then it can be executed. It can be executed using the sql command statement EXECUTE or EXEC.

EXECUTE proc_RetrieveEmployeeDetails EXEC proc_RetrieveEmployeeDetails proc_RetrieveEmployeeDetails 

But function cannot run independently

The function cannot run independently. It has to be the part of the SQL statement.

Difference 5:

Temporary table (derived) cannot be created on function. 

The temporary table cannot be created in the function. As you know if you create a temp table then it will be stored on the tempdb database. But the temp table won't allow us to create with inside the function

There are two ways to create the temp table.

       1. Create temp table

       2. Derived table SELECT * INTO #tmpEmployee FROM EmployeesThe above statement is derived table. It cannot create on function.

But it can be created in stored procedures 

The stored procedure allows us to create the temp tables in the stored procedure.

Difference 6:

From sql server 2005 onwards, TRY CATCH statements can be used in the stored procedures.

The TRY CATCH is one of the new features in the SQL server 2005 edition. It can be used with inside the stored procedure. As you know it handles the error in the catch block, whatever the statements written in the try block.

But it cannot be used in the function. But we can use raise error function.

The TRY CATCH block cannot be used with inside the functions. But we can use the raiserror function to throw the exception.

Difference 7:

Stored procedure can call the user defined functions

The function can be called from the stored procedure. 
 

CREATE PROC Pr_RetirveCustomers AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SELECT * FROM Customers SELECT * 
FROM [dbo].fn_GetOrderedCustomers (5) END

But the function cannot call the stored procedures.

The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server.

• System Stored procedure
• User defined Stored procedure
• NET CLR stored procedure
• Extended stored procedure

Except extended stored procedures no one can call the user defined functions.

Difference 8:

Stored procedures can have input and output parameters.

As you know, the input and output are the parameters which can return the results through that variable. The output parameter can be only used to return the results through the output variable. But the input parameter can be do the both input and output operations.

But the function can have only input parameters. 

This won't allow us to use the output parameters. But we can use input parameter.

Difference 9:

Stored procedures can have select and all DML operations.

The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.

But the function can do only select operation.

 The function won't allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation.

It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.

Difference 10:

Function cannot have the transaction statements.

The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function.

Stored procedure can use transaction statements.

The transaction statement can be used inside the stored procedures.

Difference 11:

Stored procedures can use all the data types available in sql server.

The parameters for the stored procedures can be any data types which are available on the sql server.

But the function cannot use the ntext, image and timestamp data types as return type.

The function won't allow several data types of the sql server as a parameter.

Difference 12:

Stored procedures can create table variable and cannot return the table variable.

The table variable is one of the performances tuning mechanism. Because it takes minimum resources and it uses the memory location for store the data. (Recommended for minimum rows)

It can be created and do the operations. But it cannot be the return type.

But the function can create, update and delete the table variable. It can return table variable.

It can be created and can do all the DML operations and it can be the return type. That is called the multi valued table function.

Difference 13:

Stored procedure can have the dynamic sql statement and which can be executed using sp_executesql statement.

The stored procedure can have the dynamic sql statement for the complex decision making operations which generated inside the stored procedures. It can be executed using the sp_executesql statement.

But the function cannot execute the sp_executesql statement.

The function can generate the dynamic sql statement. But it cannot get execute. It will not allow writing the sp_executesql command to execute the dynamically created sql statement.

Difference 14:

Stored procedure allows getdate () or other non-deterministic functions can be allowed. The stored procedure will allow all the sql server built-in functions like getdate(),DB_ID(),

DB_NAME (), etc..,

But the function won't allow the non-deterministic functions.

The function will not allow using non-deterministic functions like GETDATE ()

Conclusion

I believe that the above mentioned differences are valid. If you find any mistakes then please correct that. Give your feedback comments so that I can improve my writing skills.