Friday, 17 June 2016

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.

 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.
  

 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.



34 comments:

  1. thank you for providing the valuable information regarding msbi ..keep update with your blogs...once check it out
    MSBI Online Training Hyderabad

    ReplyDelete
  2. It is very good blog and useful for students and developer ,

    Sql server DBA Online Course Bangalore

    ReplyDelete
  3. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training

    ReplyDelete
  4. very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! שרת וירטואלי

    ReplyDelete
  5. Very nice article,Keep Updating more posts with us.
    Thank you..
    MSBI Online Training India

    ReplyDelete
  6. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
    best Workday HCM Online Training
    top Workday HCM Online Training

    ReplyDelete
  7. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
    best Workday HCM Online Training
    top Workday HCM Online Training

    ReplyDelete
  8. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
    best Workday HCM Online Training
    top Workday HCM Online Training

    ReplyDelete
  9. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
    best Workday HCM Online Training
    top Workday HCM Online Training

    ReplyDelete
  10. Thanks a lot for information, thats really awesome

    ReplyDelete
  11. it’s very helpful thanks for your valuable information follow us
    Tableau online training in Hyderabad

    ReplyDelete

  12. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.Business Announcer

    ReplyDelete

  13. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work Business Announcer

    ReplyDelete
  14. Respect and I have a neat offer you: Where To Loan For House Renovation house renovation companies

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts