New 70-461 Exam Dumps Collection from PassLeader in VCE and PDF Files (Question 136 – Question 150)

Valid 70-461 Dumps shared by PassLeader for Helping Passing 70-461 Exam! PassLeader now offer the newest 70-461 VCE dumps and 70-461 PDF dumps, the PassLeader 70-461 exam questions have been updated and ANSWERS have been corrected, get the newest PassLeader 70-461 dumps with VCE and PDF here: http://www.passleader.com/70-461.html (200 Q&As Dumps)

BTW, DOWNLOAD part of PassLeader 70-461 dumps from Cloud Storage: https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM

QUESTION 136
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter. You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. Which approach should you use?

A.    A SELECT statement that includes CASE
B.    Cursor
C.    BULK INSERT
D.    View
E.    A user-defined function

Answer: B

QUESTION 137
As part of a new enterprise project, you’re designing a new table to store financial transactions. This table could eventually store millions of rows and so storage space is very important. One of the columns in the table will store either a 1 or 0 value. Which data type would be most appropriate?

A.    bit
B.    tinyint
C.    numeric
D.    float

Answer: A

QUESTION 138
Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?

A.    varchar to decimal
B.    decimal to bit
C.    date to varchar
D.    datetime to text

Answer: D
Explanation:
There are very few conversions which are allowed to the Text data type. They include varchar, char, nchar, nvarchar and ntext. Converting datetime to text is not an allowed conversion using either CAST or CONVERT.

QUESTION 139
What is wrong with the T-SQL batch statement below.
@find varchar(30);
SET @find = ‘Man’;
SELECT LastName FROM Person WHERE LastName = @find;

A.    You can’t use the @find parameter in a query
B.    DECLARE is missing
C.    Nothing, it’s fine
D.    You should use SELECT instead of SET

Answer: B
Explanation:
Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

QUESTION 140
You’re designing a new table that will hold information about medical records. Another table called documents contains a foreign key that references this table. To make sure information is cleaned up when a delete operation is performed, you’ve added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?

A.    Corresponding rows in the referenced table (documents) will be deleted
B.    All the values that make up the foreign key are set to NULL in the documents table
C.    All the values that comprise the foreign key are set to their default values in the documents table
D.    Rows are deleted from the medical records table but the documents table is unchanged

Answer: A
Explanation:
Don’t forget that ON DELETE statements are included as part of the foreign key relationship.

QUESTION 141
Which of the following is an example of a scalar subquery?

A.    select max(price) from products
B.    select sum(price) from products
C.    select min(price) from products
D.    All of these

Answer: D

QUESTION 142
If you use the BEGIN TRANSACTION statement, then execute 2 insert statements and 2 delete statements what will happen if you execute ROLLBACK TRANSACTION?

A.    Only the INSERT statements will be rolled back
B.    All of the INSERTS and DELETES will be rolled back
C.    Only the last INSERT and last DELETE statement will be rolled back
D.    Only the DELETE statements will be rolled back

Answer: B
Explanation:
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. All modifications are erased when you issue a ROLLBACK TRANSACTION statement.

QUESTION 143
Your manager has asked you to design a new table and enforce rules for the type of information that can be stored in a particular column. The information must match a particular pattern e.g. a number between 1 and 7. This checking must be performed during insert or update operations. Which parameter should you use when you create the column?

A.    CHECK
B.    DEFAULT
C.    Foreign Key
D.    Primary Key

Answer: A

QUESTION 144
What is missing from the T-SQL statement below for creating and using a cursor?
DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;

A.    KEYSET
B.    OPEN
C.    STATIC
D.    SCROLL

Answer: B
Explanation:
You need to open the CURSOR before you can call FETCH.

QUESTION 145
Complete the missing word below. A ____ is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.

A.    Constraint
B.    DLL
C.    View
D.    Trigger

Answer: D

QUESTION 146
Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N’Test’), OBJECT_ID(N’Address’), NULL, NULL , ‘DETAILED’);

A.    Run ALTER INDEX REBUILD WITH (ONLINE = ON)
B.    Run ALTER INDEX REORGANIZE
C.    Rebuild the table
D.    Nothing, the index fragmentation is at a safe level

Answer: B
Explanation:
Generally, you should reorganize indexes if the fragmentation is between 5 and 30 percent. You should rebuild the index if the fragmentation is over 30 percent. Remember, these values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

QUESTION 147
You need to write a new query that will return names from the employee table for people who are not part of the contractors table (the two tables share a unique id, similar to social security or NI number). Your query will use a sub query to check that the employee is not in the contractors table. How might you achieve this?

A.    Use COALESCE
B.    Use EXISTS
C.    Use IS_MEMBER
D.    Use EXP

Answer: B

QUESTION 148
Your manager has asked you to extend one of the complex stored procedures used to calculate time differences between lab test results. In particular, during the stored procedures you need to check the exact minute value of a datatime value. Your manager has suggested that you use the DATENAME function and pass in the date and datepart argument. Which datepart argument should you use to get the minute?

A.    m
B.    n
C.    mcs
D.    mm

Answer: B
Explanation:
If you want to get the minute, you can either pass in mi or n as the argument to DATENAME.

QUESTION 149
You’re designing a new SQL Server 2012 table that will hold millions of records. The table will be used by a finance application to show historical transactions. The table only has 10 columns but it’s vital that the table performs well. How many primary keys should you create for the table for best performance?

A.    2
B.    10
C.    3
D.    1

Answer: D
Explanation:
You can only have 1 primary key per table.

QUESTION 150
You’ve created a standard stored procedure that’s not a CLR procedure. This procedure has several parameters, some of which are output parameters. Which of the following CANNOT be passed back as an output parameter from a stored procedure?

A.    Text
B.    image
C.    Ntext
D.    Any of these

Answer: D
Explanation:
Unless you create a CLR procedure, you can use these data types are return values.


Get the newest PassLeader 70-461 VCE dumps here: http://www.passleader.com/70-461.html (200 Q&As Dumps)

And, DOWNLOAD the newest PassLeader 70-461 PDF dumps from Cloud Storage for free: https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM