READ COMMITTED is the default isolation level.
Answer: A
Correct answer: True
Read committed is the default: it prevents dirty reads by taking shared locks while reading, but releases them as soon as the read is done, so nonrepeatable reads and phantoms remain possible. It is the default because it prevents the anomaly that matters most at the least cost in concurrency.
Please log in or register to report this question.
A differential backup makes a copy of the entire database.
Answer: B
Correct answer: False
That describes a full backup. A differential copies only what has changed since the last full backup, which is why it is quicker to take and why restoring needs the full backup and the latest differential together. Each differential grows until the next full backup resets the baseline.
Please log in or register to report this question.
SQL Server supports three recovery modes: simple, full and Bulk.
Answer: A
SQL Server provides three different recovery models that allow you to specify the way SQL Server manages log files and prepares your enterprise for a disaster. Each of these models represents a different approach to balancing the tradeoff between conserving disk space and providing for granular disaster recovery options. The three disaster recovery models offered by SQL Server are:
1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model
Please log in or register to report this question.
SERIALIZABLE is the strictest isolation level.
Answer: A
Correct answer: True
Serializable permits none of the three anomalies - no dirty reads, no nonrepeatable reads and no phantoms - and gives a result equivalent to running the transactions one at a time. It achieves the last of these by locking key ranges as well as rows, which is what blocks the insertion that would otherwise produce a phantom.
Please log in or register to report this question.
With SQL Server pessimistic locking, no lock is obtained until the user updates the data.
Answer: B
Correct answer: False
That describes optimistic concurrency. Pessimistic locking is the opposite assumption: a lock is taken when the row is read and held while the application works on it, so no one else can change it in the meantime. The two names describe the assumption made about how likely conflict is.
Please log in or register to report this question.
Better security occurs if Windows-only authentication is selected.
Answer: A
Correct answer: True
Under Windows-only authentication the credentials are managed by the domain, with its password policies, account lockout and central administration, and no password is stored or transmitted by SQL Server itself. Mixed mode adds SQL Server logins, which are convenient for applications and non-Windows clients but are one more set of credentials to manage and protect.
Please log in or register to report this question.
SQL Server AFTER Triggers may be assigned views.
Answer: B
Correct answer: False
It is the INSTEAD OF trigger that may be placed on a view, and that is its principal purpose - making updatable a view that the DBMS could not otherwise update. An AFTER trigger fires once a statement has completed against a table and cannot be defined on a view.
Please log in or register to report this question.
'dbo' stands for database object.
Answer: B
Correct answer: False
It stands for database owner. The dbo is the default schema and the account that owns the database, so an object referred to as dbo.Customer is the Customer table in the dbo schema. Reading it as database object would suggest a type of thing rather than an owner, which is the wrong idea entirely.
Please log in or register to report this question.
With SQL Server optimistic locking, an update lock is placed on the row when the row is read.
Answer: B
Correct answer: False
That describes SCROLL_LOCK, the pessimistic setting. Optimistic concurrency takes no lock while reading and merely checks at update time whether the row has changed since it was read, failing the update if it has. The assumption is that conflict is rare, which is what makes it the faster option when it holds.
Please log in or register to report this question.
By default, SQL Server will create one data file and one log file for each newly created database.
Answer: A
Correct answer: True
A new database gets one data file, with the .mdf extension, and one log file with .ldf. Further data files can be added and placed on other disks for capacity or performance, but the arrangement created by default is the simplest one that works, and it keeps the data and the log separate from the start.
Please log in or register to report this question.
