141 Multiple choice questions

1. There ____ be more than one ____ defined on the LOGON event.
   1. One, Browser
   2. Compatibility
   3. Can, trigger
   4. Cannot
2. More than a ____ instance of SQL server ____ installed at a time.
   1. Cannot
   2. SERIALIZABLE
   3. Single, can be
   4. Pausing, does not
3. The max ____ percentage cannot be lower than the min ____ percentage.
   1. Off, default
   2. Memory, memory
   3. SET statement
   4. Query Window
4. Disallows the use of Windows application log to record events.
   1. -n option
   2. Public
   3. SQL Mail
   4. -m option
5. The ____ statement, practically, marks the end of a successful implicit or explicit transaction.
   1. COMMIT TRANSACTION
   2. Explicit transaction
   3. -f option
   4. -c option
6. You need ____ SQL Server ____ for all SQL Server instances installed on the same server.
   1. One, isolation
   2. One, Browser
   3. sysjobs_view
   4. Can, trigger
7. You ____ create ____ server level roles.
   1. Cannot
   2. Can, user-defined
   3. Cannot, Agent
   4. Cannot, public
8. Statements cannot read data that has been changed but not yet committed.
   1. ROWLOCK hint
   2. READ COMMITTED
   3. TSQL Template
   4. More restricted
9. ____ is the default state of READ_COMMITTED ____.
   1. SNAPSHOT
   2. SET statement
   3. Off, SNAPSHOT
   4. Cannot
10. Used to instruct SQL Server Agent to execute a job immediately
    1. -g option
    2. -s option
    3. sp_start_job
    4. sp_configure
11. Tell whether password expiration policy should be enforced on that login
    1. HASHED option
    2. COMMIT TRANSACTION
    3. CHECK_EXPIRATION option
    4. -n option
12. Provides a basic editor to write T-SQL statements/scripts
    1. Query Window
    2. -m option
    3. -f option
    4. Table hints
13. In the ____, in files, and often group into ____ is how databases are stored
    1. sysadmin role
    2. SQL Server Profiler
    3. SQL Server Agent
    4. File system, filegroups
14. The ____ or ____ is how you change memory options manually.
    1. Management Studio, CREATE LOGIN
    2. Management Studio
    3. sp_configure
    4. Management Studio, sp_configure
15. The ____ is how you update current configured values that have been changed with sp_configure.
    1. Off, SNAPSHOT
    2. ALTER DATABASE statement
    3. RECONFIGURE statement
    4. More restricted
16. A Windows service that executes scheduled administrative tasks.
    1. TSQL Template
    2. TSQL_SPs Template
    3. SQL Server Agent
    4. SQL Server Browser
17. Used to find out further information on the various jobs
    1. sysadmin role
    2. sp_configure
    3. Lower
    4. sysjobs_view
18. The primary interface for managing the databases.
    1. sysadmin role
    2. Are mutually
    3. Credentials
    4. Management Studio
19. ____ and ____ are the two statements that can end Autocommit mode.
    1. .trc extension
    2. COMMIT TRANSACTION
    3. BEGIN TRANSACTION, SET IMPLICIT_TRANSACTIONS
    4. Standard Template
20. Permissions ____ be changed for fixed Server Level Roles.
    1. Cannot
    2. 9
    3. Latches
    4. Pause
21. You ____ remove someone from the ____ server role.
    1. Cannot, Agent
    2. Cannot, public
    3. Public
    4. Cannot, credential
22. ____ instances that you can monitor in the ____ process are what captured events in the Database Engine become.
    1. Database, SMTP
    2. Event class, trace
    3. setupadmin role
    4. More restricted
23. There are ____ fixed Server Level Roles in SQL Server
    1. 100
    2. XLOCK
    3. Pause
    4. 9
24. Granted the permission to add or removed linked servers
    1. serveradmin role
    2. sysadmin role
    3. processadmin role
    4. setupadmin role
25. Used to specify the size of memory that SQL Server can leave available for allocations within the process outside of the Server memory pool
    1. -f option
    2. -c option
    3. -s option
    4. -g option
26. Are containers for session requests with similar or identical classification criteria.
    1. TABLOCK hint
    2. On, default
    3. Workload Groups
    4. bulkadmin role
