SQL Server Practice Test 9

1. SQL Server 2014 supports up to ________ partitions by default.

2000

1000

15000

20000

2. Point out the wrong statement.

The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement

It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct

You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued

COMMIT transaction requires membership in the public role

3. ___________ is a deadlock between a statement that is reading and a statement that is performing some form of data modification.

Reader

Writer

All of the mentioned

Reader-Writer

4. ________ is a performance management tool that recapitulates an organization’s performance from several standpoints on a single page.

Data Cube

Balanced Scorecard

Dashboard

All of the mentioned

5. Point out the wrong statement.

You can enable or disable ROW or PAGE compression in online state only

Non–leaf-level pages do not receive page compression

When you are compressing indexes, leaf-level pages can be compressed with both row and page compression

None of the mentioned

6. Which of the following areas are affected by BI?

CRM

Sales

Revenue

All of the mentioned

7. Which of the following query returns object names from partition IDs?

SELECT OBJECT_NAME(p.object_id) AS TableName , 
       i.name AS IndexName 
FROM sys.partition AS p 
     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id 
                                    AND p.index_id = i.index_id 
WHERE partition_id = 72057594038845440

SELECT OBJECT_NAME(p.object_id) AS TableName , 
       i.name AS IndexName 
FROM sys.partitions AS p 
     INNER JOIN sys.index AS i ON p.object_id = i.object_id 
                                    AND p.index_id = i.index_id 
WHERE partition_id = 72057594038845440

None of the mentioned

SELECT OBJECT_NAME(p.object_id) AS TableName , 
       i.name AS IndexName 
FROM sys.partitions AS p 
     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id 
                                    AND p.index_id = i.index_id 
WHERE partition_id = 72057594038845440

8. Which of the following mode indicates the intention of the transaction to read all of the resources lower in the hierarchy?

All of the mentioned

SIX

9. Point out the correct statement.

A ROLLBACK TRANSACTION statement does produce any messages to the user

All of the mentioned

Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error

ROLLBACK TRANSACTION with a savepoint_name or transaction_name rolls back to the beginning of the transaction

10. Which of the following occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access?

Lock

Block

Exclusive lock

Deadlock