Category: Microsoft SQL Server 2012

[2017-October-Version] Free Download Real Microsoft 70-463 Dumps Microsoft SQL Server 2012 Exam Study Guide Youtube Training Online

Where can one get Notes for Microsoft 70-463 dumps? “Implementing a Data Warehouse with Microsoft SQL Server 2012” is the name of Microsoft 70-463 exam dumps which covers all the knowledge points of the real Microsoft exam. Free download real Microsoft 70-463 dumps Microsoft SQL Server 2012 exam study guide youtube training online. Pass4itsure Microsoft 70-463 dumps exam questions answers are updated (230 Q&As) are verified by experts.

The associated certifications of 70-463 dumps is Microsoft SQL Server 2012. To take this https://www.pass4itsure.com/70-463.html dumps exam, you need to have actual experience in computing work, and you need to have solid background of computer knowledge.

Exam Code: 70-463
Exam Name: Implementing a Data Warehouse with Microsoft SQL Server 2012
Q&As: 230

[2017-October-Version Microsoft 70-463 Dumps From Google Drive]: https://drive.google.com/open?id=0BwxjZr-ZDwwWSHdVQTJwd19hV3c

[2017-October-Version Microsoft 70-243 Dumps From Google Drive]: https://drive.google.com/open?id=0BwxjZr-ZDwwWM0xDUFlIbnhOOE0

70-463 dumps

QUESTION 53
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.
The package reads data from a source system. Depending on the value in a source column, the package redirects rows to one of five different data flow paths. You need to add a data flow transformation to support the package redirection.
Which data flow transformation should you use?
A. Conditional Split
B. pjvot
C. Multicast
D. Lookup
70-463 exam 
Correct Answer: A
QUESTION 54
You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages. All packages in the project must connect to the same database. The server name for the database must be
set by using a parameter named ParamConnection when any package in the project is executed. You need to develop this project with the least amount of development effort. What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. Create a package parameter named ConnectionName in each package.
B. Edit each package Connection Manager. Set the ConnectionName property to @
[$Project::ParamConnection].
C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @
[$Project::ParamConnection].
D. Set the Sensitive property of the parameter to True.
E. Create a project parameter named ConnectionName.
F. Set the Required property of the parameter to True.
Correct Answer: BEF
Question No : 55 – (Topic 1)  You are writing a SQL Server Integration Services (SSIS) package that transfers data from
a legacy system. Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes. You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.What should you do?
A. Add a data tap on the output of a component in the package data flow.
B. Deploy the package by using an msi file.
C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
D. uses the dtutil /copy command.
E. Deploy the package to the Integration Services catalog by using dtutil and use SQL
Server to store the configuration.
F. Create an OnError event handler.
G. uses the Project Deployment Wizard.
H. Use the gacutil command.
I. Create a reusable custom logging component.
J. Run the package by using the dtexec /rep /conn command.
K. Run the package by using the dtexec /dumperror /conn command.
70-463 dumps 
Answer: A
Reference:
Question No : 56 – (Topic 1)  You are deploying a new SQL Server Integration Services (SSIS) project to the test
environment. A package in the project uses a custom task component. You need to ensure that the custom object is deployed on the test environment correctly. What should you do?
A. Run the package by using the dtexec /rep /conn command.
B. Create a reusable custom logging component.
C. Create an OnError event handler.
D. Use the gacutil command.
E. Use the dtutil /copy command.
F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
G. Run the package by using the dtexec /dumperror /conn command.
H. Use the Project Deployment Wizard.
I. Deploy the package by using an msi file.
J. Add a data tap on the output of a component in the package data flow.
K. Run the package by using the dtexecui.exe utility and the SQL Log provider.
Answer: D
Reference:
Question No : 57 – (Topic 1) You are deploying a project to the SQL Server Integration Services (SSIS) catalog. You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.
What should you do?
A. Create an On Error event handler.
B. Add a data tap on the output of a component in the package data flow.
C. Use the gacutil command.
D. Create a reusable custom logging component.
E. Run the package by using the dtexecui.exe utility and the SQL Log provider.
F. Run the package by using the dtexec /rep /conn command.
G. Use the Integration Services Deployment Wizard.
H. Run the package by using the dtexec /dumperror /conn command.
I. Use the dtutil /copy command.
J. Deploy the package to the Integration Services catalog by using dtutil and use SQL
Server to store the configuration.
K. Deploy the package by using an msi file.
70-463 pdf 
Answer: G
Reference:
Question No : 58 – (Topic 1) To ease the debugging of packages, you standardize the SQL Server Integration Services
(SSIS) package logging methodology. The methodology has the following requirements: Centralized logging in SQL Server
Simple deployment Availability of log information through reports or T-SQL Automatic purge of older log entries Configurable log details You need to configure a logging methodology that meets the requirements while minimizing
the amount of deployment and development effort. What should you do?
A. Deploy the package by using an msi file.
B. Use the gacutil command.
C. Create an OnError event handler.
D. Create a reusable custom logging component.
E. Use the dtutil /copy command.
F. Use the Project Deployment Wizard.
G. Run the package by using the dtexec /rep /conn command.
H. Add a data tap on the output of a component in the package data flow.
I. Run the package by using the dtexec /dumperror /conn command.
J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL
Server to store the configuration.
Answer: I
Question No : 59 – (Topic 1)
You are creating a SQL Server Master Data Services (MDS) model for a company. The source data for the company is stored in a single table that contains the manager-to subordinate relationships. You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
A. Natural
B. Explicit
C. Parent
D. Recursive
70-463 vce 
Answer: D

Maybe you will have rather hard time in preparing for this 70-463 dumps exam. If with a proper https://www.pass4itsure.com/70-463.html dumps material, you will reach your goal in a relatively fast way.

Read More Youtube:https://youtu.be/LDY-4kuGU20

Microsoft 70-461 Exam PDF, 100% Pass Guarantee Microsoft 70-461 Practice Test Latest Version PDF&VCE

Welcome to download the newest Pass4itsure 70-414 dumps:

Our Microsoft 70-461 with Explanations presents to you the most tried and tested methods of preparation for the actual exam. The Flydumps provides a very detailed preparation for your exam preparation, giving you answers to the entire exam question with the added explanation of which answer is right and why. These answers are prepared by professionals who have had years of experience and are fully competent to give you the best and the most excellent way to prepare for your actual exam.

QUESTION 20
You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C. Create an AFTER UPDATE trigger on the view.
D. Create an INSTEAD OF UPDATE trigger on the view. Correct Answer: D

Explanation Explanation/Reference:
Explanation:

QUESTION 21
You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:

Users are able to use single INSERT statements or INSERT…SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END
B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END
C. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber = EmployeeNumber FROM inserted INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName) INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber End
D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End
Correct Answer: B Explanation

Explanation/Reference:
QUESTION 22
You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition: You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.

Which Transact-SQL query should you use?
A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED (RetailPrice, WholesalePrice) – – Create Audit Records
B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) – – Create Audit Records
C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED (RetailPrice, WholesalePrice) – – Create Audit Records
D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) – – Create Audit Records

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
QUESTION 23
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

Correct Answer: C Explanation
Explanation/Reference:

QUESTION 24
You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.
Which Transact-SQL operator should you use?
A. CROSS JOIN
B. CROSS APPLY
C. PIVOT
D. UNPIVOT

Correct Answer: C Explanation
Explanation/Reference:

QUESTION 25
You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT CustomerID FROM Customer WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

Correct Answer: BD Explanation
Explanation/Reference:
QUESTION 26
You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country

Sub totals only at the city level and country level

A grand total of the sales amount
Which Transact-SQL query should you use?
A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())
B. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
C. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Be careful with this question,because on exam can be different options for answer. And none of them is
correct : D You should report this question.
QUESTION 27
You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?
A. Float
B. Money
C. Smallmoney
D. Numeric

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Numeric is the only one in the list that can give a fixed precision and scale.
QUESTION 28
You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Correct Answer: AB Explanation
Explanation/Reference:
Explanation: Verified the answers as correct. Reference:
QUESTION 29
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Flydumps.com will provide you with the most updates material to prepare for the tests all the Microsoft 70-461 torrent are available at the site. Studying with dumps makes it much easier to pass the certification. Number of networking downloads including the Microsoft 70-461 download are available on the website. Various websites offering such information have information in various formats you can easily download the format that is suitable for you it can be in Microsoft 70-461 Testing Engine or in html.

Welcome to download the newest Pass4itsure 70-414 dumps: http://www.pass4itsure.com/70-414.html

Microsoft 70-461 Exam PDF, 100% Pass Guarantee Microsoft 70-461 Practice Test Latest Version PDF&VCE

 

Microsoft 070-448 Practice Exam, Download Microsoft 070-448 Exam Dumps With Accurate Answers

Welcome to download the newest Examwind 642-467 Study Guide dumps: http://www.examwind.com/642-467.html

NEW DUMPS– How to prepare the Microsoft 070-448 exam and to 100 percent pass it without any problem? Microsoft 070-448 just published the newest Microsoft 070-448 Flydumps with all the new updated exam questions and answers.You can get the free new version on Flydumps.com

QUESTION 21
You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution has the following components:
A dimension named DimTime that contains an attribute named Month

A measure group named FactSales that references the DimTime dimension
You need to ensure that the aggregations designed on FactSales always include the Month attribute.
What should you do?
A.
Set the Usage property appropriately.
B.
Set the IsAggregatable property appropriately.
C.
Set the AggregationUsage property appropriately.
D.
Set the AttributeHierarchyEnabled property appropriately.
Correct Answer: C
Explanation Explanation/Reference:
QUESTION 22
You create a SQL Server 2008 Analysis Services (SSAS) solution.
You enable proactive caching for a partition.
You need to ensure that SSAS can query relational data when multidimensional storage is being updated.
What should you do?

A. Set the OnlineMode property for the partition to Immediate.
B. Set the ProcessingMode property for the partition to Regular.
C. Set the OnlineMode property for the partition to OnCacheComplete.
D. Set the ProcessingMode property for the partition to LazyAggregations. Correct Answer: A

Explanation Explanation/Reference:
QUESTION 23
You create a SQL Server 2008 Analysis Services (SSAS) solution.
You create a dimension named DimCustomer that contains the following three attributes:
Customer

City

Country
The Customer attribute is the key. The DimCustomer dimension is related to a measure group named Sales that has the Granularity property set to City.
You need to ensure that you can retrieve measure values based on the City and Country attributes.
What should you do?
A. Set the source attribute to Customer and the related attribute to City.Set the source attribute to City and the related attribute to Country.
B. Set the source attribute to City and the related attribute to Customer.Set the source attribute to Country and the related attribute to City.
C. Set the source attribute to Customer and the related attribute to City.Set the source attribute to Customer and the related attribute to Country.
D. Set the source attribute to City and the related attribute to Customer.Set the source attribute to Country and the related attribute to Customer.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 24
You create a SQL Server 2008 Analysis Services (SSAS) solution. The cube structure is as shown in the following exhibit. (Click the Exhibit button.)

The Ownership column contains decimal values that represent percentage of ownership. The sum of all the values in the Ownership column for a single DimAccount member is 100%. You create the following measures:
Ownership

Amount
You need to ensure that when the Amount measure is calculated against the DimCustomer dimension, the weighted value is calculated based on the Ownership measure.
What should you do?
A. Set the MeasureExpression property of the Amount measure to [Amount]*[Ownership].
B. Set the FormatString property of the Amount measure to [Amount]*[Ownership].
C. Set the MeasureExpression property of the Amount measure to [DimCustomer]. [Amount]*[Ownership].
D. Set the FormatString property of the Amount measure to [DimCustomer]. [Amount]*[Ownership].

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 25
You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution includes a table named FactOrders that contains two columns as shown in the following table:

