In Microsoft SQL Server, what is the main difference between a local temporary table and a global temporary table?

Introduction / Context:
Temporary tables are very useful when writing complex queries, stored procedures, and scripts in SQL Server. They provide short lived storage that disappears automatically. SQL Server supports local and global temporary tables, each with different naming conventions and visibility rules. This question checks whether you understand those differences.

Given Data / Assumptions:

• The database system is Microsoft SQL Server.
• Local temporary tables are typically created with a single number sign prefix, for example #temp.
• Global temporary tables are created with a double number sign prefix, for example ##temp.
• We care about which sessions can see and use each type of temporary table.

Concept / Approach:
A local temporary table exists only in the context of the session or connection that created it. Other sessions cannot access it. When the session ends, the local temporary table is automatically dropped. A global temporary table, on the other hand, is visible to all sessions once it is created. It remains in the system until the session that created it disconnects and all other sessions that reference it are also finished.

Step-by-Step Solution:
Step 1: Recall that #table_name syntax creates a local temporary table. Step 2: Understand that local temporary tables are scoped to a single session or connection, so other users cannot see them. Step 3: Recall that ##table_name syntax creates a global temporary table. Step 4: Recognize that global temporary tables are visible to all sessions and can be shared among users until they are dropped or all sessions end. Step 5: Compare this behavior with the options and identify option a as the correct description.

Verification / Alternative check:
You can verify this behavior by opening two sessions in SQL Server Management Studio. In the first session, create a local temporary table named #test and insert some rows. In the second session, try to query #test. You will receive an error because the table does not exist in that session. Then create a global temporary table named ##test in the first session and query it from both sessions. Both will see the data, confirming the visibility difference.

Why Other Options Are Wrong:
A local temporary table is stored only in memory, while a global temporary table is always stored on disk is incorrect because both types can use tempdb and may involve disk storage depending on size and configuration. A local temporary table can be created only by administrators, whereas a global temporary table can be created by any user is reversed and not accurate; permissions depend on schema and security, not on local versus global alone. A local temporary table is used only for read operations, while a global temporary table is used only for write operations has no basis in SQL Server design. There is no difference between local and global temporary tables in SQL Server is clearly wrong because their naming and scope behavior are different.

Common Pitfalls:
A common mistake is believing that local temporary tables can be shared across sessions in the same way as global temporary tables. Another pitfall is forgetting that both types live in tempdb, which means that heavy use of temporary tables can affect tempdb performance. Understanding scope and lifetime helps you choose the right temporary structure for the task.

Final Answer:
The main difference is that a local temporary table is visible only in the current session, whereas a global temporary table is visible to multiple sessions until it is dropped or all sessions disconnect.