Question 1.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. ABC.com has a user named Rory Allen has the SQL login rallen. Rory Allen was granted the DELETE permissions to Stock table in the Inventory database. 

How would you configure ABC-DB01 to revoke Rory Allen’s SELECT permissions to the Stock table without disrupting other permissions he has to the Inventory database?

A. By running the ADD USER rallen Transact-SQL statement.
B. By running the TRUNCATE Inventory.Stock FROM rallen Transact-SQL statement.
C. By running the DENY DELETE ON Inventory.Stock TO rallen Transact-SQL statement.
D. By running DELETE SELECT ON Inventory.Stock TO rallen Transact-SQL statement.

Answer: C

Explanation:

Question 2.
You work as a network database administrator at ABC.com. Your boss, Tess King wants to know on which columns unique constraints can be created.

What must you tell your boss?

A. On nvarchar(50) NULL or nvarchar(50) NOT NULL columns.
B. On nvarchar(50) SPARSE NULL and nchar(max) SPARSE NULL columns.
C. On nvarchar(max) NOT NULL and nvarchar(max) NULL columns.
D. On all of the above.

Answer: A

Explanation:

Question 3.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 which hosts the Inventory database. ABC.com recently created a table named ItemsSold which lists the customers with their discount ratings.

How can you configure ABC-DB01 to have customers who have made purchases before granted 15% discount?

A. By using the WITH CHECK (Purchases = 0 AND Discount = 0.15) constraint.
B. By using the WITH CHECK (Purchases > 0 AND Discount 0.15) constraint.
C. By using the WITH CHECK ((Purchases = 0 AND Discount = 0) AND (Purchase < 0 AND 
    Discount = 0.15)) constraint.
D. By using the WITH CHECK ((Purchases = 0 AND Discount = 0) OR (Purchase > 0 AND  
    Discount = 0.15)) constraint.

Answer: D

Explanation:

Question 4.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. ABC.com has a Sales division which makes changes to the ItemSold and Stock tables of the Inventory database.

How can the Inventory database be configured ensuring changes listed in the ItemSold table corresponds to records in the Stock table?

A. By making use of the MERGE statement.
B. By making use of CLR triggers.
C. By making use of a Clustered Index and INNER JOIN constraints.
D. By making use of a Foreign key constraint.
E. By making use of a Unique constraint.

Answer: D

Explanation:

Question 5.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Retail database. The Retail database has a table named Customers that has a column named Address. You need to alter the Address column to enable users to query the column based on geographic location.

How would you alter the column?

A. By specifying the datetimeoffset data type.
B. By specifying the text data type.
C. By specifying the geography data type.
D. By specifying the user-defined type data type.

Answer: C

Explanation:

Question 6.
You work as a network database administrator at ABC.com. ABC.com has a poorly designed database with similar data stored in numerous tables. The tables use columns with different data types. You need to collate these tables into a single table by using the SPARSE option. 

On which of the following can the SPARSE option be used?

A. On columns of the geography data type.
B. On columns of the varchar(max) data type.
C. On IDENTITY columns.
D. On columns with user-defined data types.
E. On NOT NULL columns.

Answer: B

Explanation:

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

Explanation:

Question 8.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. ABC.com has a Finance division which utilizes two partitioned tables named ABCSales and ABCProduction.

Which of the following methods should be used on the Inventory database to archive a partition of ABCSales to ABCProduction? (Each correct option is part of the answer. Choose TWO.)

A. By making use of the ALTER DATABASE method.
B. By making use of the TRUNCATE method.
C. By making use of the JOIN method.
D. By making use of the INNERJOIN method.
E. By making use of the SWITCH method.

Answer: A, E

Explanation:

Question 9.
You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. The Inventory database has a table named ItemsSold. You need to create a new table named WeekSales that stores that last week's data from the ItemsSold table.

How should you go about creating the WeekSales table?

A. By creating a partition function with a FULL TEXT index for the WeekSales table.
B. By creating a partition function with a partition scheme before creating the WeekSales table.
C. By creating a partition scheme with a FULL TEXT index before creating the WeekSales table.
D. By using the TRUNCATE Inventory.ItemsSold command after creating one primary and 
    secondary filegroup before creating the WeekSales table.

Answer: B

Explanation:

Question 10.
You work as a network database administrator at ABC.com. Your boss, Tess King, wants to know which stored procedures can be modified to utilize the WITH COMPILE option.

What would you tell your boss?

A. Ones that use the nvarchar(max) hint.
B. Ones that use the WITH ENCRYPTION option.
C. Ones that use the SPARSE NULL option.
D. Ones that use the NTILE() hint.
E. Ones that use the MAXDOP hint.
F. By Ones that use the ORDER BY hint.

Answer: B

Explanation:

	Web www.certsbraindumps.com