Microsoft 70-464 Free Practice Questions

NEW QUESTION 1

You need to convert the functionality of Dynamic.sql to use a stored procedure. Which Transact SQL statement should you add to the stored procedure contain?

• A. CREATE PROC Production.ProductsAfterDate (@ProductID AS varchar(11), @CreationDate AS date)AS...
• B. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000)) AS...
• C. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000), OUTPUT @ProductID AS varchar(11), OUTPUT @CreationDate AS date)AS...
• D. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000),@ProductID AS varchar(11),@CreationDate AS date) AS...

Answer: C

Explanation:
@sqlstring, @ProductID, and @CreationData need to be declared as parameters. @ProductID, and
@CreationData should be output parameters.

NEW QUESTION 2

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES
Persons(P_Id)

NEW QUESTION 3

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

Answer: D

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

NEW QUESTION 4

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
* sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

NEW QUESTION 5

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

NEW QUESTION 6

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx http://msdn.microsoft.com/en-us/library/ms189563.aspx http://msdn.microsoft.com/en-us/library/ms190174.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 7

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

Answer: C

NEW QUESTION 8

You need to build a stored procedure that amortizes the invoice amount. Which code segment should you use to create the stored procedure? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
D:Documents and SettingsuseralboDesktop1.jpg
http://msdn.microsoft.com/en-us/library/ms131089.aspx http://msdn.microsoft.com/en-us/library/ms131048.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx

NEW QUESTION 9

You have a SQL Server database.
The recovery model is set to full. The transaction log is backed up every night. You discover that the transaction log never decreases in size.
You execute the DBCC SHRINKFILE statement for the transaction log and you discover that the transaction log is unaffected.
You need to ensure that you can reduce the size of the transaction log. What should you do first?

• A. Truncate the transaction log
• B. Kill long-running transactions
• C. Change the recovery model to bulk-logged
• D. Perform a full backup

Answer: A

Explanation:
The transaction log must be truncated before running the DBCC SHRINKFILE operation.

NEW QUESTION 10

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

Answer: BC

Explanation:
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.

NEW QUESTION 11

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.

Answer: ABCD

NEW QUESTION 12

You need to modify the usp_DetectSuspiciousActivity stored procedure.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F

Answer: DE

Explanation:
Note:
* Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity.

NEW QUESTION 13

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_lengt
• B. Create alerts to notify you when these values converge.
• C. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
• D. Query system DMVs to monitor total_bucket_coun
• E. Create alerts to notify you when this value increases.
• F. Query system DMVs to monitor total_bucket_coun
• G. Create alerts to notify you when this value decreases.

Answer: A

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.

NEW QUESTION 14

You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 15

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

Answer: B

NEW QUESTION 16

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

NEW QUESTION 17

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

Answer: D

NEW QUESTION 18

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

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/ms187956.aspx http://msdn.microsoft.com/en-us/library/ms191432.aspx

NEW QUESTION 19

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,

Answer: D

NEW QUESTION 20

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?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: D

NEW QUESTION 21

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data.
Each order has a sales total that can only be discovered by querying multiple tables.
You need to ensure that the value of the sales total is returned by executing a query on Orders. What should you create?

• A. A calculated column that uses a scalar function
• B. A trigger that uses a table-valued function
• C. A calculated column that uses a table-valued function
• D. A trigger that uses a ranking function

Answer: C

Explanation:
A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters.
Incorrect:
Not A: A scalar function would only be able to use other columns from the same table.

NEW QUESTION 22

You run the following code:

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>. 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

Answer: E

Explanation:
http://msdn.microsoft.com/en-us/library/ms187856.aspx

NEW QUESTION 23

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

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'

Answer: C

NEW QUESTION 24

You have a SQL Server 2012 database named Database1. Database1 has a data file named database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is
1.5 GB.
Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log
backups in the future. Which code segment 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQL
USE AdventureWorks2012; GO
-- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE; GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO
-- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
http://msdn.microsoft.com/en-gb/library/ms189493.aspx

NEW QUESTION 25

You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to
execute.
You need to view the server resource consumption when the queries are executed. 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: Start a SQL Server Profiler trace.
Box 2: Start a data collection by using Performance monitor. Box 3: Save the SQL Profiler trace.
Box 4: Save the Performance Monitor data.
Box 5: Import the performance data into SQL Server Profiler. Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at
about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data
Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.

NEW QUESTION 26
......