Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 111:

  You have an application that is used by international clients. All clients connect by using Windows Authentication. You need to ensure that system and user-defined error messages are displayed in the localized language for the clients.

  What should you do? (Each correct answer represents part of the solution. Choose two.)

  A. Use @@LANGUAGE function
  B. Use default language for each login
  C. Use @lang parameter of sp_addmessage
  D. Use the "set language" option of sp_configure
  B. Use default language for each login
  C. Use @lang parameter of sp_addmessage

  ---

  sp_configure is used to specify the default language for all newly created logins. CREATE LOGIN expression has DEFAULT_LANGUAGE = language option. It specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. sp_addmessage stores a new user-defined error message in an instance of the SQL Server Database Engine. One of the options is 'language. It is the language for this message, that is the language in which message is written. When language is omitted, the language is the default language for the session.

• Question 112:

  You have been tasked with creating a table named dbo.Widgets. You need to insert five rows into the dbo.Widgets table and return WidgetID for each of the five rows that have been inserted.

  Which Transact-SQL batch should you use?

  A. CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25)); GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
  B. CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25) ); GO INSERT dbo.Widgets (WidgetName) VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive'); SELECT SCOPE_IDENTITY();
  C. CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25) ); GO INSERT dbo.Widgets (WidgetName) VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive'); SELECT SCOPE_IDENTITY();
  D. CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25)); GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
  A. CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25)); GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');

• Question 113:

  You have two tables named Customer and SalesOrder. In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table.

  You execute the following query to list all customers that have had at least one sale.

  SELECT * FROM Customer

  WHERE Customer.CustomerID IN (SELECT Customer.CustomerID FROM SalesOrder)

  You need to identify the results of the query.

  Which results will the query return?

  A. No rows
  B. A warning message
  C. The 1000 rows in the Customer table
  D. The 900 rows in the Customer table with matching rows in the SalesOrder table
  D. The 900 rows in the Customer table with matching rows in the SalesOrder table

• Question 114:

  You have implemented change tracking on a table named Sales.SalesOrder. You need to determine all columns that have changed since the minimum valid version.

  Which function should you use?

  A. CHANGE_TRACKING_CURRENT_VERSION
  B. CHANGE_TRACKING_IS_COLUMN_IN_MASK
  C. CHANGETABLE with the CHANGES argument
  D. CHANGETABLE with the VERSION argument
  C. CHANGETABLE with the CHANGES argument

  ---

  CHANGETABLE (

  { CHANGES table , last_sync_version

  | VERSION table , } )

  [AS] table_alias [ ( column_alias [ ,...n ] )

  CHANGES table , last_sync_version

  Returns tracking information for all changes to a table that have occurred since the version that is specified by last_sync_version.

  VERSION table, { }

  Returns the latest change tracking information for a specified row. Primary key values must identify the row.

  identifies the primary key columns and specifies the values.

• Question 115:

  You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderDetail and a view named Sales.ProductOrders. The view has the following definition:

  

  The Sales.SalesOrderDetail table contains 5 million rows. Report queries that join to this view consume excessive disk I/O.

  You need to create an index on the view.

  Which Transact-SQL statement or statements should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

• Question 116:

  You are using Microsoft SQL Server 2008 Enterprise Edition. You need to maintain a history of all data modifications made to a table, including the type of modification and the values modified.

  Which tracking method should you use?

  A. Database Audit
  B. Change Tracking
  C. C2 Audit Tracing
  D. Change Data Capture
  D. Change Data Capture

  ---

  Database Audit Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

  Change Data Capture Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

  Change Tracking Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured.

  Topic 3, Volume C

• Question 117:

  You notice that a database server is responding slowly to queries. You run the following dynamic management views (DMV) query on the server.

  SELECT TOP (10)

  wait_type,

  wait_time_ms

  FROM sys.dm_os_wait_stats

  ORDER BY wait_time_ms DESC;

  The query returns a top wait type of SOS_SCHEDULER_YIELD.

  You need to identify what is causing the server response issues.

  Which resource should you investigate first?

  A. Disk
  B. CPU
  C. Memory
  D. Network
  B. CPU

• Question 118:

  You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer. The table has the following definition:

  

  The database also has a table named PreferredCustomerList. Data will be added to the PreferredCustomerList table regularly. The PreferredCustomerList table has the following definition:

  

  You need to create a view that returns all records and columns of the Customer table that are also present in the PreferredCustomerList table. Which Transact-SQL statement should you use?

  

  B. C. D.
  B. C. D.

• Question 119:

  You are a database developer responsible for maintaining an application. The application has a table named Programs that has the following definition:

  

  You need to modify the Customers table to meet the following requirements:

  ID must be the Primary Key.

  The clustered index must be on the ID column.

  The Active column must have a default value of 1, and must allow values of only 0 or 1. The Priority column must have values of "a", "b", or "c" The Postal column must contain a correctly formatted five-digit numeric Postal Code.

  Which Transact-SQL statement or statements should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 120:

  You need to ensure that tables are not dropped from your database.

  What should you do?

  A. Create a DDL trigger that contains COMMIT.
  B. Create a DML trigger that contains COMMIT.
  C. Create a DDL trigger that contains ROLLBACK.
  D. Create a DML trigger that contains ROLLBACK.
  C. Create a DDL trigger that contains ROLLBACK.

  ---

  DDL triggers can execute either when a DDL statement is executed or when the user logs on to the SQL Server instance. DDL triggers can be scoped at either the database or instance level. To scope a DDL trigger at the instance level, you use the ON ALL SERVER option. To scope a DDL trigger at the database level, you use the ON DATABASE option.

  The following is an example of a DDL trigger: CREATE TRIGGER tddl_tabledropalterprevent ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You are attempting to drop or alter tables in production!' ROLLBACK; Almost all DDL commands run within the context of a transaction. Because a DDL trigger also runs within the same transaction context, any DDL statement running in the context of a transaction can be rolled back.