MS SQL Server - 25 - Temporary Tables MCQ

25 – Temporary Tables MCQ

Best of luck!

1 / 35

1. What is a Temporary Table in SQL Server?

A table that is created without specifying a schema

A table that only stores temporary data

A table that is automatically deleted when the session ends

A table that is used for storing log data

2 / 35

2. Which prefix is commonly used to name local temporary tables in SQL Server?

3 / 35

3. What is the scope of a local temporary table in SQL Server?

Available within the current session only

Available to all sessions

Available to all databases

Available to all users

4 / 35

4. Which type of temporary table is created using a double hash prefix (##) in SQL Server?

Temporary Variable Table

Global Temporary Table

System Temporary Table

Local Temporary Table

5 / 35

5. When is data stored in a local temporary table automatically deleted in SQL Server?

After the current transaction ends

After the SQL Server instance restarts

Never, data is retained until explicitly dropped

After the session that created it ends

6 / 35

6. Which statement is used to create a temporary table in SQL Server?

SET TABLE

CREATE TEMPORARY TABLE

CREATE TABLE

DECLARE TABLE

7 / 35

7. Which of the following is true about the column data types of temporary tables in SQL Server?

They are determined automatically based on the inserted data

They are always VARCHAR data types

They can only be of numeric data types

They must be defined at the time of table creation

8 / 35

8. What is the naming convention for global temporary tables in SQL Server?

@tableName

#tableName

$tableName

##tableName

9 / 35

9. What happens when two sessions attempt to create a global temporary table with the same name simultaneously in SQL Server?

Only one table is created, and both sessions share the same data

Both tables are created, and data is isolated between sessions

The table is created, but the second session cannot access it

An error is thrown, and the table is not created

10 / 35

10. How can you explicitly drop a temporary table in SQL Server?

Use the TRUNCATE TABLE statement

Use the DELETE statement

Use the DROP TABLE statement

Temporary tables are automatically dropped when not in use

11 / 35

11. Which statement is used to insert data into a temporary table in SQL Server?

INSERT INTO

UPDATE

CREATE TABLE

DELETE FROM

12 / 35

12. What is the purpose of a global temporary table in SQL Server?

To store data permanently

To store data temporarily within a transaction

To store data that can be accessed by multiple sessions

To store data for a single session

13 / 35

13. Which of the following is true about the lifespan of a system temporary table in SQL Server?

It lasts until the database is dropped

It lasts until explicitly dropped or until the session ends

It lasts until the SQL Server instance restarts

It lasts until the session ends

14 / 35

14. Can you create indexes on temporary tables in SQL Server?

Yes, but only on local temporary tables

Yes, but only on global temporary tables

No, indexes are not allowed on temporary tables

Yes, on both local and global temporary tables

15 / 35

15. Which type of temporary table is visible to all sessions and persists until the SQL Server instance restarts?

Local Temporary Table

System Temporary Table

Global Temporary Table

Session Temporary Table

16 / 35

16. In SQL Server, what is the primary use of temporary tables?

To store temporary result sets

To replace permanent tables

To enforce data integrity

To improve query performance

17 / 35

17. Can you create triggers on temporary tables in SQL Server?

No, triggers are not allowed on temporary tables

Yes, but only on global temporary tables

Yes, but only on local temporary tables

Yes, on both local and global temporary tables

18 / 35

18. What is the maximum number of local temporary tables that can be created within a single session in SQL Server?

100

There is no maximum limit

19 / 35

19. Which statement is used to rename a temporary table in SQL Server?

RENAME TABLE

Temporary tables cannot be renamed

TEMPORARY RENAME

ALTER TABLE

20 / 35

20. In SQL Server, can you create foreign key constraints on temporary tables?

Yes, on both local and global temporary tables

Yes, but only on global temporary tables

No, foreign key constraints are not allowed on temporary tables

Yes, but only on local temporary tables

21 / 35

21. Which of the following statements is true about the schema of a temporary table in SQL Server?

Temporary tables have the same schema as the user's default schema.

Temporary tables can have custom schemas defined at creation.

Temporary tables are always created in the tempdb database.

Temporary tables always belong to the dbo schema.

22 / 35

22. What is the primary difference between local and global temporary tables in SQL Server?

Local temporary tables have a shorter lifespan.

Global temporary tables have a shorter lifespan.

Local temporary tables are visible to all sessions.

Global temporary tables can only be created within stored procedures.

23 / 35

23. When you create a temporary table with the same name as an existing permanent table, which table is used in SQL Server?

It depends on the database settings.

An error is thrown, and neither table is used.

The permanent table takes precedence.

The temporary table takes precedence.

24 / 35

24. In SQL Server, what happens to the data in a local temporary table when the session that created it disconnects unexpectedly (e.g., due to a client crash)?

Data is automatically deleted.

An error is thrown.

Data is preserved for the next session.

Data is moved to a global temporary table.

25 / 35

25. Which statement is used to check if a temporary table exists in SQL Server?

CHECK TABLE

IF OBJECT_ID('tempdb..#TableName') IS NOT NULL

TEMP TABLE_EXISTS('TableName')

EXISTS TABLE

26 / 35

26. What is the scope of a global temporary table in SQL Server?

Limited to the current session

Limited to the current user

Visible to all sessions within the same SQL Server instance

Limited to the current database

27 / 35

27. Which system database is primarily responsible for managing temporary tables in SQL Server?

msdb

tempdb

master

model

28 / 35

28. In SQL Server, what is the purpose of the COLLATE clause when creating a temporary table?

To create an index on the temporary table.

To define the primary key for the table.

To specify the filegroup for storing the table.

To specify the collation for character columns.

29 / 35

29. Which of the following is NOT a valid name for a temporary table in SQL Server?

#tempTable$

#1_temp_table

#TempTable123

#my_temp_table

30 / 35

30. When is the schema of a temporary table resolved in SQL Server?

At the time of the first INSERT statement.

When the table is queried.

When the session disconnects.

At the time of table creation.

31 / 35

31. In SQL Server, can you create a temporary table within a user-defined function (UDF)?

No, temporary tables cannot be created within UDFs.

Yes, but only for global temporary tables.

Yes, for both local and global temporary tables.

Yes, but only for local temporary tables.

32 / 35

32. What is the maximum number of global temporary tables with the same name that can be created within a single SQL Server instance?

Fifty

One

There is no maximum limit.

Ten

33 / 35

33. Which statement is used to insert data into a temporary table that has an identity column in SQL Server?

INSERT INTO IDENTITY

Temporary tables cannot have identity columns.

INSERT INTO

INSERT IDENTITY

34 / 35

34. In SQL Server, when is the data in a global temporary table automatically deleted?

When the session that created it ends.

When the SQL Server instance restarts.

When the table is explicitly dropped.

When the database is detached.

35 / 35

35. Which system database is primarily responsible for managing temporary tables in SQL Server?

master

tempdb

msdb

model

Your score is

MS SQL Server – 25 – Temporary Tables MCQ