27. SQL Server ____ be allowed to adjust and use memory ____.
    1. SQL Mail
    2. Are mutually
    3. securityadmin role
    4. Should, dynamically
28. ____ is the biggest advantage of SQL Server Authentication
    1. Credentials
    2. Compatibility
    3. TSQL Template
    4. Public
29. The CPU and I/O affinities ____ exclusive.
    1. Are mutually
    2. On, default
    3. SQL Mail
    4. SERIALIZABLE
30. You can use ____ to apply locks when you select, modify or insert rows.
    1. ROWLOCK hint
    2. TABLOCK hint
    3. UPDLOCK hint
    4. Table hints
31. ____ is the SQL Server default transaction isolation level.
    1. REPEATABLE READ
    2. READ COMMITTED
    3. Can, trigger
    4. Cannot
32. You can query ____ to find out if there are many locks outstanding.
    1. sys.dm_tran_locks
    2. Schemas
    3. Credentials
    4. sp_update_job
33. The ____ is how you make changes to the current user session only.
    1. sp_start_job
    2. SET statement
    3. One, isolation
    4. Cannot, Agent
34. Used to configure and manage the reporting services.
    1. SET TRANSACTION ISOLATION LEVEL
    2. Reporting Services Configuration Manager
    3. SQL Server Configuration Manager
    4. ALTER SERVER CONFIGURATION, PROCESS AFFINITY
35. Definitions of the types of locks that can be acquired on read operations.
    1. CHECK_EXPIRATION option
    2. One, isolation
    3. HASHED option
    4. Transaction Isolation Levels
36. A mode that means every T-SQL statement will be committed or rolled back when it is completed.
    1. READ COMMITTED
    2. Autocommit mode
    3. -g option
    4. SET statement
37. After ____ is finished and before the ____ is established is when logon triggers fire.
    1. Aggregate monitoring of sessions
    2. Authentication, user session
    3. MUST_CHANGE option
    4. Right-click, Properties
38. A Windows service that listens for incoming requests.
    1. SQL Server Agent
    2. TSQL Template
    3. SQL Server Browser
    4. sysadmin role
39. ____ and ____ are the two T-SQL statements that are needed to make a new workload group,
    1. ALTER RESOURCE GOVERNOR RECONFIGURE, Management node
    2. CREATE WORKLOAD GROUP, ALTER RESOURCE GOVERNOR RECONFIGURE
    3. Management Studio, sp_configure
    4. Resource Governor
40. Captures all the stored procedures and T-SQL batches that are run. Used to monitor the general database server activities
    1. The Standard Template
    2. Standard Template
    3. TSQL_Duration Template
    4. WINDOWS option
41. The ____ is how you change Database level settings.
    1. RECONFIGURE statement
    2. REPEATABLE READ
    3. The Standard Template
    4. ALTER DATABASE statement
42. You would use ____ for database ____. Designed for processing alerts, and running scheduled jobs. Can also send out relevant alerts and notifications through email
    1. Explicit transaction
    2. SQL Server Agent, automation
    3. .trc extension
    4. SQL Server Configuration Manager
43. When there a is particular ____ preventing the server from starting is when you most often use the ____ option.
    1. Configuration values, -f
    2. diskadmin role
    3. dbcreator role
    4. TSQL_Duration Template
44. READ COMMITTED is ____ than READ UNCOMMITTED.
    1. Credentials
    2. Can, trigger
    3. READ COMMITTED
    4. More restricted
45. ____ statement or ____ are two ways to set up credentials.
    1. SET statement
    2. ALTER DATABASE statement
    3. Credentials
    4. CREATE CREDENTIAL, Management Studio
46. ____ are the predefined templates for SQL Server Profiler (5)
    1. Management Studio, CREATE LOGIN
    2. Authentication, user session
    3. Transaction Isolation Levels
    4. Standard, TSQL, TSQL_Duration, TSQL_Grouped, TSQL_SPs
47. You can reconfigure Resource Governor in Management Studio Under ____ - Template Explorer - ____ - Configure Resource Governor.
    1. Management, resource pools
    2. One, isolation
    3. Resource Governor
    4. View, Resource Governor
