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: 1. The table must reference the ProductID column in the Product table 2. Existing records in the ProductReview table must not be validated with the Product table. 3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table. 4. Changes to records in the Product table must propagate to the ProductReview table. You also have the following databse 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: 1. Create new rows in the table without granting INSERT permissions to the table. 2. 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 requirments: - 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. You need to modify the design of the Orders table. What should you create?

Correct Answer: a user defined function

You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format. What should you do?

Correct Answer: Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

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?

Correct Answer: 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.

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 Transacr-SQL statement: You run the uspUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table. When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at the second update statement due to a CHECK constraint violation on the TotalDepositAccountCount column. What is the impact of the stored procedure on the CustomerDetails table?

Correct Answer: The value of the TotalDepositAccountCount column is not changed.

Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on 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 work on an OLTP database that has no memory-optimized file group defined. You have a table names tblTransaction that is persisted on disk and contains the information described in the following table: Users report that the following query takes a long time to complete. You need to create an index that: - improves the query performance - does not impact the existing index - minimizes storage size of the table (inclusive of index pages). What should you do?

Correct Answer: Create aclustered index on the table.

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 os independent of the other questions in this series. Information and details provided in a question apply only to that question. You have a database named DB1. There is no memory-optimized filegroup in the database. You run the following query: The following image displays the execution plan the query optimizer generates for this query: Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk. You need to create an index on the Person.Person table that meets the following requirements: 1. All users must be able to benefit from the index. 2. FirstName must be added to the index as an included column. What should you do?

Correct Answer: Create a clustered columnstore index on the table.

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?

Correct Answer: Create a clustered columnstore index on the table.

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?

Correct Answer: Create a nonclustered index on the table.

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 have a memory optimized filegroup. You create a table by running the following Transact-SQL statement: The table is currently used for OLTP workloads. The analytics user group needs to perform real-time operational analytics that scan most of the records in the table to aggregate on a number of columns. You need to add the most efficient index to support the analytics workload without changing the OLTP application. What should you do?

Correct Answer: Create a clustered indexon the table.

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues: ✑ At each level of the query plan, a low total number of rows are processed. ✑ The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer. What should you do?

Correct Answer: Create one Extended Events session with the sqlserver.missing_column_statistics event added.

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?

Correct Answer: an Activity Monitor session in Microsoft SQL Management Studio.

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: - Return a value of 0 if data inserted successfully into the Customers table. - Return a value of 1 if data is not inserted successfully into the Customers table. - Support logic that is written by using managed code. What should you create?

Correct Answer: extended procedure

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: - Return a value of 0 if data inserted successfully into the Customers table. - Return a value of 1 if data is not inserted successfully into the Customers table. - Support TRY"¦CATCH error handling - Be written by using Transact-SQL statements. What should you create?

Correct Answer: extended procedure

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 of a table before and after a data modification and take actions 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?

Correct Answer: extended procedure

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 within a transaction and roll back if a future occurs. - Run when a table is created or modified. What should you create?

Correct Answer: extended procedure

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?

Correct Answer: Create a sys.dm_os_memory_objects query.

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. Captured information must be viewable as the queries are running. What should you do?

Correct Answer: Create a sys.dm_exec_sessions query.

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?

Correct Answer: Create a sys.dm_exec_sessions query.

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. You need to ensure that the performance of each instance is consistent for the same queried and query plans. What should you do?

Correct Answer: Create a sys.dm_exec_sessions query.

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 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. You need to collect query performance data while minimizing the performance impact on the SQL Server. What should you do?

Correct Answer: Create a sys.dm_os_memory_objects query.

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 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. You need to create a baseline set of metrics to report how the computer running SQL Server operates under normal load. The baseline must include the resource usage associated with the server processes. What should you do?

Correct Answer: Create a sys.dm_os_waiting_tasks query.

Note: This question is part of a series of questions that use the same 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 are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements: What should you create?

Correct Answer: user-defined procedure

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?

Correct Answer: a System Monitor report

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 database named DB1. There is no memory-optimized filegroup in the database. You have a table and a stored procedure that were created by running the following Transact-SQL statements: The Employee table is persisted on disk. You add 2,000 records to the Employee table. You need to create an index that meets the following requirements: ✑ Optimizes the performance of the stored procedure. ✑ Covers all the columns required from the Employee table. ✑ Uses FirstName and LastName as included columns. ✑ Minimizes index storage size and index key size. What should you do?

Correct Answer: Create a nonclustered index on the table.

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 users 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?

Correct Answer: Update the transaction level of the report query session to READPAST.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

Developing SQL Databases

Develop SQL databases for scalable applications.

4.8★★★★★(239 ratings)

19,487 studentsIntermediate

Learn to design and develop databases using SQL Server tools and techniques. The exam covers schemas, stored procedures, indexing, and database logic needed to build efficient database-backed applications. This certification supports developers who want stronger database engineering skills. It helps you build systems that are more maintainable, performant, and aligned with application needs.

What you'll learn

Design database schemas- Write stored procedures- Optimize indexes- Enforce integrity- Build database logic

70-762

Lectures1

Quizzes0

Duration30 week

Students19,487

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!