Temporary Tables and Table Variables Flashcards Preview

70-464 Certification > Temporary Tables and Table Variables > Flashcards

Flashcards in Temporary Tables and Table Variables Deck (58)
Loading flashcards...
1

Create a temporary table based on the schema of an existing table

SELECT [COLUMNS] INTO #TEMPTABLE WHERE 1 = 0

2

Does a temporary table exist after database disconnection?

No

3

Find the name of a temporary table that you can then search for in the sys.objects view

use tempdb
exec sp_help #temptable

4

What happens if you create a temporary table with the same name in a different session?

It gives the temp table a unique name in the tempdb

5

Can you use user data types in a temp table?

No

6

What is the workaround to use user data types in temp tables?

Create the user data type in Tempdb

7

Create a temp table using dynamic SQL then select from it within your session. Does that work?

The temp table will be created, but you can't use it within the session scope.

If you include the insert and select from within your dynamic SQL, that will work.

8

Can you create your temp table in a session then use dynamic SQL to insert into it?

Yes

9

How can you make a temporary table global?

add a second #:

create table ##temptable

10

Can you create two global temp tables with the same name in two different sessions?

No, you will get an error that the object already exists

11

What happens if you try to create two global temp tables with the same name in two different databases and sessions?

You will get the error that the object already exists.

12

Will a temporary table that is created in a stored procedure be available to use within the same session?

No, as soon as the stored procedure is done executing, the temp table is dropped.

13

Create a stored procedure that creates a temp table and then calls another stored procedure that inserts into the table and selects from it. Does it work?

Yes, both procedures will be able to access the same temporary table.

14

Can you create a view based on a temp table?

No

15

Can you reference a temp table within a table trigger?

Yes

16

Can you reference a temp table within a function?

No.

17

When you create a temp table from a database, where does it's collation come from?

Tempdb

18

What constraints work within a temporary table?

Default
Check
Primary Key

19

Are Foreign Key constraints available for temp tables?

No

20

Can you use indexes with temp tables?

Yes, both clustered and nonclustered indexes work.

21

Can you use identity columns with a temp table?

yes

22

How does a temp table behave with regard to implicit and explicit transactions?

Exactly the same as with a permanent table: rollbacks will remove records that haven't been committed.

23

How do statistics work for temporary tables?

Exactly the same as with permanent table: stats are auto generated based on queries and are updated regularly.

24

Can you declare a table variable in one batch and select it in another?

No, table variables are just like regular variables in that the are scoped specifically for the batch they were created in.

25

Which has a shorter lifecycle, temp table or table variable?

Table variable. It is cleaned up directly after batch completion.

26

Can you create a table variable using a select into statement?

No, that syntax does not work

27

Can you use sp_help for a table variable?

No, it does not exist in the tempdb database

28

How can you see the metadata for the table variable?

Select from sys.tables with a join to sys.columns and find the columns you created in the table variable.

The name of the table variable will be a random system generated name.

29

Can you add or change a column in a table variable like you can with a temporary table?

No, table variables are not as flexible as temp tables.

30

What datatypes can you use with a table variable?

All datatypes are available to use, including user defined data types. (You don't have to declare them in tempdb)