70-462 | Precise 70-462 Dumps Questions 2021

NEW QUESTION 1
You administer a Microsoft SQL Server database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. What should you create?

• A. An Alert
• B. A Resource Pool
• C. An Extended Event session
• D. A Server Audit Specification
• E. A SQL Profiler Trace
• F. ADatabase Audit Specification
• G. A Policy
• H. AData Collector Set

Answer: F

NEW QUESTION 2
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

• A. Use a FORCESCAN hint in the query.
• B. Add a clustered index on SalesOrderId in SalesOrderHeader.
• C. Use a FORCESEEK hint in the query.
• D. Update statistics on SalesOrderId on both tables.

Answer: D

NEW QUESTION 3
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserB? Select all that apply.

• A. db_accessadmin
• B. db_ddladmin
• C. db_datawriter
• D. db_datareader
• E. db_owner

Answer: CD

Explanation: User A = db_ddladmin
User B= db_datawriter, db_datareader User C = db_accessadmin

NEW QUESTION 4
You are migrating an OLTP database from Microsoft Azure SQL Database to on-premise. You are planning the installation of a Microsoft SQL Server server failover cluster.
The server has the following partitions:

You install the operating system on the C: drive.
The database solution will use row versioning, triggers, and cursors. The tempdb database cannot be placed on the same disk subsystem as the OLTP database.
The OLTP database needs to be on the fastest disk subsystem possible. The database is currently 200GB in size. It will not substantially increase in size in the next 3 years.
You need to plan the usage of the disk subsystems.
To which hard disk should each database belong? (To answer, drag the appropriate database or databases to their corresponding hard disk or disks in the answer areAnswer: choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

Answer:

Explanation:

NEW QUESTION 5
You are configuring a Microsoft SQL Server server and setting up a new database. You need to ensure that the following requirements are met:
✑ Provide access to the database for a Windows group named CORPEmployees.
The database can be moved to another server with no additional changes to the security configuration of the database or server.
Which three actions should you perform in sequence? (To answer, move the appropriate action or actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation: References:
https://msdn.microsoft.com/en-us/library/ff929139%28v=sql.110%29.aspx
http://www.sqlcoffee.com/SQLServer2012_0009.htm

NEW QUESTION 6
You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes.
What should you do?

• A. Add a HASH hint to the query.
• B. Add a LOOP hint to the query.
• C. Add a FORCESEEK hint to the query.
• D. Add an INCLUDE clause to the index.
• E. Add a FORCESCAN hint to the Attach query.
• F. Add a columnstore index to cover the query.
• G. Enable the optimize for ad hoc workloads option.
• H. Cover the unique clustered index with a columnstore index.
• I. Include a SET FORCEPLAN ON statement before you run the query.
• J. Include a SET STATISTICS PROFILE ON statement before you run the query.
• K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Answer: G

NEW QUESTION 7
You administer a Microsoft SQL Server database.
You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Answer:

Explanation: According to these references, this answer looks correct.
References:
http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms189280.aspx

NEW QUESTION 8
You administer a Microsoft SQL Server database.
You want to import data from a text file to the database. You need to ensure that the following requirements are met:
✑ Data import is performed by using a stored procedure.
✑ Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model 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.)

Answer:

Explanation:

NEW QUESTION 9
Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.
You also need to ensure that data loss is minimal. What should you do?

• A. Perform a partial restore.
• B. Restore the latest full backup, and restore the latest differential backu
• C. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• D. Restore the latest full backup.
• E. Restore the latest full backup, and restore the latest differential backu
• F. Then, restore the latest log backup.
• G. Perform a page restore.
• H. Restore the latest full backu
• I. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• J. Perform a point-in-time restore.
• K. Restore the latest full backu
• L. Then, restore the latest differential backup.

Answer: H

Explanation: Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored.
Page restore
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. Piecemeal restore
Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored.

NEW QUESTION 10
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server 2012 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: F

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx http://msdn.microsoft.com/en-us/library/ms187728.aspx

NEW QUESTION 11
You install a Microsoft SQL Server instance.
The instance will store data extracted from two databases running on Microsoft Azure SQL Database.
You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the data. You need to ensure that the data steward is given the correct client tools to perform these tasks.
Which set of tools should you install?

• A. SQL Server DATA Tools and Distributed Replay Client
• B. Data Quality Client and SQL Server DATA Tools
• C. Master Data Services and SQL Server Data Tools
• D. Data Quality Client and Distributed Replay Client

Answer: B

Explanation: Data Quality Client is a standalone application that enables you to perform knowledge management, data quality projects, and administration in one user interface. The application is designed for both data stewards
and DQS administrators. It is a stand-alone executable file that performs knowledge discovery, domain management, matching policy creation, data cleansing, matching, profiling, monitoring, and server administration.
SQL Server Data Tools is a modern development tool that you can download for free to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.
Incorrect Answres:
A, D: The SQL Server Distributed Replay feature helps you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning.
C: Master Data Services enables you to manage a master set of your organization's data. You can organize the data into models, create rules for updating the data, and control who updates the data.
References:
https://technet.microsoft.com/en-us/library/ff877917(v=sql.110).aspx https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

NEW QUESTION 12
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. AData Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?

• A. Create an Integration Services package and use a Data Profiling transform.
• B. Create a custom .NET application based on the Knowledgebase class.
• C. Create a data quality project.
• D. Create a CLR stored procedure based on the Knowledgebase class.
• E. Create a Master Data Services (MDS) business rule.

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/hh213052.aspx http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/enus/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspx http://msdn.microsoft.com/en-us/library/bb895263.aspx

NEW QUESTION 13
You administer a Microsoft SQL Server database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?

• A. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.
• B. Use System Monitor to catch the Lock Waits/sec event
• C. Use sp_configure to set the blocked process threshol
• D. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
• E. Start a Trace using SQL Server Profiler to catch the Lock: Deadlock event

Answer: C

Explanation: The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim.
Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved. You can use this information to determine if deadlocks are significantly affecting the performance of your application.
You can then examine the application code to determine if you can make changes to minimize deadlocks. References: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-deadlock-event-class

NEW QUESTION 14
You are a database administrator for a Microsoft SQL Server environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?

• A. snapshot replication
• B. peer-to-peer replication
• C. database audits
• D. failover clustering

Answer: B

Explanation: Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
Although peer-to-peer replication enables scaling out of read operations, write performance for the topology is like that for a single node.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-rep

NEW QUESTION 15
You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed.
Which of the following tools can you use to accomplish this goal?

• A. SQL Server Management Studio
• B. SQL Server Configuration Manager
• C. Add/Remove Programs in Control Panel
• D. SQL Server Installation Center

Answer: D

NEW QUESTION 16
Which of the following editions of SQL Server 2012 can you run on a computer that is running the Windows 7 Professional (x64) operating system? (Choose all that apply.)

• A. SQL Server 2012 (x64) Developer edition
• B. SQL Server 2012 (x64) Web edition
• C. SQL Server 2012 (x64) Enterprise edition
• D. SQL Server 2012 (x64) Standard edition

Answer: AD

NEW QUESTION 17
You have a large partitioned fact table in a data warehouse. The table is stored as a clustered index.
You need to modify the indexing solution to minimize the amount of disk space required to store the table. What should you do?

• A. Enable row compression for the clustered index.
• B. Enable page compression for the clustered index.
• C. Implement a nonclustered columnstore index.
• D. Implement a clustered columnstore index.

Answer: D

Explanation: Use a clustered columnstore index for large data warehouse tables.
The clustered columnstore index is more than an index, it is the primary table storage. It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. Clustered columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance

NEW QUESTION 18
You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?

• A. GRANT DELETE ON Purchase
• B. Suppliers TC ContosoUser
• C. CREATE PROCEDURE Purchases.PurgeInactiveSuppliersWITH EXECUTE AS USER = 'dbo'ASDELETEFROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• D. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• E. CREATE PROCEDURE Purchase
• F. PurgeInactiveSuppliersASDELETEFROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchase
• G. PurgeInactiveSuppliers TO ContosoUser

Answer: D

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx