Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 May Microsoft Official New Released 70-464
100% Free Download! 100% Pass Guaranteed!

Developing Microsoft SQL Server 2012 Databases

Question No: 111 DRAG DROP – (Topic 8)

You have two existing tables, one named COUNTRY and the other named STATES. The tables are defined as follows:

Dumps4Cert 2018 PDF and VCE

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.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3:

Dumps4Cert 2018 PDF and VCE

Box 4:

Dumps4Cert 2018 PDF and VCE

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)

Question No: 112 – (Topic 8)

You have a SQL Server 2012 database named Database1. You execute the following code:

Dumps4Cert 2018 PDF and VCE

You insert 3 million rows into Sales.

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

What should you do?

Dumps4Cert 2018 PDF and VCE

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: A

Question No: 113 DRAG DROP – (Topic 8)

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:

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

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Dumps4Cert 2018 PDF and VCE

Box 1: read uncommitted

READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called quot;dirtyquot; reads.

Box 2: 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.

References: https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

Question No: 114 DRAG DROP – (Topic 8)

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.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Dumps4Cert 2018 PDF and VCE

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

Question No: 115 – (Topic 8)

You have a Microsoft SQL Azure database. You have the following stored procedure:

Dumps4Cert 2018 PDF and VCE

You discover that the stored procedure periodically fails to update Person.Contact.

You need to ensure that Person.Contact is always updated when UpdateContact 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?

  1. Add the following line of code to line 12: WITH (UPDLOCK)

  2. Add the following line of code to line 05:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  3. Add the following line of code to line 08: WITH (UPDLOCK)

  4. Add the following line of code to line 05:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Answer: C

Explanation: * Overall, you should use UPDLOCK when you read a value that you plan to update later in the same transaction to prevent the value from changing.

* UPDLOCK

Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK

isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.

Question No: 116 – (Topic 8)

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?

  1. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY

  2. BACKUP LOG DB1 TO Device1 WITH NORECOVERY

  3. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

  4. BACKUP LOG DB1 TO Device1

Answer: D Explanation:

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 No: 117 – (Topic 8)

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

Dumps4Cert 2018 PDF and VCE

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?

  1. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,

  2. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,

  3. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,

  4. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,

Answer: D

Question No: 118 – (Topic 8)

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?

  1. FILLFACTOR = 0

  2. STATISTICS_NORECOMPUTE = OFF

  3. STATISTICS_NORECOMPUTE = ON

  4. FILLFACTOR = 80

Answer: D Explanation:

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

Question No: 119 – (Topic 8)

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?

  1. A SQL Server Profiler trace

  2. A script that enables trace flags

  3. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

  4. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views

Answer: A

Explanation: 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 No: 120 – (Topic 8)

Your network contains a server that has SQL Server 2014 installed. You create a table by using the following script:

Dumps4Cert 2018 PDF and VCE

You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.

What should you recommend creating?

  1. A UNIQUE constraint

  2. A filtered index

  3. A columnstore index

  4. A CHECK constraint

Answer: A

100% Dumps4cert Free Download!
Download Free Demo:70-464 Demo PDF
100% Dumps4cert Pass Guaranteed!
Download 2018 Dumps4cert 70-464 Full Exam PDF and VCE

Dumps4cert ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No