DDL: Data Definition Language

Truncate is a simple DDL command in SQL Server.

Truncate command in SQL Server works very similar to DELETE command.

Since TRUNCATE command will not store the logs,  truncated data from a table cannot be restored back.

Here is the syntax for TRUNCATE Command:

Syntax:

TRUNCATE TABLE <Table Name>

Example:

Lets see how we can truncate data from a table with the name MOBILE:

Data in the table before running Truncate command:
















Run the following command in SQL SERVER Query Window:

TRUNCATE TABLE MOBILE

Data in the table After running Truncate command:















Few important points about TRUNCATE Command in SQL Server

1. Since TRUNCATE Command operates(manipulates) on data in the table ,many people assume it as a DML statement. But, TRUNCATE is a DDL Command.
2. TRUNCATE Command will not operate on each row (or each data). It will operate on the entire database object. So, it will consume fewer database resources than DELETE Command
3. TRUNCATE command will not store logs
4. Data Truncated once cannot be restored back.

Summary:

Here we have learnt about TRUNCATE Command.

DDL: Data Definition Language

DROP command used to drop the database objects in SQL Server.

Here are the example of DROP Command:

• DROP DATABASE:

Drop or remove a database with the name MYDB from the server.

DROP DATABASE MYDB

• DROP TABLE:

Drop or remove a table with the name MYTABLE from database.

DROP TABLE MYTABLE

• DROP PROCEDURE:

Drop or remove a stored procedure with the name MYSP from database.

DROP PROC MYSP

DROP PROCEDURE MYSP

• DROP FUNCTION:

Drop or remove a function with the name MYFUNC from database

DROP FUNCTION MYFUNC

• DROP TRIGGER:

Drop or remove a Trigger with the name MYTGR from database

DROP TRGIGGER MYTGR

Summary:

Drop is DDL command, which will remove database objects from server.

DDL: Data Definition Language.
DDL commands define the structure to the database objects.

CREATE is one of the important DDL command. As the name indicates this command is used to create the database object.

Any database object like table,view,function, stored procedure, rule, user defined database etc., can be created using this Command.

CREATE command is used to define the structure for Database also.

Here are some simple examples for database objects creation using CREATE command:

• CREATE DATABASE: Database creation syntax is given below.

          CREATE DATABASE <Database Name>

          CREATE DATABASE TEST -- This Statement creates a database with the name TEST 

• CREATE TABLE: Table creation syntax is given below.

          CREATE TABLE <TABLE Name> ( Col1, col2,....Coln)
           *Col : Column
  
           Lets create a simple table with name MOBILE with columns as Mobile Brand,Mobile   Model, and Price of the mobile.

Below picture gives commands used to create the table












Above statements Create a table with the name MOBILE in the database.

• CREATE PROCEDURE:

Here we will see a simple stored procedure.
Create a stored procedure with the name GET_DATA to fetch data from MOBILE Table

CREATE PROCEDURE GET_DATA
AS
BEGIN
        SELECT * FROM MOBILE
END

Above statements create a stored procedure with the name GET_DATA.

We will look into stored procedures in depth in coming sessions.

• CREATE VIEW:

View is a virtual table. I will discuss more about views in coming posts.

For now, lets create a view which provides list of all mobiles whose price is less than Rs.5000/-

CREATE
AS
SELECT * FROM MOBILE VIEW MOBILE_VIEW WHERE PRICE<5000
WHERE
This view will list all the data from MOBILE table whose PRICE is less than 5000.

• CREATE FUNCTION:

Functions are set of SQL Commands which perform complex operations or calculation in block and returns the output value.

Function can return a scalar value or Table. Functions will be discussed in depth in later Sessions.

For now, lets create a simple function which gives us the least price of the mobile.
CREATE FUNCTION PRICE_FUNCTION()RETURNS
AS
BEGIN
   DECLARE  @PRICE INT
   SELECT @PRICE   MIN(PRICE) FROM MOBILE
RETURN @PRICE  
END
So far Using CREATE command we have created simple Database, Tables, Views, Procedures and Function.
Summary:
In this article we have learnt about various ways to use CREATE command in SQL Server.

Commands Learnt:
CREATE,DATABASE,TABLE,VIEW, PROCEDURE,FUNCTION,AS,BEGIN,END,RETURNS,INT , MIN