You need to create a measure that returns the number of customers who have placed orders. What should you do?
A. Set the Usage property to Count of rows and the Source Column property to OrderKey.
B. Set the Usage property to Distinct count and the Source Column property to OrderKey.
C. Set the Usage property to Distinct count and the Source Column property to CustomerKey.
D. Set the Usage property to Count of rows and the Source Column property to CustomerKey. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 26
You create a SQL Server 2008 Analysis Services (SSAS) solution.
You create a dimension named DimProduct that contains an attribute named Price. You use the
DimProduct dimension in a cube.
You need to ensure that aggregations are not associated with the Price attribute.
What should you do?

A. Set the IsAggregatable property of the Price attribute to False.
B. Set the AttributeHierarchyEnabled property of the Price attribute to False.
C. Set the MembersWithData property of the Price attribute to NonLeafDataHidden.
D. Set the GroupingBehavior property of the Price attribute to DiscourageGrouping. Correct Answer: B

Explanation Explanation/Reference:
QUESTION 27
You design a SQL Server 2008 Integration Services (SSIS) package. The package extracts data from flat files and loads this data to a SQL Server 2008 database.
The package contains two Boolean variables named @IsWeekday and @IsHoliday. When the package is executed on week days, the @IsWeekday variable is set to True. When the package is executed on holidays, the @IsHoliday variable is set to True.
You need to implement the following business logic in the control flow of the package:
When the .IsWeekday variable is set to True, the Perform Weekday Processing Data Flow task is executed.

When the .IsWeekday variable is set to False, the Perform Weekend Processing Data Flow task is
executed.

When the @IsHoliday variable is set to True, the Perform Holiday Processing Data Flow task is executed.
Which control flow logic should you implement?

A. Option A
B. Option B
C. Option C
D. Option D Correct Answer: A

Explanation Explanation/Reference:
QUESTION 28
You develop a SQL Server 2008 Integration Services (SSIS) package.
You need to modify the path where the package writes event data to a text file.
What should you do?

A. Modify the log provider.
B. Modify the Connection Manager.
C. Modify the Flat File Destination.
D. Move the package file to the new path. Correct Answer: B

Explanation Explanation/Reference:
QUESTION 29
You are editing a SQL Server 2008 Integration Services (SSIS) package by using Business Intelligence Development Studio. The package uses a custom data flow component. The package runs successfully.
You attempt to add another instance of the custom data flow component to the package. The data flow
component does not appear in the toolbox or in the Choose Toolbox Items dialog box.
You need to add the custom data flow component to the toolbox.
What should you do?

A. Copy the assembly to the appropriate folder.
B. Add the assembly to the Global Assembly Cache.
C. Register the assembly by using the REGSVR32 utility.
D. Register the assembly by using the Assembly Registration utility (REGASM.EXE). Correct Answer: A

Explanation Explanation/Reference:
QUESTION 30
You design a SQL Server 2008 Integration Services (SSIS) data flow that inserts data from a source query to a destination table.
You need to insert only those rows that do not already exist in the destination table.
What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Use the Merge transformation.
B. Use the Lookup transformation.
C. Use the Union All transformation.
D. Use the Merge Join transformation.
E. Use the Fuzzy Lookup transformation.
F. Use the Fuzzy Grouping transformation. Correct Answer: BD

Explanation Explanation/Reference:
QUESTION 31
You implement a SQL Server 2008 Integration Services (SSIS) package.
The package contains the following components:
A data flow task contained in a For Loop container

A Flat File destination component that uses a Flat File connection manager

An OnError event handler
When an error occurs in the package, you plan to write diagnostic information from an expression that includes the version of the package to a text file.
You need to ensure that the required information is written to the text file.
What should you do?
A. Use the VersionGUID variable.
B. Use the ExecutionGUID variable.
C. Use the SourceID variable.
D. Use the TaskID variable.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 32
You modify a SQL Server 2008 Integration Services (SSIS) package.
The package extracts data from a text file and loads this data to a SQL Server 2008 table as shown in the following two exhibits.
You need to update the package to extract data from multiple text files of the same directory.
Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A. Add a variable to the package.
B. Add a variable to the data flow task.
C. Add a Foreach Loop container to the package.
D. Add a For Loop container to the package.
E. Add an expression to the Flat File Source component.
F. Add an expression to the Flat File connection manager.

Correct Answer: ACF Explanation
Explanation/Reference:
QUESTION 33
You are developing a SQL Server 2008 Integration Services (SSIS) data flow that extracts data from two sources. Each source database is located on a different server. Each source is based on a SELECT statement that uses an ORDER BY clause. The data flow is as shown in the following diagram.

When the paths from the two sources are connected to a Merge Join component, the following error message is displayed:
“Data Flow Task: The input is not sorted. The ‘input “Merge Join Left Input” (80)’ must be sorted.” You need to ensure that data from each query is included in each row in the data flow. You want to achieve this goal by using minimum amount of execution time.
What should you do?
A. Add a Sort transformation for each data flow.
B. Add an OLE DB Command transformation for each data flow.
C. Update the sorting properties in each source component.
D. Replace the Merge Join transformation with a Union All transformation. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 34
You are developing a SQL Server 2008 Integration Services (SSIS) package.
The package uses an OLE DB connection manager to connect to a third-party database server. The
database does not support Integrated Windows authentication. You create an XML configuration file that

provides configuration for the connection manager.
You add the configuration file to the package.
When the package executes, you receive an error message which indicates that login failure occurred.
You need to execute the package successfully.
What should you do?

A. Edit the XML configuration file.
B. Use an indirect XML configuration.
C. Run the package on the same server as the third-party database server.
D. Use a Microsoft ADO.NET connection manager instead of an OLE DB connection manager.

Correct Answer: A Explanation Explanation/Reference:
QUESTION 35
You create a SQL Server 2008 Reporting Services (SSRS) report. You plan to create a list of customers by using a List control. For each customer in the List control, you plan to include a table that contains the orders for that customer. The customer data is stored in the Customers table in the data source, and the order data is stored in the Orders table. You need to define the required dataset or datasets for this report. What should you do?
A. Create a query that joins the two tables in the data source by using the JOIN clause. Add the query as a single data set to the report.
B. Create a query that combines the two tables in the data source by using the UNION operator. Add the query as a single data set to the report.
C. Create a query for each table. Group both tables on the column that is used to join the tables. Add both queries as separate data sets to the report.
D. Create a query for each table. Sort both tables on the column that is used to join the tables. Add both queries as separate data sets to the report.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 36
You create a SQL Server 2008 Reporting Services (SSRS) report.

The report contains a table.
You need to ensure that alternate rows in the table have a pale green background.

Which code segment should you use for each text box?

A. =iif(RowNumber(Nothing) / 2, “PaleGreen”, “White”)
B. =iif(RowNumber(Nothing) Mod 2, “PaleGreen”, “White”)
C. =iif(CountRows() / 2, “PaleGreen”, “White”)
D. =iif(CountRows() Mod 2, “PaleGreen”, “White”)
Correct Answer: B Explanation

Explanation/Reference:
QUESTION 37
You develop a SQL Server 2008 Reporting Services (SSRS) report.
A Multidimensional Expressions (MDX) query used in the report contains a parameter to display sales data for various product categories. You write the following query.

You discover that the query generates an error when executed.
You need to ensure that the query executes successfully. What should you do?
A. Remove the NON EMPTY clauses.
B. Replace the CHAPTERS axis with the ROWS axis.
C. Replace the CHAPTERS axis with the SECTIONS axis.
D. Rewrite the query to display the Order Quantity on the COLUMNS axis, the Calendar Year on the ROWS axis, and the Category data on the PAGES axis.

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 38
You design a report by using SQL Server 2008 Reporting Services (SSRS).
The report uses a Table data region and contains a dataset. The dataset has a column named LastName.
You need to ensure that the report items are grouped by the first character in the LastName column.
Which expression in the Group properties dialog box should you use?

A. =First(Fields!LastName)
B. =Fields!LastName.Value
C. =First(Fields!LastName.Value)
D. =Fields!LastName.Value.Substring(0,1) Correct Answer: D

Explanation Explanation/Reference:
QUESTION 39
You create a report in SQL Server 2008 Reporting Services (SSRS) solution. You write the following code segment in the report.

The code segment generates errors when compiled.
You need to ensure that the following requirements are met:
The code segment compiles successfully.

The GetCmFromInches function can be called from other reports.
What should you do?
A. Remove the Private keyword from the function definition.
B. Remove the Shared keyword from the function definition.
C. Declare the function as Public Shared instead of Private Shared.
D. Create a custom assembly by using Microsoft Visual Basic .NET. Declare the function as Public
Shared. Correct Answer: D Explanation Explanation/Reference:
QUESTION 40
You develop a SQL Server 2008 Reporting Services (SSRS) solution.
You add a report that contains a single dataset and a query parameter.
You need to ensure that the report executes without user interaction from the Report Manager.
What should you do?

A. Configure the report to use snapshot execution.
B. Configure available values for the parameter from the dataset.
C. Configure the default value for the parameter based on an expression.
D. Configure the parameter data to refresh whenever the parameter is refreshed. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 41
You design a report by using SQL Server 2008 Reporting Services (SSRS).

The report will be included in a Windows Forms application that is distributed to remote users.
These users have no access to the report server.
You need to ensure that the application renders the report correctly.
What should you do?

A. Use the ReportViewer control along with the LocalReport property.
B. Use the ReportViewer control along with the RemoteReport property.
C. Use the Render method of the Reporting Services Simple Object Access Protocol (SOAP) API.
D. Use the RenderStream method of the Reporting Services Simple Object Access Protocol (SOAP) API. Correct Answer: A

Explanation Explanation/Reference:
QUESTION 42
You design a report by using SQL Server 2008 Reporting Services (SSRS).
Detail information is displayed each time the users render the report.
You need to ensure that the following requirements are met:

Summary information is displayed when the report is initially rendered.

Detail information is displayed only when the users click a column header. What should you do?

A.
On the column that contains detail information, set the hidden property to True. Set the Visibility can be
toggled by another report item property to True.
B. On the column that contains detail information, set the hidden property to False. Set the Visibility can be toggled by another report item property to False.
C. On the column that contains detail information, set the hidden property to True. Set the Visibility can be toggled by another report item property to False.
D. On the column that contains detail information, set the hidden property to False. Set the Visibility can be toggled by another report item property to True.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 43
You develop a SQL Server 2008 Reporting Services (SSRS) report. The report contains a parameter named Category that is used to filter a list of products. The list uses a dataset that contains the following query:

You modify the report parameter to allow multiple values. You need to modify the report to help search for multiple Category values by using minimum amount of development effort. What should you do?
A. Implement the query in a view.
B. Implement the query in a table-valued user-defined function.
C. Modify the query to use the IN operator instead of the = operator.
D. Implement the query in a stored procedure that uses dynamic SQL to execute the query. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 44
You are developing a SQL Server 2008 Integration Services (SSIS) project in Business Intelligence Development Studio.
You configure the project properties as shown in the following image.

You need to create a deployment manifest for all packages in the project. What should you do?
A. Build the Integration Services project.
B. Modify the AllowConfigurationChanges property of the project.
C. Copy each package. Rename each copy with a .SSISDeploymentManifest file extension.
D. View the code of each Data Transformation Services (DTS) package. Copy the first <DTS:Property> xml node to a file with a .SSISDeploymentManifest file extension.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 45
You create a SQL Server 2008 Integration Services (SSIS) package by using SQL Server 2008.
You use a development server named SQL_DEV1. There is no network connectivity between the
development server and the production server.
You deploy the package to the SQL_PROD1 server.
The package fails to execute. The error message states that the package cannot connect to the

SQL_DEV1 server.
You need to modify the package to refer to the SQL_PROD1 server when being executed.
What should you do?

A. Modify the properties of the package to use the DontSaveSensitive package protection level.
B. Modify the deployment manifest file in Microsoft Notepad, and then modify the connection string manually.
C. On the Connection Managers tab of the Execute Package utility, select the Connection Manager check box, and then modify the connection string.
D. Modify the properties of the package to use the ServerStorage package protection level. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 46
You are developing a SQL Server 2008 Integration Services (SSIS) package.
The package performs the following tasks:
Processes multiple files by using a ForEach Loopcontainer.

Imports the contents of the files to a table by using a Data Flowtask.

