Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load.
You need to limit the number of cores that handle I/O.
What should you configure?
A. Processor affinity
B. Lightweight pooling
C. Max worker threads
D. I/O affinity
Answer: D
Q2. 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
Answer: D
Q3. You administer a Microsoft SQL Server 2012 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. Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.
B. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
C. 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.
D. Use System Monitor to catch the Lock Waits/sec event.
Answer: B
Q4. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
. Future modifications to the table definition will not affect the applications' ability to access data.
. The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?
A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables
Answer: C
Q5. 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.
Answer: B
Q6. You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:
The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time.
What should you do on Saturday?
A. Perform a differential backup at 08:59 hours.
B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
C. Create a database snapshot at 08:59 hours.
D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
Answer: C
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. 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
Q8. You administer a Microsoft SQL Server 2012 database.
The database contains a customer table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the customer table.
What should you do?
A. Implement row-level compression.
B. Implement page-level compression.
C. Convert all indexes to Column Store indexes.
D. Implement Unicode compression.
Answer: B
Q9. You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected.
The secondary server should allow reporting queries to be performed.
If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
PRIMARY_ROLE (
ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = NONE)
B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
Answer: D
Q10. You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'
BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:\TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', ENCRYPTION BY PASSWORD =
'MyPassword1!');
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
A. ALTER DATABASE Master SET ENCRYPTION OFF;
B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD =
'MyPassword1!');
C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'
USE Orders;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer'
Answer: B
Q11. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time.
You also need to ensure that data on the primary server is unaffected.
Which configuration should you use?
A. . SQL Server that includes an application database configured to perform transactional replication
B. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
C. . Two servers configured in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
D. . SQL Server that includes an application database configured to perform snapshot replication
E. . Two servers configured in the same data center
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
. One server configured as an Active Secondary
F. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. . Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
H. . Two servers configured in the same data center
. A primary server configured to perform log-shipping every 10 minutes
. A backup server configured as a warm standby
Answer: A
Q12. You administer a Microsoft SQL Server 2012 database named Orders. Orders contains a table named OrderShip that is defined as follows:
Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?
A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL
C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL
Answer: B
Q13. You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.
Answer: A
Q14. You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2012 Online
Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?
A. You should include two servers configured in an Active-Active SQL Server 2012 Cluster
B. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to a secondary database.
C. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group
Answer: B
Q15. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.
You need to improve the performance of the view by persisting data to disk.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Create an AFTER trigger on the view.
C. Modify the view to use the WITH VIEW_METADATA clause.
D. Create a clustered index on the view.
Answer: D