SQL Notes

SQL(Structured Query Language) Concepts:

Catagories of Sql Statements :

1. DDL(Data Definition Language)   

2. DML(Data Manipulation Language) 

3. DCL(Data Control Language)

4. TCL(Transaction Control Language)

DDL
In Data Definition Language (DDL), we have three different SQL statements.

CREATE : create the new table.
Eg., Create Table Student (Rank Int,StudentName varchar(50),Mark Float)

ALTER : Alter statement can add a column, modify a column, drop a column, rename a column or rename a table.

Eg., ALTER TABLE Customers ADD Email varchar(255);                ALTER TABLE Customers MODIFY Address char(100);       ALTER TABLE Customers DROP COLUMN Email;                   ALTER TABLE Customer RENAME COLUMN Address TO Addr;

                 

Difference between TRUNCATE , DELETE and DROP in SQL Server

TRUNCATE

• TRUNCATE is a DDL command
• TRUNCATE is executed using a table lock and whole table is locked for remove all records.
• We cannot use Where clause with TRUNCATE.
• TRUNCATE removes all rows from a table.
• Minimal logging in transaction log, so it is performance wise faster.
• 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 log.
• Identify column is reset to its seed value if table contains any identity column.
• To use Truncate on a table you need at least ALTER permission on the table.
• Truncate uses the less transaction space than Delete statement.
• Truncate cannot be used with indexed views.

DELETE

• DELETE is a DML command.
• DELETE is executed using a row lock, each row in the table is locked for deletion.
• We can use where clause with DELETE to filter & delete specific records.
• The DELETE command is used to remove rows from a table based on WHERE condition.
• It maintain the log, so it slower than TRUNCATE.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
• Identity of column keep DELETE retain the identity.
• To use Delete you need DELETE permission on the table.
• Delete uses the more transaction space than Truncate statement.
• Delete can be used with indexed views.

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.
• DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
• DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.