Microsoft 70-464 Exam Practice Questions (P. 4)
- Full Access (209 questions)
- Six months of Premium Access
- Access to one million comments
- Seamless ChatGPT Integration
- Ability to download PDF files
- Anki Flashcard files for revision
- No Captcha & No AdSense
- Advanced Exam Configuration
Question #16
DRAG DROP -
You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
Correct Answer:
Box 1: Modify the TRUSTWORTHY property of the database.
Box 2: Execute the CREATE ASSEMBLY statement.
Box 3: Execute the CREATE AGGREGATE statement.
Note:
* TRUSTWORTHY CREATE signature
The TRUSTWORTHY property indicates whether the instance of SQL Server trusts the database and the contents within it.
* CREATE AGGREGATE
Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework. For the Database Engine to bind the aggregate function to its implementation, the .NET Framework assembly that contains the implementation must first be uploaded into an instance of SQL
Server by using a CREATE ASSEMBLY statement.
* Example:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO -
CREATE ASSEMBLY [CLR.Utilities] FROM 'C:\Path\To\File\CLR.Utilities.dll' WITH PERMISSION_SET = UNSAFE
GO -
CREATE AGGREGATE [dbo].[Concatenate] (@input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME [CLR.Utilities].[CLR.Utilities.Concatenate]
GO
Box 1: Modify the TRUSTWORTHY property of the database.
Box 2: Execute the CREATE ASSEMBLY statement.
Box 3: Execute the CREATE AGGREGATE statement.
Note:
* TRUSTWORTHY CREATE signature
The TRUSTWORTHY property indicates whether the instance of SQL Server trusts the database and the contents within it.
* CREATE AGGREGATE
Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework. For the Database Engine to bind the aggregate function to its implementation, the .NET Framework assembly that contains the implementation must first be uploaded into an instance of SQL
Server by using a CREATE ASSEMBLY statement.
* Example:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO -
CREATE ASSEMBLY [CLR.Utilities] FROM 'C:\Path\To\File\CLR.Utilities.dll' WITH PERMISSION_SET = UNSAFE
GO -
CREATE AGGREGATE [dbo].[Concatenate] (@input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME [CLR.Utilities].[CLR.Utilities.Concatenate]
GO
Question #18
You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows. The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers'1,2,3,4,5';
What should you do?
Database1 has a table named Customers. Customers contains more than 1 million rows. The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers'1,2,3,4,5';
What should you do?
Question #19
DRAG DROP -
You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
Correct Answer:
Box 1: Start a SQL Server Profiler trace.
Box 2: Start a data collection by using Performance monitor.
Box 3: Save the SQL Profiler trace.
Box 4: Save the Performance Monitor data.
Box 5: Import the performance data into SQL Server Profiler.
Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with
Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.
Box 1: Start a SQL Server Profiler trace.
Box 2: Start a data collection by using Performance monitor.
Box 3: Save the SQL Profiler trace.
Box 4: Save the Performance Monitor data.
Box 5: Import the performance data into SQL Server Profiler.
Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with
Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.
Question #20
DRAG DROP -
You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf.
Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:
You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf.
Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:
All Pages