Types of Commands in SQL Server

Introduction

This article shows the four basic categories of SQL Server commands and the types of commands assigned to each 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 commands 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 we will 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, 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 Commands in SQL

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. Find a detailed article about DDL Commands here: DDL Commands in SQL

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, etc. 

For example 

CREATE DATABASE DB2  
GO  
CREATE TABLE tblDemo  
(  
   Id int primary key,  
   Name char(20)  
)  
GO  
DROP DATABASE DB2  

DML Commands in SQL 

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

The following are the commands in this category. Find detailed articles about DML Commands here: DML Commands in SQL

Command	Description
Insert	Insert data into the 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 data stored in entities.

For example

INSERT INTO tblDemo VALUES (1,'Abhishek')  
GO  
DELETE FROM tblDemo WHERE Id = 4  
GO  
UPDATE tblDemo  
SET Name = 'Sunny'  
WHERE Id = 6  
GO  

DCL Commands in SQL 

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

The following are the commands in this category. Find detailed articles about DCL Commands in SQL here: DCL Commands in SQL

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.

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

CREATE table tblArticles  
(  
    ArticleId int primary key identity,  
    ArticleName varchar(10),  
    Category varchar(10)  
)  

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 is not permitted to create anything right now. In our next article, we'll learn how to grant or revoke permission on objects.

TCL Commands in SQL

Transaction Control Language (TCL) commands you to manage SQL server transactions. 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 three records, as shown below.

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

Begin Tran  
Insert INTO tblStudents VALUES ('Sumit')  
COMMIT 

Now, we have four records.

Now, we'll add three records, one by one, with save point, but we don't commit our transaction.

Begin Tran  
Insert INTO tblStudents VALUES ('Kajal')  
SAVE Tran A;  
Insert INTO tblStudents VALUES ('Rahul')  
SAVE Tran B;  
Insert INTO tblStudents VALUES ('Ram')  
SAVE Tran C;  
  
SELECT * from tblStudents  

We now have the following records in the table, in which the last three records are not yet committed.

Now, we have three 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."

ROLLBACK TRAN B  
COMMIT  

When you fire the Select query, you'll get records up to ID 6.

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

Conclusion

In this article, we have seen the types of commands in SQL Server and got an overview. We also saw how to commit transactions and roll back any transaction to any savepoint. In my next article, we'll explain 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, please give your valuable feedback and comments that enable me to provide a better article next time.