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

Table Variables

Learn how to declare, insert, query, update and delete data using Table Variables in SQL Server.

What is a Table Variable?

A Table Variable is a variable that can temporarily store multiple rows of data in SQL Server.

Table variables are declared using the DECLARE statement and the TABLE keyword.

They are useful when we need to store a small amount of temporary data during a SQL batch, stored procedure or function.

Syntax of Table Variable

DECLARE @TableName TABLE
(
    Column1 DataType,
    Column2 DataType,
    Column3 DataType
);

Creating a Table Variable

The following example creates a table variable named @Employees.

DECLARE @Employees TABLE
(
    EmployeeId INT,
    EmployeeName VARCHAR(100),
    Department VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert Data into Table Variable

INSERT INTO @Employees
(
    EmployeeId,
    EmployeeName,
    Department,
    Salary
)
VALUES
(1, 'Amit', 'IT', 70000),
(2, 'Rahul', 'HR', 60000),
(3, 'Priya', 'IT', 80000),
(4, 'Neha', 'Finance', 65000);

Select Data from Table Variable

SELECT *
FROM @Employees;

Output

EmployeeId EmployeeName Department Salary
1 Amit IT 70000
2 Rahul HR 60000
3 Priya IT 80000
4 Neha Finance 65000

Filtering Table Variable Data

We can use WHERE conditions while querying a table variable.

SELECT
    EmployeeName,
    Department,
    Salary
FROM @Employees
WHERE Salary > 65000;

Update Data

UPDATE @Employees
SET Salary = 75000
WHERE EmployeeId = 1;

Table variables support UPDATE operations just like regular tables.

Delete Data

DELETE FROM @Employees
WHERE EmployeeId = 4;

Table Variable with Primary Key

A table variable can be declared with constraints such as a PRIMARY KEY.

DECLARE @Employees TABLE
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(100),
    Salary DECIMAL(10,2)
);

Table Variable with IDENTITY

An IDENTITY column can be used when we want SQL Server to automatically generate numeric values.

DECLARE @Employees TABLE
(
    EmployeeId INT IDENTITY(1,1),
    EmployeeName VARCHAR(100),
    Department VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert Data using SELECT

Data from an existing table can be inserted into a table variable using SELECT.

DECLARE @ITEmployees TABLE
(
    EmployeeId INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2)
);

INSERT INTO @ITEmployees
SELECT
    EmployeeId,
    EmployeeName,
    Salary
FROM Employees
WHERE Department = 'IT';

SELECT *
FROM @ITEmployees;

Table Variable with JOIN

Table variables can also participate in JOIN operations.

DECLARE @EmployeeDetails TABLE
(
    EmployeeId INT,
    EmployeeName VARCHAR(100),
    DepartmentId INT
);

INSERT INTO @EmployeeDetails
SELECT
    EmployeeId,
    EmployeeName,
    DepartmentId
FROM Employees;

SELECT
    e.EmployeeName,
    d.DepartmentName
FROM @EmployeeDetails e
INNER JOIN Departments d
    ON e.DepartmentId = d.DepartmentId;

Table Variable in Stored Procedure

Table variables can be declared and used inside stored procedures.

CREATE PROCEDURE GetITEmployees
AS
BEGIN

    DECLARE @Employees TABLE
    (
        EmployeeId INT,
        EmployeeName VARCHAR(100),
        Salary DECIMAL(10,2)
    );

    INSERT INTO @Employees
    SELECT
        EmployeeId,
        EmployeeName,
        Salary
    FROM Employees
    WHERE Department = 'IT';

    SELECT *
    FROM @Employees;

END;

Temporary Table vs Table Variable

Feature Temporary Table Table Variable
Declaration CREATE TABLE #Table DECLARE @Table TABLE
Prefix # @
Scope Session / Procedure scope Batch / Procedure scope
Indexes Supports indexes Supports indexes through constraints/index definitions as applicable
Best suited for Larger temporary datasets Smaller temporary datasets

Advantages of Table Variables

  • Easy to declare and use.
  • Useful for small temporary datasets.
  • Can be used inside stored procedures and functions.
  • Supports INSERT, UPDATE, DELETE and SELECT operations.
  • Useful for storing intermediate results.

Important Points

  • Table variables are declared using DECLARE @TableName TABLE.
  • They are useful for temporary data processing.
  • They support normal DML operations.
  • They can contain constraints such as PRIMARY KEY.
  • They are commonly used for smaller temporary datasets.

Summary

Table Variables provide a convenient way to store temporary data inside SQL Server batches, procedures and functions.

They are declared using the DECLARE statement and TABLE keyword and support common SQL operations such as SELECT, INSERT, UPDATE and DELETE.