DELETE Command in SQL SERVER 2012

DELETE :

Delete command is used to delete the rows from a table,we can delete specific rows by using where clause.

Without where clause delete command will delete all rows without disturbing the table structure.

Syntax for delete command  :

DELETE [TOP(N)] [FROM]  <TABLE_NAME>
 
[WHERE <CLAUSE>]

Examples:

SELECT * FROM EMP20

DELETE FROM EMP20

it will delete all the employees from the table.

SELECT * FROM EMP20 --will get empty table

DROP TABLE EMP20--going to drop a table
 
It will drop the table permanently.

SELECT * INTO EMP20 FROM EMP--creating a table again and dumping the rows into new table.

SELECT * FROM EMP20

Now the table contains all the records.

DELETE FROM EMP20
WHERE ENO=1001
 
we are going to drop a record with eno equal to 1001

SELECT * FROM EMP20

DELETE FROM EMP20
WHERE ENAME='RAMESH'

we are going to drop a record with ename equal to 'RAMESH'

SELECT * FROM EMP20

DELETE FROM EMP20
WHERE JOB='CLERK'

we are going to drop a record with JOB equal to 'CLERK'

DELETE TOP(20) FROM EMP20-- will delete top 20 records from the table.

SELECT * FROM EMP20

DELETE TOP(2) FROM EMP20--will drop top 2records from the table.

SELECT * FROM EMP20

Thanks
GSV