Pass4Test의 Microsoft 70-454 (UPG: Transiton MCITP SQL Serv 2005 to SQL Serv 2008 DBD) 덤프는 인터넷에서 검색되는 Microsoft 70-454 (UPG: Transiton MCITP SQL Serv 2005 to SQL Serv 2008 DBD) 시험공부자료중 가장 출중한 시험준비 자료입니다. Microsoft 70-454 (UPG: Transiton MCITP SQL Serv 2005 to SQL Serv 2008 DBD) 덤프를 공부하면 시험패스는 물론이고 IT지식을 더 많이 쌓을수 있어 일거량득입니다.자격증을 취득하여 자신있게 승진하여 연봉협상하세요.

 

 

NO.1 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

NO.2 You are creating a new table in a database.Your business requires you to store data in the table for
only seven days.You need to implement a partitioned table to meet this business requirement.Which tasks
should you complete?
A.Create the partition function Create the partition scheme Create the table
B.Create the partition function Create the table Create a filtered index
C.Add a secondary file to the primary filegroups Create the table Create the distributed partitioned view
D.Create the partition function Create the partition scheme Create the distributed partitioned view
Answer: A

NO.3 You have a computed column that is implemented with a user-defined function.The user-defined
function returns a formatted account number.The column must be indexed to provide adequate search
performance.You plan to create an index on the computed column.You need to identify the valid
combination of ObjectPropertyEX values for the user-defined function.Which combination should you
use?
A.IsDeterministic = True IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
B.IsDeterministic = True IsSystemVerified = True IsPrecise = True IsTableFunction = True
C.IsDeterministic = False IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
D.IsDeterministic = False IsSystemVerified = True IsPrecise = True SystemDataAccess = False
Answer: A

NO.4 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
Answer: B

NO.5 You need to alter stored procedures to use the WITH RECOMPILE option.Which types of stored
procedures should you alter? (Each correct answer represents a complete solution.Choose two.)
A.Stored procedures implemented from CLR assemblies.
B.Stored procedures that require the FOR REPLICATION option.
C.Stored procedures that require the WITH ENCRYPTION option.
D.Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.
Answer: CD

NO.6 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

NO.7 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

NO.8 You have multiple tables that represent properties of the same kind of entities.The property values are
comprised of text, geometry, varchar(max), and user-defined types specified as 'bit NOT NULL' data
types.You plan to consolidate the data from multiple tables into a single table.The table will use
semi-structured storage by taking advantage of the SPARSE option.You are tasked to identify the data
types that are compatible with the SPARSE option.Which data type is compatible with the SPARSE
option?
A.text
B.geometry
C.varchar(max)
D.A user-defined type defined as 'bit NOT NULL'
Answer: C

NO.9 You have two partitioned tables named Transaction and TransactionHistory.You need to archive one of
the partitions of the Transaction table to the TransactionHistory table.Which method should you use?
A.ALTER TABLE ...SWITCH ...
B.INSERT ...SELECT ...; TRUNCATE TABLE
C.ALTER PARTITION FUNCTION ...MERGE ...
D.ALTER PARTITION FUNCTION ...SPLIT ...
Answer: B

NO.10 You are creating a table that stores the GPS location of customers.You need to ensure that the table
allows you to identify customers within a specified sales boundary and to calculate the distance between
a customer and the nearest store.Which data type should you use?
A.geometry
B.geography
C.nvarchar(max)
D.varbinary(max) FILESTREAM
Answer: B

NO.11 You plan to add a new column named SmallKey to the Sales.Product table that will be used in a unique
constraint.You are required to ensure that the following information is applied when adding the new
column:
'a1' and 'A1' are treated as different values 'a' and 'A' sort before 'b' and 'B' in an ORDER BY clause You
need to select the collation that meets the requirements for the new column.Which collation should you
select?
A.Latin1_General_BIN
B.SQL_Latin1_General_CP1_CI_AI
C.SQL_Latin1_General_CP1_CI_AS
D.SQL_Latin1_General_CP1_CS_AS
Answer: D

NO.12 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

NO.13 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