[Apr 2016] 70-464 braindumps

Printable of 70-464 exam cram materials and secret for Microsoft certification for consumer, Real Success Guaranteed with Updated 70-464 pdf dumps vce Materials. 100% PASS Developing Microsoft SQL Server 2012 Databases exam Today!

2016 Apr 70-464 Study Guide Questions:

Q76. You need to resolve the performance issues of the usp_ExportOpenings stored procedure. The solution must minimize the amount of hard disk space used. 

Which statement should you execute on DB1? 

A. EXEC sp_dboption 'DB1', 'auto create statistics', 'TRUE' 

B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE 

(Description, Title, Salary); 

C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description, 

Title, 

Salary) WHERE FilledDate IS NULL; 

D. EXEC sp_recompile 'usp_ExportOpenings' 

Answer: C 


Q77. You have the following query on a disk-based table: 

You discover that the query takes a long time to complete. 

The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.) 

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.) 

You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible. 

What should you do? 

A. Reorganize the index. 

B. Update statistics. 

C. Create an index on LastName. 

D. Rebuild the index. 

Answer: C 


Q78. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. 

The following is the relevant portion of the execution plan for the query: You need to create the missing index. 

Which code segment should you execute? 

A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3 

B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) 

C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4) 

D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4) 

Answer: C 


Q79. Topic 8) 

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase. 

ContentDatabase uses transaction log backups. 

The recovery model of ContentDatabase is set to FULL. 

You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log. 

Which three code segments should you execute? 

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order. 

Answer: 


Q80. 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_SelectSpeakersByName? 

A. A table variable 

B. An OFFSET-FETCH clause 

C. The ROWNUMBER keyword 

D. A recursive common table expression 

Answer: B 

Explanation: 

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/ 


70-464 free practice test

Up to the minute 70-464 book:

Q81. The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values. 

You need to update Fax to meet the following requirements: 

. Prevent null values from being used. 

. Always use an empty string instead of a null value. 

Which statement or statements 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 

Answer: A,B,E 

Explanation: E: First change the NULLs to ' '. 

A: Then set the default to the column to ' '. 

B: Finally add the NOT NULL constraint to the column. 


Q82. You execute the following code. After populating the Employees table with 10,000 rows, you execute the following query: 

You need to reduce the amount of time it takes to execute the query. 

What should you do? 

A. Partition the table and use the JobTitle column for the partition scheme. 

B. Change SUBSTRING(JobTitle,l,l) = 'C to JobTitle LIKE 'c%’ 

C. Change SUBSTRING (JobTitle, 1,1] = 'c' to LEFT(JobTitle ,1) = 'c'. 

D. Replace IX_Employees with a clustered index. 

Answer: B 


Q83. 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 


Q84. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. 

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: 

"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context." 

You verify that there is a server login for Account1 on Server2. 

You need to ensure that Account1 can connect to DB1. 

What should you do? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Update the SID for Account1 on DB1. 

B. Add Account1 to the db_datareader role. 

C. Create a new database user on DB1. 

D. Implement Windows authentication. 

Answer: B 


Q85. Which data type should you use for CustomerID? 

A. varchar(11) 

B. bigint 

C. nvarchar(11) 

D. char(11) 

Answer: D 

Explanation: 

Invoices.xml All customer IDs are 11 digits. The first three digits of a customer ID represent the customer's country. The remaining eight digits are the customer's account number. int: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) (just 10 digits max) bigint: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) http://msdn.microsoft.com/en-us/library/ms176089.aspx http://msdn.microsoft.com/en-us/library/ms187745.aspx 


70-464 test

Approved exam 70-464 dumps:

Q86. You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. 

What should you add to usp_GetOrdersAndItems? 

A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03. 

B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03. 

C. Add (UPDLOCK) to the end of line 06. 

D. Add (READPAST) to the end of line 06. 

Answer: D 

55. You need to implement a solution that addresses the bulk insert requirements. 

What should you add to line 08 in usp_ImportOrderDetails? 

A. LASTROW=0. 

B. BATCHSIZE=0. 

C. BATCHSIZE=1000. 

D. LASTROW = 1000. 

Answer: C 


Q87. You need to modify usp_SelectEmployeesByName to support server-side paging. The solution must minimize the amount of development effort required. 

What should you add to usp_SelectEmployeesByName? 

A. A table variable 

B. The ROWNUMBER keyword 

C. An OFFSET-FETCH clause 

D. A recursive common table expression 

Answer: C 

Explanation: 

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/ 


Q88. 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 SCHEMA EmployeesInfo 

D. CREATE TABLE EmployeesInfo 

Answer: B 

Explanation: 

Example Usage of Table-Valued Parameters (Database Engine) http://msdn.microsoft.com/en-us/library/bb510489.aspx (Benefits of using Table-Valued Parameters) 

/* Create a table type. */ 

CREATE TYPE LocationTableType AS TABLE 

( LocationName VARCHAR(50) 

, CostRate INT ); 

GO 

/* Create a procedure to receive data for the table-valued parameter. */ 

CREATE PROCEDURE dbo. usp_InsertProductionLocation 

@TVP LocationTableType READONLY 

AS 

SET NOCOUNT ON 

INSERT INTO AdventureWorks2012.Production.Location 

(Name 

,CostRate 

,Availability 

,ModifiedDate) 

SELECT *, 0, GETDATE() 

FROM @TVP; 

GO 

Also: 

http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename* 

AS 

TABLE) 

http://msdn.microsoft.com/en-us/library/ms175010.aspx(table data types) 

Wrong Answers: 

http://msdn.microsoft.com/en-us/library/ms174979.aspx(CREATE TABLE) 

http://msdn.microsoft.com/en-us/library/ms189462.aspx(CREATE SCHEMA) 

http://msdn.microsoft.com/en-us/library/ms176009.aspx(CREATE XML SCHEMA 

COLLECTION) 


Q89. You have a SQL Azure database. 

You execute the following script: 

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2. 

You plan to deploy an application that will search Column2. 

You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements. 

Which code segment should you execute? 

A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0 

B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2) 

C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL 

D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL 

Answer: D 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/cc280372.aspx 


Q90. You execute the following code: You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B 



see more 70-464 - Developing Microsoft SQL Server 2012 Databases

Microsoft 70-464 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-464-dumps/

P.S. New 70-464 dumps PDF: http://www.4easydumps.com/70-464-dumps-download.html