Real Microsoft Data Platform 70-464 Exam questions and Answers, 74 Microsoft 70-464 Exam Dumps |100% free
Cabinetdetherapies offers the latest updates to Microsoft Data Platform 70-464 Exam questions and Answers and Microsoft 70-464 pdf
online Download, We share 74 valid 70-464 exam dumps for free to improve your exam pass rate, and if you want to get the
full 070-464 exam content Please select: https://www.pass4itsure.com/70-464.html(Q&As:182).
[PDF] Free Microsoft Data Platform 70-464 dumps download from Google Drive:
https://drive.google.com/open?id=16YEbhDoWLhg99OfDUYtLrBhDai4Qdfi-
[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO
Exam 70-464: Developing Microsoft SQL Server Databases:
https://www.microsoft.com/en-us/learning/exam-70-464.aspx
Pass4itsure offers the latest Microsoft Data Platform 70-464 practice test free of charge (74Q&As)
QUESTION 1
You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?
A. Repeatable read
B. Read committed
C. Serializable
D. Snapshot
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 2
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)
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 3
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,
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 4
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?
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 5
You are creating a table to support an application that will cache data outside of SQL Server.
The application will detect whether cached values were changed before it updates the values.
You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?
Correct Answer: C
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/en-us/library/ms187942.aspx
http://msdn.microsoft.com/en-us/library/ms190348.aspx
QUESTION 6
Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2.
You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2.
A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?
A. Change sp1 to run as the saUser.
B. Grant User1 the EXECUTE permission on sp1.
C. Add User1 to the db_datawriter role.
D. Grant User1 the INSERT permission on Table2.
Correct Answer: B
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms191291.aspx
QUESTION 7
You execute the following code:
You create a nonclustered index named IX_CustomerName on the CustomerName column.
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 CustomerName column for the partition scheme.
B. Replace IX_CustomerName with a clustered index.
C. Replace LEFT(CustomerName ,1) = ‘a’ with CustomerName LIKE ‘a%’.
D. Replace LEFT(CustomerName ,1) = ‘a’ with SUBSTRING(CustomerName ,1,1) – ‘a’.
Correct Answer: C
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms179859.aspx
http://msdn.microsoft.com/en-us/library/ms187748.aspx
QUESTION 8
You have a table named Rooms that contains three columns.
You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Include the RoomName column and the Position column in the Room_IX index.
B. Create a nonclustered index for RoomName, Id, and Position.
C. Create a clustered index for Id.
D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint.
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 9
You have a database named database1.
Database developers report that there are many deadlocks.
You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements:
Support real-time monitoring.
Be enabled and disabled easily.
Support querying of the monitored data.
What should you implement? More than one answer choice may achieve the goal. Select the BEST answer.
A. Log errors by using trace flag 1222
B. Log errors by using trace flag 1204
C. A SQL Server Profiler template
D. An Extended Events session
Correct Answer: D
Explanation
Explanation/Reference:
http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/08/12/monitor-deadlock-in-sql- 2012/
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlo cks_2d00_in_2d00_sql_2d00_server.aspx
QUESTION 10
You execute the following code:
The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)
You need to prevent the key lookup.
What should you modify? More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: CExplanation
Explanation/Reference:
QUESTION 11
You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.
The application generates all T-SQL statements dynamically and does not use stored procedures. You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
A. Optimize for Ad hoc Workloads
B. Enable Contained Databases
C. Allow Triggers to Fire Others
D. Scan for Startup Procs
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 12
You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Define the view by using the SCHEMABINDING option.
C. Define the view by using the CHECK option.
D. Materialize the view.
Correct Answer: C
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms180800.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx
QUESTION 13
You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.
Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.
Schema1.Udf1 was defined by using the following code:
You need to write a query that will contain the following columns:
Country
CountryID
CustomerName
The solution must meet the following requirements:
Rows must be returned only if the function returns data.
The amount of time it takes the query to execute must be minimized.
Which query should you use?
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 14
You have a database hosted on SQL Azure.
You are developing a script to create a view that will be used to update the data in a table.
The following is the relevant portion of the script. (Line numbers are included for reference only.)
You need to ensure that the view can update the data in the table, except for the data in Column1.
Which code segment should you add at line 06?
A. WITH CHECK OPTION
B. WITH VIEW_METADATA
C. WITH ENCRYPTION
D. WITH SCHEMABINDING
Correct Answer: A
Explanation
Explanation/Reference:
The question concerning the view that has a clause “WHERE Column1 = ‘City1’ is wrong. That’s not what the CHECK option is made for. Actually you will be able to update ONLY the rows satisfied by that WHERE clause, that is, only the
rows with the Column1 being ‘City1’. None of the answers are valid from that question. You need a trigger to achieve that.http://msdn.microsoft.com/en-us/library/ms187956.aspx
QUESTION 15
You have a Microsoft SQL Azure database.
You have the following stored procedure:
You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees executes.
The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
A. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
B. Add the following line of code to line 13:
WITH (UPDLOCK)
C. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. Add the following line of code to line 08:
WITH (UPDLOCK)
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 16
You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1. You need to ensure that when Proc1 is executing, Proc2 can
access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Update less than 4,000 rows simultaneously.
B. Use the PAGLOCK table hint.
C. Wait for Proc2 to complete.
D. Use the ROWLOCK table hint.
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 17
You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
C. BEGIN TRANSACTION WITH MARK @transactionID
D. COMMIT TRANSACTION @transactionID
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 18
You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters “ADV” and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
A. A FOREIGN KEY constraint
B. A trigger
C. A user-defined data type
D. A CHECK constraint
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 19
You have a database that contains a table named Department. Department contains the names and locations of each department.
You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function.
You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects.
What should you add to the query?
A. OUTER APPLY
B. OUTER JOIN
C. CROSS JOIN
D. CROSS APPLY
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 20
You have a database that uses the following management views:
Sys.dm_os_volume_stats
Sys.dm_db_partition_stats
Sys.dm_db_file_space_usageSys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?
A. sys.fulltext_indexes
B. sys.dm_db_file_space_usage
C. sys.dm_os_volume_stats
D. sys.dm_db_partition_stats
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 21
You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows. The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers’1,2,3,4,5′;
What should you do?
A. Update @CustcmerTypelist to use the int data type.
B. Convert @CustomerTypeList to a table variable.
C. Convert @CustomerTypeList to an XML variable.
D. Update @CustomerTypeList to use the XML data type.
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 22
You need to identify which long running transactions use an index.
Which dynamic management view should you use?
A. sys.dm_exec_query_optimizer_info
B. sys.dm_exec_connections
C. sys.dm_exec_query_stats
D. sys.dm_exec_sessions
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 23
You create a table named Customers by using the following code segment:
You create a non-clustered index named IX_Name on the name column.
You write the following query to retrieve all of the customers that have a name that starts with the letters SMI:
You discover that the query performs a table scan.
You need to ensure that the query uses the index.
What should you do?
A. Replace LEFT(name,3) = ‘smi’ by using name like ‘smi%’
B. Replace LEFT(name,3) = ‘smi’ by using substring(name,l,3) = ‘smi’
C. Recreate IX_Name as a unique index
D. Recreate IX Name as a clustered index
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 24
You plan to execute the following code:
You need to identify how many rows will be in dbo.Table1 after you execute the code.
How many rows should you identify?
A. 0
B. 1C. 2
D. 3
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 25
You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.
Which type of objects should you identify?
A. Common table expressions (CTEs)
B. Temporary stored procedures
C. Temporary tables
D. Table variables
Correct Answer: C
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
http://msdn.microsoft.com/en-us/library/ms189084.aspx
http://msdn.microsoft.com/en-us/library/ms175010.aspx
http://msdn.microsoft.com/en-us/library/bb510489.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx
http://zacksfiasco.com/post/2010/01/21/SQL-Server-Temporary-Stored-Procedures.aspx
QUESTION 26
You execute the following code:
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 27
You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?
A. STATISTICS_NORECOMPUTE = OFF
B. FILLFACTOR = 80
C. FILLFACTOR = 0
D. STATISTICS_NORECOMPUTE = ON
Correct Answer: C
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms177459.aspx
http://msdn.microsoft.com/en-us/library/ms188783.aspx
QUESTION 28
You run the following code:
You need to ensure that the root node of the XML data stored in the Details column is .
What should you implement? More than one answer choice may achieve the goal. Select the BEST answer.
A. A user-defined data type
B. An XML index
C. A Data Definition Language (DDL) trigger
D. A data manipulation language (DML) trigger
E. An XML schema collection
Correct Answer: E
Explanation
Explanation/Reference:http://msdn.microsoft.com/en-us/library/ms187856.aspx
QUESTION 29
You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table.
What should you use? More than one answer choice may achieve the goal. Select the BEST answer.
A. A data manipulation language (DML) trigger
B. A DEFAULT constraint
C. A Data Definition Language (DDL) trigger
D. A CHECK constraint
E. A FOREIGN KEY constraint
Correct Answer: D
Explanation
Explanation/Reference:
http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402
http://msdn.microsoft.com/en-us/library/ms178110.aspx
QUESTION 30
You have a SQL Azure database.
You execute the following code:
The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows.
You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WHERE FaxNumber IS NOT NULL
B. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WITH FILLFACTOR=0
C. CREATE INDEX IX_Customers ON Customers (CustomerName)
INCLUDE (FaxNumber)
D. CREATE INDEX IX_Customers ON Customers (FaxNumber)
E. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WHERE FaxNumber IS NULL
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 31
You plan to create a new column in a table. The column must meet the following requirements:
Be able to store images that are larger than 1 MB each.
Be able to access the images from Microsoft .NET Framework applications.
You need to recommend which data type must be used in the column.
Which data type should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. nvarchar
B. varbinary
C. image
D. FileStream
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 32
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
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 33
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
Correct Answer: D
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://msdn.microsoft.com/en-us/library/cc280372.aspx
QUESTION 34
You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated.
What should you use? More than one answer choice may achieve the goal. Select the BEST answer.
A. A Data Definition Language (DDL) trigger
B. A DEFAULT constraint
C. A CHECK constraint
D. A FOREIGN KEY constraint
E. A data manipulation language (DML) trigger
Correct Answer: E
Explanation
Explanation/Reference:
http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402
http://msdn.microsoft.com/en-us/library/ms178110.aspx
QUESTION 35
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.
Correct Answer: ABD
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
QUESTION 36
You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?
A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = OFF
C. STATISTICS_NORECOMPUTE = ON
D. FILLFACTOR = 80
Correct Answer: D
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://msdn.microsoft.com/en-us/library/ms177459.aspx
QUESTION 37
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 Proc1.
What should you do?
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 38
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.
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 39
You have a database named DB1.
You plan to configure change data capture on the existing tables in DB1.
The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)
You need to minimize disk contention caused by change data capture.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Increase the autogrowth value of the database file.
B. Set the database recovery model to simple.
C. Increase the autogrowth value of the log file.
D. Configure change data capture to use to a secondary filegroup.
Correct Answer: D
ExplanationExplanation/Reference:
QUESTION 40
You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A script that enables trace flags
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 41
You have a database named Database1.
You execute the following code:
Users report that the query takes a long time to complete.
You create a full-text index for the Notes column.
You need to recommend changes to the query to reduce the amount of time it takes for the query to complete.
Which code segment should you use to replace line 03?
A. WHERE FREETEXT(notes, ‘%call%’) AND
B. INNER JOIN FREETEXTTABLE(dbo.table1, notes, ‘call’)
AS t2 ON dbo.table1.ID = t2.key WHERE
C. WHERE CONTAINS(notes, ‘call*’) AND
D. WHERE CONTAINS(notes,*%call%’> AND
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 42
You have a SQL Server 2012 instance.
You plan to create an application that uses spatial data.
You need to create an object that will support the representation of the surface area of all the oceans.
Which code segment should you use?
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 43
You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
A. Serializable
B. Snapshot
C. Repeatable read
D. Read committed
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 44
You have a SQL Server 2012 instance that hosts a single-user database.
The database does not contain user-created stored procedures or user-created functions.You need to minimize the amount of memory used for query plan caching.
Which advanced server option should you modify?
A. Scan for Startup Procs
B. Enable Contained Databases
C. Optimize for Ad hoc Workloads
D. Allow Triggers to Fire Others
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 45
You execute the following code
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.
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 46
You have a server that has SQL Server 2012 installed.
You need to identify which parallel execution plans are running in serial.
Which tool should you use?
A. Performance Monitor
B. Database Engine Tuning Advisor
C. Data Profile Viewer
D. Extended Events
Correct Answer: D
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/bb677278.aspx
http://msdn.microsoft.com/en-us/library/bb630282.aspx
http://www.sql-server-performance.com/2006/query-execution-plan-analysis/ http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in- sqlserver/
http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last %2c+execution+plans+show+true+thread+reservations./92458/ http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query- executionplan.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goals-gone-rogue.aspx http://msdn.microsoft.com/en-us/library/bb895310.aspx
http://msdn.microsoft.com/en-us/library/bb895313.aspx
http://msdn.microsoft.com/en-us/library/hh231122.aspx
QUESTION 47
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
Correct Answer: D
Explanation
Explanation/Reference:
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
QUESTION 48
You have a Microsoft SQL Azure database named DBAzure1.
You create a table in DBAzure1 by using the following script:
You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.
What should you recommend creating?
A. A CHECK constraint
B. A filtered index
C. A columnstore index
D. A UNIQUE constraint
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 49
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.
A. NEWID
B. @@IDENTITY
C. NEWSEQUENTIALID
D. IDENTITY
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 50
You have a SQL Server 2012 instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex.
ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. DBCC DBREINDEX
B. REORGANIZE
C. REBUILD
D. DBCC INDEXDEFRAG
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 51
You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
A. SCHEMABINDING
B. VIEW_METADATA
C. DISTINCT
D. DEFAULT
Correct Answer: A
Explanation
Explanation/Reference:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
http://msdn.microsoft.com/en-us/library/ms191432.aspx
QUESTION 52
You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 53
You create a view by using the following code:
Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 54
Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Create the view on Server5.
B. Add Server9 as a linked server.
C. Create the view on Server9.
D. Add the Customers table to Server9.
E. Add Server9 as a Distributor.
F. Remove the Customers table from Server5.
Correct Answer: ABCD
Explanation
Explanation/Reference:
QUESTION 55
You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
Permit insertions into a table variable.
Support structured exception handling.
Prevent changes to the definition of referenced objects.
Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
A. An inline table-valued function
B. A stored procedure
C. A scalar user-defined function
D. A multi-statement table-valued function
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 56
You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012.
You need to migrate alerts and e-mail notifications.
Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. sp_syspolicy_create_job
B. sp_add_operator
C. sp_audit_write
D. sp_add_alert
Correct Answer: BC
Explanation
Explanation/Reference:
B: sp_add_operator
Creates an operator (notification recipient) for use with alerts and jobs.
C: sp_audit_write
Adds a user-defined audit event to the USER_DEFINED_AUDIT_GROUP. If USER_DEFINED_AUDIT_GROUP is not enabled, sp_audit_write is ignored.
QUESTION 57
You have a Microsoft SQL Azure database that contains a table named Customers. You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID
of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
A. OUTER JOIN
B. CROSS JOIN
C. CROSS APPLY
D. OUTER APPLY
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 58
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 Proc1.
What should you do?
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 59
Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add SQL2 as a Distributor.
B. Add the Customers table to SQL2.
C. Add SQL2 as a linked server.
D. Create the view on SQL1.
E. Remove the Customers table from SQL1.
F. Create the view on SQL2.
Correct Answer: BCDF
Explanation
Explanation/Reference:
QUESTION 60
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
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 61
You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.
Which code segment should you use to create the table?
A. CREATE TABLE oneTable
B. CREATE TABLE ##oneTable
C. CREATE TABLE #oneTable
D. DECLARE oneTable TABLE
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 62
You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?
A. CREATE TABLE ##Table…
B. CREATE TABLE Table…
C. CREATE VIEW Table…
D. CREATE PARTITION SCHEME Table…
E. DECLARE TABLE @ Table…
Correct Answer: A
Explanation
Explanation/Reference:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name)
QUESTION 63
You have a Microsoft SQL Azure database that contains a table named Employees
You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName.
What should you do?
A. Recreate EmployeeName as a unique index
B. Recreate EmployeeName as a clustered index
C. Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’
D. Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 64
You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords.
You need to ensure that all new records have a password.
Which code segment should you use? More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: D
Explanation
Explanation/Reference:
QUESTION 65
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.
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION 66
You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client.
What should you do?
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION 67
You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
Correct Answer: A
Explanation
Explanation/Reference:
Analyze Deadlocks with SQL Server Profiler
Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can
create a trace that records, replays, and displays deadlock events for analysis.
To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler
can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.
QUESTION 68
You have the following query on a disk-based table:
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.
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION 69
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 schema and the data for all database object(s) used remain unchanged between executions. The QueryTime
column is defined as a computed column that uses the GETDATE() system function. The query plans and results are shown below:
You need to make an initial diagnosis of the situation, based solely on this input
Which two statements can you make about the performance characteristics of this query? Each correct answer presents a complete solution. Choose two.
A. The queries would perform better if the index named AccountNumber included the Name and QueryTime column.
B. The queries would perform worse if the index named AccountNumber included the NameColumn.
C. The queries would perform better if the index named AccountNumber included the Name column.
D. The object Account is a table, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
E. The object Account is an indexed view, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
F. The object Account is a view, joining the Account-AccountNumber and Account.PKAccount objects together.
Correct Answer: BD
Explanation
Explanation/Reference:
QUESTION 70
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
Explanation
Explanation/Reference:
QUESTION 71
Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables.
The tables are configured as shown in the following table.
You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.
Explanation
Explanation/Reference:
QUESTION 72
You create a table that contains the following script:
You need to prevent duplicate values in the EmployeeID 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.
Explanation
Explanation/Reference:
QUESTION 73
You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.
Your company identifies the following requirements for each stored procedure:
USP_1 cannot allow dirty reads.
USP_2 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.)
Explanation
Explanation/Reference:
* read committed
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
* SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option
only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
QUESTION 74
You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:
Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdat edDate and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3. Any error that occurs during the course of the trigger’s execution must prevent the changes from happening.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Explanation
Explanation/Reference:
Note:
* Executing a ROLLBACK TRANSACTION or COMMIT TRANSACTION Transact-SQL statement inside a stored procedure or trigger is possible, but doing so may cause errors
Conclusion:
Cabinetdetherapies Free to share 74 high-quality Microsoft Data Platform 70-464 Exam dumps Improve your pass rate,
and all of our exam content comes from Pass4itsure Experts. Free content can help you open the door to authentication,
and if you want to easily obtain a Microsoft 70-464 certificate, select the full 70-464 PDF Dumps Or 70-464 VCE
dumps:https://www.pass4itsure.com/70-464.html (q&as:182 Latest update )
[PDF] Free Microsoft Data Platform 70-464 dumps download from Google Drive:
https://drive.google.com/open?id=16YEbhDoWLhg99OfDUYtLrBhDai4Qdfi-
[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO
Pass4itsure Promo Code 15% Off
related: https://www.freecertexam.com/new-release-lpi-117-201-dumps/