Delete
|
Truncate
|
In
Delete Query we can use Where Clause
|
In
Truncate Query we can not use Where Clause
|
Syntax
DELETE
FROM
[Table_Name]
or
DELETE
FROM
[Table_Name] WHERE
[Condition]
|
Syntax:
TRUNCATE
TABLE
[Table_Name]
|
| Comparing the Delete is Slower than truncate | Comparing the Truncate is faster than the delete query |
| In Delete Query we can delete single only or multiple rows by using where clause. | In Truncate Query All the rows data truncated from table. |
| We can use triggers when delete query is executed . | We can not use triggers when truncate query is executed. |
| Delete query is logs the data. | Truncate can not log the data. |
| Delete query delete the data from table using row by row scenario. | Truncate query truncate the data from table using page by page scenario. |
| Delete can not reset the identity value in a table. | Truncate can reset the identity value in a table. |
It
does not claims the memory after delete operation is performed
Example:
Create
table
CREATE
TABLE
TempStudent
(
FName
varchar(100),
LName
varchar(100)
)
Insert
some dummy data
Insert
into
TempStudent(FName,LName)
VALUES('Rakesh','Kalluri')
Go
1000
Check
the memory used for this table.
exec
sp_spaceused
'TempStudent'
Delete
data from table
DELETE
FROM
TempStudent
Check
the memory used for this table after delete
exec
sp_spaceused
'TempStudent'
 |
It
claims the memory after truncate operation is performed
Example:
Create
table
CREATE
TABLE
TempStudent
(
FName
varchar(100),
LName
varchar(100)
)
Insert
some dummy data
Insert
into
TempStudent(FName,LName)
VALUES('Rakesh','Kalluri')
Go
1000
Check
the memory used for this table.
exec
sp_spaceused
'TempStudent'
Truncate
data from table
TRUNCATE
TABLE
TempStudent
Check
the memory used for this table after truncate
exec
sp_spaceused
'TempStudent'
 |
Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...
Thank you so much
ReplyDelete