Home › Quizzes › SQL Server Database Administration

SQL Server Database Administration

Test your knowledge of SQL Server database administration including replication, isolation levels, backup strategies, and dynamic management views

9 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8
  2. 2
  3. 3
  4. 4
Question 2 Multiple Choice (Single Answer)

What tool should you recommend for a junior DBA to use to create a simple backup strategy for a SQL Server database?

  1. Database Maintenance Plan Wizard
  2. Database Engine Tuning Advisor
  3. SQL Server Configuration Manager
  4. Database Maintenance design surface
Question 3 Multiple Choice (Single Answer)

You have a 1TB SalesDB database running on SQL Server 2003 Enterprise Edition. You have noticed that the nightly DBCC CHECKDB ('SalesDB') job is starting to take too long, and you want to reduce the amount of time that the check will take. What command should you schedule to run nightly instead?

  1. DBCC CHECKDB ('SalesDB', REPAIR_ALLOW_DATA_LOSS)
  2. DBCC CHECKDB ('SalesDB', REPAIR_REBUILD)
  3. DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY
  4. DBCC CHECKDB ('SalesDB') WITH DATA_PURITY
Question 4 Multiple Choice (Single Answer)

You want to create a replication strategy that will allow users in four branch offices to have a fresh copy of data from the database in the central office every day at 5 a.m. for their daily reports. Although there are many changes to the data during the course of a day, the entire publication is not that large. Which replication strategy should you use in SQL Server?

  1. Transactional replication
  2. Peer-to-peer replication
  3. Merge replication
  4. Snapshot replication
Question 5 Multiple Choice (Single Answer)

You write a SQL Server stored procedure that will return data for a report. The accuracy of the data is not so important; however, you want to avoid dirty reads. Which isolation level should you avoid?

  1. Read Committed
  2. Read Uncommitted
  3. Repeatable Read
  4. Serializable
Question 6 Multiple Choice (Single Answer)

You want to identify the top 10 queries in SQL Server that have been recompiled the most times on your server. What can you do?

  1. Use the sys.dm_exec_requests dynamic management view.
  2. Use the sys.dm_exec_sessions dynamic management view
  3. Use the sys.dm_exec_query_optimizer_info dynamic management view
  4. Use the sys.dm_exec_query_stats dynamic management view
Question 7 Multiple Choice (Single Answer)

Which of the following table contains information about DB2?

  1. Temporary Storage Table (TST)
  2. Program Processing Table (PPT)
  3. Resource Control Table (RCT)
  4. File Control Table (FCT)
Question 8 Multiple Choice (Single Answer)

Which command can be used to disable all the roles including the default roles?

  1. Set Role Off
  2. Drop Role All
  3. .Revoke Role All
  4. Set Role None
Question 9 Multiple Choice (Single Answer)

Which of the following views provides all the grants on all the objects in the database?

  1. DBA_SYS_PRIVS
  2. DBA_COL_PRIVS
  3. DBA_OBJ_PRIVS
  4. DBA_TAB_PRIVS
← Back to Quizzes