QUESTION 61
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
QUESTION 62
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
QUESTION 63
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
QUESTION 64
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
QUESTION 65
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
QUESTION 66
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
QUESTION 67
You are creating a function that references a table.
You need to prevent the table from being dropped.
Which option should you use when you create the function?
A. WITH ENCRYPTION
B. WITH EXECUTE AS
C. WITH SCHEMABINDING
D. WITH RETURNS NULL ON NULL INPUT
Answer: C
QUESTION 68
You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit. You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to ‘True’. Parts can be deleted if they do not belong to a kit. You have the following Transact-SQL statement to be used in a trigger:
UPDATE p SET IsDeleted = 1 FROM KitPart kp
JOIN deleted d ON kp.PartID = d.PartID
JOIN Part p ON kp.PartID = p.PartID; DELETE FROM p FROM Part p
JOIN deleted d ON p.PartID = d.PartID
LEFT OUTER JOIN KitPart kp ON p.PartID = kp.PartID WHERE kp.KitID IS NULL;
You need to implement the Transact-SQL statement in a trigger. Which trigger syntax should you use?
A. CREATE TRIGGER tr_Part_d ON Part AFTER DELETE AS BEGIN
END
B. CREATE TRIGGER tr_Part_d ON Part INSTEAD OF DELETE AS BEGIN
END
C. CREATE TRIGGER tr_KitPart_d ON KitPart AFTER DELETE AS BEGIN
END
D. CREATE TRIGGER tr_KitPart_d ON KitPart INSTEAD OF DELETE AS BEGIN
END
Answer: B
QUESTION 69
You have a third-party application that inserts data directly into a table.
You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints.
You need to ensure that the new columns do not break the third-party application.
What should you do?
A. Create a DDL trigger.
B. Create a stored procedure.
C. Create an AFTER INSERT trigger.
D. Create an INSTEAD OF INSERT trigger.
Answer: D
QUESTION 70
Your database contains two tables named Order and OrderDetails that store order information. They relate to each other using the OrderID column in each table. Your business requires that the LastModifiedDate column in the Order table must reflect the date and time when a change is made in the OrderDetails table for the related order.
You need to create a trigger to implement this business requirement.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER [uModDate] ON [OrderDetails]
INSTEAD OF UPDATE FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
B. CREATE TRIGGER [uModDate] ON [Order]
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
C. CREATE TRIGGER [uModDate] ON [Order]
AFTER UPDATE FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
D. CREATE TRIGGER [uModDate] ON [OrderDetails]
AFTER UPDATE NOT FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
Answer: D
If you want to pass Microsoft 70-433 successfully, donot missing to read latest lead2pass Microsoft 70-433 exam questions.
If you can master all lead2pass questions you will able to pass 100% guaranteed.