Download MS SQL Server 2012/2014 Certification Exam: Questions 121-150 and more Exams Relational Database Management Systems (RDBMS) in PDF only on Docsity!
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps
➢ Vendor: Microsoft
➢ Exam Code: 70- 461
➢ Exam Name: Querying Microsoft SQL Server 2012/
➢ Question 121 – Question 15 0
Visit PassLeader and Download Full Version 70- 461 Exam Dumps
QUESTION 121
Which of the following functions would you consider using to generate surrogate keys? (Choose all that apply.) A. NEWID B. NEWSEQUENTIALID C. GETDATE D. CURRENT_TIMESTAMP Answer: AB QUESTION 122 You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. Answer: CREATE VIEW sales.uv_CustomerFullNames WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers QUESTION 123 You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index. You need to run an update statement against the table. What should you do first? A. Pause the columnstore index.
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps B. Change the recovery model of the database to Bulk-logged. C. Change the non-clustered columnstore index to be a clustered columnstore index. D. Drop the columnstore index. Answer: D QUESTION 124 You are installing the Data Quality Server component of Data Quality Services. You need to provision the hardware and install the software for the server that runs the Data Quality Server. You must ensure that the minimum Data Quality Server prerequisites are met. What should you do? A. Install Microsoft Internet Explorer 6.0 SP1 or later. B. Install SQL Server 2012 Database Engine. C. Make sure the server has at least 4 GB of RAM. D. Install Microsoft SharePoint Server 2010 Standard Edition. Answer: B Explanation: Data Quality Server Minimum System Requirements:
- SQL Server 2012 Database Engine.
- Memory (RAM): Minimum: 2GB Recommended: 4GB or more Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client. QUESTION 125 You need to extract data from a table in a SQL Server 2012 database. What connection manager types can you use? (Choose all that apply.) A. An ODBC connection manager B. An OLE DB connection manager C. A File connection manager D. An ADO.NET connection manager Answer: ABD QUESTION 126 You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS). The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012. Which tool should you use? A. SQL Server dtexec utility (dtexec.exe) B. SQL Server DTExecUI utility (dtexecui.exe) C. SSIS Upgrade Wizard in SQL Server Data Tools D. SQL Server Integration Services Deployment Wizard Answer: C Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS)
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps into the application. You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do? A. Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000. B. Use sp_configure to set a value for blocked process threshold. Create an extended event session. C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step. C. Run the sp_who command from a query window. D. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log. Answer: A Explanation: Trace 1222 is used for deadlocks. To take slow queries better to use Profile! --\Burgos Verified the SQL Profiler and DBCC answers as correct. However, while Profiler will show this information, the best practice with Profiler is to use it short-term. The question specifically states "over an extended period of time". That means Profiler wouldn't be the best tool for this scenario. Therefore, DBCC would be the best answer. http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/ http://msdn.microsoft.com/en-us/library/ms188396.aspx QUESTION 131 You administer a Microsoft SQL Server 2012 database. Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error. You need to identify the root cause of the issue by retrieving the error message. What should you do? A. Create an Extended Events session by using the sqlserver.error_reported event. B. Create a SQL Profiler session to capture all ErrorLog and EventLog events. C. Flag all stored procedures for recompilation by using sp_recompile. D. Execute sp_who. Answer: A Explanation: http://msdn.microsoft.com/en-us/library/bb630282.aspx QUESTION 132 You create a view based on the following statement. You grant the Select permission to User1. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps the changes, the view functions correctly for User1. Which Transact-SQL statement should you use? A. Option A B. Option B C. Option C D. Option D Answer: C QUESTION 133 You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table. You need to ensure that the following requirements are met.
- The fields must use the minimum amount of space.
- The fields must be an incrementing series of values.
- The values must be unique among the four tables. What should you do? A. Create a ROWVERSION column. B. Create a SEQUENCE object that uses the INTEGER data type.
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps Answer:
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps
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
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps
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
70 - 461 Exam Dumps 70 - 461 Exam Questions 70 - 461 PDF Dumps 70 - 461 VCE Dumps 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
