Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 145 discussion

Actual exam question from Microsoft's 70-761

Question #: 145
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
✑ Be indexable
✑ Contain up-to-date statistics
✑ Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another's data.
Solution: You create a global temporary table in the stored procedure.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️

by Abhilash_KK at Sept. 5, 2019, 1:08 p.m.

Comments

Submit Cancel

Abhilash_KK

Highly Voted 5 years, 9 months ago

it is mentioned "The solution must prevent users from accessing one anotherâ€™s data.". So should it be local temporary table?

upvoted 13 times

Jiacheng

5 years, 4 months ago

I think global temp table can still make it. don't forget you are using it in SP, user can only exec sp, they don't know what inside the SP, and maybe they'll check permernent tables or views, but when you close the session where you create global temp table, the global temp table will gone.

upvoted 1 times

...

moehijawe

Highly Voted 5 years, 5 months ago

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

upvoted 7 times

Anette

5 years ago

Does this mean that users can access one other's data/ So the answer is NO? I understand that they have their own session and do not have to do with the others session. Any hit in this?

upvoted 1 times

MarcusJB

5 years ago

Yes, the correct answer is "No", because global temporary tables allow the access to the data for everyone.

upvoted 2 times

...

Billybob0604

Most Recent 4 years, 5 months ago

check this discussion : https://www.sqlservercentral.com/forums/topic/global-temptable-how-do-permissions-work

upvoted 1 times

...

Billybob0604

4 years, 5 months ago

The SP will be used by hundreds of users concurrently, so I would say global temporary table. If local you wouldn't be able to access the table through the SP.

upvoted 1 times

...

mch185

4 years, 5 months ago

answer is B ; tested create global temp table from a session and add rows to it from an other session and it will work :D

upvoted 1 times

...

Vermonster

4 years, 5 months ago

B is correct because it is global and other users can access it. Change it to local and it would work

upvoted 2 times

...

BabyBee

4 years, 7 months ago

This is confusing as how will other users be able to see the local temp table if only the connection that created it can see it?. Answer should be correct, Yes. "In SQL Server, local temporary tables are visible only in the current session. So if you create a local temporary table in one session, you cannot access it in other sessions." http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables

upvoted 1 times

kimalto452

4 years, 6 months ago

Solution: You create a GLOBAL temporary table in the stored procedure. Does this meet the goal?

upvoted 1 times

...

melvin9900

4 years, 11 months ago

Answer is B . https://blog.sqlauthority.com/2018/03/16/sql-server-creating-temporary-global-temporary-stored-procedures/

upvoted 2 times

Billybob0604

4 years, 5 months ago

If you need to access your Temporary Stored Procedures from a different session, you can also consider Global Temporary stored procedures. For this example, you will have to create a stored procedure name by prefixing with two # signs (##) instead of one # sign.

upvoted 1 times

...

fabzo

5 years, 7 months ago

True users would be able to access each other data with a global temp table. Is there a better explanation?

upvoted 1 times

...