Question 1.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to  develop database strategies. You are in the process of developing a strategy that has three tables named ABC1, ABC2, and ABC3. The ABC1 and ABC2 have the integer and varchar data type column types configured respectively. ABC3 has both the integer and varchar data types configured as column types. You have configured rowlevel compression on ABC1, and page-level compression on ABC2 and ABC3.

Which of the following describes the reason for this configuration?

A. It allows for the reduction of disk space usage, while modifying the data types in the tables of the database
B. It allows for the reduction of disk space usage without modifying the data types in the tables of the database
C. It allows for the increase of disk space usage, while modifying the data types in the tables of The database
D. It allows for the increase of disk space usage, while modifying the data types in the tables of the database

Answer: B

Explanation:

Question 2.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You have received instructions to design a database strategy that includes a table which hosts data imported from an outside source. This data includes a field named Transaction Time that should be configured to make use of hh:mm:ss[.n] format. You have been informed that the data type selected for the TransactionTime field must allow for storage to be kept to a minimum. You, therefore, make use of the time(1) data type.

Which of the following is TRUE with regards to the time data type?

A. The default fractional precision is 7 (100ns).
B. The default fractional precision is 3 (100ns).
C. The time produced by the time data type is not time zone aware and is based on a 24-hour clock.
D. The time produced by the time data type is time zone aware and is based on a 12-hour clock.

Answer: A, D

Explanation:

Question 3.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You have received instructions to design a strategy that has two tables named ABCVendor and ABCItem. You have configured a foreign key constraint between the ABCVendor and ABCItem tables on the ABCVendorID column. You have configured the ABCVendor table to display a 0 value for the ABCVendorID when a vendor is removed. You also want make sure that the ABCVendorID value in the ABCItem table is set to 0 when a vendor is removed. You then create a default constraint on the ABCVendorID column in the ABCItem table, which is used to set the value to 0.

Which of the following actions should you take NEXT?

A. You should consider setting the ON DELETE property of the foreign key constraint to Null.
B. You should consider setting the ON DELETE property of the foreign key constraint to Default.
C. You should consider setting the ON DELETE property of the foreign key constraint to Cascade.
D. You should consider setting the ON DELETE property of the foreign key constraint to No Action.

Answer: B

Explanation:

Question 4.
You are employed as a database developer at ABC.com. You make use of a SQL Server 2008 instance to develop database strategies. You are in the process of designing a database strategy that makes use of the Integration Services and Microsoft Sync Framework SQL Server components.

Which of the following is TRUE with regards to the use of these components?

A. Microsoft Sync Framework allows for collaboration and offline access for applications, services, and devices.
B. Microsoft Sync Framework prevents collaboration and offline access for applications, services, and devices.
C. Integration Services allows for the merging of data from Heterogeneous Data Stores.
D. Integration Services prevents the merging of data from Heterogeneous Data Stores.

Answer: A, C

Explanation:

Question 5.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You have received instructions to design a strategy that helps ABC.com’s administration manager. You have created three entities named User, UserTask, and UserAssignment. You have configured the User entity to make use of the UserID attribute, the UserTask entity to make use of the UserTaskID attribute, and the UserAssignment entity to make use of the UserAssignmentID attribute. You have been informed that the strategy must allow for users to be assigned multiple tasks. You also need to ensure that a task is deleted when it has been completed, and that the assignment linked to that task is also deleted. Furthermore, a NULL value has to replace the user reference to the user assignment as soon as a user becomes unavailable to finish a task. 

Which combination of the following actions should you take? (Choose all that apply.)

A. You should consider configuring Foreign Key constraints on the UserTaskID and UserID attributes in the UserAssignment entity.
B. You should consider configuring Foreign Key constraints on the UserTaskID and UserID  attributes in the UserTask and User entities respectively.
C. You should consider referencing the UserTask and User entities respectively.
D. You should consider referencing the UserAssignment and User entities respectively.
E. You should consider specifying the On Delete property as NULL.
F. You should consider specifying the On Delete property as Default.

Answer: A, C, E

Explanation:

Question 6.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You are in the process of designing a strategy that includes a table with 500 columns and 50 million rows. The columns in the table have been configured to make use of the nvarchar(max) data type. After being informed that less than ten percent of the rows in each column will be populated, you decide configure the table for storage space optimization.

Which of the following actions should you take?

A. You should consider changing the column data type for all columns to varbinary(max).
B. You should consider changing the column data type for all columns to varchar(max).
C. You should consider configuring all the columns in the table as sparse columns.
D. You should consider removing columns that have the least data in it.

Answer: C

Explanation:

Question 7.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. ABC.com has a database strategy that includes two tables named SalesPerson and SalesTerritory. You have been instructed to make use of a Cartesian product to generate sample data that includes the data from the SalesPerson and SalesTerritory tables.

Which of the following actions should you take?

A. You should write code that performs a cross join, and includes a WHERE clause.
B. You should write code that performs a cross join, and includes an ON clause.
C. You should write code that performs an inner join, and includes a WHERE clause.
D. You should write code that performs a cross join, but does not include a WHERE clause.

Answer: D

Explanation:

Question 8.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. A ABC.com database includes a table named User that has four columns named UserID, UserType, FirstName, and LastName. You have configured a unique clustered index on the UserID column named IX_User_UserID, and a nonclustered index on the FirstName and LastName columns named IX_User_FirstName_LastName. You have also configured a nonclustered index on the UserType column named IX_User_UserType that has FirstName and LastName as included columns
You have written and performed the query shown below:
SELECT P.FirstName, P.LastName
FROM User P
WHERE P.UserType = 'DR'
Prior to making use of an execution plan for evaluating the execution of the query, You want to determine whether the indexes are used in the best possible way.

Which of the following actions should you take? (Choose two.)

A. You should consider making sure that a clustered index scan operation is executed on the IX_Person_PersonID index.
B. You should consider making sure that an index seek operation is performed on the IX_Person_PersonType index.
C. You should consider making sure that a key lookup operation is performed on the 
    IX_Person_PersonType index.
D. You should consider making sure that a key lookup operation is performed on the 
    IX_Person_PersonID index.
E. You should consider making sure that a index seek operation is performed on the  
    IX_Person_PersonID index.
F. You should consider making sure that a index scan operation is performed on the  
    IX_Person_FirstName_LastName index.

Answer: B, D

Explanation:

Question 9.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You have created table within ABC.com’s database that includes numerous columns. You are required to create an index for the table.

Which of the following are TRUE with regards to indexes? (Choose all that apply.)

A. There has to be data in the table before you can create an index.
B. Using a clustered index allows for the physical order of the corresponding rows in a table to be determined by the logical order of the key value.
C. Using a non-clustered index allows for the physical order of the corresponding rows in a table To be determined by the logical order of the key value.
D. A table can only have one clustered index at a time

Answer: B, D

Explanation:

Question 10.
You are employed as a database developer at ABC.com. You make use of SQL Server 2008 to develop database strategies. You are preparing to develop a strategy for a database, which includes a stored procedure that returns data from a table and produces aggregations using a transaction. You are required to make sure that the strategy prevents update operations from occurring on the returned data during the implementation of the store procedure. You also have to make sure that during the implementation of the stored procedure, data can be inserted into the table.

Which of the following actions should you take?

A. You should consider configuring the use of the PARALLEL isolation level.
B. You should consider configuring the use of the READ DEDICATED isolation level.
C. You should consider configuring the use of the REPEATABLE READ isolation level.
D. You should consider configuring the use of the SNAPSHOT isolation level.

Answer: C

Explanation:

	Web www.certsbraindumps.com