https://www.examslabs.com/Microsoft/MCP/best-70-464-exam-dumps.html

NEW QUESTION NO: 10
Which data type should you use for CustomerID?
A. varchar(11)
B. bigint
C. nvarchar(11)
D. char(11)
Answer: D
Explanation/Reference:
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

NEW QUESTION NO: 11
You need to implement a new version of usp_AddMobileLocation. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:

Answer: 

Explanation/Reference:
Note:
* From scenario:
The mobile application will need to meet the following requirements:
Update the location of the user by using a stored procedure named usp_AddMobileLocation.
* DELAYED_DURABILITY
SQL Server transaction commits can be either fully durable, the SQL Server default, or delayed durable (also known as lazy commit).
Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Writing the transaction log entries to disk is required for a transaction to be durable.
Delayed durable transactions become durable when the transaction log entries are flushed to disk.

NEW QUESTION NO: 12
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
Answer: C
Explanation/Reference:
Explanation:
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

NEW QUESTION NO: 13
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 JOIN
B. CROSS JOIN
C. OUTER APPLY
D. CROSS APPLY
Answer: D

NEW QUESTION NO: 14
You need to redesign the system to meet the scalability requirements of the application.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Select and Place:

Answer: 

Explanation/Reference:
Note:
* MEMORY_OPTIMIZED_DATA
First create a memory-optimized data filegroup and add a container to the filegroup.
Then create a memory-optimized table.
* 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.
* Example:
-- create a durable (data will be persisted) memory-optimized table -- two of the columns are indexed CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON)
GO

NEW QUESTION NO: 15
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 C
C. Option D
D. Option B
Answer: B

NEW QUESTION NO: 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.
Answer: A

NEW QUESTION NO: 17
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. Snapshot
B. Repeatable read
C. Read committed
D. Serializable
Answer: D

NEW QUESTION NO: 18
You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.

SProc2 must return a status code.

You need to identify which options must be implemented to meet each stored procedure requirement.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)
Select and Place:

Answer: 


NEW QUESTION NO: 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 varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
B. UserName nvarchar(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_CS_AS NOT NULL,
Answer: B

NEW QUESTION NO: 20
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?

A. Option A
B. Option B
C. Option D
D. Option C
Answer: B