Lead2pass is the best place for preparing IT Certifications as we are providing latest and guaranteed questions for all certifications. We offer you the ultimate preparation resource of 70-459 exam question. Wondering what could be this effective? It is our training material which serves as a guide to achieving your dream as a certified professional.
QUESTION 51
You need to recommend changes to the ERP application to resolve the search issue.
The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
A. the data type of the ProductName column
B. the collation of the Products table
C. the collation of the ProductName column
D. the index on the ProductName column
Answer: C
QUESTION 52
You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should recommend?
A. repeatable read
B. serializable
C. read uncommitted
D. read committed
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms378149.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
QUESTION 53
You need to recommend a solution for Application 1 that meets the security requirements.
What should you include in the recommendation?
A. Encrypted columns
B. Certificate Authentication
C. Signed stored procedures
D. Secure Socket Layer (SSL)
Answer: C
QUESTION 54
You need to recommend a solution to improve the performance of usp_UpdateInventory.
The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. a table variable
B. a subquery
C. a common table expression
D. a cursor
Answer: C
QUESTION 55
You need to recommend a disk monitoring solution that meets the business requirements.
What should you include in the recommendation?
A. a maintenance plan
B. a SQL Server Agent alert
C. an audit
D. a dynamic management view
Answer: D
QUESTION 56
You need to recommend a solution to allow application users to perform UPDATE operations on the database tables.
The solution must meet the business requirements.
What should you recommend?
A. Create a user-defined database role and add users to the role.
B. Create stored procedures that use EXECUTE AS clauses.
C. Create functions that use EXECUTE AS clauses.
D. Create a Policy-Based Management Policy.
Answer: B
QUESTION 57
You need to recommend a solution for the deployment of SQL Server 2012.
The solution must meet the business requirements.
What should you include in the recommendation?
A. Deploy two servers that have SQL Server 2012 installed.
Implement AlwaysOn Availability Groups on both servers.
B. Upgrade the existing SQL Server 2005 instance to SQL Server 2012.
Deploy a new server that has SQL Server 2012 installed.
Implement AlwaysOn.
C. Install a new instance of SQL Server 2012 on the server that hosts the SQL Server 2005 instance.
Deploy a new server that has SQL Server 2012 installed.
Implement AlwaysOn.
D. Deploy two servers that have SQL Server 2012 installed and implement Failover Clustering.
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/bb677622.aspx
http://msdn.microsoft.com/en-us/library/ff877884.aspx
QUESTION 58
You need to recommend a solution to synchronize Database2 to App1_Db1.
What should you recommend?
A. Change data capture
B. Snapshot replication
C. Transactional replication
D. Master Data Services
Answer: C
QUESTION 59
You need to provide referential integrity between the Offices table and Employees table.
Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: AC
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx
QUESTION 60
You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName.
You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
A. OPTION (ROBUST plan)
B. OPTION (OPTIMIZE FOR UNKNOWN)
C. OPTION (KEEP PLAN)
D. OPTION (KEEPFIXED PLAN)
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
QUESTION 61
You need to create the object used by the parameter of usp_UpdateEmployeeName.
Which code segment should you use?
A. CREATE XML SCHEMA COLLECTION EmployeesInfo
B. CREATE TYPE EmployeesInfo AS Table
C. CREATE TABLE EmployeesInfo
D. CREATE SCHEMA EmployeesInfo
Answer: B
QUESTION 62
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 disk space used.
Which code segment should you use?
A. ALTER TABLE Employees
ADD Confirmed bit DEFAULT 0;
B. ALTER TABLE Employees
ADD Confirmed char(1) DEFAULT "1";
C. ALTER TABLE Employees
ADD Confirmed char(1) DEFAULT ‘0’;
D. ALTER TABLE Employees
ADD Confirmed bit DEFAULT 1;
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms177603.aspx
http://msdn.microsoft.com/en-us/library/ms176089.aspx
QUESTION 63
You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?
A. ADD SIGNATURE TO Production.ProductDetails_Insert BY CERTIFICATE PRODUCTSCERT;
B. OPEN DBCERT;ALTER PROCEDURE Production. ProductDetails_Insert WITH ENCRYPTION;CLOSE D3CERT;
C. ADD SIGNATURE TO Production.ProductDetails_Insert BY CERTIFICATE DBCERT;
D. OPEN PRODUCTSCERT;ALTER PROCEDURE Production. ProductDetails_Insert WITH ENCRYPTION;CLOSE PRODUCTSCERT;
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/bb669102.aspx
QUESTION 64
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?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms191320.aspx
http://msdn.microsoft.com/en-us/library/ms186755.aspx
QUESTION 65
You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx
http://msdn.microsoft.com/en-us/library/ms179610.aspx
http://msdn.microsoft.com/en-us/library/ff878370.aspx
QUESTION 66
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. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
Answer: ABDE
QUESTION 67
An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 68
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?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 69
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 disk space used.
Which code block should you use?
A. ALTER TABLE Attendees
ADD Confirmed bit DEFAULT 0;
B. ALTER TABLE Attendees
ADD Confirmed char(1) DEFAULT ‘0’;
C. ALTER TABLE Attendees
ADD Confirmed char(1) DEFAULT ‘1’;
D. ALTER TABLE Attendees
ADD Confirmed bit DEFAULT 1;
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms177603.aspx
QUESTION 70
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 at line 14 of Tables.sql?
A. FullName AS (FirstName + ‘ ‘ + LastName),
B. FullName nvarchar(100) NOT NULL DEFAULT
(dbo.CreateFuIlName(FirstName, LastName)),
C. FullName AS (FirstName + ‘ ‘ + LastName) PERSISTED,
D. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FullName DEFAULT
(dbo.CreateFullName (FirstName, LastName)),
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms188300.aspx
http://msdn.microsoft.com/en-us/library/ms191250.aspx
Thanks for your reading,if you want to get more 70-459 exam preparation material,you can download the free demos in PDF files on Lead2pass.