Which T-SQL command should you recommend?

You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?

You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?

A.
CREATE TABLE ##Table…

B.
CREATE TABLE Table…

C.
CREATE VIEW Table…

D.
CREATE PARTITION SCHEME Table…

E.
DECLARE TABLE @ Table…

Explanation:

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in
the currentsession, and global temporary tables are visible to all sessions. Temporary tables
cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global
temporarytable names with a double number sign (##table_name)

Show Hint

Leave a Reply 4

Your email address will not be published. Required fields are marked *

Gerry

Gerry

if you need use physical memory…

why don´t use DECLARE TABLE @ Table…

Reply

Martin

Martin

and not to forget: no transaction data for table variables.

The requirement “Be able to roll back changes to the temporary data.” is not fulfilled

Reply

Skippo

Skippo

Here are the similarities between a Table Variable and a Temporary Table:

1. Both are instantiated in tempdb (that is, both favor the use of Physical Memory, when it’s available)
2. Clustered indexes can be created on table variables and temporary tables
3. Both are logged in the transaction log (that is, changes done through Table Variables can be rolled back)
4. Just like temp and regular tables, users can perform all DML queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

However, table variables have these shortcomings:

1. Table variables can not have Non-Clustered Indexes
2. You can not create constraints in table variables
3. You can not create default values on table variable columns
4. Statistics can not be created against table variables

However, temporary tables are usually preferred over table variables in that they behave more like physical tables in respect to indexing and statistics creation and lifespan. An interesting limitation of table variables comes into play when executing code that involves a table variable.

Ref:

https://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/

Reply