Temp Tables, Table Variables and Cursors – SQL Server | Extra 1

Published / updated4 Oct 2026

Introduction

Big reports are easier to build in steps. You calculate something once, save it, and then use it in the next queries. SQL Server gives you two places to save a step: a temp table and a table variable.

You will also meet the cursor, which reads rows one at a time like a loop in C#. Many old systems are full of cursors. You need to read them, and know how to replace them.

By the end of this lesson, you will be able to:

Create, fill, and reuse a temp table with SELECT ... INTO #name.
Explain how a table variable differs from a temp table and a CTE.
Write a cursor, and explain why it is slow.
Rewrite a cursor as one set-based query, and prove both give the same result.

Why this matters

The principal wants a monthly list of students whose attendance fell below 85%, so the office can send a letter home. The same numbers also feed a count per class for the staff meeting.

A developer who thinks in loops writes a cursor: one student at a time, 10,012 times. On the SMS database, it took about 2.9 seconds.

One set-based query gave the identical answer in about 0.2 seconds. On a busy server, that difference decides whether the report page feels instant or broken.

The concept

Example: three ways to hold a step

	CTE	Temp table `#Name`	Table variable `@Name`
Stored	Not stored; part of one query	In tempdb	In tempdb
Lives until	The end of that one statement	The session ends, or you drop it	The end of the batch
Statistics	Uses the base tables'	Yes, so good plans for big data	Row count only, no column statistics
Indexes	No	Yes, any	Only those declared with the table
Undone by `ROLLBACK`	Not applicable	Yes	No
Best for	Making one query readable	Big steps reused several times	Small lists, and logging inside transactions

tempdb is a system database that SQL Server uses as scratch space. It is emptied every time the server restarts.

Think of exam correction day. A CTE is a working you do in your head for one answer.

A temp table is a rough notebook you keep open all day. A table variable is a sticky note that you throw away when the task ends.

Example: create a temp table

A name starting with # makes a local temp table: only your session can see it. The quickest way to create and fill one is SELECT ... INTO:

Example
DROP TABLE IF EXISTS #StudentAttendance;

SELECT e.Id AS EnrollmentId, COUNT(*) AS MarkedDays
INTO #StudentAttendance
FROM dbo.AttendanceRecords AS ar
JOIN dbo.Enrollments AS e ON e.Id = ar.EnrollmentId
GROUP BY e.Id;

DROP TABLE IF EXISTS first makes the script safe to run again. You can also use CREATE TABLE #Name (...) and then INSERT, when you want exact types or a primary key.

A name starting with ## makes a global temp table, visible to every session. It is rarely a good idea, because two users running the same report would share one table.

Example: what a cursor is

A cursor walks through the rows of a query one at a time. Every cursor has the same five steps:

Step	Statement
Declare	`DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT ...`
Open	`OPEN c`
Fetch the first row	`FETCH NEXT FROM c INTO @Variables`
Loop	`WHILE @@FETCH_STATUS = 0 BEGIN ... FETCH NEXT ... END`
Clean up	`CLOSE c; DEALLOCATE c;`

@@FETCH_STATUS is 0 while a row was fetched. LOCAL FAST_FORWARD makes the cursor read-only and forward-only, which is the fastest kind.

Set-based means describing the whole result in one statement, and letting SQL Server decide how to get it. Every query you wrote in Module 4 was set-based.

Apply it to the SMS

The script builds the attendance-letter report with a temp table. It then calculates the same percentages twice: with a cursor and set-based. Nothing in it changes SchoolDb.

Show extras/e1-temp-tables-cursors.sql

extras/e1-temp-tables-cursors.sql
-- SQL Extra 1: temp tables, table variables and cursors.
-- Nothing here changes SchoolDb: temp tables and table variables disappear on their own.
USE SchoolDb;
GO

SET NOCOUNT ON;  -- no "(1 row affected)" message per statement; matters inside loops
GO

-- 1. A report built in steps with a temp table.
--    Step 1: July attendance per current student, calculated once.
DROP TABLE IF EXISTS #StudentAttendance;

SELECT
    e.Id AS EnrollmentId,
    e.StudentId,
    e.SectionId,
    COUNT(*) AS MarkedDays,
    SUM(CASE WHEN ar.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) AS AttendedDays
INTO #StudentAttendance
FROM dbo.AttendanceRecords AS ar
JOIN dbo.Enrollments AS e ON e.Id = ar.EnrollmentId
WHERE e.AcademicYearId = 2
  AND ar.AttendanceDate >= '2026-07-01'
  AND ar.AttendanceDate < '2026-08-01'
GROUP BY e.Id, e.StudentId, e.SectionId;

SELECT COUNT(*) AS StudentsInTempTable FROM #StudentAttendance;

--    Step 2: the 5 lowest students with at least 20 marked days, with names.
SELECT TOP (5)
    st.FirstName,
    st.LastName,
    c.Name AS ClassName,
    s.Name AS SectionName,
    sa.MarkedDays,
    CAST(100.0 * sa.AttendedDays / sa.MarkedDays AS DECIMAL(5, 2)) AS AttendancePercent
FROM #StudentAttendance AS sa
JOIN dbo.Students AS st ON st.Id = sa.StudentId
JOIN dbo.Sections AS s ON s.Id = sa.SectionId
JOIN dbo.Classes AS c ON c.Id = s.ClassId
WHERE sa.MarkedDays >= 20
ORDER BY AttendancePercent, st.Id;

--    Step 3: reuse the same temp table: students below 85% per class.
SELECT
    c.Name AS ClassName,
    COUNT(*) AS StudentsBelow85
FROM #StudentAttendance AS sa
JOIN dbo.Sections AS s ON s.Id = sa.SectionId
JOIN dbo.Classes AS c ON c.Id = s.ClassId
WHERE sa.MarkedDays >= 20
  AND 100.0 * sa.AttendedDays / sa.MarkedDays < 85
GROUP BY c.Name, c.DisplayOrder
ORDER BY c.DisplayOrder;
GO

-- 2. The temp table lives until the session ends, so it survives GO.
SELECT COUNT(*) AS StillThereAfterGo FROM #StudentAttendance;
GO

-- 3. A table variable lives only inside its batch, and ignores ROLLBACK.
DECLARE @Log TABLE (Message VARCHAR(50) NOT NULL);

DROP TABLE IF EXISTS #Log;
CREATE TABLE #Log (Message VARCHAR(50) NOT NULL);

BEGIN TRANSACTION;
INSERT INTO @Log (Message) VALUES ('Step 1 started');
INSERT INTO #Log (Message) VALUES ('Step 1 started');
ROLLBACK TRANSACTION;