48. Granted the permission to manage disk files.
    1. setupadmin role
    2. sysadmin role
    3. diskadmin role
    4. processadmin role
49. The '____' should be included with the -m option.
    1. Event class, trace
    2. Client Application Name
    3. Implicit transaction mode
    4. CHECK_EXPIRATION option
50. Used to manage the SQL server instances and set up the clients/client protocols.
    1. sp_configure
    2. RECONFIGURE statement
    3. Reporting Services Configuration Manager
    4. SQL Server Configuration Manager
51. ____ is the ____ state for the CHECK-EXPIRATION option.
    1. One, Browser
    2. SQL Mail
    3. Off, SNAPSHOT
    4. Off, default
52. ____ the Database Engine service ____ remove users currently connected.
    1. Pausing, does not
    2. -n option
    3. TABLOCKX hint
    4. On, default
53. Used to produce a count on how many jobs a schedule is tied to.
    1. SET statement
    2. sp_start_job
    3. SQL Server Agent
    4. sp_help_jobcount
54. Granted the permission to create, alter, drop and restore any database. They cannot alter others databases.
    1. sysadmin role
    2. serveradmin role
    3. dbcreator role
    4. bulkadmin role
55. An affinity mask is used for ____ controlling ____ affinity.
    1. sysadmin role
    2. Should, dynamically
    3. TABLOCK hint
    4. Dynamically, CPU
56. You can configure processor affinity options in SQL Server 2012 by using the ____ statement and then the ____ argument.
    1. RECONFIGURE statement
    2. Authentication, user session
    3. Transaction Isolation Levels
    4. ALTER SERVER CONFIGURATION, PROCESS AFFINITY
57. Will not issue shared locks. They are not blocked by exclusive locks, and it means dirty reads.
    1. Autocommit mode
    2. setupadmin role
    3. READ UNCOMMITTED level
    4. READ COMMITTED
58. The affinity mask can be configured without a restart with the ____.
    1. ROLLBACK TRANSACTION statement
    2. RECONFIGURE statement
    3. ALTER SERVER CONFIGURATION, PROCESS AFFINITY
    4. RECONFIGURE or RECONFIGURE WITH OVERRIDE statements
59. The max CPU percentage cannot be ____ than the min CPU percentage.
    1. 9
    2. Pause
    3. XLOCK
    4. Lower
60. The ____ email system is depreciated.
    1. Cannot
    2. Public
    3. Locks
    4. SQL Mail
61. You would use workload groups to ____.
    1. Management Studio
    2. CHECK_EXPIRATION option
    3. Aggregate monitoring of sessions
    4. READ UNCOMMITTED level
62. ____ are the fixed Server Level Roles provided by SQL Server. (9)
    1. securityadmin role
    2. SQL Server Agent, automation
    3. sysadmin,
       serveradmin
       securityadmin
       processadmin
       setupadmin
       bulkadmin
       diskadmin
       dbcreator
       public
    4. Reporting Services Configuration Manager
63. Every log in belongs to the ____ server role.
    1. Schemas
    2. Latches
    3. Public
    4. Pause
64. You ____ pause the SQL Server ____.
    1. -n option
    2. TABLOCK hint
    3. Cannot, Agent
    4. Cannot, credential
65. Used to produce information on the status of a job run
    1. sysjobs_view
    2. Compatibility
    3. sp_help_jobactivity
    4. sp_start_job
66. Object ownership groups
    1. SQL Mail
    2. Schemas
    3. XLOCK
    4. Public
67. ____ is the statement that will end implicit transaction mode.
    1. Explicit transaction
    2. One, isolation
    3. Implicit transaction mode
    4. SET IMPLICIT_TRANSACTIONS OFF
68. Says the Windows password policies of the local server computer should be enforced on this particular login.
    1. CHECK_EXPIRATION option
    2. -E option
    3. CHECK_POLICY option
    4. HASHED option
69. The difference between the Management Studio GUI and sp_configure is while ____ allows access to ____ options the ____ contains the ____ used server configuration options.
    1. Management, resource pools
    2. Configuration values, -f
    3. MUST_CHANGE option
    4. sp_configure, all, GUI, most commonly
