Online Access Free 070-765 Exam Questions

You create a login named BIAppUser. The login must be able to access the Reporting database.
You need to grant access to the BIAppUser login in the database.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation:
Box 1: Reporting
The user is to be created in the Reporting database.
Box 2: CREATE USER
Box 3: FOR LOGIN [BIAppUser]
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
References:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

You administer a Microsoft SQL Server 2016 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.

• A.Enable FILESTREAM on the Server Instance.
• B.Configure the Database for Partial Containment.
• C.Create a non-empty FILESTREAM file group.
• D.Disable FILESTREAM on the Database.
• E.Set the FILESTREAM directory name on the Database.
• F.Enable Contained Databases on the Server Instance.

A Microsoft SQL Server instance has a database named Employees. The database contains information about employees including their salary.
You must create a custom system message as an alert when an error is raised.
You need to create the custom message in the sys.messages table.
Which argument insp_addmessage should you configure?

• A.@severity
• B.@with_log
• C.@1ang
• D.@replace

You have a fact table named FactSales that is 100 GB. FactSales is in a data warehouse that is partitioned by month.
You discover that queries perform index scan operations when the queries should perform index seek operations.
You need to optimize the query plan to reduce the number of scans.
How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

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

You administer a Microsoft SQL Server 2014.
A process that normally runs in less than 10 seconds has been running for more than an hour.
You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked.
Which Transact-SQL statement should you use?

• A.SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
• B.EXEC sp_who 60
• C.DBCC INPUTBUFFER (60)
• D.EXEC sp_helpdb 60