Temporary Tables and Table Variables Flashcards
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?
No
Find the name of a temporary table that you can then search for in the sys.objects view
use tempdb
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?
No
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?
Yes
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?
No
Can you reference a temp table within a table trigger?
Yes
Can you reference a temp table within a function?
No.
When you create a temp table from a database, where does it’s collation come from?
Tempdb
What constraints work within a temporary table?
Default
Check
Primary Key
Are Foreign Key constraints available for temp tables?
No
Can you use indexes with temp tables?
Yes, both clustered and nonclustered indexes work.
Can you use identity columns with a temp table?
yes
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.
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)
Can you use a table variable outside of a dynamically executed sql statement?
No, because it’s outside of the scope.
It’s the same for if you declared the variable outside of the dynamic sql and then tried to insert into it from the dynamic statement.
Can you use a view with table variables?
No
Can you use a table variable within a function?”
Yes
Which is faster within a temporary table, truncate or delete?
truncate
Can you use truncate with table variables?
No.
Use a table variable to pass data into a stored procedure
CREATE TYPE [TABLETYPE] AS TABLE ( VAL1 VARCHAR(5) )
GO
CREATE PROCEDURE DBO.PROCEDURE1
@TABLEVAR [TABLETYPE] READONLY
AS
INSERT DBO.TABLE1
SELECT * FROM @TABLEVAR
GO
DECLARE @INPUTVAR AS [TABLETYPE]
INSERT @INPUTVAR VALUES (‘A’,’B’,’C’)
EXEC DBO.PROCEDURE1 @INPUTVAR
What collation will columns use in a table variable?
They will use the same collation that the database uses in which they were created.
What constraints work within a table variable?
All except foreign keys.
Will a named constraint work with a table variable? How about for a temporary table?
No for table variable
Yes for temporary table.
When is the only time you can create an index on a table variable?
On the creation of the table variable (sql 2014 does allow inline non-unique clustered and nonclustered index definitions)
Can you use identities with table variables?
Yes, but you cannot insert into the table using the SET IDENTITY_INSERT ON like you can with temporary tables
Will a transaction still show in a table variable after a rollback?
Yes, table variables do not pay attention to transactions.
Does SQL Server maintain table statistics for table variables?
No.
What is the problem that occurs within an execution plan regarding inserting into a table variable?
You lose parallelism, so the insert could take much longer. (limitation of table variables)
Where are all temporary tables and table variables created?
tempdb - So it can become a bottleneck
How many data files does tempdb come with by default? Why could this be a problem?
1 - a heavy amount of small temp object creation and deletion can cause allocation page latch contention
How many data files should you have for Tempdb if you have <= 8 cores?
The number of files should equal the number of cores on the system.
How many data files should you have for Tempdb if you have > 8 cores?
8 and add in 4 file chunks and monitor for contention
What is a PFS page?
Page free space
How often do you get a PFS page?
Every 8088 pages
What is the system table that shows tempdb file space usage?
sys.dm_db_file_space_usage
Within sys.dm_db_file_space_usage, what does user_object show you?
Table variables, temporary tables indexes tables in table valued functions global temporary tables user defined tables
Within sys.dm_db_file_space_usage, what does Internal_object show you?
Work tables for Cursor or spool operations
Work files for hash joins
Sort activity
Within sys.dm_db_file_space_usage, what does version_store show you?
Read committed snapshot enabled - would be higher value
What are some advantages of Temporary tables?
- Intermediate result-sets that can reduce query plan complexity (Not guaranteed solution)
- Isolate table to 1 user
- Column-level stats like permanent tables
- Can use Truncate, SELECT INTO, CREATE INDEX, ALTER TABLE, IDENTITY_INSERT, ROLLBACK Transaction
What are some advantages for table variables?
- Can be used in SCALAR and Multi-Statement Table Valued Functions
- Inherits current DB collation
- Will not directly impact recompilations (although may want that in some circumstances)
- Can be passed as input params to stored procs
Temporary tables disadvantages
- Heavy tempdb usage via object creates/drops can lead to latch contention
- Not always optimal compared to inline, single-statement alternative
- Not supported inuser-defined functions
- Inherits tempdb collation for non-contained DB’s
- System caching of temp tables and associated stats may cause unexpected query plan shapes and incorrect stats
Table Variables disadvantages
- Heavy tempdb usage via object creates/drops can lead to latch contention
- No column-level statistics, cardinality estimate skews
- cannot use Truncate, SELECT INTO, CREATE INDEX, ALTER TABLE, IDENTITY_INSERT, ROLLBACK
- Parallelism inhibites for non-select operators
