DDL, DML, DCL and TCL Commands in sql Server

SQL Language statement

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

 

DDL (Data Definition Language)

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Database object may be table, view, trigger, User defined function, Sp, index etc.

CREATE

 This is use to creates data objects in the database

use TestSSIS --Table create table Employee ( EmpID int, Name Varchar(100) ) --View Create view VEmployee AS select * from Employee --SP create proc Usp_Emp As select * from Employee

Now I am creating a table
  

We can see it on the explorer

ALTER

 Alters is used to changes the structure of the database object. Like suppose I want to change the Data type of the column or we need to add the new column on the table we use Alter.

See the example here I am going to Add one column in the table.

Alter table Employee Add EmpAddress varchar(100)

Now we see a new column is added on the table

DROP

It is used to Deletes objects of the database

Drop table Employee

Now table Employee has been deleted from the Database.

Not able to find this table.

TRUNCATE

 Deletes all records from a table and resets table identity to initial value. It is only use for table object.

Now again I am creating the table and inserting the data on it.

Now I am using Truncate to deleting the records from the table.

  

Now see the output

  

Truncate only delete the data from the table. Structure will be remaining same.

See the Difference between Deleteand Truncate.

http://bageshkumarbagi-msbi.blogspot.in/2015/12/difference-between-delete-and-truncate.html

 DML (Data Manipulation language)

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. With the help of DML comment we are manipulating the data.

SELECT

 Retrieves data from a table

  

INSERT

 Inserts data into a table

  

Inserting 3 records

See the all records

UPDATE

Updates existing data into a table

See the update values.

  

DELETE

Deletes all records from a table

See the deleted values.

  

See the Difference between Deleteand Truncate.

http://bageshkumarbagi-msbi.blogspot.in/2015/12/difference-between-delete-and-truncate.html

 TCL (Transactional Control Language)

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT

 Saves work done in transactions

Begin transaction trans --DML command Commit --or Rollback transaction trans

ROLLBACK

 Restores database to original state since the last COMMIT command in transactions

See the example

Begin try Begin transaction trans update Employee set name='Ramesh' where EmpID=2 Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

  

See the committed data

  

Now see the example of Rollback

Begin try Begin transaction trans update Employee set name='Ramesh' where EmpID='Raj' Commit Print 'Transaction Committed successfully' End Try Begin Catch Rollback transaction print 'Transaction Rollback' End catch

This transaction gets failed because I supplied EmpID as Varchar.

  

SAVE TRANSACTION

 Sets a save point within a transaction

For More details read the Transaction in sql server

http://bageshkumarbagi-msbi.blogspot.in/2016/06/transaction-in-sql-server.html

DCL (Data Control Language)

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT

 Gives user’s access privileges to database

Syntax

Grant [privilege Name] on object Name  To [user Name/Public/role Name] [with Grant Option]

  

See the example

  

REVOKE

 Withdraws user’s access privileges to database given with the GRANT command

Revoke [privilage Name] on objectName from [user Name/public/role Name]

See example.