2015 Latest 70-433 Real exam questions to master and practice upon! Braindump2go Offers the New Updated Microsoft 70-433 210 Exam Questions in PDF & VCE files that can also be downloaded on every mobile device for preparation!
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 41
You have a table named Customer.
You need to ensure that customer data in the table meets the following requirements:
credit limit must be zero unless customer identification has been verified.
credit limit must be less than 10,000.
Which constraint should you use?
A. CHECK (CreditLimt BETWEEN 1 AND 10000)
B. CHECK (Verified = 1 AND CreditLimt BETWEEN 1 AND 10000)
C. CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
D. CHECK ((CreditLimt = 0 AND Verified = 0) AND (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
Answer: C
QUESTION 42
You have been tasked to delete 1000 rows from a table named NewWidgets.
There are 2000 rows in which the column ToBeDeleted set to 1.
You need to write a Transact-SQL batch that will delete exactly 1000 rows.
Which Transact-SQL batch should you use?
A. DELETE TOP (1000) dbo.NewWidgets
WHERE ToBeDeleted = 1;
B. DECLARE @BatchSize INT = 10;
WHILE (@BatchSize = 10)
DELETE TOP (@BatchSize) dbo.NewWidgets
WHERE ToBeDeleted = 1;
C. DELETE TOP ((SELECT COUNT(*) FROM dbo.NewWidgets
WHERE ToBeDeleted = 1)) w FROM dbo.NewWidgets w WHERE
w.ToBeDeleted = 1;
D. DECLARE @TotalRowCount INT = 0; WHILE (@TotalRowCount <= 1000)
BEGIN
DELETE TOP (10) dbo.NewWidgets
WHERE ToBeDeleted = 1;
SET @TotalRowCount += @@ROWCOUNT;
END
Answer: A
QUESTION 43
You work for an international charity organization.
You are writing a query to list the highest 100 different amounts that were donated.
You have written the following code segment (Line numbers are included for reference only):
01 SELECT *
02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,
03 ………………..
04 FROM Customer
05 JOIN SalesOrder
06 ON Customer.CustomerID = SalesOrder.CustomerID
07 GROUP BY Customer.CustomerID) AS DonationsToFilter
08 WHERE FilterCriteria <= 100
You need to insert a Transact-SQL clause in line 03 to complete the query.
Which Transact-SQL clause should you insert?
A. RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
B. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
C. ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
Answer: D
QUESTION 44
You work as a network database administrator at ABC.com.
ABC.com has a database server named ABC-DB01 that hosts the Inventory database and transaction files.
You notice that there are often blocking problems on the Inventory database.
You discover that the blocking is caused by a SQL statement that operates in an isolation level
set to repeatable read.
How should you configure ABC-DB01 to reduce blocking whilst preventing dirty reads?
A. By making use of the WRITE UNCOMMITTED transaction isolation level.
B. By making use of the WRITE COMMITTED transaction isolation level.
C. By making use of the SNAPSHOT transaction isolation level.
D. By making use of the NON SERIALIZABLE isolation level transaction.
Answer: C
QUESTION 45
You work as a network database administrator at ABC.com.
ABC.com has a database server named ABC-DB01 that hosts the Inventory database.
You need to use an explicit transaction to execute a number of UPDATE statements that modifies
existing data in the Inventory database.
How would you be able to roll back changes if the transaction is unsuccessful?
A. By making use of the INSTEAD OF DELETE trigger.
B. By making use of the EXECUTE AS OWNER function.
C. By making use of the INSTEAD OF INSERT trigger.
D. By making use of the XACT_ABORT option.
Answer: D
QUESTION 46
You have a user named John. He has SELECT access to the Sales schema.
You need to eliminate John’s SELECT access rights from the Sales.SalesOrder table without affecting his other permissions.
Which Transact-SQL statement should you use?
A. DROP USER John;
B. DENY SELECT ON Sales.SalesOrder TO John;
C. GRANT DELETE ON Sales.SalesOrder TO John;
D. REVOKE SELECT ON Sales.SalesOrder FROM John;
Answer: B
Explanation:
REVOKE – permanently removes both granted an denied permissions on an object, resulting in no permissions. Main thing you have to remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation.
DENY – Denies permission to the object for an operation. Once it set, it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object.
To sum up, because John does not have GRANT permissions on the Sales.SalesOrder table (instead it has GRANT permission on Sales schema), then REVOKE SELECT ON Sales.SalesOrder from John will not remove any permissions.
Here is a code that shows it clearly.
— create a login and user
CREATE LOGIN [John] WITH PASSWORD = ‘1’, CHECK_POLICY = OFF
GO
USE [AdventureWorks2008]
GO
CREATE USER [John] FOR LOGIN [John]
WITH DEFAULT_SCHEMA = [dbo]
GO
— grant permission on Sales schema
GRANT SELECT ON SCHEMA :: Sales TO [John]
— Run SELECT with John’s credentials and see
— He sees records
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT
— Revoke permisson for the table from him
REVOKE SELECT ON Sales.SalesOrderHeader FROM [John]
— He still sees data
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT
— This explicitly denies permission on SalesOrderHeader to John
— Once this is executed, he will not be able to see data
— even we grant him again.
DENY SELECT ON Sales.SalesOrderHeader TO [John]
— He sees error message: The SELECT permission was denied on the object ‘SalesOrderHeader’, database ‘AdventureWorks2008’, schema ‘Sales’.
EXECUTE AS USER = ‘John’
SELECT * FROM Sales.SalesOrderHeader
REVERT
QUESTION 47
You are the administrator of a SQL Server 2008 instance with a database named DB1.
A backup of DB1 is performed every day.
You have to minimize the size of the full database backup files of DB1.
Which Transact-SQL statement should you use?
A. BACKUP DATABASE DB1 TO DISK = ‘t:\backups\db1.bak’;
B. BACKUP DATABASE DB1 TO DISK = ‘t:\backups\db1.bak’ WITH COMPRESSION;
C. BACKUP DATABASE DB1 TO DISK = ‘t:\backups\db1.bak’ WITH DIFFERENTIAL;
D. BACKUP DATABASE DB1 TO DISK = ‘t:\backups\db1.bak’ WITH COMPRESSION, DIFFERENTIAL;
Answer: B
QUESTION 48
You are the administrator of a SQL Server 2008 instance with a database named DB1.
When you are absent, a user will use a login named Mary to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so you have to give the appropriate permissions to the user.
Which database permission should you give the user?
A. DELETE
B. CONTROL
C. DROP DATABASE
D. ALTER ANY DATASPACE
Answer: C
QUESTION 49
You are the administrator of a SQL Server 2008 instance.
Users report that they are unable to connect to the named instances.
You check and verify that they can only connect to the default instance.
You also ensure that all SQL Server instances run normally.
You have to start the service which is required to connect to the named instances.
Which service should you start?
A. Server
B. SQL Server Agent
C. SQL Server Browser
D. SQL Active Directory Helper
Answer: C
QUESTION 50
You are a database developer for your company.
You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table structure.
You need to write the stored procedure to fulfill the requirements.
Which functionality should you use?
A. FOR XML AUTO
B. the nodes method of the xml data type
C. the query method of the xml data type
D. the modify method of the xml data type
Answer: B
Braindump2go New Released 70-433 Dump PDF Free Download, 210 Questions in all, Passing Your Exam 100% Easily!
