Structured Query Language (SQL): Data Definition Language (DDL)

Summary

Structured Query Language (SQL) is a Query Language that is used to create,delete and change database objects in a Relational Database Management System (RDBMS). Within SQL there are two main types of statements: Data Definition Language (DDL) and Data Manipulation Language (DML) Statements. This article focuses on Data Definition Language (DDL). There are three main commands in Data Definition Language (DDL): CREATE, DROP and ALTER. These commands are used to CREATE and DROP databases, tables, indexes, stored procedures, triggers, defaults, constraints, and views. The ALTER command can only be used on a database table.

1. CREATE - The CREATE statement is used to create databases, tables, indexes, stored procedures, triggers, defaults, constraints, and views. The CREATE statement has the following components CREATE Object_type Object_Name (Parameters).
   

   'Create Database.
   '--------------------------------------------------------------------------------------
   CREATE DATABASE Northwind
       ON
          (NAME = Northwind_dat,
          FILENAME = 'C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\DATA\NorthwindDat.mdf',
          SIZE = 10,
          MAXSIZE = 50,
          FILEGROWTH = 5)
       LOG ON
          (NAME = Northwind_log,
          FILENAME = 'C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\DATA\NorthwindLog.ldf',
          SIZE = 5MB,
          MAXSIZE = 25MB,
          FILEGROWTH = 5MB );
   GO
   
   'Create Table.
   '--------------------------------------------------------------------------------------
   CREATE TABLE "Customers" (
         "CustomerID" nchar (5) NOT NULL,
         "CompanyName" nvarchar (40) NOT NULL,
         "ContactName" nvarchar (30) NULL,
         "ContactTitle" nvarchar (30) NULL,
         "Address" nvarchar (60) NULL,
         "City" nvarchar (15) NULL,
         "Region" nvarchar (15) NULL,
         "PostalCode" nvarchar (10) NULL,
         "Country" nvarchar (15) NULL,
         "Phone" nvarchar (24) NULL,
         "Fax" nvarchar (24) NULL,
         CONSTRAINT "PK_Customers" PRIMARY KEY CLUSTERED
         (
             "CustomerID"
         )
   )
   GO
   
   CREATE INDEX "City" ON "dbo"."Customers"("City")
   GO
   
   CREATE INDEX "CompanyName" ON "dbo"."Customers"("CompanyName")
   GO
   
   CREATE INDEX "PostalCode" ON "dbo"."Customers"("PostalCode")
   GO
   
   CREATE INDEX "Region" ON "dbo"."Customers"("Region")
   GO
   

2. DROP - The DROP statement is used to drop databases, tables, indexes, stored procedures, triggers, defaults, constraints, and views. The DROP statement has the following components DROP Object_type Object_Name.
   

   'Drop Database.
   '--------------------------------------------------------------------------------------
   if exists (SELECT * FROM sysdatabases WHERE name='Northwind')
       DROP DATABASE Northwind
   GO
   
   'Drop Table.
   '--------------------------------------------------------------------------------------DROP TABLE
   if exists (SELECT * FROM sysobjects WHERE id = object_id('dbo.Customers') and sysstat & 0xf = 3)
       DROP TABLE "dbo"."Customers"
   GO
   
   'Drop View.
   '--------------------------------------------------------------------------------------DROP VIEW
   if exists (SELECT * FROM sysobjects WHERE id = object_id('dbo.Category Sales for 1997') and sysstat & 0xf = 2)
       DROP VIEW "dbo"."Category Sales for 1997"
   GO
   

3. ALTER - The ALTER statement is used to change the structure of database tables. The ALTER statement has the following components ALTER TABLE Table_Name.
   

   'Alter Table.
   '--------------------------------------------------------------------------------------
   ALTER TABLE Region
       ADD CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
          (
              [RegionID]
          ) ON [PRIMARY]
   GO