Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 171:

  You administer a Microsoft SQL Server 2008 database named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

  The table has the following definition:

  

  The index has the following definition:

  

  You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class.

  You need to refresh statistics on the Production.Product table.

  Which Transact-SQL statement should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  I. Option I
  J. Option J
  B. Option B

• Question 172:

  You need to generate the following XML document.

  Product1

  Product2

  Product3

  Product4

  Which query should you use?

  A. SELECT Price, ProductName FROM Products AS ProductExport FOR XML PATH('Product')
  B. SELECT Price, ProductName FROM Products FOR XML AUTO, ROOT('ProductExport')
  C. SELECT Price [@Price], ProductName AS [*] FROM Products AS ProductExport FOR XML AUTO, ELEMENTS
  D. SELECT Price [@Price], ProductName AS [*] FROM Products FOR XML PATH('Product'),ROOT('ProductExport')
  D. SELECT Price [@Price], ProductName AS [*] FROM Products FOR XML PATH('Product'),ROOT('ProductExport')

• Question 173:

  You have the following rows in the Customer Table:

  

  You write the following query to return all customers that do not have NULL or 'Dormant' for their status:

  SELECT *

  FROM Customer

  WHERE Status NOT IN (NULL, 'Dormant')

  You need to identify the results of the query.

  Which result should you expect?

  

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

• Question 174:

  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);
  D. CREATE NONCLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber) INCLUDE (Country, StateProvince);

• Question 175:

  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
  B. CREATE PROCEDURE AddCustomers (@Customers Customer READONLY)

  ---

  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 176:

  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
  D. datetimeoffset

  ---

  datetimeoffset defines a date that is combined with a time of a day that has time zone awareness.

• Question 177:

  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.
  A. Marketing.GetSalesSummary should be created by using the EXECUTE AS 'SalesUser' clause. John should be granted EXECUTE permission on Marketing.GetSalesSummary.

  ---

  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 178:

  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;
  B. CREATE VIEW dbo.Region1Sales AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1 WITH CHECK OPTION;

  ---

  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 179:

  You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4.

  Which Transact-SQL statement should you use?

  A. NTILE(1)
  B. NTILE(4)
  C. NTILE(25)
  D. NTILE(100)
  B. NTILE(4)

• Question 180:

  You are updating a database table.

  You need to partition the table to store only the last 1000 rows of data in the table.

  What should you do?

  A. Create the partition function and the partition scheme, and update the table.
  B. Create the partition function, the partition scheme, and the distributed partitioned view.
  C. Create the partition function, update the table, and create a filtered index.
  D. Add a secondary file to the primary filegroups, update the table, and create the distributed partitioned view.
  A. Create the partition function and the partition scheme, and update the table.