70. ____ is the ____ state for CHECK_POLICY.
    1. Off, default
    2. -n option
    3. On, default
    4. Table hints
71. ____ is the default template for SQL Server Profiler.
    1. Standard Template
    2. TSQL_Grouped Template
    3. The Standard Template
    4. SET statement
72. You can reconfigure the ____ while configuring the Resource Governor.
    1. SQL Mail
    2. Memory, memory
    3. Resource Governor
    4. Resource pool
73. ____ is what is started with the SET IMPLICIT_TRANSACTIONS statement.
    1. Implicit transaction mode
    2. ROLLBACK TRANSACTION statement
    3. SET IMPLICIT_TRANSACTIONS OFF
    4. COMMIT TRANSACTION
74. To set up ____ Mail you would configure one or more databases as mail hosts and then define client settings so SQL Server Agent can send message via ____ mail server
    1. HASHED option
    2. Off, default
    3. Table hints
    4. Database, SMTP
75. Statements cannot read data modified but not yet committed and no other transactions can modify data that has been read until current transaction is completed.
    1. REPEATABLE READ
    2. Public
    3. Credentials
    4. Resource pool
76. Multiple logins ____ be mapped to a ____.
    1. HASHED option
    2. Cannot, Agent
    3. Cannot, credential
    4. Latches
77. 1433
    1. Can, trigger
    2. Default TCP port
    3. dbcreator role
    4. -E option
78. The sums of the minimum CPU and minimum memory percentages for all available resource polls cannot go over ____ percent.
    1. SNAPSHOT
    2. Locks
    3. 100
    4. Latches
79. Allows you to limit connections to a specified client application
    1. Credentials
    2. Script Login
    3. -m option
    4. XLOCK
80. Minimizing locks will ____.
    1. Increase concurrency and improve performance
    2. Database Engine service
    3. Resource Governor
    4. CREATE CREDENTIAL, Management Studio
81. Applies exclusive locks
    1. Latches
    2. 9
    3. XLOCK
    4. -s option
82. Captures detailed information about the executing stored procedures. Used to get information about stored procedures that are executed.
    1. TSQL_Duration Template
    2. sp_start_job
    3. Standard Template
    4. TSQL_SPs Template
83. A transaction can only recognize data modifications committed prior to the start of the transaction. Modifications made after the start will be invisible.
    1. -E option
    2. Cannot
    3. Off, SNAPSHOT
    4. SNAPSHOT
84. Removes all data modifications made and rolls back to the beginning of the transaction
    1. ALTER DATABASE statement
    2. CREATE TRIGGER statement
    3. ROLLBACK TRANSACTION statement
    4. Explicit transaction
85. Locks commonly cause ____.
    1. SERIALIZABLE
    2. Performance bottlenecks
    3. bulkadmin role
    4. MUST_CHANGE option
86. The file extension of trace files
    1. -g option
    2. .trc extension
    3. -m option
    4. -c option
87. Provides an identity outside of the SQL Server. Can be mapped to more than one login.
    1. Are mutually
    2. Credentials
    3. -E option
    4. Table hints
88. Produces the same effect as setting HOLDLOCK on all tables in the SELECT statement.
    1. Pause
    2. serveradmin role
    3. Script Login
    4. SERIALIZABLE
89. ____ is the kind of transaction started with the BEGIN TRANSACTION statement.
    1. HASHED option
    2. Explicit transaction
    3. COMMIT TRANSACTION
    4. TSQL_SPs Template
90. Internal SQL Server resource locks related to user activity and resource usage.
    1. Pause
    2. Latches
    3. -s option
    4. SQL Mail
91. Workload groups located under the ____ node - ____ in Management Studio.
    1. Management Studio
    2. Management, resource pools
    3. Resource pool
    4. Management Studio, CREATE LOGIN
92. Captures all statements submitted with information grouped by the client that submitted it. Use it to investigate queries from specific clients.
    1. READ COMMITTED
    2. TSQL_SPs Template
    3. TSQL_Duration Template
    4. TSQL_Grouped Template
93. Used to utilize captured traces, and capture and save data to analyze later. Can also create templates to define event classes and data columns
    1. bulkadmin role
    2. SQL Server Browser
    3. Resource pool
    4. SQL Server Profiler