Logs the results of the status into a table by using an Execute SQL task.
You need to ensure that all the tasks of the package except the Execute SQL task execute within a single transaction.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. In the Foreach Loop container, configure the TransactionOption property to NotSupported
B. In the Data Flow task, configure the TransactionOption property to Required
C. In the package, configure the TransactionOption property to Required
D. In the package, configure the TransactionOption property to NotSupported
E. In the Execute SQL task, configure the TransactionOption property to NotSupported
F. In the Execute SQL task, configure the TransactionOption property to Supported. Correct Answer: CE

Explanation Explanation/Reference:
QUESTION 47
You design a package by using SQL Server 2008 Integration Services (SSIS). The package is designed by using your local development environment.
The package extracts data from a remote server and inserts it in the PROD database on the local SQL Server 2008 instance. You deploy the package to the MSDB database on the production server.
You need to ensure that only users that belong to an appropriate database role can modify and execute the deployed package. You also need to ensure that these users cannot access other packages that are deployed to the server.
What should you do?
A. Assign the users to the db_ssisoperator role.
B. Assign the users to the db_ssisltduser role.
C. Assign the users to a custom database role in the PROD database.
D. Assign the users to a custom database role in the MSDB database. Correct Answer: D

Explanation Explanation/Reference:
QUESTION 48
You develop a package by using SQL Server 2008 Integration Services (SSIS).
You use a local instance of SSIS to develop and test the package.
The package uses an XML configuration file that contains a connection string. The connection string

includes a user name and a password to connect to the source data. You deploy the package to the
production server. You discover that validation errors occur when the package is being executed.
You need to ensure that the package executes successfully in the production environment.
What should you do?

A. Configure the package to use the DontSaveSensitive package protection level.
B. Configure the package to use the EncryptSensitiveWithPassword package protection level.
C. Configure the package to use the EncryptAllWithUserKey package protection level.
D. Remove the user name and the password from the connection string before you deploy the package. Correct Answer: B

Explanation Explanation/Reference:
QUESTION 49
You plan to use SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 failover cluster. You have a complex package that takes a long time to run. You need to ensure that if the package executes during a failover, the package can be resumed at the point of failure. What should you do?
A. Cluster the SSIS service. Add the SSIS service to its own cluster resource group.
B. Cluster the SSIS service. Add the SSIS service to the SQL Server cluster resource group.
C. Implement checkpoints within your package. Restart the package whenever a failover occurs.
D. Implement transactions within your package. Restart the package whenever a failover occurs. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 50
You develop a SQL Server 2008 Integration Services (SSIS) package.

The package downloads several files from a remote FTP server by using multiple FTP tasks, and then
processes data by using multiple Execute SQL tasks.
You need to configure the package to restart at the point of failure after the package downloads the files.
What should you do?

A. Configure the FTP tasks to support transactions.
B. Configure the Execute SQL tasks to support transactions.
C. Move all the FTP tasks to a sequence container.
D. Configure the package to use a checkpoint. Correct Answer: D

Explanation Explanation/Reference: QUESTION 51
You develop a SQL Server 2008 Reporting Services (SSRS) report.
The report uses the “User!UserID” expression in a text box within the report header.
You need to ensure that data-driven subscriptions successfully execute for this report.
What should you do?

A. Remove the expression from the report header.
B. Configure the report to use no credentials.
C. Configure the report to use stored credentials.
D. Modify the expression in the report header as “User!UserName.” Correct Answer: C

Explanation Explanation/Reference:
QUESTION 52
You maintain a report in a SQL Server 2008 Reporting Services (SSRS) instance.
The report contains query parameters that allow users to filter the contents of the report. The report queries the data source randomly. You configure the report to be rendered from a report execution snapshot. You need to ensure that users
can continue to use parameters to filter the report. What should you do?
A. Add a default value to all parameters.
B. Modify the datasets to use the Table query type.
C. Remove the filters from the dataset queries. Use the parameters to filter the datasets.
D. Move the queries to the stored procedures. Use the stored procedures to populate and filter the datasets of the report.

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 53
You are managing the security in SQL Server 2008 Reporting Services (SSRS).

You assign a new user to a role. You need to grant the permissions to the user to publish reports and
manage content only in a private folder.
What should you do?

A. Assign the user to the Publisher role.
B. Assign the user to the My Reports role.
C. Assign the user to the Content Manager and the Browser roles.
D. Assign the user to the Content Manager and the Report Builder roles. Correct Answer: B

Explanation Explanation/Reference: QUESTION 54
You install and configure SQL Server 2008 Reporting Services (SSRS) instance. The users of the report server plan to deliver reports through e-mail messages. You need to configure the report server for e-mail message delivery by using a remote SMTP server. What should you do?
A. Modify the rssrvpolicy.config file.
B. Modify the RSReportServer.config file.
C. Modify the Web.config file.
D. Modify the Global.asax file.

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 55
You maintain an instance of SQL Server 2008 Reporting Services (SSRS). The instance contains several
reports.

You deploy a report project on the instance by using the Business Intelligence Development Studio (BIDS).
You discover that the production data sources on the instance are replaced by the test data sources of the
report project.
You need to ensure that when the report project is deployed in future, the existing reports are unaffected.

What should you do?

A. Configure the OverwriteDataSources property.
B. Configure the TargetServerURL property.
C. Configure the TargetDataSourceFolder property.
D. Configure the TargetReportFolder property.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 56
You implement a SQL Server 2008 Integration Services (SSIS) package. The package contains the following components:
A Foreach Loop container for the Collection property as shown in the following exhibit. (Click the Exhibit button.)

A Foreach Loop container for the Variable Mappings property as shown in the following exhibit. (Click the Exhibit button.)

A String variable named FileName.

A String variable named DirectoryPath.

Both PDF and software format demos for Microsoft 070-448 exam dumps are offered by Flydumps for free.You can try Microsoft 070-448 free demo before you decide to buy the full version practice test.Microsoft 070-448 exam dumps details are researched and produced by our Professional Certification Experts who are constantly using industry experience to produce precise, and logical.Microsoft 070-448 dumps will not only help you pass in one attempt,but also save your valuable time.

Welcome to download the newest Examwind 642-467 Study Guide dumps: http://www.examwind.com/642-467.html

Microsoft 070-448 Practice Exam, Download Microsoft 070-448 Exam Dumps With Accurate Answers

Microsoft 070-432 Exam Dumps, 100% Pass Microsoft 070-432 PDF On Sale

Welcome to download the newest Pass4itsure 352-011 Practical Exam dumps: http://www.pass4itsure.com/352-011.html

Important Info: Microsoft 070-432 now is offering the latest and 100 percent pass Microsoft 070-432 exam questions and answers, by training our vce dumps you can pass Microsoft 070-432 exam easily and quickly. Visit the site Flydumps.com to get the free Microsoft 070-432 exam vce and pdf dumps and FREE VCE PLAYER!

QUESTION 21
You administer a SQL Server 2008 instance that runs on a Windows Server 2003 computer. The instance uses mixed authentication mode.
You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. You
also need to ensure that the password complexity rules continue to be enforced.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Use the ALTER LOGIN … HASHED statement to modify all logins.
B. Use the ALTER LOGIN … CHECK_POLICY = ON statement to modify all logins.
C. Use the ALTER LOGIN … CHECK_EXPIRATION = ON statement to modify all logins.
D. Use Policy-Based Management to create a policy that prevents any violation of these rules.
E. Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.

Correct Answer: BD QUESTION 22
You are reviewing and configuring the security of a SQL Server 2008 instance that contains database DB1.
The security audit policy specifies the following requirements:
Only successful and failed logon attempts are recorded in log files. The SQL Server instance is shut down if records cannot be written to the log files. You need to

configure the SQL Server instance to comply with the security audit policy.
Which Transact-SQL statements should you run?

