Tuesday, 29 March 2016

About DDL, DML, DCL, TCL, DQL in SQL Server

DDL- it is data definition language, this is used to define the database structure or schema (create, alter, drop, Rename, truncate and Comment.
  • Create: it is used to create database and database Objects like tables, index, stored procedure, views, triggers, functions and etc.
Example: To create Customer table.
create table Customer(
Id int primary key identity(1,1) not null,
Name nvarchar(50) ,
HomeAddress nvarchar(50)
  • Alter: it is used to create database and its Objects.
  • Drop: it is used to delete objects from database.
  • Truncate: it is used to remove all records from a table, including all spaces allocated for records are removed.
  • Rename: It is used to rename the objects.
  • Comment: // Single line Comments, /* --Multi Line Comments-- */ used to comment the SQL statements. CL in SQL
DML –it is Data Manipulation Language, this commands is used for managing data within schema objects (Select, Insert, Update and Delete).
  • Select: To retrieve data from the database table.
  • Insert: To insert date into a table.
  • Update: To update the existing data in a table.
  • Delete: delete all records from a table.
DCL-it is Data Control Language; this command is for control the access (Grant, Revoke) to data stored in the database.
  • Grant: All users access privileges to database.
  • Revoke: Withdraw user’s access privileges given by using the Grant command.
TCL-it is Transaction Control Language, this commands is used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. (Commit, Rollback and Savepoint).
  • Commit: Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
  • Rollback: Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
  • Save point: creates points within groups of transactions in which to ROLLBACK.
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
DQL-it is Data Query Language, this commands is used to retrieve(select) data from the database .

  • Select: To retrieve data from the database table.

No comments:

Post a Comment