Backup & Recovery
This type of backup contains all data since the last full backup and is usually smaller.
What is a differential backup?
This principle says users should only get the exact permissions required for their job.
This slow operation occurs when SQL Server must read every row in a table.
What is a table scan?
These are some reasons storing a phone number as an INT is a bad idea.
This built-in mechanism ensures that a group of SQL statements either all complete successfully or none of them do.
What is a transaction?
This recovery model keeps the transaction log intact until backed up, allowing point-in-time recovery.
What is the FULL recovery model?
These are a server-level identity and a database-level identity both required to access the server and underlying databases.
What are a login and a user?
Created on columns to speed up searches, this structure acts like a book index. You may have more than one of these per table.
What is a non-clustered index?
Breaking data into multiple related tables to reduce repetition is called this.
What is normalization?
This SQL Server component allows you to automate backups, cleanups, and recurring scripts.
What is SQL Server Agent?
After accidental data deletion, this restore option allows you to pick a specific moment right before the incident.
What is STOPAT (or STOPATMARK)?
This system feature could track failed logins, permission changes, and other security events.
What is SQL Server Audit?
These are the reasons having too many indexes could be bad.
When one student can enroll in many classes and one class can have many students, this relationship and structure are required.
What is a many to many relationship and an associative (or join) table?
This kind of object returns a saved SELECT statement like a virtual table.
What is a view?
During a restore sequence, the database must stay in this state until all backup files are applied.
What is the NORECOVERY state? (Readonly also accepted)
Instead of assigning SELECT permissions to each individual analyst, you should create this organizational object.
What is a database role?
This feature keeps long-term query statistics and helps identify queries that suddenly became slower.
What is the Query Store?
This normal form is violated when storing multiple values in a single cell [1, 2, 3, 4] or multiple repeated columns [FavoriteColor1] [FavoriteColor2] [FavoriteColor3] etc.
1st normal form.
This records all changes made to a database.
Your log file grows uncontrollably even though you take regular full backups. Name the most likely cause.
The database is in full recovery model but log backups are not being performed.
You want to prevent analysts from seeing salary data in the Employees table, but allow them to see all other columns. This SQL Server feature can enforce this.
What are column level permissions (or create a view)
A backend application calls the same SQL query 10,000 times a day to retrieve user data. Using ____ in the SELECT clause is a bad idea because _____.
SQL Server must fetch all columns even if only one is needed
Larger I/O == slower queries
Prevents 'covering' indexes from working
Increases network bandwidth
If new columns are added, queries silently change behavior
Risk returning sensitive data
Most databases strive for ______ because too much normalization may result in too many _____ during queries.
What are third normal form and joins?
These are the four properties (and definitions of each) that make up ACID.
Atomicity: a transaction is "all or nothing".
Consistency: the database always moves from one valid state to another.
Isolation: ensures that one transaction's changes can't interfere with another's until it has fully completed.
Durability: once a transaction commits, it is permanent.