70-459 Exam Questions

You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements: - Have a default value of TRUE. - Minimize the amount of dis...

You need to modify Production.ProductDetails_Insert to comply with the application requirements. Which code segment should you execute?

You need to create a function that will use a SELECT statement in ProductsByProductType.sql. Which code segment should you use to complete the function?

You are planning the ManufacturingSteps table. You need to define the ProductID column in the CREATE TABLE statement. Which code segment should you use?

You need to prepare the database to use the .NET Framework ProcessProducts component. Which code segments should you execute? (Each correct answer presents part of the solution. Ch...

An administrator provides a digital certificate named ServerCert. You need to implement Transparent Data Encryption (TDE) on ProductsDB. Which code segment should you use?

You need to provide referential integrity between the Sessions table and Speakers table. Which code segment should you add at line 47 of Tables.sql?

You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements: - Have a default value of false. - Minimize the amount of di...

You are evaluating the table design. You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute. What should you add a...

You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required. What should you add to usp_SelectSp...

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID. Which line of code should you modify in CountryFromID.sq...

Which data type should you use for CustomerlD?

You have a SQL Server 2012 database named database1. Database1 has a data file named databasel_data.mdf and a transaction log file named databaseljog.ldf. Databasel_data.mdf is 1.5...

You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute?

You need to convert the functionality of Legacy.sql to use a stored procedure. Which code segment should the stored procedure contain?

You need to prepare the database to use the .NET Framework tax component. Which code segments should you execute? (Each correct answer presents part of the solution. Chooseall that...

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy. What should you do?

You need to implement security for the restore and audit process. What should you do?

You need to modify the stored procedure usp.LookupConcurrentUsers. What should you do?

You are designing your maintenance plan. Which command should you use only during the monthlymaintenance window?

You need to modify the usp.DetectSuspiciousActivity stored procedure. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You need to implement a backup strategy to support the requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table. Which three actions should you perform? (Each correct answer...

You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort. Which code should you use?

You need to configure security on the Product table for customer support representatives. Which two actions should you perform? (Each correct answer presents part of the solution....

You need to change the ProdDB database. Which two database options should you change to meet the requirements? (Each correct answer presents part of the solution. Choose two.)

You administer an instance of SQL Server 2014. You are tasked with tuning a common set of queries. You have the results of several test executions, along with query plans. The sche...

You have a SQL Server 2014 database named Database1. You execute the following code: You insert 3 million rows into Sales. You need to reduce the amount of time it takes to execute...

You use SQL Server 2012 to maintain the data used by the applications at your company. You plan to create a disk-based table named Tablel by using the following statement. (Line nu...

You are designing a database named DB1. You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements: - The strategy must not di...

You have a SQL Server 2012 database named Database1. You execute the following code: You insert 3 million rows into Sales. You need to reduce the amount of time it takes to execute...

You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows. You need to recommend a repeatable method to migrate the database to SQL Database. Which met...

You are the new database administrator for aSQL Server 2014 instance. You conduct an assessment on the instance and determine that the auto create statistics setting on the databas...

You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock oc...

You are creating a database that will store usernames and credit card numbers for an application. You need to recommend a solution to store and resuse the credit card numbers in th...

You have a table named ORDERS that contains 10,514,003 Orders. The ORDERS table has an IDENTITY (1,1) column named ORDERID. ORDERID is the UNIQUE CLUSTERED INDEX and PRIMARY KEY fo...

You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing...

You administer a SQL Server 2014 instance. Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing i...

You execute the following code: You create a nonclustered index named IXCustomerName on the CustomerName column. You execute the following query: You need to reduce the amount of t...

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the data...

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sal...

You create a stored procedure that retrieves all of the rows from a table named Table1. You need to recommend a solution to ensure that all of the statements in the stored procedur...

You have a SQL Server instance on a server named Server1. You need to recommend a solution to perform the following tasks every week: - Rebuild the indexes by using a new fill fact...

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs...

You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. You have multiple queries that read data and pr...

You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring. You need to be able to create...

You administer a Microsoft SQL Server 2012 instance. You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

You deploy a SQL Server instance named SQLProd that uses SQL Server 2014. You need to recommend a solution to monitor the transactions that are running currently against SQLProd. T...

You have two databases named DB1 and DB2 that are located on the same server. You plan to create a stored procedure named SProc1 in DB1. SProc1 will query a table named Table2 in D...

You have two SQL Server 2014 instances named SQLDev and SQLProd. You plan to create a new database on SQLProd that will use SQL Server Authentication. You need to ensure that when...