SQL Server Commands - DDL, DML, DCL, TCL, DQL
#DeocdeITeS
Types of SQL Server Commands - DDL, DML, DCL, TCL, and DQL
SQL Server supports 5 types of commands for all type of activities using T-SQL.
Data Definition Language (DDL)
DDL is a set of commands used to create, modify, or delete Database or Database objects. All DDL changes are auto commit and reflect instantly. These commands deal with the physical structure of objects. DDL consists of 5 commands:-
Command Description Example
Create Used to CREATE new Database or DB objects like Tables or vies, etc. Create Table TableName (ColumnName DataType);
OR
Create Database DatabaseName
Drop Used to DROP existing Database or DB objects like Tables or vies, etc. DROP Table TableName
OR
DROP Database DatabaseName
Rename Used to RENAME existing objects. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type. Exec SP_RENAME CURRENTOBJECTNAME, NEWOBJECTNAME, OBJECTTYPE
Alter Used to ALTER existing Database or DB objects like Tables or vies, etc. ALTER TABLE TABLENAME
OR
ALTER DATABASE DATABASENAME
Truncate Used to delete the database from Table in one go TRUNCATE TABLE TABLENAME
Data Manipulation Language(DML)
DML commands are used to manipulate data inside the table like data insert, delete, modify. Once you create a table using DDL commands, DML commands will be used for data changes. DML consists of 3 commands described below:-
Command Description Example
Insert Insert new rows in the table INSERT INTO TABLENAME (COL1, COL2)
VALUES(VAL1, VAL2)
Update Update existing data in the table UPDATE TABLENAME
SET COL1 = NEWVALUE
WHERE CONDITION -- OPTIONAL
Delete Delete existing data from the table DELETE TABLE TABLENAME
WHERE CONDITION -- OPTIONAL
Data Control Language (DCL)
DCL commands as the name implies are used to control access permissions of users over data. You can control user permissions using DCL commands. DCL consists of 3 commands:-
Command Description Example
GRANT Grant access permission on the object to the user GRANT PERMISSION NAME Like INSERT \ DELETE ON OBJECTNAME TO USERNAME
DENY Deny access permission on the object to the user DENY PERMISSION NAME Like INSERT \ DELETE ON OBJECTNAME TO USERNAME
REVOKE Revoke existing access permission on the object from the user REVOKE PERMISSION NAME Like INSERT \ DELETE ON OBJECTNAME TO USERNAME
Transaction Control Language (TCL)
TCL commands are used to control transactions in SQL Server. You can use them to control transactions in runtime. This helps to ensure data integrity & successful completion of transactions. TCL also helps in achieving error handling. TCL consist of 5 commands:-
Command Description Example
BEGIN TRAN To start the new transaction BEGIN TRAN Transaction Name Optional, preferred when running multiple transactions
COMMIT TRAN To commit or SAVE running transaction COMMIT;
ROLLBACK Rollback or UNDO changes and go to the original state ROLLBACK;
SAVEPOINT: It used to create points in between transactions for partial rollback. Instead of complete transaction rollback, you can create SAVEPOINT and use them for a partial rollback SAVEPOINT SavePoint Name for Refrence
AUTOCOMMIT: AUTOCOMMIT command automatically commits each transaction after its execution. If this command is set, then no need to explicitly issue a commit. We cannot rollback our transactions if AUTOCOMMIT is on. This needs to be set /unset before we begin any transactions. AUTOCOMMIT
Data Query Language (DQL)
DQL command is used to retrieve data from Objects like Table, View, Functions.
Command Description Example
Select Retrieve data from objects SELECT * FROM TABLENAME
WHERE CONDITION -- OPTIONAL
OR
SELECT COL1, COL2…COLN FROM TABLENAME
WHERE CONDITION -- OPTIONAL