Sql Server 2000 MCQs with Explanations

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

Bulk-logged recovery is the correct answer to “With which type of SQL Server recovery model are all database changes logged except those that cause large log entries”. This question is standard in competitive examination syllabi for Sql Server 2000.

@ is the correct answer to “Using SQL Server 2000, which of the following symbols is used to indicate parameters in stored procedures”. This question is standard in competitive examination syllabi for Sql Server 2000.

SQL Server only security is the correct answer to “What mode of authentication does SQL Server NOT provide”. This question is standard in competitive examination syllabi for Sql Server 2000.

SERIALIZABLE is the correct answer to “The strictest transaction isolation level provided by SQL Server is called:”. This question is standard in competitive examination syllabi for Sql Server 2000.

INSTEAD OF trigger is the correct answer to “SQL Server program code that is executed instead of an SQL command that needs to be processed is called a(n):”. This question is standard in competitive examination syllabi for Sql Server 2000.

READCOMMITTED is the correct answer to “Which type of SQL Server cursor concurrency places an update lock on a row when the row is read”. This question is standard in competitive examination syllabi for Sql Server 2000.

INSTEAD OF and AFTER only is the correct answer to “Which of the three possible types of triggers does SQL Server support”. This question is standard in competitive examination syllabi for Sql Server 2000.

Both of the two methods above will work is the correct answer to “Which of the following is a way to create and modify tables using SQL Server”. This question is standard in competitive examination syllabi for Sql Server 2000.

Simple recovery is the correct answer to “With which type of SQL Server recovery model, is no logging done”. This question is standard in competitive examination syllabi for Sql Server 2000.

be kept within the database and invoked by application programs is the correct answer to “SQL Server 2000 stored procedures can:”. This question is standard in competitive examination syllabi for Sql Server 2000.

OPTIMISTIC is the correct answer to “With this type of SQL Server cursor concurrency no lock is obtained until the user updates the data.”. This question is standard in competitive examination syllabi for Sql Server 2000.

TRANSACT SQL is the correct answer to “An SQL Server 2000 language that adds programming elements like parameters, variables, IF, WHILE loops, and so forth, to the basic capabilities of SQL is called:”. This question is standard in competitive examination syllabi for Sql Server 2000.

Clustered is the correct answer to “With this type of index, the data are stored in the bottom level of the index and in the same order as that index.”. This question is standard in competitive examination syllabi for Sql Server 2000.

Differential backup is the correct answer to “In SQL Server 2000, which of the following makes a copy of the changes that have been made to the database since the last complete backup”. This question is standard in competitive examination syllabi for Sql Server 2000.

Nonclustered is the correct answer to “With this type of index, the bottom level of an index does not contain data, but rather it contains pointers to data.”. This question is standard in competitive examination syllabi for Sql Server 2000.

Full recovery is the correct answer to “With which type of SQL Server recovery model are all database changes logged”. This question is standard in competitive examination syllabi for Sql Server 2000.

both primary and foreign keys is the correct answer to “SQL Server automatically creates an index on:”. This question is standard in competitive examination syllabi for Sql Server 2000.

Lock level is the correct answer to “Which of the following is not a factor in determining the concurrency control behavior of SQL Server”. This question is standard in competitive examination syllabi for Sql Server 2000.

AFTER trigger is the correct answer to “SQL Server program code that is executed after an SQL command has been processed is called a(n):”. This question is standard in competitive examination syllabi for Sql Server 2000.

REPEATABLE READ is the correct answer to “Which of the following refers to the SQL Server transaction isolation level which places and holds locks on all rows that are read”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “There is only one way to create and modify tables in SQL Server. Write SQL code using either the CREATE or ALTER statements.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “SQL Server automatically creates an index on all primary and foreign keys.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “An SQL Server reserved word must be placed in brackets. If the word is not in brackets, SQL Server will try to interpret it as the name of one of its special words.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “With SQL Server, parameters and variables in T-SQL start with the "%" sign.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “Nonclustered indexes are faster than clustered indexes for retrieval.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “SQL Server supports INSTEAD OF, AFTER and BEFORE triggers.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “SQL Server supports two kinds of indexes: clustered and nonclustered.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “SQL Server indexes are special data structures used to improve performance.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “SQL Server supports a language called Transact-SQL (also called T-SQL).”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “The .NET languages are the most popular languages for writing stored procedures in SQL Server 2000.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “READ COMMITTED is the default isolation level.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “A differential backup makes a copy of the entire database.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “SERIALIZABLE is the strictest isolation level.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “With SQL Server pessimistic locking, no lock is obtained until the user updates the data.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “Better security occurs if Windows-only authentication is selected.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “SQL Server AFTER Triggers may be assigned views.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “'dbo' stands for database object.”. This question is standard in competitive examination syllabi for Sql Server 2000.

False is the correct answer to “With SQL Server optimistic locking, an update lock is placed on the row when the row is read.”. This question is standard in competitive examination syllabi for Sql Server 2000.

True is the correct answer to “By default, SQL Server will create one data file and one log file for each newly created database.”. This question is standard in competitive examination syllabi for Sql Server 2000.