DDL and DML statements in SQL Server
DDL and DML statements constitute the vocabulary of SQL Server. It is very important to know the significance of their usage and understand their full import. This article aims to simplify the process of understanding these very important constituents of the database dictionary.
What does DDL and DML stand for?
DDL stands for Data Definition Language and DML stands for Data Manipulation Language.
What are some of the commands convered by DDL?
- Create
- Alter
- Drop
- Truncate
What are some of the commands convered by DML?
-Select
-Update
-Insert
-Delete
Illustrations :
Listed are illustrations for all the above.
Create is used in various flavors and can be used to create a table, stored procedure, view, trigger or for that matter any object in the schema.
As an illustration let us consider the Create Table command.
Example
------------
Create Table xyz
(
idx integer PRIMARY KEY,
col1 varchar(10) not null,
col2 bit
)
Here we have defined a table object with idx as the primary key. The primary key constraint is specified as a column level constraint.
Other columns have different data types like varchar, bit.
Alter command can be used in different scenarios. To illustrate, consider two scenarios - dropping a column and altering a column data type.
Both are possible with Alter command.
Example
-------------
Alter table table1 drop column col1
Alter table table1 alter column col1 varchar(25)
Drop command is used to drop an object from the schema. It can be used to drop a table, stored procedure etc.
Example
-------------
Drop table table1
Drop proc xsp_proc
Truncate is used to delete all data i.e. rows from a table.
It clears the table completely.
It cannot take any condition. i.e. WHERE clause
Example
-------------
Truncate table table1
It is to be noted that DDL commands are irreversible. i.e. unlike DML commands they cannot be rolled back.
Select command is used to return a rowset from a table.
This command can be refined with an appropriate where clause, and have multiple conditions in this clause depending on the
data that you wish to retrieve.
Select * selects all rows and results in a table scan which is slow.
Examples
---------------
Select * from table
Select col1, col2 from table
Select col3 from table where col2 > 5
Update command is used to update a column or multiple columns with or without a condition attached.
Examples
---------------
Update table set col1 = 'A'
Update table set col1 = 'G' where col2 = 7
Update table set col1 = 1, col2 = 2, col3 = 9
Insert command is used to add data to a table.
Examples
---------------
Insert into table values (1,'Jane')
Insert into table (col1,col2) values (3,'Mary')
Note : Adding indexes to a table slows down inserts as the index has to be updated at each insert.
Delete command is used to remove data from a table. (usually specific rows depending on a condition).
Example
-------------
Delete from table where col2 = 80
Delete from table
It is to be noted that all DML commands are reversible. i.e. they can be rolled back.
Enhancements
-----------------------
All the above clauses come with some or other enhancements.
Select, Insert, Update and Delete come with the TOP clause.
Create and Alter table allow creation of persisted computed columns, They also allow specifying max clause for varchar, nvarchar and varbinary data.
These are the salient features of DDL and DML statements in SQL Server.