Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.
Delete Command require Log file updation for each row of
deleting process. But the Truncate command not.
So, the Truncate Command is so faster than Delete Command.
Delete command only delete the rows from the table but the
schema of the table yet remains.Whereas Truncate command
delete the rows along with the tale's schema from the
memory permanently.
Delete command deletes the data from the tables .. but can
get back on issue of roll back whereas when you issue
truncate deletes the records and oracle implicitly issues
commit command and even when you issue rollback you won't
get the data back
Truncate:
- deallocates the data pages in a table and only this
deallocation is stored in transaction log
- aquires only table and page locks for the whole table.
since no row locks are used less memory is required (lock
is a pure memory object)
- resets identity column if there is one
- removes ALL pages. NO empty pages are left behind in a
table
- fast(er)
- doesn't fire delete triggers
Delete:
- removes one row at the time and every deleted row is
stored in the transaction log
- aquires table and/or page and row locks for the whole
table
- leaves identity column alone
- can leave empty pages in a table since empty page
removal requires a table lock which doesn't necessarily
happen
- slow(er)
- fires delete triggers
http://weblogs.sqlteam.com/mladenp/archive/2007/10/03/SQL-
Server-Why-is-TRUNCATE-TABLE-a-DDL-and-not.aspx
Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.
i want to create procedure for create table in sql server
2005
for example
create procedure create_table
@table varchar(20)
as
create @table(
id int,
name char(20)
)
but it will get error
what is solution?