Creating Databases and Tables – CREATE, ALTER and DROP
Quick Answer: SQL Server uses DDL commands to create and manage database structures. CREATE creates databases and tables, ALTER changes existing structures, and DROP removes database objects.
1. Why Do We Need CREATE, ALTER and DROP?
Imagine Priya manages a grocery shop with 500 different products. She needs to maintain product names, prices and available stock.
Initially, she records only product names and prices. Later, she wants to track stock quantities too.
How can she create the database structure and change it when business needs grow?
The problem without database structure management
Priya could store product details inside one text column. This works for basic storage, but it makes future changes difficult.
Example 1: Store product details in one text column
Assume the GroceryDB database already exists.
CREATE TABLE ProductNotes
(
ProductDetails VARCHAR(200)
);
INSERT INTO ProductNotes
VALUES
('Rice | 1200 | 20'),
('Sugar | 800 | 15'),
('Milk | 600 | 30');
SELECT ProductDetails
FROM ProductNotes;
Expected output
ProductDetails -------------------- Rice | 1200 | 20 Sugar | 800 | 15 Milk | 600 | 30
The records are stored successfully. However, this design creates problems.
- Product names, prices and quantities are not separate columns.
- Searching products by price becomes difficult.
- Changing the structure needs additional work.
- Maintaining consistent product information becomes harder.
Now imagine Priya managing 500 products and updating stock daily. She needs properly designed database tables.
We need commands to create database structures. We also need commands to change and remove them.
The solution: CREATE, ALTER and DROP
DDL = Data Definition Language. It manages database structures.
CREATE = creates a new database object.
ALTER = changes the structure of an existing object.
DROP = removes an existing database object.
Table = a database structure that stores information in rows and columns.
We will use these commands to manage Priya's grocery database. Each command has a different responsibility.
Animation 1: Managing a grocery database structure
Follow Priya as she creates, changes and removes database objects.
Priya creates her database, builds a table, adds stock tracking and removes temporary data structures.
2. Implement CREATE, ALTER and DROP in SQL Server
Let's implement these commands using Priya's grocery shop example.
Create a database using CREATE DATABASE
CREATE DATABASE = creates a new database inside a SQL Server instance.
Priya first needs a separate database for grocery shop information.
Example 2: Create and manage a grocery database
Run this complete script on a SQL Server instance where GroceryTrainingDB does not already exist.
CREATE DATABASE GroceryTrainingDB;
GO
USE GroceryTrainingDB;
GO
CREATE TABLE Products
(
ProductId INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);
GO
INSERT INTO Products
(ProductId, ProductName, Price)
VALUES
(1, 'Rice', 1200),
(2, 'Sugar', 800);
GO
ALTER TABLE Products
ADD StockQuantity INT NOT NULL
CONSTRAINT DF_Products_Stock DEFAULT 0;
GO
CREATE TABLE TemporaryImport
(
ImportId INT
);
GO
DROP TABLE TemporaryImport;
GO
SELECT ProductId, ProductName, Price, StockQuantity
FROM Products;
GO
Notice how CREATE DATABASE creates Priya's database. The CREATE TABLE statement defines product columns.
The ALTER TABLE command adds stock tracking. Existing products receive the default stock value of zero.
The temporary table is created and removed using DROP TABLE. The Products table remains available.
Expected output
ProductId ProductName Price StockQuantity --------- ----------- -------- ------------- 1 Rice 1200.00 0 2 Sugar 800.00 0
Important: GO is a batch separator understood by tools such as SSMS. It is not a Transact-SQL statement sent directly to the SQL Server engine.
Understand DROP DATABASE
DROP DATABASE = removes an entire database from the SQL Server instance.
For example, the following command removes the training database:
DROP DATABASE GroceryTrainingDB;
Warning: Do not execute this command while you still need the database. Dropping a database removes its database objects and stored data. Always check the database name and take required backups first.
3. Quick Summary
CREATE vs ALTER vs DROP
| Command | Purpose | Example |
|---|---|---|
CREATE |
Creates a database object. | CREATE TABLE Products |
ALTER |
Changes an existing object. | ALTER TABLE Products |
DROP |
Removes an existing object. | DROP TABLE Products |
The important terms to remember
- DDL: Commands used to manage database structures.
- CREATE: Creates databases, tables and other objects.
- ALTER: Adds, changes or removes table columns and constraints.
- DROP: Removes database objects.
- GO: Separates batches in SQL Server tools.
4. Common Mistakes and Best Practices
1. Creating a database that already exists
Running the same CREATE DATABASE statement again causes an error.
Better approach: Check whether the database exists before creating it.
2. Using DROP without checking the object
A wrong table or database name can cause unwanted data loss.
Better approach: Verify the object name and take backups before destructive changes.
3. Adding a NOT NULL column to an existing table
Existing records need valid values for the new column.
Better approach: Provide a suitable DEFAULT value or populate existing records before enforcing NOT NULL.
Design tip: Use meaningful database and column names. Plan table structures before production deployment. Review schema changes before applying them.
5. How CREATE, ALTER and DROP Come Up in Interviews
Question 1: What is DDL in SQL Server?
What the interviewer expects: Explain database structure management commands.
Strong answer: "DDL stands for Data Definition Language. It includes commands such as CREATE, ALTER and DROP. We use them to manage database structures."
Question 2: What is the difference between ALTER and DROP?
What the interviewer expects: Explain modification versus removal.
Strong answer: "ALTER changes an existing database object's structure. DROP removes the object itself. For example, ALTER can add a stock column, while DROP removes a table."
Question 3: What happens when we add a column using ALTER TABLE?
What the interviewer expects: Explain the effect on existing records.
Strong answer: "ALTER TABLE adds a column to an existing table. If the column allows NULL, existing records can contain NULL. If it has a suitable DEFAULT constraint, SQL Server can populate existing rows with that default value."
6. Hands-On Practice
Beginner: School database
Create a database named SchoolDB. Create a Students table with StudentId and StudentName. Add a Class column using ALTER.
Expected result: A Students table containing all three columns.
Intermediate: Hospital database
Create a database named HospitalDB. Create a Patients table and add an AppointmentDate column later.
Hint: Use CREATE DATABASE, CREATE TABLE and ALTER TABLE.
Advanced: Grocery database changes
Extend Priya's Products table with additional business information.
- Add a Category column.
- Change the Price column to a suitable precision.
- Create a temporary import table.
- Remove the temporary table using DROP.
Challenge: Perform the changes without deleting existing product records.
7. Next Steps and Community
Previous article: Databases, Tables, Rows, Columns and Data Types in SQL Server
Next article: SQL Server Constraints – Primary Key, Foreign Key, Unique and Check
For more details, read the official Microsoft documentation:
- Microsoft Learn: CREATE DATABASE
- Microsoft Learn: CREATE TABLE
- Microsoft Learn: ALTER TABLE
- Microsoft Learn: DROP TABLE
Practice creating and changing Priya's grocery database. Share your SQL scripts and discuss different database design choices with other learners.