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:

  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 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:

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

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

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, from which the last three records are not yet committed.

unCommited records

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".
  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 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.