Categories
70-762 Dumps

70-762 Real Exam Dumps Questions and answers 21-30

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.21

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:

image

Employee ID

image

image

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:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock. Syntaxis: datetimeoffset [ (fractional seconds precision) ] For the use quot;datetimeoffsetquot;, the Fractional seconds precision is 7.

References: https://msdn.microsoft.com/en-us/library/bb630289.aspx

Question No.22

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 a FOR UPDATE trigger on the table.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update

Question No.23

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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following

Transact SQL statements:

image

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 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:

  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 create an object that allows finance users to be able to retrieve the required data. The object must not have a negative performance impact.

How should you complete the Transact-SQL statements?

To answer, select the appropriate Transact-SQL segments in the answer area.

image

Correct Answer:

image

Question No.24

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:

image

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

UspA: This stored procedure updates only the EmployeeStatus column. UspB: This stored procedure updates only the EmployeePayRate column.

The application uses views to control access to data. Views must meet the following requirements:

Allow user access to all columns in the tables that the view accesses. Restrict updates to only the rows that the view returns.

image

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?

  1. Update the transaction level of the report query session to READPAST.

  2. Modify the report queries to use the UNION statement to combine the results of two or more queries.

  3. Set the READ_COMMITTED_SNAPSHOT database option to ON.

  4. Update the transaction level of the report query session to READ UNCOMMITTED.

Correct Answer: C

Explanation:

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

Question No.25

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:

image

The following image displays the execution plan the query optimizer generates for this query:

image

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?

  1. Create a clustered index on the table.

  2. Create a nonclustered index on the table.

  3. Create a nonclustered filtered index on the table.

  4. Create a clustered columnstore index on the table.

  5. Create a nonclustered columnstoreindex on the table.

  6. Create a hash index on the table.

Correct Answer: B

Explanation:

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:

They can be data typesnot allowed as index key columns.

They are not considered by the Database Engine when calculating the number of index key columns or index key size.

Question No.26

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:

image

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 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:

  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 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.

You need to enable referential integrity for the ProductReview table. How should you complete the relevant Transact-SQL statement?

To answer select the appropriate Transact-SQL segments in the answer area. Select two alternatives.

image

  1. For the first selection select: WITH CHECK

  2. For the first selection select: WITH NOCHECK

  3. For the second selection select: ON DELETE NO ACTION ON UPDATE CASCADE

  4. For the second selection select: ON DELETECASCADE ON UPDATE CASCADE

  5. For the second selection select: ON DELETE NO ACTION ON UPDATE NO ACTION

  6. For the second selection select: ON DELETE CASCADE ON UPDATE NO ACTION

Correct Answer: BC

Explanation:

B: We should use WITH NOCHECK as existing records inthe ProductReview table must not be validated with the Product table.

C: Deletes should not be allowed, so we use ON DELETE NO ACTION. Updates should be allowed, so we use ON DELETE NO CASCADE NO ACTION: the Database Engine raises an error, and the updateaction on the row in the parent table is rolled back.

CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.

Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifieswhat

action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifieswhat action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

Note: 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 referencedby theProductReview table.

  4. Changes to records in the Product table must propagate to the ProductReview table.

References:

https://msdn.microsoft.com/en-us/library/ms190273.aspx https://msdn.microsoft.com/en-us/library/ms188066.aspx

Question No.27

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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

image

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 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:

  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 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.

You need to update the SalesHistory table

How should you complete the Transact_SQL statement?

To answer select the appropriate Transact-SQL, segments in the answer area.

image

Correct Answer:

image

Question No.28

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

The Account table was created using the following Transact-SQL statement:

image

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

You frequently run the following Transact-SQL SELECT statements:

image

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

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one.

References: https://msdn.microsoft.com/en-us/library/ms190457.aspx

Question No.29

DRAG DROP

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 the series.

You have a database named Sales that contains the following database tables. Customer, Order, and Products. The Products table and the order table shown in the following diagram.

image

The Customer table includes a column that stores the date 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 begin to modify the table design to adhere to third normal form. Which column should you remove for each table?

To answer drag the appropriate column names to the correct locations. Each column name 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.

image

Correct Answer:

image

Question No.30

DRAG DROP

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:

image

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.

image

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:

image

The following table displays a selected columns and rows from the Customer table. Thr 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.

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.

Users report that the following SELECT statement statement takes a long time to complete:

image

You need to create an index that meets the following requirements:

  1. Improve the performance of the SELECT statement.

  2. requires minimum index key size.

  3. Only contains active customers.

  4. Makes no change to the existing primary key.

  5. Contains all of the columns required by the SELECT statement.

Which three Transact_SQL segments should you use to develop the solution?

To answer, move the appropriate code blocks from the list of code blocks to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Get Full Version of 70-762 Dumps

Categories
70-762 Dumps

70-762 Real Exam Dumps Questions and answers 31-40

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.31

DRAG DROP

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.

image

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.

image

Correct Answer:

image

Question No.32

HOTSPOT

You have a database named Sales.

You need to create a table named Customer that includes the columns described in the following table:

image

How should you complete the Transact SQL statement?

To answer, select the appropriate Transact-SQL segments in the answer area.

image

Correct Answer:

image

Question No.33

DRAG DROP

You have two database tables. Table1 is a partioned table and Table 2 is a nonpartioned table.

Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.

You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.

Which Transact-SQL statement should you run for each table?

To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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.

image

Correct Answer:

image

Question No.34

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.

You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

You need to improve the application response time.

Solution: You update the application to use implicit transactions when connecting to the database.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs-explicit.html

Question No.35

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:

image

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.

image

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:

image

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?

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Correct Answer: D

Explanation:

Two tables is enough.CustomerID must be in both tables.

Question No.36

HOTSPOT

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:

image

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

UspA: This stored procedure updates only the EmployeeStatus column. UspB: This stored procedure updates only the EmployeePayRate column.

The application uses views to control access to data. Views must meet the following requirements:

Allow user access to all columns in the tables that the view accesses. Restrict updates to only the rows that the view returns.

image

Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views.

You need to identify the view attribute to use when creating vwEmployee. In the table below, identify the attributes that you must use.

NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.37

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 collect query performance data while minimizing the performance impact on the SQL Server.

What should you do?

  1. Create a sys.dm_os_waiting_tasks query.

  2. Create a sys.dm_exec_sessions query.

  3. Create a Performance Monitor Data CollectorSet.

  4. Create a sys.dm_os_memory_objects query.

  5. Create a sp_configure `max server memory#39; query.

  6. Create a SQL Profiler trace.

  7. Create a sys.dm_os_wait_stats query.

  8. Create an Extended Event.

Correct Answer: C

Explanation:

SQL Server Data Collector is afeature for performance monitoring and tuning available in SQL Server Management Studio.

Integration Services packages transform and load the collected data into the Microsoft Data Warehouse database.

Collection sets are defined and deployed on a serverinstance and can be run independently of each other. Each collection set can be applied to a target that matches the target types of all the collector types that are part of a collection set. The collection set is run by a SQL Server Agent job or jobs, and data is uploaded to the management data warehouse on a predefined schedule. Predefined data collection sets include:

The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. It does not store all executed statements, only 10 worst performing ones.

Disk Usage data collection set collects information about disk space used byboth data and log files for all databases on the SQL Server instance, growth trends, andaverage day growth.

Etc.

References: http://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

Question No.38

You have a view that includes an aggregate.

You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

You need to ensure that you can update the view. What should you create?

  1. table-valued function

  2. a schema-bound view

  3. a partitioned view

  4. a DML trigger

Correct Answer: D

Explanation:

When you use the SchemaBinding keyword while creating a view or function you bindthe structure of any underlying tables or views. It means that as long as that schemabound object exists as a schemabound object (ie you don#39;t remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.

References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

Question No.39

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:

image

image

image

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

image

Be written by using Transact-SQL statements. What should you create?

  1. extended procedure

  2. CLR procedure

  3. user-defined procedure

  4. DML trigger

  5. scalar-valued function

  6. table-valued function

Correct Answer: D

Explanation:

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

Question No.40

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

The Account table was created using the following Transact-SQL statement:

image

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

You frequently run the following Transact-SQL SELECT statements:

image

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

CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode); Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

We need an index on the productCode column as well.

References: https://msdn.microsoft.com/en-us/library/ms190457.aspx

>

Get Full Version of 70-762 Dumps

Categories
70-762 Dumps

70-762 Real Exam Dumps Questions and answers 1-10

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.1

DRAG DROP

You are analyzing the performance of a database environment.

Applications that access the database are experiencing locks that are held for a large amount of time. You are experiencing isolation phenomena such as dirty, nonrepeatable and phantom reads.

You need to identify the impact of specific transaction isolation levels on the concurrency and consistency of data.

What are the consistency and concurrency implications of each transaction isolation level?

To answer, drag the appropriate isolation levels to the correct locations. Each isolation level 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.

image

Correct Answer:

image

Question No.2

You are developing an application that connects to a database. The application runs the following jobs:

image

The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.

If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.

You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.

Which trace event class in the Locks event category should you use?

  1. LockAcquired

  2. LockCancel

  3. LockDeadlock

  4. LockEscalation

Correct Answer: A

Explanation:

The Lock: Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.

The Lock: Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the typeof locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

Question No.3

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?

  1. A.Create a sys.dm_os_waiting_tasks query.

  2. Create a sys.dm_exec_sessions query.

  3. Create a PerformanceMonitor Data Collector Set.

  4. Create a sys.dm_os_memory_objects query.

  5. Create a sp_configure `max server memory#39; query.

  6. Create a SQL Profiler trace.

  7. Create a sys.dm_os_wait_stats query.

  8. Create an Extended Event.

Correct Answer: F

Explanation:

Toview deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.

Trace Flag 1204 and Trace Flag 1222

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processesand then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

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

Question No.4

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?

  1. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.

  2. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

  3. Enable trace flag 1222 in thestartup options for Microsoft SQL Server.

  4. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.

Correct Answer: C

Explanation:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

The output format for Trace Flag 1222 only returns information in an XML-like format. References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Question No.5

DRAG DROP