94. Used to instruct SQL Server Agent to make changes to a job
    1. -s option
    2. SNAPSHOT
    3. -f option
    4. sp_update_job
95. ____ and ____ are the two affinity mask options.
    1. Affinity mask, affinity I/O mask
    2. Explicit transaction
    3. One, isolation
    4. COMMIT TRANSACTION
96. Used for starting an instance with minimal configuration.
    1. XLOCK
    2. -f option
    3. HASHED option
    4. Pause
97. A feature you can use to specify limits on resource consumption by incoming requests
    1. Resource Governor
    2. sysadmin role
    3. -c option
    4. dbcreator role
98. You will prevent new users from connecting to the Database Engine if you ____ it.
    1. Cannot
    2. Pause
    3. SQL Mail
    4. -g option
99. The HASHED argument should be used only when ____ databases from one ____ to another.
    1. Windows, SQL Server
    2. diskadmin role
    3. migrating, server
    4. dbcreator role
100. ____ is the T-SQL statement used for transaction isolation levels.
     1. SET TRANSACTION ISOLATION LEVEL
     2. ROLLBACK TRANSACTION statement
     3. securityadmin role
     4. Standard Template
101. Update locks that are held in place until a transaction is completed.
     1. UPDLOCK hint
     2. Cannot, Agent
     3. -s option
     4. TABLOCK hint
102. The acquired lock is to be applied at the table level
     1. ROWLOCK hint
     2. TABLOCKX hint
     3. TABLOCK hint
     4. Table hints
103. Granted the permission to change server-wide configuration options and shut down the server
     1. setupadmin role
     2. diskadmin role
     3. serveradmin role
     4. securityadmin role
104. Used for directly affecting the I/O affinity mask.
     1. affinity masks
     2. I/O affinity task
     3. bulkadmin role
     4. One, isolation
105. Allows you to start only a named instance of SQL Server
     1. SNAPSHOT
     2. -n option
     3. XLOCK
     4. -s option
106. You ____ to use the Query Window in order to write T-SQL.
     1. sysadmin role
     2. Autocommit mode
     3. Don't have
     4. ROWLOCK hint
107. Another statement ____ need to be written to mark the start of a new ____ transaction.
     1. Cannot, Agent
     2. TSQL Template
     3. Does not, implicit
     4. On, default
108. Granted the permission to end processes that are running.
     1. SERIALIZABLE
     2. processadmin role
     3. Autocommit mode
     4. securityadmin role
109. ____ then ____ are the two T-SQL statements that are needed for creating resource pools.
     1. View, Resource Governor
     2. CREATE RESOURCE POOL, ALTER RESOURCE GOVERNOR RECONFIGURE
     3. CREATE CREDENTIAL, Management Studio
     4. Resource Governor
110. ____ is the default transaction management mode.
     1. Autocommit mode
     2. setupadmin role
     3. READ COMMITTED
     4. diskadmin role
111. If you need to provide support for ____, ____ applications, or if you have ____ in place is when you would use SQL Server Authentication.
     1. Implicit transaction mode
     2. Older applications, third-party, mixed operating systems
     3. Right-click, Properties
     4. SET IMPLICIT_TRANSACTIONS OFF
112. Specifies that the login is to be mapped to a Windows login
     1. Cannot, public
     2. -m option
     3. WINDOWS option
     4. SNAPSHOT
113. ____ are the two email systems SQL Server Agent can use to send emails.
     1. Database, SMTP
     2. Database Mail or SQL Mail
     3. CREATE TRIGGER statement
     4. SQL Mail
114. Granted the permission to manage logins and their properties, as well as GRANT, DENY, and REVOKE permissions at server-level, and if they have access to database-level permissions. They can reset passwords
     1. SERIALIZABLE
     2. Credentials
     3. securityadmin role
     4. bulkadmin role
115. Used to extract and read the job schedule.
     1. MUST_CHANGE option
     2. WINDOWS option
     3. HASHED option
     4. sp_get_schedule_description
116. The ____ or ____ statement are two ways to configure logins and their passwords.
     1. Management Studio, sp_configure
     2. Event class, trace
     3. Management Studio
     4. Management Studio, CREATE LOGIN
