Questions

Q1. Name three types of latches that can be encountered in the SQL engine ?

Q2. SQL Server securable is an item in SQL Server for which you can assign permissions. Name five SQL Server securable?

Q3. Name all the SQL Server Replication components ?

Q4. A transaction is a unit of work that might include multiple activities. Typically transaction is characterized by four properties (commonly referred as ACID) ; name those four properties.?

Q5. Name five SQL Server 2012 Query Hints?

Q6. Name five SQL Server 2012 Table Hints?

Q7. Name three SQL Severe Database Console Commands (DBCC) used for Database Maintenance?

Q8. Name three SQL Severe Database Console Commands (DBCC) used for database validation or retrieve information from a database ?

Q9. What is the Consol command to start the default instance of the SQL Server Database Engine ?

Q10. What is the T-SQL command to stop the Database Engine ?

Answers

Q1. Correct Answer

Latch

Page Latch

Page I/O Latch

Q2. Correct Answer

User

Database role

Application role

Assembly

Message type

Route

Service

Remote service binding

Full-text catalog

Certificate

Asymmetric key

Symmetric key

Contract

Schema

Q3. Correct Answer

Publisher

Article

Publication

Distributor

Subscriber

Agent

Q4. Correct Answer

Atomicity

Consistency

Isolation

Durability

Q5. Correct Answer

EXPAND VIEWS

FAST

FORCE ORDER

KEEP PLAN

KEEPFIXED PLAN

MAXDOP

MAXRECURSION

OPTIMIZE FOR

OPTIMIZE FOR UNKNOWN

PARAMETERIZATION

RECOMPILE

ROBUST PLAN

USE PLAN

Q6. Correct Answer

FORCESCAN

FORCESEEK

HOLDLOCK

NOLOCK

NOWAIT

PAGLOCK

READCOMMITTED

READCOMMITTEDLOCK

READPAST

READUNCOMMITTED

REPEATABLEREAD

ROWLOCK

SERIALIZABLE

TABLOCK

TABLOCKX

UPDLOCK

XLOCK

Q7. Correct Answer

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC SHRINKFILE

DBCC FREEPROCCACHE

DBCC UPDATEUSAGE

Q8. Correct Answer

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS

DBCC CHECKTABLE

DBCC CHECKDB

Q9. Correct Answer

net start “SQL Server (MSSQLSERVER)”

-or-

net start MSSQLSERVER

Q10. Correct Answer

SHUTDOWN ;

-or-

SHUTDOWN WITH NOWAIT;