Types of Commands in SQL Server

This article explains various types of SQL Server commands, like DDL, DCL, DML, and TCL etc.

This article shows the four basic categories of SQL Server commands and what types of commands are assigned to which category. Also, we will see what each category means in SQL Server with which we can define, manipulate, secure or control our data saved in entities or objects. So, let's get started.

Whenever we run/execute any query in a SQL Server query window, they're nothing but command or lines of commands.

Using these commands, we can create, delete or modify objects or data in our data/databases. And these commands are categorized into a group that we're going to learn in this article.

We refer to these lines of commands as a query or script. When we use these commands in an editor, they interact with our database and perform an action and return a result depending on that.

These commands are categorized into:

  • DDL
  • DCL
  • DML
  • TCL

Let's see these categories one-by-one.

DDL

Data Definition Language (DDL) commands category is responsible for dealing with the structure of objects. With these commands, we can modify our object/entity structure. For example, if there's one table and you want to modify the structure of that table, you can use DDL commands.

The following are the commands in this category.

Command

Description

Create Used to create objects.
Alter Used to modify the created object.
Drop Used to delete the object.

Using these commands, you can create any object like tables, views, databases, triggers, and so on.

For example -

  1. CREATE DATABASE DB2  
  2. GO  
  3. CREATE TABLE tblDemo  
  4. (  
  5.    Id int primary key,  
  6.    Name char(20)  
  7. )  
  8. GO  
  9. DROP DATABASE DB2  
DML

Data Manipulation Language (DML) commands manipulate the data stored in objects like tables, views, etc. With the help these commands, you can easily modify, insert, and delete your data.

The following are the commands in this category.
 

Command

Description

Insert Insert data into table.
Delete Delete data from the table.
Update Update data into a table.
Insert Insert bulk data into a table.

Using these commands, you can manipulate any kind of data stored in entities.

For example:

  1. INSERT INTO tblDemo VALUES (1,'Abhishek')  
  2. GO  
  3. DELETE FROM tblDemo WHERE Id = 4  
  4. GO  
  5. UPDATE tblDemo  
  6. SET Name = 'Sunny'  
  7. WHERE Id = 6  
  8. GO  
DCL

Data Control Language (DCL) commands are for security purposes. These commands are used to provide roles, permissions, access and so on.

The following are the commands in this category:

Command

Description
Grant Provide user access to the Database or any other object.
Revoke Take back the access from the user.

For example, we have the following data.

Database: CSharpCornerDB
Table:
User: CSharpCorner


Currently, we haven't provided any permission for this user.

table

Now, we'll create a table in the CSharpCornerDB database.

  1. CREATE table tblArticles  
  2. (  
  3.     ArticleId int primary key identity,  
  4.     ArticleName varchar(10),  
  5.     Category varchar(10)  
  6. )  
If we execute this command, we'll get an error message.

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'CSharpCornerDB'.


This is because this user doesn't have permission to create anything right now. We'll learn how to grant or revoke permission on objects in our next article.

TCL

Transaction Control Language (TCL) commands are for managing transactions in SQL Server. The following are the commands in this category.

Command

Description

Commit Used to save any transaction permanently.
Rollback This command is used to restore the database to its last committed state.
Save Tran This command is used to save the transaction so that we can roll back that transaction to the point whenever necessary.

For example, we have a table named "tblStudent" with 3 records as shown below.

name

Now, we'll begin our transaction and add another record and commit that transaction.

  1. Begin Tran  
  2. Insert INTO tblStudents VALUES ('Sumit')  
  3. COMMIT  
Now, we have 4 records.

id

Now, we'll add three records, one by one with save point, but we don't commit our transaction.
  1. Begin Tran  
  2. Insert INTO tblStudents VALUES ('Kajal')  
  3. SAVE Tran A;  
  4. Insert INTO tblStudents VALUES ('Rahul')  
  5. SAVE Tran B;  
  6. Insert INTO tblStudents VALUES ('Ram')  
  7. SAVE Tran C;  
  8.   
  9. SELECT * from tblStudents  
Now, we have the following records in the table, in which the last three records are not yet committed.

unCommited records

Now, we have 3 savepoints - A, B, and C. Since our transaction is not yet committed, we can roll it back to any savepoint. We'll roll it back to point B, i.e., at "Rahul".
  1. ROLLBACK TRAN B  
  2. COMMIT  
Now, when you fire the Select query, you'll get records up to ID 6.

records upto ID 6

So, these were the categories and types of commands in SQL Server with which you can play with the data.

In this article, we have seen the types of commands in SQL Server and got an overview of that. We also saw how to commit transactions and how to roll back any transaction to any savepoint.

In my next article, we'll be explaining how to deal with the GRANT and REVOKE commands. Until then, keep learning and keep sharing.

If there's any mistake in this article, please let me know. Also, give your valuable feedback and comments that enable me to provide a better article next time.