List the DDL, DML, DCL and TCL statements in SQL Server

DDL stands for Data Definition Language. These statements are used to define the database structure (also known as database schema). Given below are the DDL statements:

  • CREATE – CREATE is used for creating database objects, such as tables, views, indexes, etc.
  • DROP – DROP is used for deleting database objects.
  • ALTER – ALTER is used for modifying the structure of database objects.
  • RENAME – RENAME is used for renaming database objects.
  • TRUNCATE – TRUNCATE is used for deleting all records from a table.
  • COMMENT – COMMENT is used for adding comments to a data dictionary.

DML stands for Data Manipulation Language. These statements are used to manage data within database objects. Given below are the DML commands:

  • SELECT – SELECT is used to retrieve data from a database.
  • INSERT – INSERT is used to insert data into a table.
  • UPDATE – UPDATE is used to update existing data within a table.
  • DELETE – DELETE is used to delete all records from a table, the space for the records remain.
  • MERGE – MERGE is used to UPSERT operation (conditional INSERT/UPDATE).
  • CALL – CALL is used to call a PL/SQL or Java subprogram.
  • EXPLAIN PLAN – EXPLAIN PLAN is used to explain access path to data.
  • LOCK TABLE – LOCK TABLE is used to control concurrency.

DCL stands for Data Control Language. Given below are the DCL statements:

  • GRANT – GRANT command gives user's access privileges to database.
  • REVOKE – REVOKE command withdraws access privileges given with the GRANT command.

TCL stands for Transaction Control Language. The TCL statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – COMMIT saves the work done.
  • SAVEPOINT – SAVEPOINT identifies a point in a transaction to which you can later roll back.
  • ROLLBACK – ROLLBACK restores database to original state since the last COMMIT.
  • SET TRANSACTION – SET TRANSACTION changes transaction options like isolation level and what rollback segment to use.

No comments:

Post a Comment