What Are DDL Commands and What Are They Used For?

DDL commands are part of SQL and work alongside DML, DCL and TCL commands to create and manage databases. They form the basic building blocks for administering SQL and are useful to know if you’re going to be managing or creating database using Structured Query Language.

SQL commands are comprised mainly of DDL and DML. You would create or delete a database with DDL commands and add, move or change data with DML. DCL commands will help you manage users, permissions and data security while TCL helps manage the changes you make within DML. They all work together within SQL to provide the tools you need to effectively create and manage databases. Knowing these commands are fundamental for anyone wanting to get into database admin or support.

I’m no SQL expert but I have used and administered them. I am also close friends with someone who writes this stuff in his sleep. The following tutorial may be my words, but the knowledge and expertise are all his. Any errors or omissions will be mine alone.

What follows are the basic commands for each language. DDL commands list the terms necessary to create databases, tables and objects. DML commands list the terms necessary to manage objects and data within the database you created. DCL commands list the terms necessary to manage users and TCL commands are what you may use to set save points and commit your changes.

DDL (Data Definition Language)

DDL (Data Definition Language) is used to define the database scheme. It helps create and manage the database and objects within it. DDL statements change the structure of the database in a number of ways such as creating, deleting, modifying schema and objects.

Depending on the flavor of SQL you’re using, DDL statements can include:

• ALTER TABLE
• ALTER VIEW
• COMPUTE STATS
• CREATE DATABASE
• CREATE FUNCTION
• CREATE ROLE
• CREATE TABLE
• CREATE VIEW
• DROP DATABASE
• DROP FUNCTION
• DROP ROLE
• DROP TABLE
• DROP VIEW
• GRANT
• REVOKE

Core SQL DDL statements are:

• ALTER
• TRUNCATE
• COMMENT
• CREATE
• DESCRIBE
• RENAME
• DROP
• SHOW
• USE

These will appear in all versions of SQL. Each DDL command will require an object type and an identifier in order to work.

For example: CREATE TABLE [table name] ( [column definitions] ) [table parameters] or DROP objecttype objectname.

DML (Data Manipulation Language)

Another element of SQL is DML (Data Manipulation Language). This allows you to retrieve, insert, update, remove and generally manage data within an SQL database. These commands will be the ones you will use on a day to day basis when managing databases. This isn’t a specific language on its own but forms part of SQL.

• SELECT
• INSERT
• UPDATE
• DELETE
• BULK INSERT
• MERGE
• READTEXT
• UPDATETEXT
• WRITETEXT
• BEGIN
• COMMIT
• ROLLBACK
• COPY INTO <table>
• COPY INTO <location>
• PUT
• GET
• LIST
• REMOVE

Again, different flavors of SQL will use some or all of these DML commands and many even add their own.

For example: SELECT [column name(s)] from [table name] or UPDATE [table name] SET [column name = value] where [condition].

DCL (Data Control Language)

DCL (Data Control Language) is used to control users and the system around the database itself. It is most often used to manage user permissions and manage data security so it’s important to know.

Common DCL commands include:

• GRANT
• REVOKE
• SETUSER
• EXECUTE AS, statement
• EXECUTE AS, clause
• REVERT
• OPEN MASTER KEY.
• CLOSE MASTER KEY
• OPEN SYMMETRIC KEY
• CLOSE SYMMETRIC KEY

For example: GRANT [privilege] ON [object] TO [user] or REVOKE [permission] ON [object] FROM [user].

TCL (Transaction Control Language)

TCL (Transaction Control Language) is used to group DML statements together to form logical transactions and to manage any changes made to the data by DML statements. If you perform DML commands, they are not necessarily made permanent until you close the current session. If you want to manually commit for any reason, you can use a TCL command.

• BEGIN DISTRIBUTED TRANSACTION
• BEGIN TRANSACTION
• COMMIT TRANSACTION
• COMMIT WORK
• ROLLBACK TRANSACTION
• ROLLBACK WORK
• SAVE TRANSACTION

For example: COMMIT; will save any changes made with DML before you move on to other commands. SAVEPOINT savepoint_name; will create a save position much like a save game. Should anything go wrong, you can roll the database back to that point and start again.

Those four are the basic building blocks of managing SQL databases. This is a very high level page for those wanting to explore the subject of SQL. Get to know these commands and how they are used and a whole new world of databases and database management comes into play. As SQL is behind most cloud applications, websites, web apps, apps and most productivity suites around it is a good skill to learn!