Flashcards in Temporary Tables and Table Variables Deck (58)
Create a temporary table based on the schema of an existing table
SELECT [COLUMNS] INTO #TEMPTABLE WHERE 1 = 0
Does a temporary table exist after database disconnection?
Find the name of a temporary table that you can then search for in the sys.objects view
exec sp_help #temptable
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
Can you use user data types in a temp table?
What is the workaround to use user data types in temp tables?
Create the user data type in Tempdb
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.
Can you create your temp table in a session then use dynamic SQL to insert into it?
How can you make a temporary table global?
add a second #:
create table ##temptable
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
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.
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.
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.
Can you create a view based on a temp table?
Can you reference a temp table within a table trigger?
Can you reference a temp table within a function?
When you create a temp table from a database, where does it's collation come from?
What constraints work within a temporary table?
Are Foreign Key constraints available for temp tables?
Can you use indexes with temp tables?
Yes, both clustered and nonclustered indexes work.
Can you use identity columns with a temp table?
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.
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.
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.
Which has a shorter lifecycle, temp table or table variable?
Table variable. It is cleaned up directly after batch completion.
Can you create a table variable using a select into statement?
No, that syntax does not work
Can you use sp_help for a table variable?
No, it does not exist in the tempdb database
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.
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.