70-464 Exam
Tips to Pass 70-464 Exam (81 to 90)
It is impossible to pass Microsoft 70-464 exam without any help in the short term. Come to Examcollection soon and find the most advanced, correct and guaranteed Microsoft 70-464 practice questions. You will get a surprising result by our Renovate Developing Microsoft SQL Server 2012 Databases practice guides.
Q81. Topic 8)
You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.
Salary must meet the following requirements:
. Contain numeric data.
. Contain only encrypted data that remains encrypted in memory. You need to identify which encryption type and data type must be used for Salary.
Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.
Answer:
181. You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on
Table1.
What should you create?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function
Answer: C
Q82. You need to convert the functionality of Legacy.sql to use a stored procedure. Which code segment should the stored procedure contain?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms187926.aspx http://msdn.microsoft.com/en-us/library/ms190782.aspx http://msdn.microsoft.com/en-us/library/bb669091.aspx http://msdn.microsoft.com/en-us/library/windows/desktop/ms709342.aspx http://msdn.microsoft.com/en-us/library/ms188001.aspx
Q83. You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: "Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10
Cannot insert the value NULL into column 'InvoiceDate', table 'DB1.Accounting.Invoices' column does not allow nulls. INSERT fails."
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?
A. InvoiceDate varchar(l00) 'InvoiceDate',
B. InvoiceDate varchar(100) 'Customer/InvoiceDate', '
C. InvoiceDate date '@InvoiceDate',
D. InvoiceDate date 'Customer/@InvoiceDate',
Answer: C
Topic 2, Scenario 2
Application Information
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed.
You have an application that is used to schedule and manage conferences.
Users report that the application has many errors and is very slow.
You are updating the application to resolve the issues.
You plan to create a new database on SQL1 to support the application. A junior database administrator has created all the scripts that will be used to create the database. The script that you plan to use to create the tables for the new database is shown in Tables.sql. The script that you plan to use to create the stored procedures for the new database is shown in StoredProcedures.sql. The script that you plan to use to create the indexes for the new database is shown in Indexes.sql. (Line numbers are included for reference only.)
A database named DB2 resides on SQL2. DB2 has a table named SpeakerAudit that will audit changes to a table named Speakers.
A stored procedure named usp_UpdateSpeakersName will be executed only by other stored procedures. The stored procedures executing usp_UpdateSpeakersName will always handle transactions.
A stored procedure named usp_SelectSpeakersByName will be used to retrieve the names of speakers. Usp_SelectSpeakersByName can read uncommitted data.
A stored procedure named usp_GetFutureSessions will be used to retrieve sessions that will occur in the future.
Procedures.sql
Indexes.sql Tables.sql
11. You are evaluating the index design.
You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute. The solution must minimize the amount of database fragmentation.
Which line of code should you use to replace line 12 of Indexes.sql?
A. (LastName);
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);
Answer: C
Q84. You have database objects that were created by using the following script:
The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.
The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit
button.)
You need to ensure that usp_GetCustomersByDate completes as quickly as possible. What should you do?
A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint.
B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement.
C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement.
D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint.
Answer: A
Q85. You need to implement a solution that meets the job application requirements. What should you do?
A. Create a one-to-one relationship between the Openings table and the Applications table.
B. Create a one-to-one relationship between the Candidates table and the Applications table.
C. Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column.
D. Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateID column.
Answer: D
Q86. 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(l) DEFAULT '1'
C. ALTER TABLE Attendees
ADD Confirmed bit DEFAULT 1;
D. ALTER TABLE Attendees
ADD Confirmed char(l) DEFAULT ‘1’;
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms177603.aspx
Q87. Topic 8)
You have two existing tables, one named COUNTRY and the other named STATES. The tables are defined as follows:
You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Answer:
Q88. You are testing disaster recovery procedures.
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1 Cannot find server certificate with thumbprint ,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\ Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
6. You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q89. 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: B
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
Q90. You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter
named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
A. Define an XML column in Table1 by using an XML schema collection.
B. Create an XML schema collection in the database from the text file.
C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
D. use the modify method to insert the XML schema into each row of the XML column in Table1.
Answer: A,B,D
Explanation: Explanation/Reference: http://msdn.microsoft.com/en-us/library/bb510420.aspx http://msdn.microsoft.com/en-us/library/ms187856.aspx http://msdn.microsoft.com/en-us/library/ms176009.aspx http://msdn.microsoft.com/en-us/library/hh403385.aspx http://msdn.microsoft.com/en-us/library/ms184277.aspx