Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Altering Tables

The ALTER TABLE statement is used to modify the structure of an existing SQL Server table. We can add, modify or remove columns without recreating the complete table.

What is ALTER TABLE?

The ALTER TABLE statement is used when we need to change the structure of an existing table.

For example, we can add a new column, remove an existing column or change the data type of a column.

SQL
ALTER TABLE TableName
ADD ColumnName DataType;

Common ALTER TABLE Operations

Operation Purpose
Add Column Adds a new column to an existing table.
Alter Column Changes the data type or definition of a column.
Drop Column Removes a column from the table.
Add Constraint Adds a constraint to the table.

1. Add a New Column

The ADD clause is used to add a new column to an existing table.

SQL
ALTER TABLE Students
ADD Email VARCHAR(150);

The above statement adds an Email column to the Students table.

2. Add Multiple Columns

Multiple columns can be added to a table using ALTER TABLE.

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

3. Modify a Column

The ALTER COLUMN clause can be used to change the definition or data type of an existing column.

SQL
ALTER TABLE Students
ALTER COLUMN StudentName VARCHAR(200);

Here, the maximum length of the StudentName column is changed to 200 characters.

4. Remove a Column

The DROP COLUMN clause is used to remove an existing column from a table.

SQL
ALTER TABLE Students
DROP COLUMN Address;
Important

Dropping a column removes that column and its stored data from the table. Use this operation carefully.

5. Add a Constraint

ALTER TABLE can also be used to add constraints to an existing table.

SQL
ALTER TABLE Students
ADD CONSTRAINT PK_Students
PRIMARY KEY (StudentId);

Table Before ALTER

StudentId StudentName Age Course
1 Samadhan 22 Java
2 Samiksh 21 .NET

Practical Example

Suppose we want to add an Email column to the Students table.

SQL
ALTER TABLE Students
ADD Email VARCHAR(150);

Table After ALTER

StudentId StudentName Age Course Email
1 Samadhan 22 Java samadhan@example.com
2 Samiksh 21 .NET samiksha@example.com

ALTER TABLE Flow

SQL Server

                                                        Existing Table
                                                              ↓
                                                         ALTER TABLE
                                                              ↓
                                                         Choose Operation
                                                              ↓
                                                         ADD / ALTER / DROP
                                                              ↓
                                                       Updated Table Structure

Verify the Updated Table

After altering a table, we can verify its structure using the following query:

SQL
EXEC sp_help 'Students';
Important Points
  • ALTER TABLE changes the structure of an existing table.
  • ADD is used to add columns.
  • ALTER COLUMN is used to modify a column definition.
  • DROP COLUMN removes a column and its data.
  • Always be careful when changing a table containing important production data.

Summary

In this section, we learned how to modify an existing SQL Server table using ALTER TABLE. We learned how to add columns, modify columns, remove columns and add constraints.