11 June, 2020
The Regenerate Guide To 70-762 Exam Topics
We provide real 70-762 exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Microsoft 70-762 Exam quickly & easily. The 70-762 PDF type is available for reading and printing. You can print more and practice many times. With the help of our Microsoft 70-762 dumps pdf and vce product and material, you can easily pass the 70-762 exam.
Also have 70-762 free dumps questions for you:
Question 1
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 that contains a table named Employees. The table stores information about the employees of your company.
You need to implement and enforce the following business rules:
- Limit the values that are accepted by the Salary column.
- Prevent salaries less than $15,000 and greater than $300,000 from being entered.
- Determine valid values by using logical expressions.
- Do not validate data integrity when running DELETE statements.
Solution: You implement cascading referential integrity constraints on the table. Does the solution meet the goal?
You have a database that contains a table named Employees. The table stores information about the employees of your company.
You need to implement and enforce the following business rules:
- Limit the values that are accepted by the Salary column.
- Prevent salaries less than $15,000 and greater than $300,000 from being entered.
- Determine valid values by using logical expressions.
- Do not validate data integrity when running DELETE statements.
Solution: You implement cascading referential integrity constraints on the table. Does the solution meet the goal?
Question 2
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 questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, 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 is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out.
Solution: You create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.
Does this meet the goal?
After you answer a question in this section, 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 is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out.
Solution: You create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.
Does this meet the goal?
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?
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?
Question 4
You maintain a Microsoft Azure SQL Database instance.
You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema. You must create a stored procedure that allows User1 to view the following information:
details for each connection to the database
a list of all active user connections and internal tasks
You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.
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.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.
Solution:
Box 1: Sys.dm_exec_connections
Sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection. Returns server wide connection information for SQL Server. Returns current database connection information for SQL Database.
Box 2: sys.dm_exec_sessions
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.
Box 3: GRANT VIEW DATABASE STATE To User1
SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE cannot be granted in the master database.
Does this meet the goal?
You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema. You must create a stored procedure that allows User1 to view the following information:
details for each connection to the database
a list of all active user connections and internal tasks
You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.
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.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.
Solution:
Box 1: Sys.dm_exec_connections
Sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection. Returns server wide connection information for SQL Server. Returns current database connection information for SQL Database.
Box 2: sys.dm_exec_sessions
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.
Box 3: GRANT VIEW DATABASE STATE To User1
SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE cannot be granted in the master database.
Does this meet the goal?
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.
Solution:
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.
Does this meet the goal?
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.
Solution:
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.
Does this meet the goal?
Question 6
You are reviewing the execution plans in the query plan cache. You observe the following:
- There are a large number of single use plans.
- There are a large number of simple execution plans that use multiple CPU cores. You need to configure the server to optimize query plan execution.
Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.
Solution:
* Optimize for ad hoc workloads
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
* Cost Threshold for Parallelism
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.
5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoft from some time in the 1990s. There's no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.
Does this meet the goal?
- There are a large number of single use plans.
- There are a large number of simple execution plans that use multiple CPU cores. You need to configure the server to optimize query plan execution.
Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.
Solution:
* Optimize for ad hoc workloads
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
* Cost Threshold for Parallelism
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.
5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoft from some time in the 1990s. There's no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.
Does this meet the goal?
Question 7
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)
You need to insert a new customer record into the tables as a single unit of work.
In which order should you use the Transact-SQL segments to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Solution:
The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1-side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.
Does this meet the goal?
You need to insert a new customer record into the tables as a single unit of work.
In which order should you use the Transact-SQL segments to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Solution:
The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1-side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.
Does this meet the goal?
Question 8
You have a database named Sales.
You need to create a table named Customer that includes the columns described in the following table:
How should you complete the Transact SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Solution:
Box 1: MASKED WITH (FUNCTION ='default()')
The Defualt masking method provides full masking according to the data types of the designated fields. Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL Box 2: MASKED WITH (FUNCTION ='partial(3,"XXXXXX",0)')
The Custom String Masking method exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
examples:
PhoneNumber varchar(10) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') Box 3: MASKED WITH (FUNCTION ='email()')
The Email masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. .aXXX@XXXX.com.
Example definition syntax: Email varchar(100) MASKEDWITH (FUNCTION = 'email()') NULL References: https://msdn.microsoft.com/en-us/library/mt130841.aspx
Does this meet the goal?
You need to create a table named Customer that includes the columns described in the following table:
How should you complete the Transact SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Solution:
Box 1: MASKED WITH (FUNCTION ='default()')
The Defualt masking method provides full masking according to the data types of the designated fields. Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL Box 2: MASKED WITH (FUNCTION ='partial(3,"XXXXXX",0)')
The Custom String Masking method exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
examples:
PhoneNumber varchar(10) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') Box 3: MASKED WITH (FUNCTION ='email()')
The Email masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. .aXXX@XXXX.com.
Example definition syntax: Email varchar(100) MASKEDWITH (FUNCTION = 'email()') NULL References: https://msdn.microsoft.com/en-us/library/mt130841.aspx
Does this meet the goal?
Question 9
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?
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?
Question 10
You have a database that users query frequently.
The users report that during peak business hours, the queries take longer than expected to execute.
A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.
While performing the trace, the performance of the database server worsens, and the server crashes.
You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.
What should you recommend?
The users report that during peak business hours, the queries take longer than expected to execute.
A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.
While performing the trace, the performance of the database server worsens, and the server crashes.
You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.
What should you recommend?
Question 11
You are developing an app that allows users to query historical company financial data. You are reviewing email messages from the various stakeholders for a project.
The message from the security officer is shown in the Security Officer Email exhibit below. TO: Database developer
From: Security Officer
Subject: SQL object requirements
We need to simplify the security settings for the SQL objects. Having a assign permissions at every object in SQL is tedious and leads to a problem. Documentation is also more difficult when we have to assign permissions at multiple levels. We need to assign the required permissions at one object, even though that object may be obtaining from other objects.
The message from the sales manager is shown in the Sales Manager Email exhibit below. TO: Database developer
From: Sales Manager Subject: Needed SQL objects
When creating objects for our use, they need to be flexible. We will be changing the base infrastructure frequently. We need components in SQL that will provide backward compatibility to our front end applications as the environments change so that do not need to modify the front end applications. We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.
This is an example of the types of data we need to be able to have queries against without having to change the front end applications.
The message from the web developer is shown in the Web Developer Email exhibit below. TO: Database developer
From: Web Developer
Subject: SQL Object component
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.
You need to create one or more objects that meet the needs of the security officer, the sales manager and the web developer.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
Solution:
* Stored procedure: Yes
A stored procedure to implement the following:
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.
* Trigger: No
No requirements are related to actions taken when changing the data.
* View: Yes
Because: We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.
Does this meet the goal?
The message from the security officer is shown in the Security Officer Email exhibit below. TO: Database developer
From: Security Officer
Subject: SQL object requirements
We need to simplify the security settings for the SQL objects. Having a assign permissions at every object in SQL is tedious and leads to a problem. Documentation is also more difficult when we have to assign permissions at multiple levels. We need to assign the required permissions at one object, even though that object may be obtaining from other objects.
The message from the sales manager is shown in the Sales Manager Email exhibit below. TO: Database developer
From: Sales Manager Subject: Needed SQL objects
When creating objects for our use, they need to be flexible. We will be changing the base infrastructure frequently. We need components in SQL that will provide backward compatibility to our front end applications as the environments change so that do not need to modify the front end applications. We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.
This is an example of the types of data we need to be able to have queries against without having to change the front end applications.
The message from the web developer is shown in the Web Developer Email exhibit below. TO: Database developer
From: Web Developer
Subject: SQL Object component
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.
You need to create one or more objects that meet the needs of the security officer, the sales manager and the web developer.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
Solution:
* Stored procedure: Yes
A stored procedure to implement the following:
Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.
* Trigger: No
No requirements are related to actions taken when changing the data.
* View: Yes
Because: We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.
Does this meet the goal?
Question 12
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.
Solution:
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
Does this meet the goal?
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.
Solution:
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
Does this meet the goal?
Question 13
You need to create a view that can be indexed. You write the following statement.
What should you add at line 02?
What should you add at line 02?
Question 14
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables. Module 2: Natively compiled stored procedure
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?
Does this meet the goal?
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables. Module 2: Natively compiled stored procedure
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?
Does this meet the goal?
Question 15
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 have a Microsoft SQL Server database named DB1 that contains the following tables:
Users frequently run the following query. The users report that the query takes a long time to return results.
You need to minimize the amount of time required for the query to return data. What should you do?
You have a Microsoft SQL Server database named DB1 that contains the following tables:
Users frequently run the following query. The users report that the query takes a long time to return results.
You need to minimize the amount of time required for the query to return data. What should you do?
Question 16
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Returning Data by Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.
SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery.
Native Compilation of Tables and Stored Procedures
In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile
memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.
References: https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/dn249342.aspx
Does this meet the goal?
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Returning Data by Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.
SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery.
Native Compilation of Tables and Stored Procedures
In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile
memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.
References: https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/dn249342.aspx
Does this meet the goal?
Question 17
You need to implement triggers to automate responses to the following events:
SQL Server logons
Database schema changes
Database updates
Which trigger types should you use? To answer, drag the appropriate trigger types to the appropriate scenarios. Each trigger 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.
NOTE: Each correct selection is worth one point.
Solution:
Box 1: LOGON
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.
Box 2: INSTEAD OF INSERT
An "INSTEAD of trigger" is executed instead of the original operation, and not combining with the operation. INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute a whole different statement, or just help us check and examine the data before the action is done.
Box 3: DDL
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Note:
You can write triggers that fire whenever one of the following operations occurs:
DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database
References: https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
https://social.technet.microsoft.com/wiki/contents/articles/28152.t-sql-instead-of-triggers.aspx https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017
Does this meet the goal?
SQL Server logons
Database schema changes
Database updates
Which trigger types should you use? To answer, drag the appropriate trigger types to the appropriate scenarios. Each trigger 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.
NOTE: Each correct selection is worth one point.
Solution:
Box 1: LOGON
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.
Box 2: INSTEAD OF INSERT
An "INSTEAD of trigger" is executed instead of the original operation, and not combining with the operation. INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute a whole different statement, or just help us check and examine the data before the action is done.
Box 3: DDL
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Note:
You can write triggers that fire whenever one of the following operations occurs:
DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database
References: https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
https://social.technet.microsoft.com/wiki/contents/articles/28152.t-sql-instead-of-triggers.aspx https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017
Does this meet the goal?
Question 18
You suspect deadlocks on a database.
Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Question 19
You run the following Transact-SQL statements:
Records must only be added to the orders table by using the view. If a customer name does not exist then a new customer name must be created.
You need to ensure that you can insert rows into the orders table by using the view. What should you do?
Records must only be added to the orders table by using the view. If a customer name does not exist then a new customer name must be created.
You need to ensure that you can insert rows into the orders table by using the view. What should you do?
Question 20
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 questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a 3-TB database. The database server has 64 CPU cores. You plan to migrate the database to Microsoft Azure SQL Database.
You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.
Solution: You select the Basic service tier. Does this meet the goal?
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a 3-TB database. The database server has 64 CPU cores. You plan to migrate the database to Microsoft Azure SQL Database.
You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.
Solution: You select the Basic service tier. Does this meet the goal?
![\"70-762](\"/images/70-762.files/image183.jpg\")
Question 21
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?
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?
Question 22
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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:
You must modify the ProductReview Table to meet the following requirements:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* Changes to records in the Product table must propagate to the ProductReview table.
You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.
You must modify the Orders table to meet the following requirements:
* Create new rows in the table without granting INSERT permissions to the table.
* Notify the sales person who places an order whether or not the order was completed.
You must add the following constraints to the SalesHistory table:
* a constraint on the SaleID column that allows the field to be used as a record identifier
* a constant that uses the ProductID column to reference the Product column of the ProductTypes table
* a constraint on the CategoryID column that allows one row with a null value in the column
* a constraint that limits the SalePrice column to values greater than four
Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:
* The table must hold 10 million unique sales orders.
* The table must use checkpoints to minimize I/O operations and must not use transaction logging.
* Data loss is acceptable.
Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Solution:
From question: Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
CREATE VIEW (Transact-SQL) creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.
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.
References: https://msdn.microsoft.com/en-us/library/ms187956.aspx
Does this meet the goal?
You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:
You must modify the ProductReview Table to meet the following requirements:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* Changes to records in the Product table must propagate to the ProductReview table.
You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.
You must modify the Orders table to meet the following requirements:
* Create new rows in the table without granting INSERT permissions to the table.
* Notify the sales person who places an order whether or not the order was completed.
You must add the following constraints to the SalesHistory table:
* a constraint on the SaleID column that allows the field to be used as a record identifier
* a constant that uses the ProductID column to reference the Product column of the ProductTypes table
* a constraint on the CategoryID column that allows one row with a null value in the column
* a constraint that limits the SalePrice column to values greater than four
Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:
* The table must hold 10 million unique sales orders.
* The table must use checkpoints to minimize I/O operations and must not use transaction logging.
* Data loss is acceptable.
Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Solution:
From question: Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
CREATE VIEW (Transact-SQL) creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.
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.
References: https://msdn.microsoft.com/en-us/library/ms187956.aspx
Does this meet the goal?
Question 23
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 an application to track customer sales.
You need to create a database object that meets the following requirements:
Launch when table data is modified.
Evaluate the state a table before and after a data modification and take action based on the difference. Prevent malicious or incorrect table data operations.
Prevent changes that violate referential integrity by cancelling the attempted data modification.
Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
Launch when table data is modified.
Evaluate the state a table before and after a data modification and take action based on the difference. Prevent malicious or incorrect table data operations.
Prevent changes that violate referential integrity by cancelling the attempted data modification.
Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?
Question 24
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 observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks.
What should you do?
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 observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks.
What should you do?
Question 25
You have a Microsoft Azure SQL Database named MyDb that uses server version V12.
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
- Statistics must be aggregated every 15 minutes.
- Query stores must use no more than 1,024 megabytes (MB) of storage.
- Query information must be retained for at least 15 days.
- Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
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.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.
Solution:
or
Both answers are correct.
References: https://msdn.microsoft.com/en-us/library/mt604821.aspx
Does this meet the goal?
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
- Statistics must be aggregated every 15 minutes.
- Query stores must use no more than 1,024 megabytes (MB) of storage.
- Query information must be retained for at least 15 days.
- Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
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.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.
Solution:
or
Both answers are correct.
References: https://msdn.microsoft.com/en-us/library/mt604821.aspx
Does this meet the goal?