Microsoft 70-466 Implementing Data Models and Reports with Microsoft SQL Server 2012 Exam Practice Test

Page: 1 / 14
Total 186 questions
Question 1

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain usercreated stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching.

Which advanced server option should you modify?



Answer : C

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hocworkloads-server-configuration-option


Question 2

You plan to design an application that temporarily stores data in a SQL Azure database.

You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.

Which type of objects should you identify?



Answer : C

There are two types of temporary tables: local and global. Local temporary tables are visible only to their

creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

References: https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx


Question 3

You have a database for a mission-critical web application. The database is stored on a SQL Server 2012

instance and is the only database on the instance. The application generates all T-SQL statements dynamically and does not use stored procedures.

You need to maximize the amount of memory available for data caching.

Which advanced server option should you modify?



Answer : C

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server helps user to isolate their database from the instance in 4 ways.

Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)

All metadata are defined using the same collation.

User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.

The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases


Question 4

You are modifying a SQL Server Analysis Services (SSAS) multidimensional database.

You have identified a dimension that is no longer used by any cubes.

You need to delete the dimension.

What should you do?



Answer : D

To delete a dimension in SQL Server Data Tools.

In Solution Explorer, right-click the dimension that you want to delete, and then click Delete.

References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/databasedimensions-modify-or-delete-a-database-dimension-in-solution-explorer


Question 5

You are developing a multidimensional project that includes a dimension named Organization.

The dimension is based on the DimOrganization table in the data warehouse.

The following diagram illustrates the table design.

The Organization dimension includes a parent-child hierarchy named Organizations.

The dimension includes the following dimension attributes:

Organization, which is a key attribute

Organizations, which defines the parent-child hierarchy

Currency Code, which is a regular attribute

When users browse the dimension, three hierarchies are visible to them.

You need to ensure that the Organization hierarchy is not visible to users.

What should you do?



Answer : D

The DimensionAttribute.AttributeHierarchyVisible Property gets or sets whether the attribute hierarchy is visible to client applications.

References: https://msdn.microsoft.com/en-us/library/

microsoft.analysisservices.dimensionattribute.attributehierarchyvisible.aspx


Question 6

You have an application that uses a view to access data from multiple tables.

You need to ensure that you can insert rows into the underlying tables by using the view.

What should you do?



Answer : C

Using INSTEAD OF triggers you can override an INSERT, UPDATE, or DELETE operation on a view. For

example, you might define an INSTEAD OF INSERT trigger on a view to replace the standard INSERT statement.

References: https://msdn.microsoft.com/en-us/library/def01zh2.aspx


Question 7

You are developing a SQL Server Analysis Services (SSAS) multidimensional database. The underlying data source does not have a time dimension table.

You need to implement a time dimension.

What should you do?



Answer : A

In Microsoft SQL Server Analysis Services, you can use the Dimension Wizard in SQL Server Data Tools

(SSDT) to create a time dimension when no time table is available in the source database.

Generate a time table in the data source Select this option when you have permission to create objects in

the underlying data source. The wizard will then generate a time table and store this table in the data

source. The wizard then creates the time dimension from this time table.

Generate a time table on the server Select this option when you do not have permission to create objects in the underlying data source. The wizard will then generate and store a table on the server instead of in the data source. (The dimension created from a time table on the server is called a server time dimension.) The wizard then creates the server time dimension from this table.

References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/create-a-timedimension-by-generating-a-time-table


Page:    1 / 14   
Total 186 questions