Microsoft  70-433 Exam real braindumps answers

Exam70-433:
TS: Microsoft SQL Server 2008, Database Development

Published: December 10, 2008
Language(s): English, French, German, Japanese, Russian, Spanish, Portuguese (Brazil), Chinese (Simplified)
Audience(s): Developers
Technology: Microsoft SQL Server 2008
Type: Proctored Exam

overview
Audience Profile
Candidates should have one to two years of experience using Microsoft SQL Server 2008 or two to three years of experience

using a previous version of SQL Server. Candidates for this exam are IT professionals who work in jobs in which database

development is their main area of responsibility, or they work in environments in which databases are central to their

primary job roles. These job roles include the following:
IT generalists who work in environments in which SQL Server 2008 is part of an overall solution
IT professionals who manage systems that run SQL Server 2008
Developers who build applications that use SQL Server 2008
IT professionals who work with third-party applications that are built on SQL Server 2008
Credit Toward Certification
When you pass Exam 70-433: TS: Microsoft SQL Server 2008, Database Development, you complete the requirements for the

following certification(s):
Microsoft Certified Technology Specialist (MCTS): SQL Server 2008, Database Development
Microsoft Certified Technology Specialist (MCTS): SQL Server 2008, Database Development
 
Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft.

Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in

which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format. 

Microsoft  70-433 exam braindumps and answers

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;

Answer: B

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

Answer: AC

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;

Answer: B

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;

Answer: D

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;

Answer: B

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);

Answer: D

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))

Answer: C

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);

Answer: C

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;

Answer: B

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

Answer: C

70-447  70-444  70-443  70-441   70-431  70-454   70-647Big5  70-443GB2312  70-577  70-685  Test4pass