Data Definition Language - SQL Server Multiple Choice Questions 2

This set of SQL Server Quiz focuses on “Data Definition Language – 2”.

1. Point out the wrong statement.
a) You cannot drop a database currently being used
b) A database cannot be dropped regardless of its state
c) Any database snapshots on a database must be dropped before the database can be dropped
d) None of the mentioned
View Answer

Answer: b
Explanation: A database can be dropped regardless of its state: offline, read-only, suspect, and so on.

2. Which of the following database should be backed up regularly?
a) Adventure Works
b) Pub
c) TEMPDB
d) Master
View Answer

Answer: d
Explanation: When a database is dropped, the master database should be backed up.

3. The DROP DATABASE statement must run in ________ mode.
a) Autocommit
b) Commit
c) Readcommit
d) None of the mentioned
View Answer

Answer: a
Explanation: Autocommit mode is the default transaction management mode.

4. Features provided by ALTER DATABASE statement do not include ____________
a) Deletes database
b) Modifies file
c) Changes the attributes of a database
d) Changes the database collation
View Answer

Answer: a
Explanation: ALTER DATABASE is used to modify the database.

5. Point out the correct statement.
a) Database snapshots cannot be modified
b) Database snapshots can be modified
c) A collation name can be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses
d) None of the mentioned
View Answer

Answer: a
Explanation: A database snapshot is a read-only, static view of a SQL Server database.

Subscribe Now: SQL Server Newsletter | Important Subjects Newsletters

6. Command used to shrink the size of the database is _____________
a) DBCC SHRINK
b) SHRINKDATABASE
c) DBCC SHRINKDATABASE
d) DBCC SHRINKDB
View Answer

Answer: c
Explanation: To decrease the size of a database, use DBCC SHRINKDATABASE.

7. Maximum number of filegroups specified for each database is ___________
a) 32, 667
b) 32, 777
c) 32, 766
d) 32, 767
View Answer

Answer: d
Explanation: A maximum of 32, 767 files and 32, 767 file groups can be specified for each database.

8. When does most ALTER DATABASE statements fail?
a) Database is in the RESTORING state
b) Database is in the STORING state
c) Database is in the RESTORE state
d) None of the mentioned
View Answer

Answer: a
Explanation: When a database is in RESTORING state then it means one or more files of the primary filegroup is been restored or one or more secondary files are being resotored offline.

9. Which of the following statement modifies view index?
a) ALTER INDEX
b) ALTER VIEW INDEX
c) ALTER INDEX_VIEW
d) None of the mentioned
View Answer

Answer: a
Explanation: ALTER INDEX modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.

10. Which of the following index options are added by the CREATE INDEX statement?
a) XML INDEX
b) INCLUDED (column)
c) PARTITION SCHEME
d) ONLINE
View Answer

Answer: d
Explanation: CREATE INDEX includes the following new syntax elements:XML INDEX, INCLUDED and PARTITION SCHEME.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server for quizzes, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

« Prev - SQL Server Questions and Answers – Data Definition Language – 1

» Next - SQL Server Questions and Answers – Data Definition Language – 3

Related Posts:

Recommended Articles: