SQL CREATE, ALTER and DROP Commands with Practical Examples

Introduction

SQL Server provides several commands to create, modify, and delete database objects. Among these commands, CREATE, ALTER, and DROP are some of the most important SQL commands used by database developers and administrators.

These commands belong to DDL (Data Definition Language). DDL commands help us define and manage the structure of a database, including databases, tables, schemas, and other database objects.

In this tutorial, we will learn how to use CREATE, ALTER, and DROP commands in Microsoft SQL Server with practical examples.

By the end of this tutorial, you will understand how to create a database, create tables, add or modify columns, rename database objects, and safely remove tables or databases.

---

1. What is DDL in SQL Server?

DDL stands for Data Definition Language.

It is a category of SQL commands used to define and manage the structure of database objects.

DDL commands do not primarily work with individual records. Instead, they manage the structure in which data is stored.

Common DDL commands include:

Command	Purpose
CREATE	Creates a new database object
ALTER	Modifies an existing database object
DROP	Permanently removes a database object
TRUNCATE	Removes all rows from a table while keeping its structure

Example:

CREATE TABLE Students
(
    StudentID INT,
    StudentName VARCHAR(100)
);

This command creates a new table named Students.

---

2. What is the CREATE Command?

The CREATE command is used to create new database objects such as databases, tables, views, and stored procedures.

2.1 CREATE DATABASE

The CREATE DATABASE command creates a new database in SQL Server.

Syntax:

CREATE DATABASE DatabaseName;

Practical Example:

CREATE DATABASE CollegeDB;

After executing this command, SQL Server creates a database named CollegeDB.

2.2 View Available Databases

To check the databases available on your SQL Server instance, execute:

SELECT name
FROM sys.databases;

2.3 Select a Database

Before creating tables, select the database in which you want to work.

USE CollegeDB;
GO

The USE command changes the current database context.

2.4 CREATE TABLE

The CREATE TABLE command creates a new table inside the selected database.

Syntax:

CREATE TABLE TableName
(
    Column1 DataType,
    Column2 DataType,
    Column3 DataType
);

Practical Example:

CREATE TABLE Students
(
    StudentID INT,
    StudentName VARCHAR(100),
    Age INT,
    City VARCHAR(50)
);

This creates a Students table with four columns.

2.5 CREATE TABLE with PRIMARY KEY and IDENTITY

A primary key uniquely identifies each record in a table. The IDENTITY property automatically generates numeric values for a column.

CREATE TABLE Students
(
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Age INT,
    City VARCHAR(50)
);

Explanation:

• IDENTITY(1,1) starts numbering from 1 and increases by 1.

• PRIMARY KEY ensures that each StudentID is unique.

• NOT NULL prevents StudentName from being empty with a NULL value.

2.6 CREATE TABLE with DEFAULT Constraint

A DEFAULT constraint inserts a predefined value when a column value is not supplied in an INSERT statement.

CREATE TABLE Courses
(
    CourseID INT IDENTITY(1,1) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Fees DECIMAL(10,2),
    CourseStatus VARCHAR(20) DEFAULT 'Active'
);

If CourseStatus is not provided during insertion, SQL Server uses Active.

---

3. What is the ALTER Command?

The ALTER command is used to modify the structure of an existing database object.

In SQL Server, ALTER TABLE is commonly used to add, modify, or remove columns and constraints.

3.1 Add a New Column

Use ADD to add a new column to an existing table.

Syntax:

ALTER TABLE TableName
ADD ColumnName DataType;

Example:

ALTER TABLE Students
ADD Email VARCHAR(150);

This adds an Email column to the Students table.

3.2 Add Multiple Columns

You can add more than one column in a single ALTER TABLE statement.

ALTER TABLE Students
ADD Phone VARCHAR(15),
    Address VARCHAR(200);

This adds two new columns: Phone and Address.

3.3 Modify an Existing Column

Use ALTER COLUMN to change the data type or NULL setting of an existing column.

Syntax:

ALTER TABLE TableName
ALTER COLUMN ColumnName NewDataType;

Example:

ALTER TABLE Students
ALTER COLUMN StudentName VARCHAR(150) NOT NULL;

This changes the maximum length of StudentName to 150 characters and makes it NOT NULL.

Important: The change can fail if existing records contain NULL values or data that cannot fit into the new definition.

3.4 Drop a Column

Use DROP COLUMN to remove an existing column from a table.

ALTER TABLE Students
DROP COLUMN Address;

This removes the Address column from the Students table.

Important: Removing a column also removes the data stored in that column. Check dependencies and take a backup when necessary.

3.5 Add a Constraint Using ALTER TABLE

You can add constraints to an existing table using ALTER TABLE.

Example:

ALTER TABLE Students
ADD CONSTRAINT UQ_Students_Email UNIQUE (Email);

This creates a UNIQUE constraint on the Email column, preventing duplicate non-NULL email values.

3.6 Drop a Constraint

To remove a constraint, use its name with DROP CONSTRAINT.

ALTER TABLE Students
DROP CONSTRAINT UQ_Students_Email;

This removes the UNIQUE constraint from the Students table.

3.7 Rename a Table

SQL Server provides the sp_rename stored procedure to rename an existing table.

EXEC sp_rename 'Students', 'StudentDetails';

The table is now named StudentDetails.

3.8 Rename a Column

You can also rename a column using sp_rename.

EXEC sp_rename
    'StudentDetails.StudentName',
    'FullName',
    'COLUMN';

This changes the column name from StudentName to FullName.

Note: Renaming an object does not automatically update references in stored procedures, views, application code, or other dependent objects.

---

4. What is the DROP Command?

The DROP command is used to permanently remove database objects.

When you drop a table, its structure and stored records are removed.

4.1 DROP TABLE

Syntax:

DROP TABLE TableName;

Example:

DROP TABLE StudentDetails;

This permanently removes the StudentDetails table.

4.2 DROP DATABASE

The DROP DATABASE command removes an entire database.

Syntax:

DROP DATABASE DatabaseName;

Example:

DROP DATABASE CollegeDB;

This removes the CollegeDB database.

Warning: Dropping a database is a destructive operation. Its database objects and data are removed. Always verify the database name and take a backup before executing this command.

4.3 Check Before Dropping a Table

You can check whether a table exists before attempting to remove it.

IF OBJECT_ID('dbo.Students', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Students;
END;

This checks whether the Students table exists before dropping it.

4.4 Drop a Database Only If It Exists

IF DB_ID('CollegeDB') IS NOT NULL
BEGIN
    DROP DATABASE CollegeDB;
END;

This checks whether the database exists before attempting to drop it.

---

5. Difference Between CREATE, ALTER and DROP

CREATE, ALTER, and DROP are all DDL commands, but they perform different operations.

Feature	CREATE	ALTER	DROP
Purpose	Creates objects	Modifies objects	Removes objects
Existing object required	No	Yes	Yes
Structure	Creates new structure	Changes existing structure	Removes structure
Example	CREATE TABLE	ALTER TABLE	DROP TABLE

Example:

-- Create a table
CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeName VARCHAR(100)
);

-- Add a column
ALTER TABLE Employees
ADD Salary DECIMAL(10,2);

-- Remove the table
DROP TABLE Employees;

---

6. Difference Between DROP, DELETE and TRUNCATE

These three commands are often confused by beginners.

Feature	DROP	DELETE	TRUNCATE
Removes records	Yes	Yes	Yes
Removes table structure	Yes	No	No
WHERE condition	No	Yes	No
Identity reset	Object removed	Normally not reset	Normally reset
Command type	DDL	DML	DDL

DELETE Example

DELETE FROM Students
WHERE StudentID = 5;

This removes only the record where StudentID is 5.

TRUNCATE Example

TRUNCATE TABLE Students;

This removes all rows from the table while retaining its structure.

DROP Example

DROP TABLE Students;

This removes the complete table, including its structure and data.

Remember:

• Use DELETE when you want to remove selected records.

• Use TRUNCATE when you want to remove all rows but keep the table.

• Use DROP when you want to remove the table itself.

---

7. Practical Example: Create and Modify a Student Database

Let's understand CREATE, ALTER, and DROP through a practical example.

Step 1: Create a Database

CREATE DATABASE StudentDB;
GO

Step 2: Select the Database

USE StudentDB;
GO

Step 3: Create a Students Table

CREATE TABLE Students
(
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Age INT,
    City VARCHAR(50)
);

Step 4: Insert Sample Records

INSERT INTO Students (StudentName, Age, City)
VALUES
('Rahul', 21, 'Raipur'),
('Priya', 22, 'Bilaspur'),
('Amit', 20, 'Durg');

Step 5: Add a New Column

ALTER TABLE Students
ADD Email VARCHAR(150);

Step 6: Update Existing Records

UPDATE Students
SET Email = 'rahul@example.com'
WHERE StudentID = 1;

Step 7: View Table Records

SELECT *
FROM Students;

Step 8: Modify a Column

ALTER TABLE Students
ALTER COLUMN City VARCHAR(100);

Step 9: Remove a Column

ALTER TABLE Students
DROP COLUMN Email;

Step 10: Remove the Table

Run this only when you no longer need the example table.

DROP TABLE Students;

Step 11: Remove the Database

Run this only after confirming that the entire practice database is no longer required.

USE master;
GO

DROP DATABASE StudentDB;

---

8. Common Errors While Using CREATE, ALTER and DROP

Beginners may encounter several errors while working with DDL commands.

1. Database Already Exists

If you try to create a database with a name that already exists, SQL Server reports an error.

Check first:

IF DB_ID('CollegeDB') IS NULL
BEGIN
    CREATE DATABASE CollegeDB;
END;

2. Table Already Exists

Creating a table with an existing name in the same schema causes an error.

Check first:

IF OBJECT_ID('dbo.Students', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Students
    (
        StudentID INT,
        StudentName VARCHAR(100)
    );
END;

3. Cannot Drop a Table Because of a Foreign Key

A table referenced by a foreign key may not be dropped until the dependency is handled.

Check the related tables and constraints before attempting to remove it.

4. Cannot Alter a Column Because of Existing Data

Changing a column to a smaller data type or making it NOT NULL may fail if existing values do not meet the new definition.

Review the existing data before changing the column.

5. Permission Denied

Your SQL Server login may not have sufficient permissions to create, modify, or drop a database object.

Ask the database administrator to provide the required permissions.

---

9. Best Practices for Using DDL Commands

Follow these practices while working with SQL Server:

1. Always verify the database name before executing CREATE, ALTER, or DROP commands.

2. Take a backup before making major structural changes.

3. Use meaningful names for databases, tables, columns, and constraints.

4. Avoid dropping tables or databases without checking dependencies.

5. Use appropriate data types for each column.

6. Test structural changes in a development environment before applying them to production.

7. Use IF EXISTS or IF NOT EXISTS checks where appropriate.

8. Keep a record of important database structure changes.

9. Check application dependencies before renaming tables or columns.

10. Execute destructive commands carefully because they can permanently remove important data.

---

10. Frequently Asked Questions (FAQs)

Q1. What is CREATE in SQL Server?

CREATE is a DDL command used to create database objects such as databases, tables, views, and stored procedures.

Q2. What is ALTER in SQL Server?

ALTER is used to modify the structure of an existing database object, such as adding or changing table columns.

Q3. What is DROP in SQL Server?

DROP permanently removes a database object, such as a table or database.

Q4. What is the difference between CREATE and ALTER?

CREATE creates a new object, whereas ALTER modifies an object that already exists.

Q5. Can we add a column using ALTER TABLE?

Yes. Use ALTER TABLE TableName ADD ColumnName DataType to add a new column.

Q6. Can we change a column's data type?

Yes. Use ALTER TABLE with ALTER COLUMN to change a column's data type or NULL setting, subject to existing data and dependencies.

Q7. What happens when we DROP a table?

The table structure and its stored records are removed from the database.

Q8. What is the difference between DROP and TRUNCATE?

DROP removes the table itself, while TRUNCATE removes all rows but keeps the table structure.

Q9. Can we rename a table in SQL Server?

Yes. You can use the sp_rename stored procedure to rename a table.

Q10. Are CREATE, ALTER, and DROP DDL commands?

Yes. All three commands belong to Data Definition Language (DDL).

---

Conclusion

In this tutorial, we learned how to use CREATE, ALTER, and DROP commands in SQL Server with practical examples. We explored how to create databases and tables, add or modify columns, rename database objects, and remove tables or databases.

We also understood the differences between CREATE, ALTER, DROP, DELETE, and TRUNCATE commands, along with common errors and best practices.

Learning these commands is an important step toward becoming a database developer. At Param Classes, students can strengthen their SQL Server and database development skills through practical examples, step-by-step tutorials, and hands-on projects.

Keep practicing these commands in SQL Server Management Studio (SSMS) to build a strong foundation in database management.