SELECT
    (SELECT COUNT(*) FROM @Log) AS TableVariableRows,
    (SELECT COUNT(*) FROM #Log) AS TempTableRows;
GO

-- 4. Row by row with a cursor: July attendance percent for every current student.
DROP TABLE IF EXISTS #ByCursor;
CREATE TABLE #ByCursor (EnrollmentId INT PRIMARY KEY, AttendancePercent DECIMAL(5, 2) NOT NULL);

DECLARE @Start DATETIME2 = SYSDATETIME();
DECLARE @EnrollmentId INT, @Marked INT, @Attended INT;

DECLARE enrollment_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT Id
    FROM dbo.Enrollments
    WHERE AcademicYearId = 2;

OPEN enrollment_cursor;
FETCH NEXT FROM enrollment_cursor INTO @EnrollmentId;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT
        @Marked = COUNT(*),
        @Attended = SUM(CASE WHEN Status IN ('Present', 'Late') THEN 1 ELSE 0 END)
    FROM dbo.AttendanceRecords
    WHERE EnrollmentId = @EnrollmentId
      AND AttendanceDate >= '2026-07-01'
      AND AttendanceDate < '2026-08-01';

    IF @Marked > 0
        INSERT INTO #ByCursor (EnrollmentId, AttendancePercent)
        VALUES (@EnrollmentId, CAST(100.0 * @Attended / @Marked AS DECIMAL(5, 2)));

    FETCH NEXT FROM enrollment_cursor INTO @EnrollmentId;
END;

CLOSE enrollment_cursor;
DEALLOCATE enrollment_cursor;

SELECT
    COUNT(*) AS Students,
    SUM(CASE WHEN AttendancePercent < 85 THEN 1 ELSE 0 END) AS Below85,
    DATEDIFF(MILLISECOND, @Start, SYSDATETIME()) AS CursorMs
FROM #ByCursor;
GO

-- 5. The same result in one set-based statement.
DROP TABLE IF EXISTS #BySet;

DECLARE @Start DATETIME2 = SYSDATETIME();

SELECT
    ar.EnrollmentId,
    CAST(100.0 * SUM(CASE WHEN ar.Status IN ('Present', 'Late') THEN 1 ELSE 0 END) / COUNT(*)
         AS DECIMAL(5, 2)) AS AttendancePercent
INTO #BySet
FROM dbo.AttendanceRecords AS ar
JOIN dbo.Enrollments AS e ON e.Id = ar.EnrollmentId
WHERE e.AcademicYearId = 2
  AND ar.AttendanceDate >= '2026-07-01'
  AND ar.AttendanceDate < '2026-08-01'
GROUP BY ar.EnrollmentId;

SELECT
    COUNT(*) AS Students,
    SUM(CASE WHEN AttendancePercent < 85 THEN 1 ELSE 0 END) AS Below85,
    DATEDIFF(MILLISECOND, @Start, SYSDATETIME()) AS SetBasedMs
FROM #BySet;

-- Both methods must give identical rows: this returns 0.
SELECT COUNT(*) AS DifferentRows
FROM (
    SELECT EnrollmentId, AttendancePercent FROM #ByCursor
    EXCEPT
    SELECT EnrollmentId, AttendancePercent FROM #BySet
) AS d;
GO

Example: step 1, calculate once

The first query reads 600,000 attendance rows a single time and saves one row per student:

StudentsInTempTable
10012

Every later step reads these 10,012 small rows instead of the big attendance table.

Example: step 2 and 3, reuse the step

The 5 lowest students with at least 20 marked days in July:

FirstName	LastName	ClassName	SectionName	MarkedDays	AttendancePercent
Aisha	Reddy	Class 1	C	27	81.48
Aisha	Sharma	Class 1	E	27	81.48
Aisha	Nair	Class 1	G	27	81.48
Aisha	Reddy	Class 1	I	27	81.48
Aisha	Sharma	Class 1	K	27	81.48

Why "at least 20 days"? Without it, the list starts with Kabir and Rohan at 66.67%.

They have only 3 marked days in July, because the sample data records the 12 fixed students for 1 to 3 July only. Judging a student on 3 days would be unfair, so real reports set a minimum.

The same temp table then gives the count per class for the staff meeting. Classes 1 to 10 each have exactly 10 such students. The generated data spreads absences evenly; a real school's numbers will not be this tidy.

Example: temp tables survive GO

After GO, StillThereAfterGo returns 10012: the temp table is still there. A table variable would be gone. Using it in a later batch fails:

Output
Msg 1087, Level 15, State 2
Must declare the table variable "@Log".

Example: table variables ignore ROLLBACK

Part 3 inserts one row into a table variable and one into a temp table, then rolls back:

TableVariableRows	TempTableRows
1	0

The temp table row was undone; the table variable row stayed. That sounds like a bug, but it is useful.

In a CATCH block, you can save the error details in a table variable and roll back. The details are still there, so you can write them to a log table afterwards.

Example: the cursor vs one query

Part 4 loops over 10,012 enrollments and runs one small query for each. Part 5 does the same in one GROUP BY. Three runs on LocalDB:

Method	Run 1	Run 2	Run 3	Students below 85%
Cursor	2,749 ms	2,955 ms	2,906 ms	102
Set-based	215 ms	187 ms	222 ms	102

DifferentRows is 0: both give identical rows. EXCEPT returns rows from the first query that are missing from the second, so 0 proves the rewrite is correct.

The cursor runs 10,012 separate queries, each with its own small cost. The set-based query reads the data once, in one plan. Your timings will differ, but the gap stays.

The count here is 102, not 100, because it has no 20-day minimum: Kabir and Rohan are in it.

Example: SET NOCOUNT ON

Without SET NOCOUNT ON, SQL Server sends a "(1 row affected)" message for every statement. Inside the cursor, that is over 10,000 messages.

The first version of this script printed about 200 KB of them. Put SET NOCOUNT ON at the top of scripts and procedures.

Example: when a cursor is the right tool

Cursors are not forbidden. Use one when each row must go through something that only handles one row at a time. For example, an uploaded bank file has 40 payments, and each must go through usp_RecordFeePayment, which records one payment per call.

Admin scripts that loop over databases or tables are another fair use. The rule: if the work can be written as one query, write one query.

Common mistakes

A cursor for something one query can do. It is many times slower. Fix: describe the whole result with JOIN, GROUP BY, and CASE.
Forgetting CLOSE and DEALLOCATE. The cursor name stays taken, and the next run fails. Fix: always clean up, and use LOCAL cursors.
Table variables for big data. With no column statistics, SQL Server guesses how many rows match each filter or join, and the plan can be poor. Fix: use a temp table for thousands of rows.
No DROP TABLE IF EXISTS before SELECT ... INTO. The second run fails with "There is already an object named...". Fix: drop first.
Global ## temp tables in reports. Two users overwrite each other's data. Fix: use local # tables.

In real projects

From 16 years of .NET projects

I once inherited a nightly fee-reminder job that took 47 minutes. It was one cursor over every student, with four queries inside the loop.

We rewrote it as three set-based steps with temp tables in between. It ran in under two minutes, and the code became shorter and easier to test.

Since then, when I review a pull request with a cursor, I ask one question: "Does each row really have to be handled alone?" The answer is usually no.

Best practices

Think in sets first; use a cursor only when each row truly needs separate handling.
Use a temp table for big or reused steps; a CTE when the step is used once.
Keep table variables for small lists and for logging inside transactions.
Start scripts with SET NOCOUNT ON and DROP TABLE IF EXISTS.
When you replace a cursor, prove the new query returns identical rows with EXCEPT.

Recap

A temp table (#Name) lives in tempdb until the session ends, and has statistics.
A table variable (@Name) lives for one batch and ignores ROLLBACK.
A CTE is not stored; it belongs to one statement.
A cursor handles one row at a time; here it was about 13 times slower.
One set-based query gave the same 10,012 rows in about 0.2 seconds.
SET NOCOUNT ON stops a message for every statement.

Homework

Library module task: For every open book issue that is overdue on 28 July 2026, show the days late and the fine (2 per day). Write it twice: first with a cursor that fills a table variable, then as one query.

A correct result looks like: both versions return one row: book issue 2, 8 days late, a fine of 16.00.

Show the solution

library-assignment/e1-library-cursor.sql
-- Library homework, Extra 1: fines for open, overdue issues on a given date,
-- first row by row with a cursor, then in one set-based query.
USE SchoolDb;
GO

SET NOCOUNT ON;
GO

DECLARE @AsOf DATE = '2026-07-28';
DECLARE @Fines TABLE (BookIssueId INT PRIMARY KEY, DaysLate INT NOT NULL, Fine DECIMAL(8, 2) NOT NULL);
DECLARE @Id INT, @DueOn DATE;

-- 1. Row by row.
DECLARE issue_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT Id, DueOn
    FROM dbo.BookIssues
    WHERE ReturnedOn IS NULL
      AND DueOn < @AsOf;

OPEN issue_cursor;
FETCH NEXT FROM issue_cursor INTO @Id, @DueOn;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @Fines (BookIssueId, DaysLate, Fine)
    VALUES (@Id, DATEDIFF(DAY, @DueOn, @AsOf), DATEDIFF(DAY, @DueOn, @AsOf) * 2);

    FETCH NEXT FROM issue_cursor INTO @Id, @DueOn;
END;

CLOSE issue_cursor;
DEALLOCATE issue_cursor;

SELECT BookIssueId, DaysLate, Fine FROM @Fines;

-- 2. The same result in one statement.
SELECT
    Id AS BookIssueId,
    DATEDIFF(DAY, DueOn, @AsOf) AS DaysLate,
    CAST(DATEDIFF(DAY, DueOn, @AsOf) * 2 AS DECIMAL(8, 2)) AS Fine
FROM dbo.BookIssues
WHERE ReturnedOn IS NULL
  AND DueOn < @AsOf;
GO

Both results are identical, and the second needs no loop, no variables, and no cleanup.

Quiz

How long does a local temp table (#Name) live?
1. Until the end of the statement
2. Until the end of the batch
3. Until the session ends or it is dropped
4. Forever
Show answer
Answer: C. Until the session ends or it is dropped It survives GO and is dropped when the session closes.
Inside a transaction, you insert into a table variable and then ROLLBACK. What happens to the row?
1. It is removed
2. It stays
3. The table variable is deleted
4. An error is raised
Show answer
Answer: B. It stays Table variables are not affected by ROLLBACK.
Which one has statistics that help SQL Server plan big queries?
1. A temp table
2. A table variable
3. A CTE
4. A cursor
Show answer
Answer: A. A temp table Temp tables get statistics like normal tables.
What does @@FETCH_STATUS = 0 mean in a cursor loop?
1. The cursor is closed
2. The last fetch returned a row
3. No rows are left
4. An error happened
Show answer
Answer: B. The last fetch returned a row 0 means a row was fetched, so the loop continues.
Why was the cursor about 13 times slower here?
1. Cursors cannot use indexes
2. It ran 10,012 separate small queries instead of one
3. Temp tables are slow
4. LocalDB limits cursors
Show answer
Answer: B. It ran 10,012 separate small queries instead of one Each loop iteration is its own query with its own cost; one set-based query reads the data once.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a temp table in SQL Server? concept

A table whose name starts with #, stored in tempdb and visible only to your session. It lives until the session ends or you drop it. You use it to save an intermediate step of a report and reuse it in later queries.

See the example · Permalink

F What is the difference between #Name and ##Name? concept

#Name is a local temp table, visible only to the session that created it. ##Name is a global temp table, visible to every session. Reports should use local tables, so two users never share or overwrite each other's data.

See the example · Permalink

F What is a cursor, and what are its steps? concept

A cursor reads a query's rows one at a time. The steps are DECLARE, OPEN, FETCH NEXT, a WHILE @@FETCH_STATUS = 0 loop, and finally CLOSE and DEALLOCATE.

See the example · Permalink

Mid (3-5 yrs)

M Compare a temp table, a table variable, and a CTE. concept

A CTE is not stored and belongs to one statement. A temp table lives in tempdb until the session ends, has statistics, and can have any index. A table variable lives for one batch, has no column statistics, and is not undone by ROLLBACK. Use temp tables for big or reused steps.

See the example · Permalink

M Why is a cursor usually slower than a set-based query? concept

A cursor runs one small query per row, each with its own overhead. A set-based query reads the data once in one plan. In the SMS, a cursor over 10,012 students took about 2.9 seconds, and one GROUP BY gave identical results in about 0.2 seconds.

See the example · Permalink

M What happens to a table variable's rows when a transaction rolls back? concept

They stay. Table variables are not part of the transaction, so ROLLBACK does not undo them. This is useful in a CATCH block: save error details in a table variable, roll back, and then write them to a log table.

See the example · Permalink

Senior (6-9 yrs)

S A nightly job loops over 10,000 students with a cursor and takes 40 minutes. How do you fix it? scenario

Find what each loop iteration does, and express the whole job as a few set-based statements, using temp tables for intermediate steps. Prove the new version gives the same rows with EXCEPT, then measure. Keep a loop only for work that truly must happen one row at a time.

How to answer: Show the method: understand, rewrite in sets, prove equality, measure. Mention SET NOCOUNT ON, testing on a copy of production-sized data, and running old and new versions side by side before switching.

See the example · Permalink

S When is a cursor the right choice? concept

When each row must go through logic that handles one row at a time, such as calling a procedure like usp_RecordFeePayment for every payment in an uploaded file. Admin scripts that loop over databases or tables are another fair use. Use LOCAL FAST_FORWARD.

How to answer: Avoid "never use cursors". Give the rule (if it can be one query, write one query), then real exceptions, and mention the alternative of a set-based procedure that accepts many rows.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you guide a team that writes most reports with loops and cursors? design

Set a review rule: set-based first, cursors only with a written reason. Provide patterns (temp-table steps, window functions, conditional aggregation), pair on rewriting the slowest jobs, and track their run times so the gains are visible.

How to answer: Focus on changing habits, not one query: standards, examples, mentoring, and measurement. Mention tempdb sizing and monitoring as temp tables become more common.

See the example · Permalink

Resources

Code for this lesson: git tag extra-1-temp-tables-cursors (code repository coming soon)
Files: extras/e1-temp-tables-cursors.sql, library-assignment/e1-library-cursor.sql
Video: coming soon

What's next

You can now build reports in steps. Next: loading a whole list of changes into a table in one statement, inserting new rows and updating existing ones. Extra 2: MERGE and upserts

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: three ways to hold a step​

Example: create a temp table​

Example: what a cursor is​

Apply it to the SMS​

Example: step 1, calculate once​

Example: step 2 and 3, reuse the step​

Example: temp tables survive GO​

Example: table variables ignore ROLLBACK​

Example: the cursor vs one query​

Example: SET NOCOUNT ON​

Example: when a cursor is the right tool​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: three ways to hold a step

Example: create a temp table

Example: what a cursor is

Apply it to the SMS

Example: step 1, calculate once

Example: step 2 and 3, reuse the step

Example: temp tables survive GO

Example: table variables ignore ROLLBACK

Example: the cursor vs one query

Example: SET NOCOUNT ON

Example: when a cursor is the right tool

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next