Microsoft 70-433 Exam Questions has already been updated recently! Braindump2go Provide you the Latest 70-433 Exam Dumps: 70-433 PDF and 70-433 VCE! Braindump2go helps you keep in step with Microsoft Official Exam Center!
Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Certification Provider: Microsoft
Keywords: 70-433 Exam Dumps,70-433 Practice Tests,70-433 Practice Exams,70-433 Exam Questions,70-433 PDF,70-433 VCE Free,70-433 Book,70-433 E-Book,70-433 Study Guide,70-433 Braindump,70-433 Prep Guide
QUESTION 201
You administer a Microsoft SQL Server 2008 database.
The database contains tables named Customer, Subscriptions, and Orders that have the following definitions:
Customers are considered active if they meet the following requirements:
You need to create a view that shows unique rows where the customer either has made an Order or has a Subscription.
Which Transact-SQL statement should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
QUESTION 202
You are the administrator of a SQL Server 2008 instance with a database named DB1.
which contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID(‘Service.Table’) IS NOT NULL
04 DROP TABLE Service.Table;
05
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 …
12 GO
The following exception is raised when a user tries to invoke Procedure1,
“Msg 262, Level 14, State 1, Procedure Procedure1, Line 5
CREATE TABLE permission denied in database ‘DB1’.”
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?
A. Between lines 01 and 02, you should insert the WITH EXECUTE AS ‘dbo’ clause.
B. Between lines 01 and 02, you should insert the EXECUTE AS USER = ‘dbo’ statement.
C. You should give the user the ALTER permission on the Service schema
D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.
Answer: A
QUESTION 203
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
Answer: D
Explanation:
datetimeoffset defines a date that is combined with a time of a day that has time zone awareness.
QUESTION 204
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
QUESTION 205
You work as a network database administrator at ABC.com.
ABC.com has a database server named ABC-DB01 that hosts the Sales database.
The Sales database has a table named Shipping that has two columns that stored date data.
You need to alter the table to be able to store a customer’s local time as well as specifying the difference between the customer’s local and UTC time.
How would you alter the table to store this data in one column?
A. By making use of the time data type.
B. By making use of the date data type.
C. By making use of the text data type.
D. By making use of the datetimeoffset data type.
E. By making use of the smalldatetime data type.
Answer: D
QUESTION 206
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
Explanation:
As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes.
SQL Server provides three ways to force a stored procedure to recompile:
The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed.
You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure.
A. CREATE and ALTER PROCEDURE syntax for CLR Stored Procedure does not have RECOMPILE option.
B. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.
C. ENCRYPTION option and RECOMPILE option can go together.
QUESTION 207
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
QUESTION 208
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
Explanation:
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 209
You work as a network database administrator at ABC.com.
You need to implement error handling by using a TRY…CATCH block.
How would you pass control to your CATCH block when an error is raised?
A. By using a severity level of 2.
B. By using a severity level of 4.
C. By using a severity level of 8.
D. By using a severity level of 16.
Answer: D
QUESTION 210
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
Explanation:
Fill Factor
The fill-factor option is provided for fine-tuning index data storage and performance.
When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 60 means that 40 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table.
Page Splits
A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table. When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations.
Fragmentation
Fragmentation breaks down to physical and logical fragmentation (or, internal and external fragmentation).
Physical/Internal fragmentation is caused when there is wasted space in the index caused by page splits, deletes, FILLFACTOR and PAD_INDEX. Logical/External fragmentation is when the pages that make up the leaf levels of the index are not in good order. This is usually caused by page splits. Both cause performance problems. Internal fragmentation causes problems because the indexes get bigger and bigger requiring more and more pages to store the data, which means that reads from the index slow things down. External fragmentation causes problems because when the data needs to be read from the index it has to skip all over the place following the links between pages, again, slowing things down.
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. You can remedy index fragmentation by either reorganizing an index or by rebuilding an index.
To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause.
Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.
The index is reorganized within the existing pages allocated to it; no new pages are allocated.
Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocatiang new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.
You can use the CREATE INDEX with the DROP_EXISTING clause or ALTER INDEX with the REBUILD clause statements to set the fill-factor value for individual indexes. EXEC sys.sp_configure ‘fill factor (%)’, ’60’; will modify the server default fill factor value.
Braindump2go Regular Updates of Microsoft 70-433 Preparation Materials Exam Dumps, with Accurate Answers, Keeps the Members One Step Ahead in the Real 70-433 Exam. Field Experts with more than 10 Years Experience in Certification Field work with us.
