DDL in SQL Server: CREATE, ALTER, DROP, TRUNCATE

DDL commands create or change the STRUCTURE of tables.

CREATE   -> make a new table
ALTER    -> change a table
DROP     -> delete a table completely
TRUNCATE -> delete all rows, keep the table

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

-- Create a database for these basic lessons (only if it does not exist)
IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

-- Delete old tables if they exist, so this file can be run again
DROP TABLE IF EXISTS Marks;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Teachers;
GO

CREATE Table

CREATE TABLE Students
(
    StudentID INT,
    Name      VARCHAR(50),
    Age       INT
);

SELECT * FROM Students;    -- empty table

ALTER Table - add a new column

ALTER TABLE Students ADD City VARCHAR(50);

SELECT * FROM Students;    -- City column is added

ALTER Table - change a column's data type / size

ALTER TABLE Students ALTER COLUMN Name VARCHAR(100);

ALTER Table - remove a column

ALTER TABLE Students DROP COLUMN City;

Rename a column (SQL Server uses sp_rename)

EXEC sp_rename 'Students.Name', 'StudentName', 'COLUMN';

SELECT * FROM Students;

Caution: Changing any part of an object name could break scripts and stored procedures.

TRUNCATE - removes All rows, but the table stays

INSERT INTO Students VALUES (1, 'Ravi', 20);
INSERT INTO Students VALUES (2, 'Sita', 21);
SELECT * FROM Students;    -- 2 rows

TRUNCATE TABLE Students;
SELECT * FROM Students;    -- 0 rows, table still exists

(1 row affected)
(1 row affected)

DROP - deletes the table completely

DROP TABLE Students;
-- SELECT * FROM Students;   -- this would now give an error: Invalid object name

-- =============================================
-- CONSTRAINTS (rules on columns)
--   PRIMARY KEY -> unique + not empty, identifies each row
--   NOT NULL    -> value is required
--   UNIQUE      -> no duplicate values
--   DEFAULT     -> value used when none is given
--   CHECK       -> value must pass a condition
--   FOREIGN KEY -> value must exist in another table
-- =============================================

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name      VARCHAR(50) NOT NULL,
    Email     VARCHAR(100) UNIQUE,
    Age       INT CHECK (Age >= 5),
    City      VARCHAR(50) DEFAULT 'Hyderabad'
);

CREATE TABLE Marks
(
    MarkID    INT PRIMARY KEY,
    StudentID INT FOREIGN KEY REFERENCES Students(StudentID),
    Subject   VARCHAR(30),
    Marks     INT
);

-- IDENTITY : SQL Server fills the number automatically (1, 2, 3 ...)
CREATE TABLE Teachers
(
    TeacherID INT IDENTITY(1,1) PRIMARY KEY,
    Name      VARCHAR(50)
);

INSERT INTO Teachers (Name) VALUES ('Meena');
INSERT INTO Teachers (Name) VALUES ('Kiran');
SELECT * FROM Teachers;    -- TeacherID is 1 and 2 automatically

-- See the columns of a table
EXEC sp_help 'Students';

(1 row affected)
(1 row affected)

No views with schema binding reference table 'Students'.