You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use?
A.
Scalar-valued function
B.
Inline function
C.
User-defined data type
D.
Stored procedure
Hi Rob,
Do you have latest 70-465?
0
0
100% Valid 70-764 Exam Questions PDF Free Download.v.2018-3-16.332q
QUESTION 161
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stored sales data. One fact table has 100 million rows.
You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You remove all clustered indexes, sort the transactions in the table, and create a clustered index on the table, so that the table is not a heap.
Does the solution meet the goal?
A. Yes
B. No
Answer: A
QUESTION 162
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stored sales data. One fact table has 100 million rows.
You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You load the data in a heap table.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
QUESTION 163
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a data warehouse that stored sales data. One fact table has 100 million rows.
You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You generate a new certificate on new instance.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
Certificates are of no use in this scenario.
QUESTION 164
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You attempt to restore a database on a new SQL Server instance and receive the following error message:
“Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint
`0x7315277C70764B1F252DC7A5101F6F66EFB1069D.”
You need to ensure that you can restore the database successfully.
Solution: You add the backup set password to the restore command.
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 165
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You attempt to restore a database on a new SQL Server instance and receive the following error message:
“Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint
`0x7315277C70764B1F252DC7A5101F6F66EFB1069D.”
You need to ensure that you can restore the database successfully.
Solution: You restore the certificate on the new instance.
Does this meet the goal?
A. Yes
B. No
Answer: A
QUESTION 166
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that includes a table named Candidate.
You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.
Solution: You run the following query:
Does this meet the goal?
A. Yes
B. No
Answer: A
QUESTION 167
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that includes a table named Candidate.
You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.
Solution: You run the following query:
Does this meet the goal?
A. Yes
B. No
Answer: A
QUESTION 168
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that includes a table named Candidate.
You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.
Solution: You run the following query:
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 169
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Audit configured to send audit even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW SERVER STATE permission to User1.
Does this meet the goal?
A. Yes
B. No
Answer: A
QUESTION 170
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Adult configured to send audit even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You assign the db_datareader role to User1.
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 171
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Adult configured to send audit even records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW ANY DEFINITION permission to User1.
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 172
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: You download the log files by using Microsoft Azure Storage Explorer, and then you open the files by using Microsoft SQL Server Management Studio (SSMS).
Does this meet the goal?
A. Yes
B. No
Answer: A
QUESTION 173
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 174
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL database that has Blob Auditing configured.
You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.
Does this meet the goal?
A. Yes
B. No
Answer: B
QUESTION 175
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.
The backup strategies for each database are described in the following table.
Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.
SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
– database name
– impacted file id
– impacted file physical name
– impacted page id
– event type that identifies the error type
– error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
– Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
– Capture queries based on resource consumption.
– Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to view the information about the corrupted pages on SalesDb3.
How should you complete the Transact-SQL statement?
Select two.
A. SELECT * FROM msdb.. corrupted_pages
B. SELECT * FROM msdb..suspect_pages
C. SELECT * FROM system..corrupted_pages
D. SELECT * FROM system..suspect_pages
E. WHERE event_type = 1
F. WHERE event_type = 2
G. WHERE event_type = 3
Answer: BF
QUESTION 176
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
A company has offices in Vancouver, Tokyo, and Paris. The company uses Microsoft SQL Server 2016 Standard edition. You must design a reporting solution that uses data from a point of sale (POS) application’s transactional database.
After data is entered into the POS system, users must be able to run reports within 24 hours. Latency must be minimized.
You need to implemented a solution that minimizes licensing costs.
What should you implement?
A. a Microsoft Azure Stretch Database
B. log shipping
C. an Always On Availability Group with all replicas in synchronous-commit mode
D. a file share witness
E. a Microsoft SQL Server failover cluster instance (FCI)
F. a Windows cluster with a shared-nothing architecture
G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
Answer: A
QUESTION 177
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You need to deploy a new Microsoft SQL Server environment that meets the following requirements:
– The SQL Server instance must be highly available.
– There must be minimal downtime incurred during hardware failure or operating system maintenance.
– All instance-level security settings and SQL Server Agent jobs must be available without additional synchronization tasks.
What should you implement?
A. a Microsoft Azure Stretch Database
B. log shipping
C. an Always On Availability Group with all replicas in synchronous-commit mode
D. a file share witness
E. a Microsoft SQL Server failover cluster instance (FCI)
F. a Windows cluster with a shared-nothing architecture
G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
Answer: E
QUESTION 178
You have a database named DB1.
Users report that a database application that updates the data in DB1 is unresponsive.
You need to identify which process prevents the application from responding.
What should you do?
A. Run DBCC INPUTBUFFER.
B. Query sys.dm_exec_session_wait_stats.
C. Run sp_autostats.
D. Run sp_who.
Answer: B
QUESTION 179
You have 10 Microsoft SQL Server 2016 servers.
You deploy a management data warehouse named DW1. You configure DW1 to gather all the performance data from the servers.
You configure a Data Collector on a SQL server named SV1.
You query the data warehouse on DW1 and discover that data from SV1 is unavailable.
You need to ensure that you can review the performance data from SV1 when you query DW1.
What should you do?
A. Start the SQL Server Agent service on DW1.
B. Execute the msdb.sp_syscollector_set_warehouse_connection_user stored procedure on SV1.
C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.
D. Start the SQL Server Agent service on SV1.
Answer: C
QUESTION 180
You manage a Microsoft SQL Server environment. You plan to configure Database Mail.
You need to ensure that all users can access a private Database Mail profile.
What should you do?
A. On the tempdb database, add the DatabaseMailUserRole to userid 0.
B. On the msdb database, add the DatabaseMailUserRole to the public user.
C. On the master database, add the DatabaseMailUserRole to the public user.
D. On the profile, grant access to the public user.
Answer: B
QUESTION 181
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
A customer that uses an on-premises instance reports that queries take a long time to complete.
You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.
Which Transact-SQL segment should you use?
A. sp_autostats
B. AUTO_UPDATE_STATISTICS_ASYNC
C. SET AUTO_UPDATE_STATISTICS ON
D. CREATE STATISTICS
Answer: C
QUESTION 182
You are a database administrator at Contoso, Ltd. You are preparing to move a global sales application from a development environment to a production environment.
You have a database named Contoso that has a schema named Sales. All objects in the database have the same owner. The schema has a large number of views and stored procedures. None of the stored procedures perform IDENTITY_INSERT operations or dynamic SQL commands. You create all views by using the WITH SCHEMABINDING option.
All employees in the sales division are members of an Active Directory Domain Services (AD DS) security group named Contoso\Sales.
The following database objects are the only objects queried when a user from Contoso\Sales is using the application.
Views
SalesReport
SalesInvoice
Stored procedures
InvoiceExecute ?performs read/write operations
InvoiceSearch ?performs read-only operations
When granting permissions to the database, you should use the principle of least privilege.
You create a new user-defined database role named SalesRole and add Contoso\Sales as a member of SalesRole.
You need to grant all employees in the Sales division permission to use the views and stored procedures.
Which two solutions will meet the requirements? Each correct answer presents a complete solution.
A. Grant the SELECT permission on the Sales schema to SalesRole.
Grant the EXECUTE permission on the Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
B. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to Contoro\Sales.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to Contoso\Sales.
C. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
D. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole.
Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.
Grant the SELECT permission on all tables referenced by Sales.SalesReport, Sales.SalesInvoice, and Sales.InvoiceSearch to SalesRole.
Grant the SELECT, INSERT, UPDATE, and DELETE permissions on all tables referenced by Sales.InvoiceExecute to SalesRole.
Answer: BC
Explanation:
Incorrect Answers:
A: There is no Sales Schema.
D: No need for DELETE permissions.
0
0