Types of Commands in SQL Server

This article shows what types of commands are assigned to which category and also what those 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 a 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 are the category responsible for dealing with the structure of objects. I mean that 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 created object.
Drop	Used to delete object.

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

For example:

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

DML

Data Manipulation Language (DML) commands manipulate data stored in objects like tables, views and so on. 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 table.
Update	Update data into table.
Insert	Into Insert bulk data into table.

Using these commands you can manipulate any kind of 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

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 Database or any other object.
Revoke	Take back the access from user.

For example: we have the following data.

Database: CSharpCornerDB
Table:
User: CSharpCorner

Currently we didn't provide any permission to 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 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 database to its last committed state.
Save Tran	This command is used to save the transaction so that we can rollback that transaction to the point whenever necessary.

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



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

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

Now we have 4 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

Now we have the following records in the table, from which the last three records are not yet committed.



Now we have 3 savepoints, in other words 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, in other words at "Rahul".

ROLLBACK TRAN B
COMMIT

Now when you fire the select query, you'll get records up to ID 6.



So this was the article based on types of commands in SQL Server with which you can play with data stored in SQL Server.

In this article, we have seen the types of commands in SQL Server and done some overview of that. We have also seen how to commit transactions and how to roll back any transaction to any saved point.

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 then please let me know. Please provide your valuable feedback and comments that enable me to provide a better article the next time.