How to 100% pass 70-458 exam? GreatExam provides the guaranteed 70-458 exam preparation material to boost up your confidence in 70-458 exam. Successful candidates have provided their reviews about our 70-458 dumps. Now GreatExam supplying the new version of 70-458 VCE and PDF dumps. We ensure our 70-458 exam questions are the most complete and authoritative compared with others’, which will ensure your 70-458 exam pass.
QUESTION 1
Hotspot Question
You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
– The first row of the file contains the first row of data.
– Each record is of the same length.
– The date follows the U.S. date format.
– The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms178804.aspx
http://msdn.microsoft.com/en-us/library/ms187828.aspx
QUESTION 2
Drag and Drop Question
You administer a Microsoft SQL Server 2012 database.
You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the CarrierTrackingNumber column.
Which four Transact- SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://msdn.microsoft.com/en-us/library/ms189280.aspx
QUESTION 3
You administer a SQL Server 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions.
A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. DENY SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. DENY SELECT ON Object::Regions FROM Sales
E. REVOKE SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Schema::Customers FROM UserA
H. EXEC sp_droproleraember ‘Sales’, ‘UserA’
I. REVOKE SELECT ON Object::Regions FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales
Answer: F
Explanation:
http://msdn.microsoft.com/en-us/library/ms188369.aspx
http://msdn.microsoft.com/en-us/library/ms187750.aspx
http://msdn.microsoft.com/en-us/library/ff848791.aspx
QUESTION 4
You administer a SQL Server 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions.
A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from the Regions table.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemerr.ber ‘Sales’, ‘DserA’
J. EXEC sp droprolemember ‘Sales’, ‘UserA’
Answer: E
Explanation:
http://msdn.microsoft.com/en-us/library/ms188369.aspx
http://msdn.microsoft.com/en-us/library/ms187750.aspx
http://msdn.microsoft.com/en-us/library/ff848791.aspx
QUESTION 5
You administer a SQL 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions.
A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to remove the Select permission for UserA on the Regions table.
You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Schema::Customers FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. REVOKE SELECT ON Object:: Regions FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. EXEC sp_droproiemember ‘Sales’, ‘DserA’
G. REVOKE SELECT ON Schema::Customers FROM UserA
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema:: Customers FROM Sales
J. REVOKE SELECT ON Schema:: Customers FROM Sales
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms188369.aspx
http://msdn.microsoft.com/en-us/library/ms187750.aspx
http://msdn.microsoft.com/en-us/library/ff848791.aspx
QUESTION 6
You administer a SQL Server 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions.
A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met:
– The Sales role does not have the Select permission on the Customers schema.
– UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?
A. DENY SELECT CN Object::Regions FROM Sales
B. DENY SELECT CN Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT CN Object::Regions FRCM UserA
F. DENY SELECT CN Schema::Customers FRCM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FOR UserA
I. EXEC sp addrolemember ‘Sales’, ‘UserA’
J. EXEC sp droprolemember ‘Sales’. ‘UserA’
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms188369.aspx
http://msdn.microsoft.com/en-us/library/ms187750.aspx
http://msdn.microsoft.com/en-us/library/ff848791.aspx
QUESTION 7
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
– A data file of 2 terabytes is located on a dedicated LUN (drive D).
– A transaction log of 10 GB is located on a dedicated LUN (drive E).
– Drive D has 1 terabyte of free disk space.
– Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.
Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered.
Which backup option should you use?
A. STANDBY
B. Differential
C. FULL
D. CHECKSUM
E. BULK_LOGGED
F. CONTINUE_AFTER_ERROR
G. SIMPLE
H. DBO_ONLY
I. COPY_ONLY
J. SKIP
K. RESTART
L. Transaction log
M. NO_CHECKSUM
N. NORECOVERY
Answer: F
QUESTION 8
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
– A data file of 2 terabytes is located on a dedicated LUN (drive D).
– A transaction log of 10 GB is located on a dedicated LUN (drive E).
– Drive D has 1 terabyte of free disk space.
– Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.
Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours.
Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.
You need to perform a full database backup that will be restored on the development server. Which backup option should you use?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. BULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE_AFTER_ERROR
Answer: L
QUESTION 9
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
– A data file of 2 terabytes is located on a dedicated LUN (drive D).
– A transaction log of 10 GB is located on a dedicated LUN (drive E).
– Drive D has 1 terabyte of free disk space.
– Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.
Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours.
Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You implement log shipping of the financial database to another SQL Server 2012 instance.
You decide to failover to this secondary database.
You need to ensure that all transactions will be replicated to the secondary database.
Which backup option should you use?
A. Differential
B. Transaction
C. FULL
D.
E. SIMPLE
BULK_LOGGED
F. SKIP
G. RESTART
H. STANDBY
I. CHECKSUM
J. DBO_ONLY
K. COPY ONLY
L. NORECOVERY
M. NO_CHECKSUM
N. CONTINUE_AFTER_ERROR
Answer: L
QUESTION 10
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
– A data file of 2 terabytes is located on a dedicated LUN (drive D).
– A transaction log of 10 GB is located on a dedicated LUN (drive E).
– Drive D has 1 terabyte of free disk space.
– Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.
Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the backup size is as small as possible.
Which backup should you perform every two hours?
A. BULK_LOGGED
B. NO_CHECKSUM
C. FULL
D. RESTART
E. CHECKSUM
F. STANDBY
G. DBO.ONLY
H. NORECOVERY
I. SIMPLE
J. SKIP
K. Transaction tog
L. COPY_ONLY
M. Differential
N. CONTINUE_AFTER_ERROR
Answer: K
QUESTION 11
Drag and Drop Question
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.
You are developing the part of the SSIS package that populates the ProjectDates dimension table.
The business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
– ExpectedStartDate – New values should be tracked over time.
– ActualStartDate – New values should not be accepted.
– ExpectedEndDate – New values should replace existing values.
– ActualEndDate – New values should be tracked over time.
You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which settings should you select? (To answer, select the appropriate setting or settings in the answer area.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms141715.aspx
http://msdn.microsoft.com/en-us/library/ms141662.aspx
QUESTION 12
You are developing a data flow transformation to merge two data sources.
One source contains product data and the other source contains data about the country in which the product was manufactured.
Both data sources contain a two-character CountryCode column and both use SQL Server.
Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.
You need to ensure that the Merge Join transformation works correctly without additional transformations.
What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. set the appropriate SortKeyPosition properties on the data sources.
B. set the ValidateExternalMetaData property on the Merge Join transformation to True.
C. set the IsSorted property on both data sources.
D. Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
Answer: AC
Explanation:
http://msdn.microsoft.com/en-us/library/ms141775.aspx
http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx
QUESTION 13
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources.
One source is hosted in a SQL Azure database.
Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. Multicast
B. Merge Join
C. Term Extraction
D. union All
E. Merge
Answer: DE
Explanation:
http://msdn.microsoft.com/en-us/library/ms141703.aspx
http://msdn.microsoft.com/en-us/library/ms141775.aspx
http://msdn.microsoft.com/en-us/library/ms141020.aspx
http://msdn.microsoft.com/en-us/library/ms141809.aspx
http://msdn.microsoft.com/en-us/library/ms137701.aspx
QUESTION 14
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure
E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms137786.aspx
QUESTION 15
You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?
A. Expression task
B. Script task
C. Execute SQL task
D. Execute Process task
E. Term Extraction transformation
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/hh213137.aspx
QUESTION 16
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
Answer: D
QUESTION 17
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
A. Run the Configuration component in the Data Quality Client.
B. Install ADOMD.NET.
C. Run the Data Quality Server Installer.
D. Make the data available for DQS operations.
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/gg492277.aspx
QUESTION 18
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file.
A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?
A. Create an Integration Services package and use a Data Profiling transform.
B. Create a custom .NET application based on the Knowledgebase class.
C. Create a data quality project.
D. Create a CLR stored procedure based on the Knowledgebase class.
E. Create a Master Data Services (MDS) business rule.
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/hh213052.aspx
http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspx http://msdn.microsoft.com/en-us/library/bb895263.aspx
QUESTION 19
You are implementing the indexing strategy for a fact table in a data warehouse.
The fact table is named Quotes.
The table has no indexes and consists of seven columns:
– [ID]
– [QuoteDate]
– [Open]
– [Close]
– [High]
– [Low]
– [Volume]
Each of the following queries must be able to use a columnstore index:
– SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
– SELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND
‘20101231’.
– SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
You need to ensure that the indexing strategy meets the requirements.
The strategy must also minimize the number and size of the indexes.
What should you do?
A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
B. Create three coiumnstore indexes:
One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low]
One containing [QuoteDate] and [Volume]
C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
D. Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close]
One containing [ID], [QuoteDate], [High], and [Low]
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/gg492088.aspx
http://msdn.microsoft.com/en-us/library/gg492153.aspx
QUESTION 20
You are designing a data warehouse with two fact tables.
The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?
A. Join the two fact tables.
B. Merge the fact tables.
C. Create a time dimension that can join to both fact tables at their respective granularity.
D. Create a surrogate key for the time dimension.
Answer: D
Explanation:
With dimensionally modeled star schemas or snowflake schemas, decision support queries follow a typical pattern: the query selects several measures of interest from the fact table, joins the fact rows with one or several dimensions along the surrogate keys, places filter predicates on the business columns of the dimension tables, groups by one or several business columns, and aggregates the measures retrieved from the fact table over a period of time.
The following demonstrates this pattern, which is also sometimes referred to as a star join query:
– select ProductAlternateKey,
– CalendarYear,sum(SalesAmount)
– from FactInternetSales Fact
– join DimTime
– on Fact.OrderDateKey = TimeKey
– join DimProduct
– on DimProduct.ProductKey =
– Fact.ProductKey
– where CalendarYear between 2003 and 2004
– and ProductAlternateKey like ‘BK%’
– group by ProductAlternateKey,CalendarYear
All the 70-458 braindumps are updated. Get a complete hold of 70-458 PDF dumps and 70-458 practice test with free VCE player through GreatExam and boost up your skills.