Valid 70-762 Dumps shared by PassLeader for Helping Passing 70-762 Exam! PassLeader now offer the newest 70-762 VCE dumps and 70-762 PDF dumps, the PassLeader 70-762 exam questions have been updated and ANSWERS have been corrected, get the newest PassLeader 70-762 dumps with VCE and PDF here: https://www.passleader.com/70-762.html (182 Q&As Dumps)
BTW, DOWNLOAD part of PassLeader 70-762 dumps from Cloud Storage: https://drive.google.com/open?id=0B-ob6L_QjGLpN3RVQ25sVUM5dkU
NEW QUESTION 166
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?
A. a nonclustered index
B. a schema-bound view
C. a stored procedure
D. an INSTEAD OF trigger
Answer: B
Explanation:
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. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql
NEW QUESTION 167
You have multiple queries that take a long time to complete. You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis. Which Transact-SQL statement should you run?
A. SET STATISTICS PROFILE OFF
B. SET SHOWPLAN_TEXT OFF
C. SET SHOWPLAN_ALL ON
D. SET STATISTICS PROFILE ON
Answer: C
Explanation:
SET SHOWPLAN_ALL ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.
Incorrect:
Not D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql
NEW QUESTION 168
You have a database with multiple tables. You must insert data into the tables by using views. Which of the following two statements best describes an updatable view? (Choose two.)
A. The view must not include the primary key of the table.
B. The view may include GROUP BY or HAVING statements.
C. The view may include a composite field.
D. Each view must reference columns from one table.
E. The view must not include subqueries.
Answer: BD
Explanation:
B: The columns being modified must not be affected by GROUP BY, HAVING, or DISTINCT clauses.
D: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
Incorrect:
Not A: Primary keys are allowed.
Not C: The columns cannot be derived in any other way, such as through the computation. The column cannot be computed from an expression that uses other columns.
Not E: The restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017
NEW QUESTION 169
You manage a database that supports an Internet of Things (IoS) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995% uptime. The database uses a table named Checkins that is 100 gigabytes (GB) in size. The Checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use stored procedures for all access to the tables. You observe that the wait type PAGELATCH_IO causes large amounts of blocking. You need to resolve the blocking issues while minimizing downtime for the database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Convert all stored procedures that access the Checkins table to natively compiled procedures.
B. Convert the Checkins table to an In-Memory OLTP table.
C. Convert all tables to clustered columnstore indexes.
D. Convert the Checkins table to a clustered columnstore index.
Answer: AB
Explanation:
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. SQL Server In-Memory OLTP helps improve performance of OLTP applications through efficient, memory- optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In- Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables
NEW QUESTION 170
You have a table that stores transactions partitioned by year. Users occasionally experience performance issues when they access the table. The table is locked exclusively when the records are updated. You need to prevent exclusive locks on the table and maintain data integrity. What should you do?
A. Set the LOCK_EXCALATION property to DISABLE.
B. Set the DATA_COMPRESSION property to ROW at the partition level.
C. Set the LOCK_EXCALATION property to AUTO.
D. Set the LOCK_EXCALATION property to TABLE.
E. Set the DATA_COMPRESSION property to PAGE at the partition level.
Answer: C
Explanation:
The default lock escalation mode is called TABLE, it implements SQL Server’s lock escalation on all types of tables whether partitioned or not partitioned. There are two more lock escalation modes: AUTO and DISABLE. The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE. The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
https://www.mssqltips.com/sqlservertip/4359/altering-lock-escalation-for-sql-server-tables/
NEW QUESTION 171
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?
A. a DML trigger
B. a schema-bound view
C. a stored procedure
D. a DDL trigger
Answer: A
Explanation:
When you create a view, you must obey the following conditions in order to make the view updateable: The columns being modified in the view have to directly reference the underlying table columns. As a consequence, the view’s columns being modified cannot be the result of an aggregate function or a computed column. The only way to make data changes on a non-updateable view is by using INSTEAD OF DML triggers. This way you can use procedural code to overcome the limitation.
https://www.mssqltips.com/sqlservertip/5984/sql-server-trigger-on-view-example/
NEW QUESTION 172
You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?
A. a System Monitor report
B. a sys.dm_tran_database_transaction dynamic management view query
C. an Extended Events session that uses Query Editor
D. a Microsoft SQL Profiler trace
Answer: C
Explanation:
Extended Events: considered as “the best way” by the SQL Server purists. You can configure Extended Events to find Locking Issues in SQL Server.
Incorrect:
Not D: SQL Trace and SQL Server Profiler are deprecated. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.
https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/
NEW QUESTION 173
You have multiple queries that take a long time to complete. You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis. Which Transact-SQL statement should you run?
A. SET STATISTICS IO ON
B. SET SHOWPLAN_TEXT ON
C. SET STATISTICS XML ON
D. SET STATISTICS PROFILE ON
Answer: B
Explanation:
SET SHOWPLAN_TEXT ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
Incorrect:
Not C: SET STATISTICS XML ON causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.
Not D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. The additional result set contains the SHOWPLAN_ALL columns for the query and these additional columns.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-text-transact-sql
NEW QUESTION 174
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_operational_stats dynamic management view.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
NEW QUESTION 175
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_usage_stats dynamic management view.
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/
NEW QUESTION 176
You are creating the following two stored procedures:
– A natively-compiled stored procedure.
– An interpreted stored procedure that accesses both disk-based and memory-optimized tables.
– Both stored procedures run within transactions.
You need to ensure that cross-container transactions are possible. Which setting or option should you use?
A. The BEGIN TRANSACTION AND COMMIT OR ROLLBACK TRANSACTION statements for the connection.
B. The SERIALIZABLE table hint on memory-optimized tables.
C. The CONTAINS MEMORY_OPTIMIZED_DATA option for the file group.
D. The SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database.
Answer: A
Explanation:
Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. Two or more statements can be corralled into the same transaction. In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.
Incorrect:
Not B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017
NEW QUESTION 177
Hotspot
You have tables with the following requirements:
– Some records in the table named OrderLines do not exist in the table named Order.
– The column named OrderNumber must be a unique value in the Order table.
– The OrderNumber column on the OrderLines table allows null values.
– The OrderNumber column on the OrderLines table must be validated with the Order table.
– Must not allow a new record in the OrderLines table that does not match a record in the Order table.
How should you complete the statements? (To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area.)
Answer:
Explanation:
Box 1: PRIMARY KEY.
Box 2: NOCHECK. Need NOCHECK as some records in the table named OrderLines do not exist in the table named Order.
Box 3: FOREIGN KEY. FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.
https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/
NEW QUESTION 178
Drag and Drop
You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer. The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016. You have performance issues with the stored procedure. You need to resolve the performance issues and ensure the table statistics are updated automatically. 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.)
Answer:
Explanation:
Step 1: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL. Sets certain database behaviors to be compatible with the specified version of SQL Server.
Step 2: UPDATE STATISTICS Customers. UPDATE STATISTICS updates query optimization statistics on a table or indexed view.
Step 3: EXEC sp_recompile N’ ‘Manage_customer’. sp_recompile causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql
NEW QUESTION 179
Hotspot
You are designing a data warehouse that will be clustered across four servers. Several of the tables in the data warehouse contain transient data that you can rebuild as needed. The schema for the data changes periodically. You must minimize the work required to make schema changes and deploy those changes across the server farm. Administrators must be able to make the following schema changes:
– Adding columns to tables.
– Removing columns from tables.
– Adding tables.
– Removing tables.
Changes to the transient data are done as singleton operations. You must make the data available on all the servers in the server farm. Data movement between servers must occur in real time or near real time. You must be able to run SELECT statements against the data from any server at any time. You need to configure the environment. Which technologies should you implement? (To answer, select the appropriate technologies in the answer area.)
Answer:
Explanation:
Box 1: Durable in-Memory OLTP. Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with Always On Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.
Box 2: Always On Availability Groups. The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. An availability group fails over at the level of an availability replica.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memory-oltp-databases
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server
NEW QUESTION 180
Drag and Drop
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The ChillerSensorNumber column stores the identifier for the IoT devices. You need to create an indexed view that meets the following requirements:
– Persists the data on disk to reduce the amount of I/O.
– Provides the number of ChillerSensorNumber items.
– Creates only a set of summary rows.
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 bat between panes or scroll to view content.)
Answer:
Explanation:
Box1: WITH SCHEMABINDING. Persists the data on disk to reduce the amount of I/O.
Box 2: COUNT_BIG(ChillerSensorNumber). Provides the number of ChillerSensorNumber items. The function COUNT_BIG returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.
Box 3: GROUP BY ChillerSensorNumber. Creates only a set of summary rows.
https://docs.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql
NEW QUESTION 181
……
Get the newest PassLeader 70-762 VCE dumps here: https://www.passleader.com/70-762.html (182 Q&As Dumps)
And, DOWNLOAD the newest PassLeader 70-762 PDF dumps from Cloud Storage for free: https://drive.google.com/open?id=0B-ob6L_QjGLpN3RVQ25sVUM5dkU