117. Granted the permission to execute the BULK INSERT statement
     1. Autocommit mode
     2. Query Window
     3. dbcreator role
     4. bulkadmin role
118. Allows you to increase the number of extents allocated for each file in a filegroup.
     1. -E option
     2. -s option
     3. -n option
     4. -g option
119. You create a logon trigger with the ____.
     1. ROLLBACK TRANSACTION statement
     2. READ COMMITTED
     3. SQL Server Agent
     4. CREATE TRIGGER statement
120. The ____ can be used in real time.
     1. Resource Governor
     2. View, Resource Governor
     3. HASHED option
     4. Resource pool
121. There can be ____ transaction ____ levels set at a time.
     1. -f option
     2. One, isolation
     3. -g option
     4. -s option
122. Triggers that can fire stored procedures when a LOGON event is raised.
     1. Logon Triggers
     2. sp_configure
     3. -f option
     4. Can, trigger
123. ____ are the optional password policies available. (3)
     1. The Standard Template
     2. User must change password at next login
        Enforce password expiration
        Enforce password policy
     3. ALTER SERVER CONFIGURATION, PROCESS AFFINITY
     4. CREATE WORKLOAD GROUP, ALTER RESOURCE GOVERNOR RECONFIGURE
124. The ____ password policies are enforced for ____ logins.
     1. migrating, server
     2. Windows, SQL Server
     3. Cannot, Agent
     4. One, Browser
125. Using the ____ statement or Right-clicking on it in the ____ of the Management Studio and selecting Enable are the two ways can you enable the Resource Governor.
     1. SQL Server Configuration Manager
     2. CREATE CREDENTIAL, Management Studio
     3. Reporting Services Configuration Manager
     4. ALTER RESOURCE GOVERNOR RECONFIGURE, Management node
126. ____ on the object and select ____ to get information about an objects properties in Management Studio.
     1. TSQL Template
     2. Right-click, Properties
     3. -m option
     4. CHECK_POLICY option
127. Used to show or change server-level settings
     1. sp_configure
     2. On, default
     3. -f option
     4. Lower
128. Has the server prompt the user for a new password on their first login with that password
     1. -s option
     2. COMMIT TRANSACTION
     3. MUST_CHANGE option
     4. -f option
129. Used for assigning processors to specific threads.
     1. affinity masks
     2. Script Login
     3. Latches
     4. Can, trigger
130. ____ are the hints that XLOCK can be specified with.
     1. I/O affinity task
     2. Cannot, public
     3. ROWLOCK, PAGLOCK, TABLOCK
     4. Workload Groups
131. Specifics that the password that follows the PASSWORD argument is already hashed
     1. HASHED option
     2. WINDOWS option
     3. -n option
     4. -s option
132. Captures all T-SQL statements submitted by clients and the time issued. Used for debugging client applications.
     1. TABLOCK hint
     2. -s option
     3. TSQL Template
     4. Standard Template
133. The row locks are to be taken
     1. SNAPSHOT
     2. Locks
     3. Table hints
     4. ROWLOCK hint
134. There are ____ predefined templates for SQL Server Profiler.
     1. Locks
     2. XLOCK
     3. 9
     4. 5
135. Used to prevent concurrent use of a resource by multiple different transactions
     1. Locks
     2. Cannot
     3. 9
     4. XLOCK
136. The executable process of the SQL Server Database Engine.
     1. Management Studio
     2. Database Engine service
     3. Table hints
     4. serveradmin role
137. An option in Management Studio, allows you to script a login
     1. Script Login
     2. Locks
     3. -n option
     4. Compatibility
138. Capture statements submitted and their execution time. Used to identify the slower queries.
     1. bulkadmin role
     2. TSQL_Duration Template
     3. dbcreator role
     4. CHECK_EXPIRATION option
139. Refers to an exclusive lock on the table
     1. Table hints
     2. TABLOCKX hint
     3. -f option
     4. Cannot, Agent
140. Used for shortening the startup time when starting SQL Server from the command prompt
     1. -f option
     2. One, isolation
     3. -c option
     4. WINDOWS option
141. Granted the permission to preform any activity in the server
     1. securityadmin role
     2. dbcreator role
     3. setupadmin role
     4. sysadmin role