Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 108 discussion

Actual exam question from Microsoft's 70-762

Question #: 108
Topic #: 1

HOTSPOT -
You plan to create a stored procedure that uses a table parameter as an input parameter. The table value parameter may hold between 1 and 10,000 rows when you run the stored procedure.
The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.
You need to create the stored procedure and ensure that it runs as quickly as possible.
How should you complete the procedure? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct answer selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017

by Bartek at Feb. 11, 2020, 9:44 a.m.

Comments

Submit Cancel

dibitok

Highly Voted 5 years, 3 months ago

In the last dropdown should be #MyInputTable

upvoted 32 times

...

Anette

Highly Voted 4 years, 11 months ago

1. @MyInputTable 2. CREATE TABLE #MyInputTable 3. #MyInputTable 4. JOIN #MyInputTable m

upvoted 17 times

...

Barbedx

Most Recent 4 years, 5 months ago

I think in last box need to be temp table because we need to avoid unnecessary data duplication if a variable table have it. Also, of course we need add a DISTINCT to insert query

upvoted 1 times

...

kimalto452

4 years, 6 months ago

why not use mydatatype for variable name ? why we need two tables with the same name...

upvoted 1 times

kimalto452

4 years, 6 months ago

i already see "from @myinputtable " xD

upvoted 1 times

...

TheDUdeu

4 years, 6 months ago

You need the table variable to take in the data and the temp table to take in the rows from the input data. First should be table variable the others should use the temp table

upvoted 1 times

...

lh2607

4 years, 7 months ago

Last box should be joining on #MyInputTable, if the join was on @MyInputTable then it would be pointless doing the first half of the code as it wouldn't be used.

upvoted 4 times

...

arindam_pal

4 years, 10 months ago

Sorry can't delete the previous comments, it seems both temp table or table type variable will work at the last box as per coding perspective.

upvoted 1 times

NickMane

4 years, 9 months ago

yep I agree

upvoted 1 times

...

databasejamdown

4 years, 8 months ago

Jay2's comment will explain why your first statement is actually correct. There may be anywhere from 1 to 10000 records. Statistics on the temp table will allow appropriate optimizatin. A table variable will not allow that type of optimization to meet the speed requirement

upvoted 2 times

...

kiri2020

4 years, 7 months ago

why would you be creating temp table, populating it from the variable table, but then using the variable table after all?

upvoted 5 times

...

arindam_pal

4 years, 10 months ago

the last table must be temp table #MyInputTable, can not be table type variable. You can not use table type variable in last statements. This has been tested in database.

upvoted 3 times

...

stm22

4 years, 11 months ago

1. @MyInputTable ........... passes in the input table variable 2. CREATE TABLE #MyInputTable ..........creates a temp table 3. #MyInputTable ............... inserts into temp table 4. JOIN @MyInputTable ........... from input table

upvoted 1 times

...

Mehdi123

5 years, 2 months ago

Last value should be #InputTable. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch,

upvoted 3 times

...

gtc108

5 years, 3 months ago

@dibitok I agree.

upvoted 6 times

Nickname17

5 years, 2 months ago

The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.

upvoted 2 times

...

Bartek

5 years, 4 months ago

What a difference between @MyInputTable and #MyInputTable in last window ? In my opinion there should be #MyInputTable becouse nevertheless we are inserting rows from Table Param here

upvoted 5 times

Jiacheng

5 years, 3 months ago

Read requirement carefully, he wants to return something from parameter table, so need to be @VariableTable in the last box

upvoted 2 times

...

Nelly100

5 years, 2 months ago

@Myinput table is a table variable and #MyInputTable is temptable. A temptable is usually fast than a table variable. In this case, the temptable has clustered index created automatically during table creation. To to meet the requirement of running the stored procedure quickly, the last box has to be #MyInputable to quicken the join

upvoted 13 times

Nickname17

5 years, 2 months ago

Why “ A temptable is usually fast than a table variable”?

upvoted 1 times

raja1234567890

5 years ago

In this case yes as table variable is created.

upvoted 1 times

...

Jay2

5 years ago

Because a table variable has no statistics and the optimizer thinks it will get only 1 row instead of the 10.000. To ensure the optimizer can do a better job, it is good practice to use a temp table.

upvoted 12 times

...