You have a trigger named CheckTriggerCreation that runs when a user attempts to create a trigger. The CheckTriggerCreation trigger was created with the ENCRYPTION option and additional proprietary business logic.

You need to prevent users from running the ALTER and DROP statements or the sp_tableoption stored procedure.

Which three Transact-SQL segments should you use to develop the solution?

To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.6

HOTSPOT

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.

image

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.

image

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.

image

Correct Answer:

image

Question No.7

DRAG DROP

You are analyzing the performance of a database environment.

You suspect there are several missing indexes in the current database.

You need to return a prioritized list of the missing indexes on the current database. 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 once or not at all. You may need to drag the split bar between panes or scroll to view content.

image

Correct Answer:

image

Question No.8

DRAG DROP

You are evaluating the performance of a database environment.

You must avoid unnecessary locks and ensure that lost updates do not occur. You need to choose the transaction isolation level for each data scenario.

Which isolation level should you use for each scenario?

To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation 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.

image

Correct Answer:

image

Question No.9

DRAG DROP

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.

image

Correct Answer:

image

Question No.10

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?

  1. Create a clustered indexon the table.

  2. Create a nonclustered index on the table.

  3. Create a nonclustered filtered index on the table.

  4. Create a clustered columnstore index on the table.

  5. Create a nonclustered columnstore index on the table.

  6. Create a hash index on thetable.

Correct Answer: D

Explanation:

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

Get Full Version of 70-762 Dumps

Categories
70-762 Dumps

70-762 Real Exam Dumps Questions and answers 11-20

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.11

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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

image

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 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:

  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 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.

You need to create the Sales Order table. How should you complete the table definition?

To answer select the appropriate Transact- SQL segments in the answer area.

image

Correct Answer:

image

Question No.12

DRAG DROP

You must create two staging database tables. The tables have the following requirements:

image

You need to select the correct storage mechanism for each table. Which storage mechanism should you use?

To answer, drag the appropriate table types to the correct tables. Each table 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.

image

Correct Answer:

image

Question No.13

DRAG DROP

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:

image

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.

image

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:

image

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.

When a procedure calls spDeleteCustAcctRelationship, if the calling stored procedures has already started an active transaction, all the detections made by the spDeleteCustAccRelationship stored procedure must be committed by the caller; otherwise changes must be committed within the spDeleteCustAcctRelationship stored procedure.

If any error occurs during the delete operation, only the deletes made by the soDeleteCustACCTRelationships stored procedure must be rolled back and the status must be updated.

You need to complete the stored procedure to ensure all the requirements are met. How should you complete the procedure?

To answer, drag the Transact-SQL segments to the correct location. 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: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.14

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.

image

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:

If an open order includes a discontinued product, the records for the product must not be deleted. The stored procedure must return a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order.

What should you do?

To answer, select the appropriate Transact-SQL segments in the answer area.

image

Correct Answer:

image

Question No.15

DRAG DROP

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:

image

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.

image

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:

image

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

You need to create Website Customer.

How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations, Each Transact-SQL segment may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.16

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:

image

image

image

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.

image

Support TRY…CATCH error handling.

What should you create?

  1. extended procedure

  2. CLR procedure

  3. user-defined procedure

  4. DML trigger

  5. DDL trigger

  6. scalar-valued function

  7. table-valued function

Correct Answer: B

Explanation:

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements.DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger canalso be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code thatare members of an assembly created in the .NET Framework and uploaded in SQL Server.

References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

Question No.17

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.

image

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 modify the database design to meet the following requirements:

Rows in the Orders table must always have a valid value for the ProductID column.

Rows in the Products table must not be deleted if they are part of any rows in the Orders table. All rows in both tables must be unique.

In the table below, identify the constraint that must be configured for each table.

NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.18

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 stored information about the employees of your company.

image

You need to implement the following auditing rules for the Employees table: Record any changes that are made to the data in the Employees table.

image

Customize the data recorded by the audit operations.

Solution: You implement a user-defined function on the Employees table. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

SQL Server 2016 provides two features that track changes to data in adatabase: change data capture and change tracking. These features enable applications to determine the DML changes

(insert, update, and delete operations) that were made to user tables in a database.

Change data is made available to change data capture consumers through table-valued functions (TVFs).

References: https://msdn.microsoft.com/en-us/library/cc645858.aspx

Question No.19

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:

image

Users report that the following query takes a long time to complete.

image

You need to create an index that:

image

image

improves the query performance does not impact the existing index

image

minimizes storage size of the table (inclusive of index pages). What should you do?

  1. Create aclustered index on the table.

  2. Create a nonclustered index on the table.

  3. Create a nonclustered filtered index on the table.

  4. Create a clustered columnstore index on the table.

  5. Create a nonclustered columnstore index on the table.

  6. Create a hashindex on the table.

Correct Answer: C

Explanation:

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Question No.20

DRAG DROP

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:

image

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.

image

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:

image

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

You need to create Sales.FemaleCustomers. How should you complete the view definition?

To answer, drag the appropriate Transact- SQL segments to the correct locations. Each Transact_SQL segment may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.

image

Correct Answer:

image

Get Full Version of 70-762 Dumps