tempdb
What remains in an encrypted state after TDE has been turned off?
certificate
In cell-level encryption, what protects the symmetric key?
varbinary
What data type is required for cell-level encryption?
deterministic
In Always Encrypted, which encryption type produces the same output for the same input every time?
Column Encryption Setting=Enabled
What is the syntax of an Always Encrypted connection string?
master
Which database stores all SQL logins?
CONTROL
Which SQL permission is almost as powerful as owner, though it can have some of its privileges revoked?
WITH GRANT OPTION
Which SQL permissions option allows a user to extend their permission to another user?
ALTER ROLE
Which SQL statement should you use instead of sp_addrolemember?
database audit specification
After you create a server-level audit, what must you create to make the audit useful?
audit level
The three SQL audit levels are: server level, database level, and ____________?
Simple
Which database recovery model does not permit transaction log backups?
Back up the log.
If your database recovery model is set to full, what must you do to reduce the size of the transaction log?
Bulk-logged
Which database recovery model only performs minimal logging?
partial
What type of backup only backs up specific files or filegroups?
STOPAT
What RESTORE option do you need to use when conducting a point-in-time restore?
RECOVERY
What RESTORE option do you need to use to bring the database online after restoring multiple backups?
NORECOVERY
What RESTORE option do you need to use to indicate you are in the middle of restoring multiple backkups?
PARTIAL
What RESTORE option do you need to use to indicate you are conducting a piecemeal restore?
SINGLE_USER
What ALTER DATABASE option should you use if you need to perform disaster recovery?
NO_TRUNCATE
What BACKUP option should you use to try to back up an orphaned transaction log?
9002
What SQL error number indicates the transaction log is full?
DBCC CHECKDB
Which single DBCC command runs CHECKTABLE, CHECKCATALOG, and CHECKALLOC?
Simple
Which database recovery model automatically truncates the log when you perform a full database backup?
SQL Profiler
Which tool provides a graphical interface for SQL Trace?
SNAPSHOT isolation and online indexing
Which two activities cause heavy tempdb utilization?
sys.dm_os_waiting_tasks, sys.dm_tran_locks, and sys.dm_exec_requests
Which three DMVs can you use to identify locks?
sys.dm_exec_requests, sys.dm_exec_session_wait_stats
Which two EXEC DMVs can you use to identify waits and blocking?
sys.dm_os_wait_stats, sys.dm_os_waiting_tasks
Which two OS DMVs can you use to identify waits and blocking?
STALE_QUERY_THRESHOLD_DAYS
Which ALTER DATABASE option can you use to reduce the risk of the Query Store turning to read only?
REORGANIZE
Which ALTER INDEX option keeps the index online during defragmentation?
Paritioning
What SQL feature horizontally divides a very large table into multiple sections?
rowstore heap
What type of table stores data as rows and columns, but with no index?
columnstore
What type of table stores data as single columns?
non-clustered
Before converting a rowstore to a columnstore, which pre-existing indexes should you drop?
What is the most common way for a SQL Agent operator to receive a notification?
email accounts
What is a SQL email profile comprised of?
DatabaseMailUserRole
To send email in SQL, what role must a user be a member of?
REMOTE_ONLY
Which sp_spaceused parameter can you use to report space utilization in an Azure Stretch database?
-d
Which SQL startup parameter indicates the path to the master database data file?
VIEW SERVER STATE
Which permission is required to view the Activity Monitor?
Disk I/O
The Resource Governor can restrict SQL usage of CPU, RAM, and __________?
1105
What SQL error number indicates a full database?
all
Which version(s) of SQL 2016 support(s) replication?
Network bandwidth and reliability
What is the primary factor to consider when choosing between SQL replication and log shipping?
Windows Server Failover Clustering
What Windows feature must be installed before you can implement an Always On Availability Group?
Disk mirroring
A Basic Availability Group replaces which deprecated high availability technology?
SYNCHRONOUS
Always On Availability Group automatic failover requires which commit mode?
The tie-breaking vote
A witness provides what service to a failover cluster?
ASYNCHRONOUS
Which Always On Availability Group commit mode is ideal for slow WAN links?