A. sp_configure ‘show advanced options’, l:GORECONFIGURE:GOsp_configure ‘default trace enabled’. 1 ;GORECONFIGUREGO
B. sp_configure ‘show advanced options’. 1 ;GORECONFIGURE;GOsp_configure ‘common criteria compliance enabled’. 1 ;GORECONFIGUREGO
C. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH =’\\MAIN_SERVER\AuditV ) WITH (ON_FAILURE = SHUTDOWN) ;GOCREATE DATABASE AUDIT SPECIFICATION Audit_SpecificationFOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP).ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON);GO ALTER SERVER AUDIT SrvAucfit WITH (STATE=ON);GO
D. CREATE SERVER AUDIT Srv_Audit TO FILE (FILEPATH =`\\MAIN_SERVER\Audit\’) WITH (ON_FAILURE = SHUTDOWN);GOCREATE SERVER AUDIT SPECIFICATION Audit_SpecificationFOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP),ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON);GOALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);GO

Correct Answer: D
QUESTION 23
You are managing a SQL Server 2008 instance for Home.com. You use a Microsoft SQL Server 2008 Analysis Services (SSAS) instance. A data mining structure should be deployed which contains the Microsoft Clustering mining model. From Scalable K- Means to Non-scalable K-Means.
You should alter the attribute of the mining model. Which is the correct answer?
A. You should change MODELLING_CARDINALITY
B. You should change CLUSTERING_METHOD
C. You should change INDEXDEFRAG
D. You should change SHRINKFILE

Correct Answer: B QUESTION 24
You are mastering the company database, including the SQL Server 2005 database which include a table called Production. Product. The table has more than 50
columns The most common queries are listed below Transact-SQL statements. –QUERY 1SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM
[Production]. [Product]WHERE [ProductLine] = ‘T’ –QUERY 2SELECT * FROM [Production].
[Product] ORDER BY [Name]

When you are minimizing the space required for the indexes.

You should select the best indexes to maintain the most common searched to the Production. Product tableWhich is the correct answer?

A. You should develop a clustered index on the Name column.
B. You should develop a nonclustered index on the Name column.
C. You should develop a clustered index on the ProductLine, ListPrice, Class and Style columns.
D. You should develop a nonclustered index on the ProductLine, ListPrice columns.

Correct Answer: A QUESTION 25
You administer a SQL Server 200S instance. The instance contains an On-Line Analytical Processing (OLAP) database along with a dimension table named
Customers.
The data of the Customers table is updated every hour.

The Customers table contains redundant data.
You need to conserve the disk space used to store the Customers table.
Which compression technology should you use?

A. Row compression
B. Page compression
C. Backup compression
D. Windows NITS file system compression

Correct Answer: B QUESTION 26
You maintain a SQL Server 2008 instance that contains a database named AdventureWorks.

The AdventureWorks database contains a table named OrderSpecs. The OrderSpecs table has a clustered primary key named OrderId on the OrderId column.
The OrderSpecs table has a single XML column named ProductSpecs that stores XML data, and there is an XML index on the same XML column.
You add a new column named ProductId to the OrderSpecs table.
You need to include ProductId in the primary key.
What should you do?

A. Alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.
B. Drop the XML index on the table. Modify the primary key. Recreate the XML index.
C. Disable the XML index on the ProductSpecs column. Modify the primary key. Enable the XML index on the ProductSpecs column.
D. Move the XML data to a temporary table. Clear the XML data from the original table by setting the ProductSpecs column to NULL. Modify the primary key. Repopulate the ProductSpecs column.

Correct Answer: B QUESTION 27
You administer a SQL Server 2008 instance.
You use the SQL Server Import and Export Wizard to export data from SQL Server to a Microsoft Excel file.
You save the package definition to a file.
You need to reexecute the package from the command line.
Which utility should you use?

A. bcp.exe
B. dta.exe
C. dtexec.exe
D. sqlmaint.exe Correct Answer: C QUESTION 28
You maintain a SQL Server 2008 instance that contains a database named AdventureWorks. The AdventureWorks database contains two tables as shown in the following table:

The OrderHistory table contains two table partitions: one partition contains all data and the other partition is empty.
You plan to develop a process to move data from the partition that contains data for the oldest month of the Orders table to the appropriate partition of the
OrderHistory table. You need to ensure that the process can be repeated at the end of every month.
What should you do?

A. Alter the partition function of the Orders table by using the split option. Alter the Orders table by using the merge option.
B. Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the split option.
C. Alter the partition function of the Orders table by using the split option. Alter the OrderHistory table by using the split option. Alter the partition function of the OrderHistory table by using the merge option. Alter the Orders table by using the merge option.
D. Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the merge option. Alter the Orders table by using the merge option.

Correct Answer: D QUESTION 29
You are mastering the company database, with SQL Server 2008 computer named SQLTEST1The master database on SQLTEST1 is down. Some SQL Server Agent jobs should be included in the SQLTEST1.The whole database should be backed up every day. You rebuild and restore the master database. You should make sure that SQLTEST1 functions as it did before the database lost. Which is the correct answer?
A. You should reserve the model and master databases for the database.
B. You should reserve the Log Transaction for the database.
C. You should rebuild the whole table schema for the database.
D. You should rebuild the whole SQL Server Web Service for the database.

Correct Answer: A QUESTION 30
You administer a SQL Server 2008 instance that contains a database named AdventureWorks. The AdventureWorks database contains a table named Orders.
The Orders table has several indexes and a very large number of rows. The database supports an enterprise Web environment that is constantly used.
Thirty percent of the indexes on the Orders table is fragmented.
You need to defragment the indexes by ensuring that the effect on database availability is minimized.
Which option should you include in the ALTER INDEX statement?

A. Online
B. Fill factor
C. Pad index
D. Sort in tempdb

Correct Answer: A QUESTION 31
You administer a SQL Server 2008 instance.
You need to find out the number of users who access the SQL Server instance by using Windows System Monitor.
Which performance object should you capture?

A. SQLServer:Exec Statistics
B. SQLServenBuffer Manager
C. SQLServer:Access Methods
D. SQLServer:General Statistics

Correct Answer: D QUESTION 32
You administer a SQL Server 2008 instance. The instance contains databases for a finance application and a manufacturing application. You configure the Resource Governor to limit the maximum CPU query time allowed by the applications. The administrator of the manufacturing application reports that certain reports no longer execute successfully. You need to modify the Resource Governor configuration to allow the manufacturing application to consume more CPU time.
Which Resource Governor component should you configure?
A. The workload group that is used by default
B. The workload group that is used by the finance application
C. The classifier function that identifies the finance application
D. The workload group that is used by the manufacturing application Correct Answer: D QUESTION 33
You troubleshoot the performance of a SQL Server 2008 instance.
You need to identify the longest-running common language runtime (CLR) queries.
Which dynamic management view should you use?

A. sys.dm_os_wait_stats
B. sys.dm_exec_requests
C. sys.dm_exec_sessions
D. sys.dm_exec_query_stats

Correct Answer: D QUESTION 34
You are mastering the company database; a new SQL Server 2008 computer named TesterDB should be set by yourself. Reporting Services will work in TesterDB. It should be set to run database backups and other maintenance job automatically. In the network environment, the company has no other SQL Server computers. The whole approach SQL2 will be made by using SQL1s DNS name. You should enable any necessary devices on SQL2. Which is the correct answer?(select more than one)
A. You should utilize SQL Server Web Service technology
B. You should utilize SQL Server Browser technology
C. You should utilize Internet Information Services technology
D. You should utilize Microsoft Distributed Transaction Coordinator technology

Correct Answer: BD QUESTION 35
You are mastering the company database called Testing; you should back up the Testing database fully. You do transaction log once every two hours from 08:00 to 17:00.You should run database snapshots for the Testing database. You should develop database snapshot every day at 07:00. At 8:30 the user removes the whole information on the Testing database which was entered intothe CurrentSpeaker table yesterday. Deletion is the first step for the database. It is now 11:45 and many other changes have happened in the database. You should find solution to solve this problem .Which is the correct answer?
A. You should utilize the SELECT subquery in the INSERT statement to move the deleted rows from this morning??s database snapshot to the CurrentSpeaker table.
B. You should utilize the Delete subquery in the INSERT statement to transfer the deleted rows from yesterday mornings database snapshot to the CurrentSpeaker
C. You should reserve last nights Full Backup and all transaction log backups until the
10:00 backup. Use the STOP AT statement on the some restores to prevent
D. You should reserve last nights Full Backup and all transaction log backups until the
10:00 backup. Utilize the STOP AT statement on the last restore to prevent

Correct Answer: A QUESTION 36
You migrate an application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.
You need to monitor the SQL Server instance to record the use of features that will be discontinued.
What should you do?

A. Use the SQL Server 2008 Upgrade Advisor.
B. Use the SQL Server Profiler that captures the SQL:BatchCompleted and Exception event classes.
C. Use a SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes.
D. Use a SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes.

Correct Answer: D QUESTION 37
You administer a remote SQL Server 2008 instance. Users report that the performance of the application is poor.
You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote SQL Server instance.
You need to analyze the workload of the remote SQL Server instance on a local SQL Server instance by using the Database Engine Tuning Advisor.
What should you do?

A. Use the data collector to recapture the workload.
B. Use SQL Profiler to recapture the workload to a trace file.
C. Enable the XP_MSVER stored procedure on the local server.
D. Enable the XP_MSVER stored procedure on the remote server.

Correct Answer: B QUESTION 38
You manage a SQL Server 2008 instance. You plan to maintain a management data warehouse that collects performance data by using the data collector.
You need to implement a process that routinely gathers and uploads data in the management data warehouse on different schedules.
What data collection process should you implement?

A. Create a cached data collection.
B. Create a scheduled non-cached data collection.
C. Create an on-demand non-cached data collection.
D. Create two different SQL Agent jobs that are scheduled at the same time. One job creates a data collection and the other job uploads the data collection. Correct Answer: A QUESTION 39
You maintain a SQL Server 2008 instance. Your failure recovery plan requires you to back up the default trace.
You need to backup the subdirectory that contains the default trace.
Which SQL Server database engine subdirectory should you back up?

A. LOG
B. BINN
C. DATA D. INSTALL

Correct Answer: A QUESTION 40
You are mastering the company database, you should check the log of the SQL Server 2008 Enterprise Edition everyday, and you get the information on the torn page errors of the database. In order to fine the solution of the torn page, which is the correct answer?
A. You should utilize the latest database backup to restore only the torn page. Then restore any transaction logs which have been made since the full backup.
B. You should reserve the newest database backup and then reserve any transaction logs which have been made since the past backup.
C. You should operate DBCC CHECKDB.
D. You should reserve the database from the newest database snapshot.

Correct Answer: A QUESTION 41
You administer a SQL Server 2008 instance. The instance contains various SQL Server Agent jobs that use seven shared schedules to run each job on each day of the week. You need to ensure that a job named Job5 no longer runs on Thursdays. What should you do?
A. Delete the schedule for Thursday.
B. Disable the schedule for Thursday.
C. Remove the schedule for Thursday from Job5.
D. Add a new Job5 schedule for Thursday and disable it.

Correct Answer: C QUESTION 42
You administer a SQL Server 2008 instance. You have a stored procedure that implements a database maintenance process.
You need to create a SQL Server Agent job that runs the stored procedure. You also need to ensure that the job is removed after successful completion.
What should you do?

A. Create a job that is scheduled to run once.
B. Create a job that uses the automatically delete job option.
C. Create a job that is assigned to the Database Maintenance category.
D. Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.

Correct Answer: B QUESTION 43
You maintain a SQL Server 2008 instance. You create a new SQL Agent job that includes a Windows PowerShell job step.
The PowerShell job step uses the SQLCmd utility to transfer data between servers.
You need to ensue that an operator named ResponseTeam is notified by an e-mail message if the job fails.
What should you do?

A. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on job failure.
B. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.
C. Create the ResponseTeam operator. Assign the ResponseTeam operator as the failCsafe operator. Enable the job.
D. Create the ResponseTeam operator. Configure the job step proxy account to use the ResponseTeam operator account. Select the proxy account for the e-mail profile.

Correct Answer: A
QUESTION 44
You maintain several SQL Server 2008 instances. You need to ensure that the names of all user-defined stored procedures contain the prefix usp_ on all
instances.
You also need to ensure that you cannot create stored procedures that do not contain this prefix.
What should you do?

A. Create a policy that targets the name of the stored procedure that is evaluated on change.
B. Create a policy that targets the name of the stored procedure that is evaluated on demand.
C. Create a condition that targets the name of stored procedure that is evaluated on demand.
D. Create a condition that targets the name of the stored procedure that is evaluated on change.

Correct Answer: A QUESTION 45
You maintain a SQL Server 2008 instance that contains a database named Finance. The data file and the transaction log file are located on the E: drive. The E:
drive has only 5 percent available space.
You need to move both files to the V: drive.
Which procedure should you use?

A. Run the following Transact-SQL statement. ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements.
ALTER DATABASE Finance MODIFY FILE ( NAME = Finance_Data, FILENAME =
‘v:\SQLServer\Finance_Data.mdf’); ALTER DATABASE Finance SET MULTI_USER;

B. Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = ‘v:\SQLServer\Finance_Data.mdf’); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME = ‘v:\SQLServer\Finance_Log.ldf’); ALTER DATABASE Finance SET ONLINE;
C. Stop the SQL Server service. Move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_db @dbname = N’Finance’, @physname = N’v:\SQLServer\Finance_Data.mdf’;
D. Stop the SQL Server Service. Move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_db @dbname = N’Finance’, @filename1 = N’v:\SQLServer\Finance_Data.mdf’, @filename2 = N’v:\SQLServer\Finance_Log.ldf’;

Correct Answer: B
QUESTION 46
You are mastering the company database, with SQL Server 2008 computer named SQLDB. The function of the SQLDB is to perform transaction log backups and so on.SQL1 should be reset by another administrator using the tool of SQL Server Surface Area Configuration.
You should see that SQL1 no longer runs the maintenance tasks automatically. On SQLDB, You should make sure the tasks of the maintenance are finished without menWhich is the correct answer?
A. You should reset the Web service to log on by using the role of administrator.
B. You should reset the server so that the SQL Server Agent service starts automatically.
C. You should reset operate a full backup of the master database by hand.
D. You should reset SQLDB to use Windows Integrated authentication.

Correct Answer: B QUESTION 47
You maintain multiple SQL Server 2008 instances on the same physical computer.
Users can connect to the default instance but are unable to connect to the named instances. You verify that currently all SQL Server instances run.
You need to start the service required to connect to the named instances.
Which Service should you start?

A. Server
B. SQL Server Agent
C. SQL Server Browser
D. SQL Active Directory Helper

Correct Answer: C QUESTION 48
You manage a SQL Server 2008 instance.
You need to verify whether a database integrity check (DBCC CHECKDB) was run for a particular database.
Which log file should you examine?

A. log.trc
B. default.trc
C. ERRORLOG
D. SQLAGENT
Correct Answer: C QUESTION 49
You administer a SQL Server 2008 instance.
A SQL Server Agent job executes every 2 minutes. The job logs information to a text file named SQL1.log located on the E: drive.
Users report that the server is unresponsive. You discover the following problems:

The SQL Server Agent job no longer functions.
The SQL Server Agent service does not run.
You need to find out the cause of the SQL Server Agent service failure.
Which log should you examine?

A. SQL1.log
B. log_xx.trc
C. ERRORLOG
D. SQLAGENT.OUT

Correct Answer: D QUESTION 50
You administer a SQL Server 200S instance. A user named Mary reports that she is waiting for a query to complete. You need to ascertain whether the query is
blocked.
Which tool should you use?

A. The Windows System Monitor tool
B. The Database Engine Tuning Advisor tool
C. The Activity Monitor tool in Microsoft SQL Server Management Studio
D. The Job Activity Monitor tool in Microsoft SQL Server Management Studio

Correct Answer: C

If you fail in Microsoft 070-432 exam test with Microsoft 070-432 exam dumps, we promise to give you full refund! You only need to scan your Microsoft 070-432 exam score report to us together with your receipt ID. After our confirmation, we will give you full refund in time.Or you can choose to charge another IT exam Q&As instead of Microsoft 070-432 exam dumps.Useful Cisco certifications exam dumps are assured with us.If our Microsoft 070-432 exam dumps can’t help you pass American Microsoft 070-432 exam,details will be sent before we send the exam to you.We don’t waste our customers’ time and money! Trusting www.flydumps.com is your best choice!

Welcome to download the newest Pass4itsure 352-011 Practical Exam dumps: http://www.pass4itsure.com/352-011.html

Microsoft 070-432 Exam Dumps, 100% Pass Microsoft 070-432 PDF On Sale

Microsoft 70-461 PDF Exams,Latest Upload Microsoft 70-461 Question Description With Accurate Answers

ATTENTION : The following Microsoft 70-461 exam questions and answers were updated in recent days with the change of new Microsoft 70-461 exam, more new added questions are available at Flydumps.Please visit Flydumps and get valid Microsoft 70-461 PDF and VCE exam dumps with free new version VCE player.

QUESTION 1
You develop a Microsoft SQL Server 2012 server database that supports an application. The application
contains a table that has the following definition:

CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and
ItemsInWarehouse values for each row.

Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
B. ALTER TABLE Inventory ADD ItemsInStore – ItemsInWarehouse = TotalItemss
C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Reference: http://technet.microsoft.com/en-us/library/ms190273.aspx
QUESTION 2
You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

You need to improve the performance of the view by persisting data to disk. What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Create an AFTER trigger on the view.
C. Modify the view to use the WITH VIEW_METADATA clause.
D. Create a clustered index on the view.

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188783.aspx

QUESTION 3
You develop a database for a travel application. You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information.
What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Correct Answer: I Explanation
Explanation/Reference:
Explanation: Reference: http://msdn.microsoft.com/en-us/library/ff848733.aspx Reference: http://msdn.microsoft.com/en-us/library/bb630289.aspx
QUESTION 4
You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.
Correct Answer: E Explanation

Explanation/Reference:
QUESTION 5
You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.

Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?
A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx

QUESTION 6
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms190306.aspx Reference: http://msdn.microsoft.com/
en-us/library/ms188792.aspx

QUESTION 7
Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?
A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p
B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p
C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx Reference: http://
blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs- union-all-optimalperformance-comparison/

QUESTION 8
You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.
You need to protect the code from being viewed by your customers.
Which stored procedure option should you use?
A. ENCRYPTBYKEY
B. ENCRYPTION
C. ENCRYPTBYPASSPHRASE
D. ENCRYPTBYCERT

Correct Answer: B Explanation
Explanation/Reference:
Explanation:
Reference: http://technet.microsoft.com/en-us/library/bb510663.aspx Reference: http://
technet.microsoft.com/en-us/library/ms174361.aspx Reference: http://msdn.microsoft.com/en-us/library/
ms187926.aspx Reference: http://technet.microsoft.com/en-us/library/ms190357.aspx Reference: http://
technet.microsoft.com/en-us/library/ms188061.aspx

QUESTION 9
You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO
B. CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO

Correct Answer: A Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/gg471497.aspx Reference: http://msdn.microsoft.com/
en-us/library/ff929144.aspx

QUESTION 10
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Correct Answer: B Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx

QUESTION 11
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data.

The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?
A. views
B. table partitions
C. table-valued functions
D. stored procedures

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 12
You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.

Enables the returned result set to perform a join with a table.
Which object should you use?
A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function

Correct Answer: C Explanation
Explanation/Reference:
QUESTION 13
You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
Accepts a single input parameter for CustomerID.

Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO
B. EXECUTE dbo.GetCustomerRatIng 1745
C. DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
, @CustomerRatingSyCustomer

D. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result GO
E. DECLARE @CustomerRatIngByCustcmer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT
F. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT AS DECLARE @Result INT SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerVaLue FROM Customers WHERE Customer= = @CustomerID RETURNS @Result

Correct Answer: AE Explanation
Explanation/Reference:
QUESTION 14
You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.
You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK
Correct Answer: E Explanation

Explanation/Reference:
Explanation:
Reference: http://technet.microsoft.com/en-us/library/ms189857.aspx Reference: http://
msdn.microsoft.com/en-us/library/ms187373.aspx

QUESTION 15
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)
You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query.
What should you do?
A. Use a FORCESCAN hint in the query.
B. Add a clustered index on SalesOrderId in SalesOrderHeader.
C. Use a FORCESEEK hint in the query.
D. Update statistics on SalesOrderId on both tables. Correct Answer: D

Explanation
Explanation/Reference:
Explanation:
References: http://msdn.microsoft.com/en-us/library/ms187348.aspx

QUESTION 16
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Two answers will return the correct results (the “WHERE CONVERT…” and “WHERE …
AND … ” answers).
The correct answer for Microsoft would be the answer that is most “efficient”. Anybody have a clue as to
which is most efficient? In the execution plan, the one that I’ve selected as the correct answer is the query
with the shortest duration. Also, the query answer with “WHERE CONVERT…” threw warnings in the
execution plan…something about affecting CardinalityEstimate and SeekPlan.

I also found this article, which leads me to believe that I have the correct answer:
http://technet.microsoft.com/en-us/library/ms181034.aspx

QUESTION 17
You develop a database for a travel application. You need to design tables and other database objects.
You need to store media files in several tables.
Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.
What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Correct Answer: F Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188362.aspx

QUESTION 18
You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Correct Answer: C Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx

QUESTION 19
You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
B. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL);
C. CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));
Correct Answer: D Explanation

Explanation/Reference:
QUESTION 20
You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C. Create an AFTER UPDATE trigger on the view.
D. Create an INSTEAD OF UPDATE trigger on the view. Correct Answer: D

Explanation Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx

Preparing Microsoft 70-461 exam is not difficult now.You can prepare from Microsoft 70-461 Certification or Microsoft 70-461 dumps.Here we have mentioned some sample questions.You can use our Microsoft 70-461 study material notes for test preparation.Latest Microsoft 70-461 study material available.

ON Sale Microsoft 70-460 Pass Certification Dumps | Microsoft 70-460 Exam demo

New VCE and PDF– You can prepare Microsoft 70-460 exam in an easy way with Microsoft 70-460 questions and answers. By training our Microsoft 70-460 vce dumps with all the latest questions, you can pass the Microsoft 70-460 exam in the first attempt.

Testlet 1
Topic 1, Tailspin Toys Case A

Overview
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in five regions worldwide. The company sells toys at various retail stores. The company also sells toys directly to consumers through a web site.
The company has the following departments:
Sales

Distribution

Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.

Existing Environment
Current DatabaseEnvironment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint mode.

Sales Database
A database named Sales contains two tables named FactSales and DimProduct.
FactSales contains the following columns:
SalesID

TotalDue

OrderDate

DimProduct contains the following columns:
ProductID

ProductName

ProductCategory

ProductSubcategory
The Sales database contains information about the products. Most of the products have a category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement:
Sales department users must be allowed to view the sales transactions from their region only.

Sales department users must be able to view the contents of the manufacturing reports.

Manufacturing department users must be able to create new manufacturing reports.

Third-party and custom solutions must NOT be deployed to the reporting server.

Sales department users must NOT be able to create new manufacturing reports.

Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The manufacturing department also plans to make its reports accessible from SharePoint. All manufacturing reports will use an existing database named Manufacturing.

Reporting Requirements
Tailspin Toys identifies the following reporting requirements:
All reports must contain the company logo and a header that contains the date and the time that the report was executed.

All reports must be created by using the SQL Server Data Tools.

Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following requirements:
Manufacturing department managers must be able to view product issues by product type,
manufacturing plant location, and error type.
The manufacturing department managers must be able to change views by choosing options from drop-down lists.

Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following requirements:
Users must be able to view the report by using a web browser. By default, subcategories and product details must be hidden when using the browser.

Users must be able to subscribe to receive the report by email. The report must be sent by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must display sales data by fiscal quarter.

Technical Requirements
Tailspin Toys identifies the following technical requirements:
Products in the DimProduct table that do NOT have a subcategory must use the category value as the subcategory value.

SSRS must NOT connect to databases more frequently than once every 30 minutes.

Sales department users must be able to use Microsoft Excel to browse tabular data.
QUESTION 1
You need to configure a hierarchy for DimProduct that meets the technical requirements.
What should you do?
A. Set ProductCategory as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductName. For ProductCategory, click Hide if Name Equals Parent
B. Set ProductCategory as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductName. For ProductSubcategory, click Hide if Name Equals Parent.
C. Set ProductName as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductCategory. For ProductCategory, click Hide if Name Equals Parent
D. Set ProductName as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductCategory. For ProductSubcategory, click Hide if Name Equals Parent.
Correct Answer: B QUESTION 2

After you deploy the RegionalSales report, you attempt to configure the subscriptions.
You discover that the subscription creation screen does not display the option to deliver the report by
email.
You need to ensure that subscriptions can be delivered by using email.
What should you do?

A. From Central Administration, modify the properties of the Reporting Service Application.
B. Modify the Rssrvrpolicy.config file.
C. Modify the Rsmgrpolicy.config file.
D. From Central Administration, modify the SMTP settings of the SharePoint Server server farm.

Correct Answer: C QUESTION 3
After you deploy the RegionalSales report, users report that they cannot see product data when they
receive the reports by email.
You need to ensure that the sales department managers can see all of the data.

In the report, you update the Hidden property of each group.

What should you do next?

A. In When the report is initially run, select Show or hide based on an expression. Set the expression to = (GlobalsIRenderFormaUsInteractive).
B. In When the report is initially run, select Show or hide based on an expression. Se: The expression to =NOT (GlobalslRenderFormat.IsInteractive)
C. In when the report is initially run, select Show.
D. In When the report is initially run, select Hide.

Correct Answer: D QUESTION 4
You need to recommend a solution for the sales department that meets the security requirements. What should you recommend?
A. Create one role for all of the sales department users. Configure the role to have read access to the sales transactions. Ensure that all of the reports that access the sales transaction data restrict read access to the data from the corresponding sales department region only.
B. Create one role for each region. Configure each role to have read access to a specific region. Add the sales department users to their corresponding role.
C. Create one role for all of the sales department users. Add a DAX filter that reads the current user name and retrieves the user’s region.
D. Create a table for each region. Create a role for each region. Grant each role read access to its corresponding table.

Correct Answer: C QUESTION 5
You need to ensure that all reports meet the reporting requirements.
What is the best way to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a report part. Publish the report part to a server that has SSRS installed. Add the report part to each new report that is created.
B. Create a report part. Publish the report part to a SharePoint site. Add the report part to each new report that is created.
C. Create a report. Copy the report to source code control. Create each new report by using the report template in source code control.
D. Create a report. Copy the report to the PrivateAssemblies\ProjectItems\ReportProject folder in the Visual Studio directory. Create each new report by using the locally stored report
Correct Answer: D Testlet 1

Topic 2, Tailspin Toys Case B

Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe, and Asia.
Tailspin Toys plans to implement a business intelligence (BO solution for its US-based headquarters to manage the sales data, including information on customer transactions, products, sales quotas, and bonuses.

Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat files, and the following:
A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored on a network drive in a directory owned by an analyst.

A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx uses
MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains a Microsoft SharePoint Server 2013 server farm.
Implementation Plans

Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
A fact table to store transactional data, including transaction date, productID, customerID, quantity, and sales amounts.

Dimension tables to store information about each customer, each product, each date, and each sales department user.

BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1 will contain two distinct count measures named UniqueCustomers and UniqueProducts. The measures are expected to aggregate hundreds of millions of rows from DB1.

A tabular model named SalesCommission that will contain information about sales department user quotas and commissions.

A tabular model named Research that will contain the migrated model from Research.xlsx.

An instance of SSAS in tabular mode named Tabular.

Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
Power View reports that use data from the Research model.

Reports for each year the company recorded sales data that used the SalesCommission model. The reports will use the Dates_Between() and the DatesInPeriod() DAX functions in queries.

Reports that use CUBE1 that contain the following query statements:
A report named SalesByCategory that uses CUBE1 and the following query statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
Reports created by sales department specialists that use CUBE1 and contain drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports will be stored in a SharePoint Server document library named Library1.

Reports created by sales department managers that use the SalesCommission model. The reports will contain visualizations that show sales department users their current sales as compared to their quota.

Power Pivot models stored in a SharePoint Server document library that is configured as a PowerPivot Gallery named Gallery1.

Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to their respective customers only.
Access to reports must be controlled by using SharePoint permissions.

ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
Nightly updates of DB1 must support the incremental load of dimension and fact tables on separate schedules. Fact data may be loaded before dimension data.

ETL processes must be able to update dimension attributes without losing context for historical facts.

Referential integrity between dimension and fact tables must be maintained at all times.

Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and UniqueProducts measures.
The time required to process CUBE1 each night must be minimized.

Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.

Ensure that you are provided with only the best and most updated Microsoft 70-460 Certification training materials, we also want you to be able to access Microsoft 70-460 easily, whenever you want.We provide all our Microsoft 70-460 Certification exam training material in PDF format, which is a very common format found in all computers and gadgets. Now we add the latest Microsoft 70-460 content and to print and share content.

Microsoft 70-459- Testcos expert Team Helps You to Get Your IT Certification

100% latest and accurate Microsoft 70-459 exam dumps.Test-talk.org ensures that you are equipped with the latest questions and answers, which give you a better chance of passing the Microsoft 70-459 exam. Also free sharing Microsoft 70-459 VCE test engine and PDF

Testlet 1
A. Datum
Overview
General Overview
A. Datum Corporation has offices in Miami and Montreal.
The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has a 5-ms
latency.

A. Datum standardizes its database platform by using SQL Server 2012 Standard edition.
Databases
Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev.
Servers and databases are managed by a team of database administrators. Currently, all of the database administrators have the same level of permissions on all of the servers and all of the databases.
The Customers database contains two tables named Customers and Classifications. The following graphic shows the relevant portions of the tables:

The following table shows the current data in the Classifications table: The Inventory database is used mainly for reports. The database is recreated every day. A full backup of the database currently takes three hours to complete.
Stored Procedures
A stored procedure named sp1 generates millions of rows of data for multiple reports. Sp1 combines data from five different tables from the Sales and
Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from a table in the Products database and searches for information in Table1 based on input
from the Products table. After the process is complete, Table1 is deleted.

A stored procedure named sp2 is used to generate a product list. Sp2 takes several minutes to run due to locks on the tables the procedure accesses.

A stored procedure named sp3 is used to update prices. Sp3 is composed of several UPDATE statements called in sequence from within a transaction.
Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
A stored procedure named sp4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables

from the Sales, Customers, and Inventory databases. Sp4 uses an EXECUTE AS clause.
A stored procedure named sp5 changes data in multiple databases. Security checks are performed each time sp5 accesses a database.
You suspect that the security checks are slowing down the performance of sp5.
All stored procedures accessed by user applications call nested stored procedures. The nested stored procedures are never called directly.
Design Requirements Data Recovery
You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour. You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.

Classification Changes
You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future.
Management requests that historical data be maintained for the previous classifications.

Security
A group of junior database administrators must be able to view the server state of the SQL Server instance that hosts the Sales database. The junior database administrators will not have any other administrative rights.
QUESTION 1
You need to recommend a solution for the planned changes to the customer classifications. What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add a table to track any changes made to the classification of each customer.
B. Add columns for each classification to the Customers table.
C. Implement change data capture.
D. Add a row to the Customers table each time a classification changes.
E. Add a column to the Classifications table to track the status of each classification.
Correct Answer: AC QUESTION 2
You need to recommend a change to sp3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?
A. Set the IMPLICIT_TRANSACTIONS option to off.
B. Set the XACT_ABORT option to off
C. Set the IMPLICIT_TRANSACTIONS option to on.
D. Set the XACT_ABORT option to on.

Correct Answer: D QUESTION 3
You need to recommend a solution to meet the security requirements of the junior database administrators. What should you include in the recommendation?
A. a shared login
B. a database role
C. a credential
D. a server role

Correct Answer: D QUESTION 4
You need to recommend a solution to minimize the amount of time it takes to execute sp1. With what should you recommend replacing Table1?
A. a temporary table
B. a function
C. a view
D. a table variable

Correct Answer: C QUESTION 5
You need to recommend a disaster recovery strategy for the Inventory database. What should you include in the recommendation?
A. Log shipping
B. Always on availability groups
C. SQL Server Failover Clustering
D. Peer-to-peer replication

Correct Answer: A QUESTION 6
You need to recommend a solution to ensure that sp4 adheres to the security requirements. What should you include in the recommendation?
A. Configure data manipulation language(DML) triggers.
B. Enable SQL Server Audit.
C. Enable trace flags.
D. Enable C2 audit tracing.

Correct Answer: B QUESTION 7
You need to recommend a change to sp3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails. Which change should you recommend?
A. Set the IMPLICIT_TRANSACTIONS option to on.
B. Set the XACT_ABORT option to off.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the XACT_ABORT option to on. Correct Answer: B
Testlet 2

Contoso Ltd
Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application. Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2. The new version will use SQL Server 2012.
The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.
You receive several support calls reporting unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.

Tables
The current database schema contains a table named OrderDetails. The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order. The product price is stored in a table named Products.
The Products table was defined by using the SQL_Latinl_General_CP1_CI_AS collation. A column named ProductName was created by using the varchar data type. The database contains a table named Orders. Orders contains alt of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security.

Stored Procedures
The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:
Customer Problems Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.

Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:
Backup Issues
Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

Search Issues
Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks.

Import Issues
During the monthly import process, database administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.

Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately.

Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Proc1 and Sales.Proc2 execute.

Our material on our site Microsoft 70-459 is exam-oriented, keeping in view the candidates requirements and level of understanding.Microsoft 70-459 materials are in the most popular and easy-to-use PDF version. You can use it on any devices with you anywhere.

New Questions– Free Download Of Latest Microsoft 70-448 VCE And PDF From Flydumps

With Flydumps Microsoft 70-448 practice tests, you can pass the exam easily and go further on Microsoft career path. The Microsoft 70-448 Flydumps are authenticated by expert and covering all aspect of Microsoft 70-448 exam. Visit www.Flydumps.com to get the Microsoft 70-448 100% pass ensure!

QUESTION 1
You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to process a cube by using an XML for Analysis (XMLA) script.
You need to ensure that aggregations are populated.
What should you do?

A. Execute a processIndex command.
B. Execute a processUpdate command.
C. Execute a processStructure command.
D. Execute a processData command. Correct Answer: A

Explanation Explanation/Reference:
QUESTION 2
You maintain a SQL Server 2008 Analysis Services (SSAS) instance.
You plan to run the Usage-Based Optimization Wizard. You need to enable query logging.
What should you do?

A. Set the LogDir server property to a valid path.
B. Set the QueryLogSampling server property to 10.
C. Set the AllowedBrowsingFolders server property to include the folder from the LogDir setting.
D. Set the QueryLogConnectionString server property to a valid connection string. Correct Answer: D

Explanation Explanation/Reference:
QUESTION 3
You maintain a SQL Server Analysis Services (SSAS) database. The database is configured by using multiple security roles.
The database is accessed by a Microsoft ASP.NET application that runs on a remote computer. The application is configured to use Windows Authentication. You need to ensure that the users of the application can successfully access the SSAS database.
You also need to ensure that security restrictions of the roles are applied.
What should you do?
A. Configure Kerberos authentication
B. Configure Analysis Services for HTTP authentication
C. Set the AnonymousConnectionsEnabled policy to True
D. Set the Security\RequireClientAuthentication property to True

Correct Answer: A Explanation Explanation/Reference:
QUESTION 4
You are maintaining a SQL Server 2008 Analysis Services (SSAS) solution in the production environment. You modify the solution to include two new measure groups in the development environment. You need to ensure that only one measure group is deployed to the cube in the production environment. What should you do?
A. Use the Deployment Wizard.
B. Use Microsoft SQL Server Management Studio (SSMS) to issue an XMLA command.
C. Use Microsoft SQL Server Management Studio (SSMS) to issue an UPDATE MEMBER command.
D. Use Business Intelligence Development Studio (BIDS) along with the Deploy only changes option set to True.

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 5
You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a dimension named Customer.
You need to configure the Dimension Data settings to meet the following requirements:

Deny access to the {[Customer].[Country].&[Germany],[Customer].
[Country].&[France]} set of attribute members.
New members added to the attribute are visible by default. What should you do?

A.
Add all the country members except those of France and Germany to the Allowed Set property.

B.
Add the following set to the Denied Set property. {[Customer].[Country].&[Germany], [Customer]. [Country].&[France]}

C.
Add the following set to the Denied Set property. Except([Customer].[Country].[Country], {[Customer]. [Country].&[Germany],[Customer].[Country].&[France]})

D.
Add the following set to the Allowed Set property. Extract({[Customer]. [Country].&[Germany],[Customer].[Country].&[France]},[Customer].[Country])

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 6
You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a Product dimension. The dimension contains the Category and Subcategory attributes.
The attributes have a Rigid relationship type.
The data source for the Product dimension changes the relationship between the Category and Subcategory values.
You need to execute an XML for Analysis (XMLA) command to process the dimension to successfully reflect the change.
What should you do?
A. Use the ProcessAdd command.
B. Use the ProcessUpdate command.
C. Use the ProcessIndexes command.
D. Use the ProcessClear and the ProcessDefault commands. Correct Answer: D

Explanation Explanation/Reference:
QUESTION 7
You maintain a SQL Server 2008 Analysis Services (SSAS) database on a production server. The database contains a cube.
You add new functionality to the cube on a development server. You need to deploy the new functionality from the development server to the production server by ensuring that the effect on the users is minimized.
What should you do?
A. Use the Synchronize Database Wizard.
B. Copy the appropriate files from the development server to the production server.
C. Detach the production database. Copy the appropriate files to the production server, and then attach the database on the production server.
D. Modify the Target Server property to the production server by using Business Intelligence Development Studio (BIDS). Deploy the solution.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 8
You maintain a SQL Server 2008 Analysis Services (SSAS) database on a default instance.
You plan to document the Analysis Services performance information.
You need to collect information related to the temporary file usage when the database is processed.
Which object should you use?

A. MSAS 2008: Memory
B. MSAS 2008: Cache
C. MSAS 2008: Proc Aggregations
D. MSAS 2008: Storage Engine Query Correct Answer: C

Explanation Explanation/Reference:
QUESTION 9
You are a server administrator of a SQL Server 2008 Analysis Services (SSAS) instance. The instance
contains a database that is used by the members of the Sales group.

You configure a new role named Northern Region by using the “{[Customers].[Region].
[Region].[Northern]}’ allowed permission set.

You assign the Sales group to the Northern Region role.
You need to verify that users in the Northern Region role can view data only for their region.

What should you do?

A. Temporarily add your account to the Northern Region role.
B. Add the User ID=Northern Region; parameter to the connection string.
C. Add the Roles=Northern Region; parameter to the connection string.
D. Select the Enable Visual Totals check box for the Northern Region role. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 10
You maintain a SQL Server 2008 Reporting Services (SSRS) instance.
The network security model for the domain that hosts the instance changes.
You need to ensure that the instance will only support Kerberos authentication.
What should you do?

A. Edit the RSReportServer.config file.
B. Edit the rssrvpolicy.config file.
C. Edit the Site Settings of the instance by using the Report Manager.
D. Edit the Server Settings by using Microsoft SQL Server Management Studio (SSMS). Correct Answer: A

Explanation Explanation/Reference:
QUESTION 11
You plan to deploy SQL Server 2008 Reporting Services (SSRS) on a SQL Server 2008 failover cluster. Internet Information Services (IIS) is installed on both nodes of the cluster. The IIS is virtualized on the Network Load Balancing (NLB) cluster. You need to deploy SSRS in a fault- tolerant scale-out deployment. What should you do?
A. Install SSRS on both cluster nodes to use separate ReportServer databases.
B. Install SSRS on both cluster nodes to use the same ReportServer database.
C. Install SSRS on both cluster nodes and cluster the SSRS service by using a new resource group.
D. Install SSRS on both cluster nodes and cluster the SSRS service by using the SQL Server resource group.

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 12
You maintain a SQL Server 2008 Reporting Services (SSRS) database server.
The instance contains a large number of reports that take a long time to execute. The reports contain
multiple execution snapshots.
You have a new production server along with SSRS installed.
You need to migrate the reports and their snapshots to the production server in the minimum amount of

time.

What should you do?
A. Use the rs.exe utility.
B. Use the rskeymgmt.exe utility.
C. Deploy the solutions by using Business Intelligence Development Studio (BIDS).
D. Copy the Report Server databases to the production server. Configure the server by using the Reporting Services Configuration tool.

Correct Answer: D Explanation
Explanation/Reference:
QUESTION 13
You manage the security in SQL Server 2008 Reporting Services (SSRS). The report server contains a folder named Management Reports.
All groups can view and modify reports in any folder.
You need to ensure that only the Managers group can view and modify reports in the Management
Reports folder.

What should you do?

A. Remove all groups from the Management Reports folder. Add the Managers group to the folder and assign the Content Manager role.
B. Create a Managers role. Assign the Managers role to the Management Reports folder. Remove all other groups from the Management Reports folder.
C. Create a Managers role. Assign the Managers role to the root folder. Remove all groups from the Management Reports folder.
D. Add the Managers group to the root folder and assign the Content Manager role. Remove all groups from the Management Reports folder.

Correct Answer: A Explanation
Explanation/Reference:
QUESTION 14
You maintain a SQL Server 2008 Reporting Services (SSRS) instance.
A developer accidentally deploys 1000 reports to the SSRS instance.
You need to restore the SSRS instance to its original state.
Which database should you restore?

A. The master database
B. The MSDB database
C. The Report Server database
D. The Report Server Temporary database

Correct Answer: C Explanation Explanation/Reference:
QUESTION 15
You create a report by using SQL Server 2008 Reporting Services (SSRS).
You plan to deploy the report on a report server. The report will be delivered on a regular basis to a
dynamic list of subscribers.
You need to create a new subscription on the report server.
What should you do?

A. Select the report you created, and then click the New Subscription button.
B. Select the My Subscriptions page, and then click the New Data-driven Subscription button.
C. Select the report you created. Click the Subscriptions tab, and then click the New Data-driven Subscription button.
D. Select the report you created. Click the Subscriptions tab, and then click the New Subscription button. Correct Answer: C

Explanation Explanation/Reference:
QUESTION 16
You create a report by using SQL Server 2008 Reporting Services (SSRS).
The report is complex and takes several minutes to generate.
You need to configure the report server to cache the report for 30 minutes after the initial request.
What should you do?

A. Implement report snapshots.
B. Implement report execution caching.
C. Configure the data source to use snapshot isolation.
D. Configure the data source to use a database snapshot. Correct Answer: B

Explanation Explanation/Reference:
QUESTION 17
You create a SQL Server 2008 Analysis Services (SSAS) solution. You have a table named Orders that contains the following columns:
OrderKey

Shipper

OrderAmount
You create a dimension named DimOrders from the Orders table. You set the OrderKey as a key column
and the Shipper column as an attribute. You create a measure group named FactOrders from the Orders
table.

You need to create a dimension relationship to ensure that the OrderAmount column can be aggregated by
the Shipper column.
What should you do?

A. Set the relationship between the DimOrders dimension and the FactOrders measure group to No Relationship.
B. Create a fact dimension relationship between the DimOrders dimension and the FactOrders measure group.
C. Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to Shipper and the measure group columns to OrderAmount.
D. Create a regular dimension relationship between the DimOrders dimension and the FactOrders measure group. Set the Granularity attribute to OrderKey and the measure group columns to OrderAmount.

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 18
You create a SQL Server 2008 Analysis Services (SSAS) solution. The cube structure is as shown in the following exhibit. (Click the Exhibit button.)

You need to ensure that the Amount measure can be aggregated for the DimCustomer dimension. What should you do?
A. Create a regular relationship between DimCustomer and FactTransaction.
B. Create a referenced relationship between DimCustomer and FactCustomerAccount. Set DimAccount as an intermediate dimension.
C. Create a many-to-many relationship between DimCustomer and FactTransaction. Set FactCustomerAccount as an intermediate measure group.
D. Create a many-to-many relationship between DimCustomer and FactCustomerAccount. Set FactTransaction as an intermediate measure group.

Correct Answer: C Explanation
Explanation/Reference: QUESTION 19
You implement a SQL Server 2008 Analysis Services (SSAS) solution. You use data mining extensions (DMX) to query a data mining model. You write the following DMX code segment to query a Bike Buyer data mining model in a Decision Tree data mining structure. (Line numbers are included for reference only.)

You need to ensure that the input columns automatically map to the columns in the data mining structure. Which code fragment should you insert at line 05?
A. NATURAL PREDICTION JOIN
B. PREDICTION JOIN
C. INNER JOIN

D. OPENQUERY Correct Answer: A
Explanation Explanation/Reference:
QUESTION 20
You create a SQL Server 2008 Analysis Services (SSAS) solution. You create a key performance indicator (KPI) named GPMargin for your solution. You set the Value expression of the KPI in the following manner.

You need to set a Trend expression of the KPI to show a difference in the values compared with the previous quarter. Which MDX code segment should you insert at line 02?
A. KPIValue( “GPMargin” ) > ( KPIValue( “GPMargin” ),
B. KPITrend( “GPMargin” ) > ( KPIValue ( “GPMargin” ),
C. KPIStatus( “GPMargin” ) > ( KPIValue( “GPMargin” ),
D. KPIGoal( “GPMargin” ) > ( KPIValue( “GPMargin” ),

Correct Answer: A Explanation Explanation/Reference:

Well-regarded for its level of detail, assessment features, and challenging review questions and hands-on exercises,Microsoft 70-448 helps you master the concepts and techniques that will enable you to succeed on the Microsoft 70-448 exam the first time.

Real Microsoft 70-433 Exam Dumps From Flydumps With New Added Questions For Free Download

How To Pass Microsoft 70-433 Exam Easily? The 100% valid Flydumps latest Microsoft 70-433 question answers ensure you 100% pass! Free download more Microsoft 70-433 new PDF format updated exam questions on Flydumps.com.

QUESTION 1
.
You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John’s SELECT access rights from the Sales.SalesOrder table
without affecting his other permissions.
Which Transact-SQL statement should you use?

A. DROP USER John;
B. DENY SELECT ON Sales.SalesOrder TO John;
C. GRANT DELETE ON Sales.SalesOrder TO John;
D. REVOKE SELECT ON Sales.SalesOrder FROM John;
Correct Answer: B Explanation
Explanation/Reference:
Explanation/Reference:
REVOKE permanently removes both granted an denied permissions on an object, resulting in no
permissions.Main thing you have to remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object
for the operation, user will be able to perform the operation.

DENY Denies permission to the object for an operation. Once it set, it takes precedence over all other GRANT permissions, user will not be able to perform the
operation against the object.

To sum up, because John does not have GRANT permissions on the Sales.SalesOrder table (instead it has GRANT permission on Sales schema), then REVOKE
SELECT ON Sales.SalesOrder from John will not remove any permissions.

Here is a code that shows it clearly.
— create a login and user
CREATE LOGIN [John] WITH PASSWORD = ‘1’, CHECK_POLICY = OFF GO
USE [AdventureWorks2008]
GO
CREATE USER [John] FOR LOGIN [John]
WITH DEFAULT_SCHEMA = [dbo]
GO
— grant permission on Sales schema
GRANT SELECT ON SCHEMA :: Sales TO [John]
— Run SELECT with John’s credentials and see
— He sees records
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT
— Revoke permisson for the table from him
REVOKE SELECT ON Sales.SalesOrderHeader FROM [John] — He still sees data
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT
— This explicitly denies permission on SalesOrderHeader to John — Once this is executed, he will not be able to see data — even we grant him again.
DENY SELECT ON Sales.SalesOrderHeader TO [John]
— He sees error message: The SELECT permission was denied on the object ‘SalesOrderHeader’, database ‘AdventureWorks2008’, schema ‘Sales’.
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT

QUESTION 2
. You need to create a column that allows you to create a unique constraint. Which two column definitions should you choose? (Each correct answer presents a complete solution. Choose two.)
A. nvarchar(100) NULL
B. nvarchar(max) NOT NULL
C. nvarchar(100) NOT NULL
D. nvarchar(100) SPARSE NULL
Correct Answer: AC Explanation
Explanation/Reference:
QUESTION 3
.
You manage a SQL Server 2008 database that is located at your company’s corporate headquarters. The database contains a table named dbo.Sales. You need
to create different views of the dbo.Sales table that will be used by each region to insert, update, and delete rows. Each regional office must only be able to insert,
update, and delete rows for their respective region.
Which view should you create for Region1?

A. CREATE VIEW dbo.Region1Sales AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
B. CREATE VIEW dbo.Region1Sales AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1 WITH CHECK OPTION;
C. CREATE VIEW dbo.Region1Sales WITH SCHEMABINDING AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
D. CREATE VIEW dbo.Region1Sales WITH VIEW_METADATA AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
Correct Answer: B Explanation
Explanation/Reference:
Explanation/Reference:
CHECK OPTION
Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the
WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

QUESTION 4
.
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:

CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, [OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL
AND SalesPersonID IS NOT NULL;

You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows.
You need to create an index to optimize the query. The index must conserve disk space while optimizing your query.
Which index should you create?

A. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CustomerID) INCLUDE (CommentDate,SalesPersonID);
B. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (SalesPersonID) INCLUDE (CommentDate,CustomerID);
C. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CustomerID) INCLUDE(CommentDate) WHERE SalesPersonID IS NOT NULL;
D. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CommentDate, SalesPersonID) INCLUDE(CustomerID) WHERE CommentDate IS NOT NULL;
Correct Answer: D Explanation
Explanation/Reference:
QUESTION 5
.
Your database is 5GB and contains a table named SalesHistory. Sales information is frequently inserted and updated.
You discover that excessive page splitting is occurring. You need to reduce the occurrence of page splitting in the SalesHistory table.
Which code segment should you use?.

A. ALTER DATABASE Sales MODIFY FILE (NAME = Salesdat3, SIZE = 10GB);
B. ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);
C. EXEC sys.sp_configure ‘fill factor (%)’, ’60’;
D. UPDATE STATISTICS Sales.SalesHistory(Products) WITH FULLSCAN, NORECOMPUTE;
Correct Answer: B Explanation
Explanation/Reference:
Explanation/Reference:

Fill Factor
The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the
percentage of space on each leaf- level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying
a fill-factor value of 60 means that 40 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying
table.

Page Splits
A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When
a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This
reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can
cause fragmentation that causes increased I/O operations.

Fragmentation
Fragmentation breaks down to physical and logical fragmentation (or, internal and external fragmentation).
Physical/Internal fragmentation is caused when there is wasted space in the index caused by page splits, deletes, FILLFACTOR and PAD_INDEX. Logical/
External fragmentation is when the pages that make up the leaf levels of the index are not in good order. This is usually caused by page splits. Both cause
performance problems. Internal fragmentation causes problems because the indexes get bigger and bigger requiring more and more pages to store the data,
which means that reads from the index slow things down. External fragmentation causes problems because when the data needs to be read from the index it has
to skip all over the place following the links between pages, again, slowing things down.

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time
these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in
which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. To reorganize one or more indexes, use the ALTER INDEX
statement with the REORGANIZE clause.

Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match
the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing
pages allocated to it; no new pages are allocated.

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the
specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance
by reducing the number of page reads required to obtain the requested data.
You can use the CREATE INDEX with the DROP_EXISTING clause or ALTER INDEX with the REBUILD clause statements to set the fill-factor value for individual
indexes. EXEC sys.sp_configure ‘fill factor (%)’, ’60’; will modify the server default fill factor value.

QUESTION 6
.
You have a table named dbo.Customers. The table was created by using the following Transact- SQL statement:
CREATE TABLE dbo.Customers
(
CustomerID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, AccountNumber nvarchar(25) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
AddressLine1 nvarchar(255) NOT NULL,
AddressLine2 nvarchar(255) NOT NULL,
City nvarchar(50) NOT NULL,
StateProvince nvarchar(50) NOT NULL,
Country nvarchar(50) NOT NULL,
PostalCode nvarchar(50) NOT NULL,
CreateDate datetime NOT NULL DEFAULT(GETDATE()),
ModifiedDate datetime NOT NULL DEFAULT(GETDATE())
)
You create a stored procedure that includes the AccountNumber, Country, and StateProvince columns from the dbo.Customers table. The stored procedure
accepts a parameter to filter the output on the AccountNumber column.
You need to optimize the performance of the stored procedure. You must not change the existing structure of the table.
Which Transact-SQL statement should you use?

A. CREATE STATISTICS ST_Customer_AccountNumber ON dbo.Customer (AccountNumber) WITH FULLSCAN;
B. CREATE CLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber);
C. CREATE NONCLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber) WHERE AccountNumber = ”;
D. CREATE NONCLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber) INCLUDE (Country, StateProvince);
Correct Answer: D Explanation
Explanation/Reference:
QUESTION 7
.
You have a table named Customer.
You need to ensure that customer data in the table meets the following requirements:
credit limit must be zero unless customer identification has been verified.
credit limit must be less than 10,000.
Which constraint should you use?

A. CHECK (CreditLimt BETWEEN 1 AND 10000)
B. CHECK (Verified = 1 AND CreditLimt BETWEEN 1 AND 10000)
C. CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
D. CHECK ((CreditLimt = 0 AND Verified = 0) AND (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
Correct Answer: C Explanation
Explanation/Reference:
QUESTION 8
.
You have a table named AccountsReceivable. The table has no indexes. There are 75,000 rows in the table. You have a partition function named
FG_AccountData. The AccountsReceivable table is defined in the following Transact-SQL statement:
CREATE TABLE AccountsReceivable (
column_a INT NOT NULL,
column_b VARCHAR(20) NULL)
ON [PRIMARY];

You need to move the AccountsReceivable table from the PRIMARY file group to FG_AccountData.
Which Transact-SQL statement should you use?

A. CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
B. CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
C. CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
D. CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
Correct Answer: C Explanation
Explanation/Reference: QUESTION 9
. You have a SQL Server 2008 database named Contoso with a table named Invoice. The primary key of the table is InvoiceId, and it is populated by using the identity property. The Invoice table is related to the InvoiceLineItem table. You remove all constraints from the Invoice table during a data load to increase load speed. You notice that while the constraints were removed, a row with InvoiceId = 10 was removed from the database. You need to re-insert the row into the Invoice table with the same InvoiceId value. Which Transact-SQL statement should you use?
A. INSERT INTO Invoice (InvoiceId, …VALUES (10, …
B. SET IDENTITY_INSERT Invoice ON; INSERT INTO Invoice (InvoiceId, … VALUES (10, … SET IDENTITY_INSERT Invoice OFF;
C. ALTER TABLE Invoice; ALTER COLUMN InvoiceId int; INSERT INTO Invoice (InvoiceId, … VALUES (10, …
D. ALTER DATABASE Contoso SET SINGLE_USER; INSERT INTO Invoice (InvoiceId, … VALUES (10, … ALTER DATABASE Contoso SET MULTI_USER;
Correct Answer: B Explanation
Explanation/Reference:
QUESTION 10
.
You are developing a new database. The database contains two tables named SalesOrderDetail and Product.
You need to ensure that all products referenced in the SalesOrderDetail table have a corresponding record in the Product table.
Which method should you use?

A. JOIN
B. DDL trigger
C. Foreign key constraint
D. Primary key constraint
Correct Answer: C Explanation
Explanation/Reference:
QUESTION 11
.
You are creating a table that stores the GPS location of customers. You need to ensure that the table allows you to identify customers within a specified sales
boundary and to calculate the distance between a customer and the nearest store.
Which data type should you use?

A. geometry
B. geography
C. nvarchar(max)
D. varbinary(max) FILESTREAM
Correct Answer: B Explanation
Explanation/Reference:
Explanation/Reference:
The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and
polygons on the earth’s surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data
type.

QUESTION 12
. You plan to add a new column named SmallKey to the Sales.Product table that will be used in a unique constraint. You are required to ensure that the following information is applied when adding the new column:
‘a1’ and ‘A1’ are treated as different values ‘a’ and ‘A’ sort before ‘b’ and ‘B’ in an ORDER BY clause
You need to select the collation that meets the requirements for the new column. Which collation should you select?
A. Latin1_General_BIN
B. SQL_Latin1_General_CP1_CI_AI
C. SQL_Latin1_General_CP1_CI_AS
D. SQL_Latin1_General_CP1_CS_AS
Correct Answer: D Explanation
Explanation/Reference:
Explanation/Reference:
SQL Server Collation Name consists of several parts, one of them is responsible for CaseSensitivity CI specifies case-insensitive, CS specifies case-sensitive.
BIN specifies the binary sort order to be used.
So, because we want case-sensitive location, B and C are not suitable. Latin1_General_BIN use binary sort order, but we want linguistical sort order (according to
rules of language), not based on the code point values of characters.

QUESTION 13
.
You have multiple tables that represent properties of the same kind of entities. The property values are comprised of text, geometry, varchar(max), and user-
defined types specified as ‘bit NOT NULL’ data types.
You plan to consolidate the data from multiple tables into a single table. The table will use semi- structured storage by taking advantage of the SPARSE option.
You are tasked to identify the data types that are compatible with the SPARSE option. Which data type is compatible with the SPARSE option?

A. text
B. geometry
C. varchar(max)
D. A user-defined type defined as ‘bit NOT NULL’
Correct Answer: C Explanation
Explanation/Reference:
Explanation/Reference: 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. The following data types cannot be specified as SPARSE: geography geometry image next text timestamp user-defined data types
QUESTION 14
.
You currently store date information in two columns. One column contains the date in local time and one column contains the difference between local time and
UTC time. You need to store this data in a single column.
Which data type should you use?

A. time
B. datetime2
C. datetime2(5)
D. datetimeoffset
Correct Answer: D Explanation
Explanation/Reference:
Explanation/Reference:
datetimeoffset defines a date that is combined with a time of a day that has time zone awareness.

QUESTION 15
.
You have two partitioned tables named Transaction and TransactionHistory. You need to archive one of the partitions of the Transaction table to the
TransactionHistory table.
Which method should you use?

A. ALTER TABLE … SWITCH …
B. INSERT … SELECT …; TRUNCATE TABLE
C. ALTER PARTITION FUNCTION … MERGE …
D. ALTER PARTITION FUNCTION … SPLIT …

Correct Answer: B Explanation
Explanation/Reference:
QUESTION 16
.
You are creating a new table in a database. Your business requires you to store data in the table for only seven days.
You need to implement a partitioned table to meet this business requirement.
Which tasks should you complete?

A. Create the partition function Create the partition scheme Create the table
B. Create the partition function Create the table Create a filtered index
C. Add a secondary file to the primary filegroups Create the table Create the distributed partitioned view
D. Create the partition function Create the partition scheme Create the distributed partitioned view
Correct Answer: A Explanation

Explanation/Reference: QUESTION 17
.
You need to alter stored procedures to use the WITH RECOMPILE option.

Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)

A. Stored procedures implemented from CLR assemblies.
B. Stored procedures that require the FOR REPLICATION option.
C. Stored procedures that require the WITH ENCRYPTION option.
D. Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.

Correct Answer: CD Explanation
Explanation/Reference:
Explanation/Reference: As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes. SQL Server provides three ways to force a stored procedure to recompile: The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run. Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure.
A. CREATE and ALTER PROCEDURE syntax for CLR Stored Procedure does not have RECOMPILE option.
B. The RECOMPILE option is ignored for procedures created with FOR REPLICATION. C. ENCRYPTION option and RECOMPILE option can go together.
QUESTION 18
. You have a SQL Server database. The database contains two schemas named Marketing and Sales. The Marketing schema is owned by a user named MarketingManager. The Sales schema is owned by a user named SalesManager. A user named John must be able to access the Sales.Orders table by using a stored procedure named Marketing.GetSalesSummary. John is not granted a SELECT permission on the Sales.Orders table. A user named SalesUser does have SELECT permission on the Sales.Orders table. You need to implement appropriate permissions for John and the stored procedure Marketing.GetSalesSummary. What should you do?
A. Marketing.GetSalesSummary should be created by using the EXECUTE AS ‘SalesUser’ clause. John should be granted EXECUTE permission on Marketing.GetSalesSummary.
B. Marketing.GetSalesSummary should be created by using the EXECUTE AS OWNER clause. John should be granted EXECUTE WITH GRANT OPTION on Marketing.GetSalesSummary.
C. Marketing.GetSalesSummary should be created by using the EXECUTE AS CALLER clause. John should be granted IMPERSONATE permission for the user named SalesUser.
D. Marketing.GetSalesSummary should be created without an EXECUTE AS clause. John should be granted SELECT permission on the Sales.Orders table.

Correct Answer: A Explanation
Explanation/Reference:
Explanation/Reference:
1.
When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module. So John should
be granted EXECUTE permission on Marketing.
GetSalesSummary stored procedure.
2.
Additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. The
user executing the module is, in effect, impersonating the specified user. Because John is not granted a SELECT permission on the Sales.Orders table which is
referenced by the stored procedure, EXECUTE AS CALLER is not suitable. (CALLER specifies the statements inside the module are executed in the context of
the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that
are referenced by the module.) Because the user named SalesUser DOES have SELECT permission on the Sales.Orders table, he can be specified in EXECUTE
AS clause. It means that Marketing. GetSalesSummary stored procedure should be created by using the EXECUTE AS ‘SalesUser’ clause.

QUESTION 19
.
You need to create a stored procedure that accepts a table-valued parameter named @Customers.
Which code segment should you use?

A. CREATE PROCEDURE AddCustomers (@Customers varchar(max))
B. CREATE PROCEDURE AddCustomers (@Customers Customer READONLY)
C. CREATE PROCEDURE AddCustomers (@Customers CustomerType OUTPUT)
D. CREATE PROCEDURE ADDCUSTOMERS (@Customers varchar (max)) AS EXTERNAL NAME Customer.Add.NewCustomer

Correct Answer: B Explanation
Explanation/Reference:
Explanation/Reference:
To create and use table-valued parameters, follow these steps:

1.
Create a table type and define the table structure.
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
2.
Declare a routine that has a parameter of the table type. /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE

usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location] ([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
3.
Declare a variable of the table type, and reference the table type. /* Declare a variable that references the type. */ DECLARE @LocationTVP
AS LocationTableType;
4.
Fill the table variable by using an INSERT statement.
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
5.
After the table variable is created and filled, you can pass the variable to a routine. /* Pass the table variable data to a stored procedure. */ EXEC
usp_InsertProductionLocation @LocationTVP;
Restrictions
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE,
DELETE, or INSERT on a table-valued parameter in the body of a routine.

QUESTION 20
.
You have a computed column that is implemented with a user-defined function. The user-defined function returns a formatted account number. The column must
be indexed to provide adequate search performance.
You plan to create an index on the computed column. You need to identify the valid combination of ObjectPropertyEX values for the user-defined function.
Which combination should you use?

A. IsDeterministic = True IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
B. IsDeterministic = True IsSystemVerified = True IsPrecise = True IsTableFunction = True
C. IsDeterministic = False IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
D. IsDeterministic = False IsSystemVerified = True IsPrecise = True SystemDataAccess = False

Correct Answer: A Explanation
Explanation/Reference:

The Microsoft 70-433 Questions & Answers covers all the knowledge points of the real exam. We update our product frequently so our customer can always have the latest version of Microsoft 70-433.We provide our customers with the excellent 7×24 hours customer service.We have the most professional Microsoft 70-433 expert team to back up our grate quality products.If you still cannot make your decision on purchasing our product, please try our Microsoft 70-433 free pdf