70-464 Royal Pack Testengine pdf
100% Actual & Verified — 100% PASS
Unlimited access to the world's largest Dumps library! Try it Free Today!https://www.exambible.com/70-464-exam/
Exam Number/Code: 70-464
Exam name: Developing Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing
Simulation of 70-464 test engine materials and practice for Microsoft certification for candidates, 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:
Q31. You use SQL Server 2012 to maintain the data used by the applications at your company.
You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.)
You need to ensure that Table1 contains a column named UserName. The UserName column will:
Store string values in any language.
Accept a maximum of 200 characters.
Be case-insensitive and accent-insensitive.
Which code segment should you add at line 03?
A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,
B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,
D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
161. You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
. Use only tables that are not persistent in the database.
. Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
A. A system table
B. A partitioned table
C. A table variable
D. A temporary table
Q32. You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
A. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY
B. BACKUP LOG DB1 TO Device1 WITH NORECOVERY
C. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY
D. BACKUP LOG DB1 TO Device1
http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx http://msdn.microsoft.com/en-us/library/ms190925.aspx
Q33. You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?
A. Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge.
B. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
C. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases.
D. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases.
Explanation: From scenario:
You need to anticipate when POSTransaction table will need index maintenance.
The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.
Q34. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?
A. The Distributed Transaction Coordinator (DTC)
B. Transactional replication
C. Change data capture
D. Change tracking
Q35. While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
Updated 70-464 exam cram:
Q36. You need to create the object used by the parameter of usp_InsertSessions. Which statement should you use?
A. CREATE XML SCHEMA COLLECTION SessionDataTable
B. CREATE TYPE SessionDataTable AS Table
C. CREATE SCHEMA SessionDataTable
D. CREATE TABLE SessionDataTable
Q37. Topic 8)
You create a disk-based table that contains the following script:
You need to prevent duplicate values in the SKU field.
Which five code segments should you use?
To answer, move the appropriate code segments from the list of code segments to the
answer area and arrange them in the correct order.
Q38. Topic 8)
You have a table named Table1. Table1 has 1 million rows.
Table1 has a columnstore index for a column named Column1.
You need to import data to Table1. The solution must minimize the amount of time it takes
to import the data.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.
Q39. Topic 8)
You plan to deploy two stored procedures named SP1 and SP2 that read data from the database.
Your company identifies the following requirements for each stored procedure:
. SP1 must allow dirty reads.
. SP2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
Q40. You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a
slow local disk.
You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:
The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Option A
B. Option B
C. Option C
D. Option D
Explanation: * You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of key values.
Pinpoint 70-464 testing material:
Q41. 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
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/
Q42. Developers report that usp_UpdateSessionRoom periodically returns error 3960.
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?
A. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
C. Move the SELECT statement at line 49 to line 57.
D. Move the SET statement at line 46 to line 53.
Topic 3, Scenario 3
You have two servers named SQL1 and SQL2. SQL1 has SQL Server 2012 Enterprise installed. SQL2 has SQL Server 2008 Standard installed.
You have an application that is used to manage employees and office space. 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. 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. A database named DB2 resides on SQL2. DB2 has a table named EmployeeAudit that will audit changes to a table named Employees.
A stored procedure named usp_UpdateEmployeeName will be executed only by other stored procedures. The stored procedures executing usp_UpdateEmp!oyeeName will always handle transactions.
A stored procedure named usp_SelectEmployeesByName will be used to retrieve the names of employees. Usp_SelectEmployeesByName can read uncommitted data.
A stored procedure named usp_GetFutureOfficeAssignments will be used to retrieve office assignments that will occur in the future.
Q43. Topic 8)
You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2 that will have the following specifications:
. Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. . Table2 will contain a column named Column2 that is the product of two other columns in Table2. . Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.
Q44. You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Q45. You plan to create a new table that has the following requirements:
Uses a GUID data type as the primary key. Uses a clustered index as the primary key. Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.
see more 70-464 - Developing Microsoft SQL Server 2012 Databases