Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 161:

  DRAG DROP

  You have a table that contains a column named Department. Each user is assigned to a database role name that matches the Department column value. Users authenticate to the database with accounts that are assigned to their

  department's database role. You belong to the db_owner role and have rights to run the EXECUTE AS USER statement.

  Users must only view records that match their department.

  You need to implement Microsoft SQL Server row-level security (RLS) and test the implementation.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Step 1: Create a filter predicate by using a SQL Server inline table-valued user-defined function.

  Create a new schema, and an inline table-valued function.

  Step 2: Create a security policy

  Create a security policy adding the function as a filter predicate.

  The CREATE SECURITY POLICY command creates a security policy for row level security.

  Step 3: Run EXECUTE AS USER

  References:

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

  https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

• Question 162:

  You have configured Resource Governor with three resource pools.

  You have assigned the first resource pool a minimum CPU and memory value of 20%.

  You have assigned the second resource pool a minimum CPU and memory value of 30%.

  You want to assign maximum CPU and memory values to the third resource pool.

  Which of following maximum CPU and memory value you can assign to this resource pool?

  A. 10%
  B. 30%
  C. 50%
  D. 100%
  C. 50%

• Question 163:

  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.
  B. On the msdb database, add the DatabaseMailUserRole to the public user.

• Question 164:

  You are troubleshooting an application that runs a query.

  The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock occurs.

  What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

  A. Query the sys.dm_exec_requests dynamic management view.
  B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
  C. Query the sys.dm_exec_sessions dynamic management view.
  D. Enable trace flag 1222, and then view the SQL Server error log.
  C. Query the sys.dm_exec_sessions dynamic management view.

• Question 165:

  DRAG DROP

  You plan to deploy SQL Server 2012. You must create two tables named Table 1 and Table 2 that will have the following specifications:

  Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.

  Table2 will contain a column named Column2 that is the product of two other columns in Table2.

  Both Table1 and Table2 will contain more than 1 million rows.

  You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables.

  Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.

  Select and Place:

  

  

  ---

  Column1 – Sparse; Column2 - Computed

  - Sparse columns are ordinary columns that have an optimized storage for null values.

  Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

  -A Persisted column would be faster to retrieve.

  - A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more

  operators. Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED

  keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change.

  References: http://msdn.microsoft.com/en-us/library/cc280604.aspx http://msdn.microsoft.com/en-us/library/ms186241.aspx

• Question 166:

  DRAG DROP

  You define a table by using the following statement.

  

  The Teachers table contains one million records. A sample record of the XML data output is as follows:

  

  You plan to create queries against the table based on grade.

  You need to recommend which indexes should be applied to the XML fields. The solution must meet the following requirements:

  Reduce the amount of time required to query the table based on grade.

  Minimize the size of the index.

  What should you recommend? To answer, drag the appropriate index types to the correct fields. Each index type 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

  Select and Place:

  

  

• Question 167:

  DRAG DROP

  You need to recommend a backup process for data warehouse database. The solution must meet the following requirements:

  Ensure that if a hardware failure occurs, you can bring the database online without losing more than 24 hours of transactions.

  Minimize the amount of administrative effort required to restore any lost data.

  Minimize the space used by transaction logs.

  What should you include in the recommendation? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Set the database to the simple recovery model;

  Perform a weekly full backup of the database;

  Perform a differential backup of the database every night

  Note:

  -Simple recovery model No log backups.

  -Full recovery model Requires log backups

• Question 168:

  You administer a Microsoft SQL Server 2016 database.

  The database is currently configured to log ship to a secondary server.

  You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online.

  You want to perform a tail-log backup. You need to leave the primary database in a restoring state.

  Which option of the BACKUP LOG command should you use?

  A. NO_TRUNCATE
  B. NORECOVERY
  C. STANDBY
  D. FORMAT
  B. NORECOVERY

• Question 169:

  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 examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure.

  What should you use?

  A. Activity Monitor
  B. sp_who3 stored procedure
  C. SQL Server Management Studio (SSMS) Object Explorer
  D. SQL Server Data Collector
  E. SQL Server Data Tools (SSDT)
  F. SQL Server Configuration Manager
  A. Activity Monitor

• Question 170:

  You are implementing a SQL Server 2016 five-node failover cluster.

  You need to choose a quorum configuration.

  Which configuration should you use?

  A. Distributed File System (DFS)
  B. Node Majority
  C. Cluster Shared Volume (CSV)
  D. Node and Disk Majority
  D. Node and Disk Majority