Recertification for MCSE: Data Platform
Question No: 161 DRAG DROP – (Topic 12)
You plan to install two SQL Server 2014 environments named Environment1 and Environment.
Your company identifies the following availability requirements for each environment:
->Environment1 must have mirroring with automatic failover implemented.
->Environment2 must have AlwaysOn with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify?
To answer, drag the appropriate number to the correct environment in the answer area.
Database Mirroring (SQL Server)
Failover and Failover Modes (AlwaysOn Availability Groups)
Question No: 162 – (Topic 12)
You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement.
Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed.
Explanation: * an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
RESTART [ WITH lt;constantgt; ]
The next value that will be returned by the sequence object. If provided, the RESTART
WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.
* CREATE SEQUENCE
Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
Question No: 163 DRAG DROP – (Topic 12)
You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
->Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
->Table2 will contain a column named Column2 which is the product of two other
columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.
A Persisted column would be faster to retrieve.
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators.
Unless otherwise specified, computed columns are virtual columns that are not physically
stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change.
Use Sparse Columns
Specify Computed Columns in a Table
Question No: 164 – (Topic 12)
You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?
Change data capture
Master Data Services
Explanation: Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
About Change Data Capture (SQL Server)
Question No: 165 DRAG DROP – (Topic 12)
You are designing a database for a university.
The database will contain two tables named Classes and Enrollment that have the following specifications:
->Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
->Enrollment will store information about students and their classes. Performance
must be enhanced for queries of the current enrollments.
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.
* The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
FileTables (SQL Server) Partitioned Tables and Indexes
Question No: 166 DRAG DROP – (Topic 12)
You plan to deploy SQL Server 2014.
Your company identifies the following monitoring requirements:
->Tempdb must be monitored for insufficient free space.
->Deadlocks must be analyzed by using Deadlock graphs.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
* You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and
sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.
/ 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.
/ SQL Server Profiler and SQL Server Management Studio use a deadlock wait-for graph to describe a deadlock. The deadlock wait-for graph contains process nodes, resource nodes, and edges representing the relationships between the processes and the resources.
Question No: 167 – (Topic 12)
You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database. What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
Max Degree of Parallelism
Windows System Resource Manager (WSRM)
Explanation: SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
D: PROCESS AFFINITY
Enables hardware threads to be associated with CPUs.
Question No: 168 – (Topic 12)
Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
A dynamic management view
Explanation: A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
Not A: Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Question No: 169 – (Topic 12)
You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
Create a SQL Server Integration Services (SSIS) package, and then run the package.
Back up the database, and then restore the database.
Extract a data-tier application, and then import the application.
Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.
Explanation: SQL Server Integration Services Most flexibility
Data Transfer Efficiency: Good
/ SSIS can be used to perform a broad range of data migration tasks. SSIS provides support for complex workflow and data transformation between the source and destination. It is a good choice to transfer of data for databases that require many changes to work on Microsoft Azure SQL Database. You can use SSIS data transfer packages with another mechanism for transferring the database schema, such as a Data-tier Application package. SSIS for Azure and Hybrid Data Movement
Not D: Generate Scripts Wizard
Has explicit option for Azure SQL Database scripts generation Data Transfer Efficiency: Poor
Good for smaller database
/ Using the Generate Scripts wizard to migrate a SQL Server database to Azure SQL Database should be limited to:
Teams who have experience with the wizard.
Migrating simple databases that need few schema changes to run on Azure SQL Database. The scripts generated from the source database can be modified before being used to create the new version of the database on Azure SQL Database, but using a database project in the SQL Server Data Tools has richer support for making schema changes.
Migrating small databases that do not have much data. The wizard generates scripts that use insert statements instead of bulk copies to transfer the data. The insert statements can be throttled when the tables contain too much data, and are not as fast as bulk copies.
Question No: 170 DRAG DROP – (Topic 12)
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.
Table1 contains the indexes shown in the following table.
You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend?
To answer, drag the appropriate index storage type to the correct index in the answer area.
Index Storage Type
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
/The base table has not been partitioned.
/The index key is unique and it does not contain the partitioning column of the table.
/ You want the base table to participate in collocated joins with more tables using different join columns.
Partitioned Tables and Indexes
EnsurePass 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