Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

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

Temporary Tables and Table Variables Flashcards

1
Q

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

A

SELECT [COLUMNS] INTO #TEMPTABLE WHERE 1 = 0

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Does a temporary table exist after database disconnection?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

use tempdb

exec sp_help #temptable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

It gives the temp table a unique name in the tempdb

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Can you use user data types in a temp table?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

Create the user data type in Tempdb

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you make a temporary table global?

A

add a second #:

create table ##temptable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

You will get the error that the object already exists.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Can you create a view based on a temp table?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Can you reference a temp table within a table trigger?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Can you reference a temp table within a function?

A

No.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

Tempdb

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What constraints work within a temporary table?

A

Default
Check
Primary Key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Are Foreign Key constraints available for temp tables?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Can you use indexes with temp tables?

A

Yes, both clustered and nonclustered indexes work.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Can you use identity columns with a temp table?

A

yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

How do statistics work for temporary tables?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

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

25
Q

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

A

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

26
Q

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

A

No, that syntax does not work

27
Q

Can you use sp_help for a table variable?

A

No, it does not exist in the tempdb database

28
Q

How can you see the metadata for the table variable?

A

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
Q

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

A

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

30
Q

What datatypes can you use with a table variable?

A

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

31
Q

Can you use a table variable outside of a dynamically executed sql statement?

A

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.

32
Q

Can you use a view with table variables?

A

No

33
Q

Can you use a table variable within a function?”

A

Yes

34
Q

Which is faster within a temporary table, truncate or delete?

A

truncate

35
Q

Can you use truncate with table variables?

A

No.

36
Q

Use a table variable to pass data into a stored procedure

A

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

37
Q

What collation will columns use in a table variable?

A

They will use the same collation that the database uses in which they were created.

38
Q

What constraints work within a table variable?

A

All except foreign keys.

39
Q

Will a named constraint work with a table variable? How about for a temporary table?

A

No for table variable

Yes for temporary table.

40
Q

When is the only time you can create an index on a table variable?

A

On the creation of the table variable (sql 2014 does allow inline non-unique clustered and nonclustered index definitions)

41
Q

Can you use identities with table variables?

A

Yes, but you cannot insert into the table using the SET IDENTITY_INSERT ON like you can with temporary tables

42
Q

Will a transaction still show in a table variable after a rollback?

A

Yes, table variables do not pay attention to transactions.

43
Q

Does SQL Server maintain table statistics for table variables?

A

No.

44
Q

What is the problem that occurs within an execution plan regarding inserting into a table variable?

A

You lose parallelism, so the insert could take much longer. (limitation of table variables)

45
Q

Where are all temporary tables and table variables created?

A

tempdb - So it can become a bottleneck

46
Q

How many data files does tempdb come with by default? Why could this be a problem?

A

1 - a heavy amount of small temp object creation and deletion can cause allocation page latch contention

47
Q

How many data files should you have for Tempdb if you have <= 8 cores?

A

The number of files should equal the number of cores on the system.

48
Q

How many data files should you have for Tempdb if you have > 8 cores?

A

8 and add in 4 file chunks and monitor for contention

49
Q

What is a PFS page?

A

Page free space

50
Q

How often do you get a PFS page?

A

Every 8088 pages

51
Q

What is the system table that shows tempdb file space usage?

A

sys.dm_db_file_space_usage

52
Q

Within sys.dm_db_file_space_usage, what does user_object show you?

A 
Table variables, temporary tables 
indexes 
tables in table valued functions
global temporary tables 
user defined tables
53
Q

Within sys.dm_db_file_space_usage, what does Internal_object show you?

A

Work tables for Cursor or spool operations
Work files for hash joins
Sort activity

54
Q

Within sys.dm_db_file_space_usage, what does version_store show you?

A

Read committed snapshot enabled - would be higher value

55
Q

What are some advantages of Temporary tables?

A
  • 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
56
Q

What are some advantages for table variables?

A
  • 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
57
Q

Temporary tables disadvantages

A
  • 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
58
Q

Table Variables disadvantages

A
  • 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

70-464 Certification (22 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions