Vivid 70-762 Dumps 2021

NEW QUESTION 1
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 have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:
* Allow users access to the CustomerName and CustomerNumber columns for active customers.
* Allow changes to the columns that the view references. Modified data must be visible through the view.
* Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:
* Allow users access to the CustomerName, Address, City, State and PostalCode columns.
* Prevent changes to the columns that the view references.
* Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transact-SQL statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.
You need to create Sales.FemaleCustomers.
How should you complete the view definition? 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.

Answer:

Explanation: Box 1:WITH SCHEMABINDING:
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
Box 2:Box 2: WITH CHECK OPTION
CHECK OPTION forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Note: Sales.Female.Customers must meet the following requirements: References: https://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 2
Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

You need to create one nonclustered na index that contains all of the columns in the above table. You must minimize index

• A. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);
• B. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);
• C. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);
• D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

Answer: D

NEW QUESTION 3
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock.
Syntaxis: datetimeoffset [ (fractional seconds precision) ]
For the use "datetimeoffset", the Fractional seconds precision is 7. References: https://msdn.microsoft.com/en-us/library/bb630289.aspx

NEW QUESTION 4
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.
What should you do?

• A. Create a clustered index on the table.
• B. Create a nonclustered index on the table.
• C. Create a nonclustered filtered index on the table.
• D. Create a clusteredcolumnstore index on the table.
• E. Create a nonclustered columnstore index on the table.
• F. Create a hash index on the table.

Answer: C

Explanation: A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed
filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
References: https://technet.microsoft.com/en-us/library/cc280372(v=sql.105).aspx

NEW QUESTION 5
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 have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.
Changes to the price of any product must be less a 25 percent increase from the current price. The shipping department must be notified about order and shipping details when an order is entered into the database.
You need to implement the appropriate table objects.
Which object should you use for each table? To answer, drag the appropriate objects to the correct tables. Each object 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.

Answer:

Explanation: The Products table needs a primary key constraint on the ProductID field.
The Orders table needs a foreign key constraint on the ProductID field, with a reference to the ProductID field in the Products table.

NEW QUESTION 6
You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non business hours.
You run the following Transact-SQL statement:

You analyze the execution plan for the statement. (Click the Exhibit button).

You need to add an index that optimizes performance. How should you complete the Transact-SQL statement?

Answer:

Explanation:

NEW QUESTION 7
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 the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.
You need to create a database object. What should you create?

• A. extended procedure
• B. CLR procedure
• C. user-defined procedure
• D. DML trigger
• E. scalar-valued function
• F. table-valued function

Answer: F

Explanation: User-defined scalar functions return a single data value of the type defined in the RETURNS clause. References: https://technet.microsoft.com/en-us/library/ms177499(v=sql.105).aspx

NEW QUESTION 8
You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?

• A. a System Monitor report
• B. a sys.dm_exec_query_stats dynamic management view query
• C. a sys.dm_exec_session_wait_stats dynamicmanagement view query
• D. an Activity Monitor session in Microsoft SQL Management Studio.

Answer: C

Explanation: sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has “signaled” that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.

NEW QUESTION 9
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database named DB1 that includes a table named sales . Orders. You grant a user named User1 select permissions on the sales schema.
You need to ensure that User1 can select data from the sales, orders table without specifying the schema name in any Transact SQL statements.
Solution: You move the sales.orders table to the dbo schema. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 10
You have a table named Person.Address that includes the following columns:
AddressID
AddressLine1
AddressLine2
City
StateProvinceID
PostakCode
RowGuid
ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
AddressLine1
AddressLine2
City
StateProvinceID
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.

Answer:

Explanation: Box 1: INDEX
Box 2: ON
Box 3: INCLUDE
INCLUDE (column [ ,... n ] ) specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2021

NEW QUESTION 11
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit s5les data to the corporate office. The data must be inserted into the summary table that includes the StorelD, ProductID, Qtysold, Totprodsales, and Datesold Columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?

• A. Create a unique constraint.
• B. Create a foreign key constraint to the storeID column in each of the store tables.
• C. Create a rule and bind it to the storeID column.
• D. Create a check constraint.

Answer: B

NEW QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one. References: https://msdn.microsoft.com/en-us/library/ms190457.aspx

NEW QUESTION 13
You run the following Transact-SQL following statement:

Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records.
You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.
Which Transact-SQL statement should you run?

• A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH(ONLINE = OFF)
• B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY= ON)
• C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY=OFF)
• D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)
• E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

Answer: B

Explanation: IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

NEW QUESTION 14
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
You have a reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.
What should you do?

• A. Create a clustered indexon the table.
• B. Create a nonclustered index on the table.
• C. Create a nonclustered filtered index on the table.
• D. Create a clustered columnstore index on the table.
• E. Create a nonclustered columnstore index on the table.
• F. Create a hash index on thetable.

Answer: D

Explanation: The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size.
A clustered columnstore index is the physical storage for the entire table.

NEW QUESTION 15
You are monitoring a Microsoft Azure SQL Database. The database is experiencing high CPU consumption.
You need to determine which query uses the most cumulative CPU.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation: Box 1: sys.dm_exec_query_stats
sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server. Box 2: highest_cpu_queries.total_worker_time DESC
Sort on total_worker_time column
Example: The following example returns information about the top five queries ranked by average CPU time. This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption.
USE AdventureWorks2012; GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text"
FROM (SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
References: https://msdn.microsoft.com/en-us/library/ms189741.aspx

NEW QUESTION 16
Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
- UspA: This stored procedure updates only the EmployeeStatus column.
- UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
- Allow user access to all columns in the tables that the view accesses.
- Restrict updates to only the rows that the view returns. Exhibit

You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources. What should you do?

• A. Update the transaction level of the report query session to READPAST.
• B. Modify the report queries to use the UNION statement to combine the results of two or more queries.
• C. Set the READ_COMMITTED_SNAPSHOT database option to ON.
• D. Update the transaction level of the report query session to READ UNCOMMITTED.

Answer: C

Explanation: References: https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

NEW QUESTION 17
You have a database named Database1.
Users report that they experience deadlock issues- You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit. (Click the Exhibit button.)
***Exhibit is Missing***
You view the contents of the Resource List section as shown in the Resource List exhibit, (Click the Exhibit button.)
***Exhibit is Missing***
You view deadlock information as shown in the Deadlock List exhibit. (Click the Exhibit button.)
***Exhibit is Missing***

Answer:

Explanation: