70-762 Exam
Top Microsoft 70-762 training tools Choices
Pass4sure 70-762 Questions are updated and all 70-762 answers are verified by experts. Once you have completely prepared with our 70-762 exam prep kits you will be ready for the real 70-762 exam without a problem. We have Improved Microsoft 70-762 dumps study guide. PASSED 70-762 First attempt! Here What I Did.
Q11. 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 create an object that meet the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run written a transaction and roll back if a future occurs.
- Run when a table is created or modified. 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: B
Explanation:
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Q12. 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.
Q13. 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:
1. Allow users access to the CustomerName and CustomerNumber columns for active customers.
2. Allow changes to the columns that the view references. Modified data must be visible through the view.
3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:
1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.
2. Prevent changes to the columns that the view references.
3. 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 Transacr-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 must update the design of the Customer table to meet the following requirements.
1. You must be able to store up to 50 accounts for each customer.
2. Users must be able to retrieve customer information by supplying an account number.
3. Users must be able to retrieve an account number by supplying customer information. You need to implement the design changes while minimizing data redundancy.
What should you do?
A. Splitthe table into three separate tables. Include the AccountNumber and CustomerID columns in the first table. Include the CustomerName and Gender columns in the second table. Include the AccountStatus column in the third table.
B. Split the table into two separate tables. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first table. Include the AccountNumber and AccountStatus columns in the second table.
C. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first table. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.
D. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. IncludeAccountNumber, AccountStatus and CustomerID columns in the second table.
Answer: D
Explanation:
Two tables is enough.CustomerID must be in both tables.
Q14. HOTSPOT
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.
The Leads table must include the columns described in the following table.
The data types chosen must consume the least amount of storage possible. You need to select the appropriate data types for the Leads table.
In the table below, identify the data type that must be used for each table column. NOTE: Make only one selection in each column.
Answer:
Explanation:
Bit is aTransact-SQL integer data type that can take a value of 1, 0, or NULL.
Smallint is aTransact-SQL integer data type that can take a value in the range from -32,768 to 32,767.
int, bigint, smallint, and tinyint (Transact-SQL) Exact-number data types that use integer data.
References:https://msdn.microsoft.com/en-us/library/ms187745.aspx https://msdn.microsoft.com/en-us/library/ms177603.aspx
Q15. DRAG DROP
You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, DELETE and BACKUP DATABASE statements.
You have the following requirements:
- You must run all the stored procedures within the same transaction.
- You must automatically start a transaction when stored procedures include DML statements.
- You must not automatically start a transaction when stored procedures include DDL statements.
You need to run all three stored procedures.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.
Answer:
Explanation:
Note:
Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement,which returns the connection to autocommit mode. In autocommit mode, allindividual statements are committed if they complete successfully.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE (DDL) FETCH
REVOKE
BEGIN TRANSACTION GRANT
SELECT CREATE (DDL) INSERT
TRUNCATE TABLE DELETE (DML) OPEN
UPDATE (DML) DROP (DDL)
Note 2: XACT_STATE returns the following values.
1 The currentrequest has an active user transaction. The request can perform any actions, including writing data and committing the transaction. The transaction is committable.
-1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.the transaction is uncommittable and should be rolled back.
0 There is no active user transaction for the current request. Acommit or rollback operation would generate an error.
References:
https://technet.microsoft.com/en-us/library/ms187807(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms189797(v=sql.110).aspx
Q16. 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 independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
What should you do?
A. A. Create asys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create aPerformance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure ‘max server memory’ query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.
Answer: B
Explanation:
sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.
Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.
Q17. 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 independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor and optimize the SQL Server to maximize throughput, response time, and overall SQL performance.
You need to identify previous situations where a modification has prevented queries from selecting data in tables.
What should you do?
A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a Performance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure ‘max server memory’query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.
Answer: G
Explanation:
sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
Q18. 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:
The application uses views to control access to data. Views must meet the following requirements:
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
Q19. HOTSPOT
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.
You need to implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:
What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer:
Explanation:
Using TRY...CATCH inTransact-SQL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct. TRY…CATCH can use the following error function to capture error information:
ERROR_MESSAGE() returns the complete text of the error message. The text includes the
values supplied for any substitutable parameters such as lengths, object names, or times. References:https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
Q20. 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_tran_database_transaction dynamic management view query
C. an Extended Events session that uses Query Editor
D. an Activity Monitor session in Microsoft SQL Management Studio.
Answer: D
Explanation:
References: https://msdn.microsoft.com/en-us/library/hh212951.aspx