Microsoft 70-462 Free Practice Questions

Q1. You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. 

You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. 

You need to reduce fragmentation. You need to achieve this goal without taking the index offline. 

Which Transact-SQL batch should you use? 

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING 

B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE 

C. ALTER INDEX ALL ON OrderDetail REBUILD 

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD 

View Answer

Q2. You administer a Microsoft SQL Server 2012 server. 

When transaction logs grow, SQL Server must send an email message to the database administrators. 

You need to configure SQL Server to send the email messages. 

What should you configure? 

A. SQL Mail 

B. An Extended Events session 

C. Alerts and operators in SQL Server Agent 

D. Policies under Policy-Based Management 

View Answer

Q3. You administer a Microsoft SQL Server 2012 database. 

You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges. 

You need to ensure that the job step can run using a different user account. 

What should you use? 

A. a schedule 

B. an alert 

C. an operator 

D. a proxy 

View Answer

Q4. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

A data file of 2 terabytes is located on a dedicated LUN (drive D). 

A transaction log of 10 GB is located on a dedicated LUN (drive E). 

Drive D has 1 terabyte of free disk space. 

Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. 

You need to ensure that the backup size is as small as possible. 

Which backup should you perform every two hours? 

A. BULK_LOGGED 

B. NO_CHECKSUM 

C. FULL 

D. RESTART 

E. CHECKSUM 

F. STANDBY 

G. DBO.ONLY 

H. NORECOVERY 

I. SIMPLE 

J. SKIP 

K. Transaction log 

L. COPY_ONLY 

M. Differential 

N. CONTINUE_AFTER_ERROR 

View Answer

Q5. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster. 

An additional node named Node C has been added to the existing cluster. 

You need to ensure that the SQL Server instance can use all nodes of the cluster. 

What should you do? 

A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A. 

B. Use Node A to install SQL Server on Node C. 

C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

D. Use Cluster Administrator to add a new Resource Group to Node B. 

View Answer

Q6. You administer a Microsoft SQL Server 2012 instance. 

The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. 

You plan to define a backup strategy for the database. You need to ensure that the following requirements are met: 

. No more than 5 minutes worth of transactions are lost. 

. Data can be recovered by using the minimum amount of administrative effort. 

What should you do? Choose all that apply. A. Configure the database to use the SIMPLE recovery model. 

B. Create a DIFFERENTIAL database backup every 4 hours. 

C. Create a LOG backup every 5 minutes. 

D. Configure the database to use the FULL recovery model. 

E. Create a FULL database backup every 24 hours. 

F. Create a DIFFERENTIAL database backup every 24 hours. 

View Answer

Q7. 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 and 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 the Regions table. 

Which Transact-SQL statement should you use? 

A. REVOKE SELECT ON Schema::Customers FROM UserA 

B. REVOKE SELECT ON Object::Regions FROM UserA 

C. EXEC sp_addrolemember 'Sales', 'UserA' 

D. DENY SELECT ON Schema::Customers FROM Sales 

E. EXEC sp_droprolemember 'Sales', 'UserA' 

F. REVOKE SELECT ON Schema::Customers FROM Sales 

G. DENY SELECT ON Object::Regions FROM UserA 

H. REVOKE SELECT ON Object::Regions FROM Sales 

I. DENY SELECT ON Schema::Customers FROM UserA 

J. DENY SELECT ON Object::Regions FROM Sales 

View Answer

Q8. You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. 

Currently, Server01l/HA is the primary replicA. 

You need to ensure that the following requirements are met: 

Backup operations occur on Server02/HA. 

If Server02/HA is unavailable, backup operations occur on Server03/HA. 

Backup operations do not occur on Server01/HA. 

How should you configure HaContoso? 

A. . Set the backup preference of HaContoso to Prefer Secondary. 

. Set the backup priority of Server02/HA to 20. 

. Set the backup priority of Server03/HA to 10. 

B. . Set the backup preference of HaContoso to Secondary only. 

. Set the backup priority of Server02/HA to 20. 

. Set the backup priority of Server03/HA to 10. 

C. . Set the backup preference of HaContoso to Secondary only. 

. Set the backup priority of Server02/HA to 10. 

. Set the backup priority of Server03/HA to 20. 

D. . Set the exclude replica of Server01/HA to true. 

. Set the backup priority of Server02/HA to 10. 

. Set the backup priority of Server03/HA to 20. 

View Answer

Q9. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. 

You need to increase the values in the Price column of all products in the Products table by 5 percent. 

You also need to log the changes to the ProductsPriceLog table. 

Which Transact-SQL query should you use? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

View Answer

Q10. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, 

RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

B. SELECT Id, Name, Marks, 

DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank 

FROM StudentMarks 

C. SELECT StudentCode as Code, 

DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

D. SELECT StudentCode as Code, 

NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

E. SELECT StudentCode AS Code,Marks AS Value FROM ( 

SELECT StudentCode, Marks AS Marks, 

RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank 

FROM StudentMarks) tmp 

WHERE Rank = 1 

F. SELECT StudentCode AS Code,Marks AS Value FROM ( 

SELECT StudentCode, Marks AS Marks, 

RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank 

FROM StudentMarks) tmp 

WHERE Rank = 1 

G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

View Answer

Q11. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

A data file of 2 terabytes is located on a dedicated LUN (drive D). 

A transaction log of 10 GB is located on a dedicated LUN (drive E). 

Drive D has 1 terabyte of free disk space. 

Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. 

You need to ensure that your backup will continue if any invalid checksum is encountered. 

Which backup option should you use? 

A. STANDBY 

B. Differential 

C. FULL 

D. CHECKSUM 

E. BULK_LOGGED 

F. CONTINUE_AFTER_ERROR 

G. SIMPLE 

H. DBO_ONLY 

I. COPY_ONLY 

J. SKIP 

K. RESTART 

L. Transaction log 

M. NO_CHECKSUM 

N. NORECOVERY 

View Answer

Q12. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster. 

An additional node named Node C has been added to the existing cluster. 

You need to ensure that the SQL Server instance can use all nodes of the cluster. 

What should you do? 

A. Run the New SQL Server stand-alone installation Wizard on Node C. 

B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

C. Use Node B to install SQL Server on Node C. 

D. Use Node A to install SQL Server on Node C. 

View Answer

Q13. You administer a Microsoft SQL Server 2012 database named Contoso on a server named 

Server01. 

You need to prevent users from disabling server audits in Server01. 

What should you create? 

A. an Alert 

B. a Resource Pool 

C. an Extended Event session 

D. a Policy 

E. a Database Audit Specification 

F. a SQL Profiler Trace 

G. a Server Audit Specification 

View Answer

Q14. You administer a Microsoft SQL Server 2012 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). 

You discover I/O issues. The SAN is at capacity and additional disks cannot be added. 

You need to reduce the I/O workload on the SAN at a minimal cost. 

What should you do? 

A. Move user databases to a local disk. 

B. Expand the tempdb data and log files. 

C. Modify application code to use table variables. 

D. Move the tempdb files to a local disk. 

View Answer

Q15. You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

View Answer