• Full Backup: A complete backup of the database.
• Differential Backup: Backs up only the data that has changed since the last full backup.
• Transaction Log Backup: Backs up the transaction log, allowing for point-in-time recovery.
• File and Filegroup Backup: Backs up specific files or filegroups.

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to store and retrieve data as requested by other software applications.

SQL Server is used for data storage, retrieval, and management. It supports various operations, including transactions, security, and backup, allowing businesses to manage their data effectively.

• Clustered Index: Sorts and stores the data rows in the table based on the index key. Each table can have only one clustered index.
• Non-Clustered Index: A separate structure that contains a pointer to the data rows. A table can have multiple non-clustered indexes.

A UDF is a function created by the user to encapsulate reusable code that can be called within SQL statements. There are two types:

• Scalar UDF: Returns a single value.
• Table-valued UDF: Returns a table.

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.
• FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table records.
• CROSS JOIN: Returns the Cartesian product of the two tables.

SQL Server Agent is a component that allows for automation of tasks such as running jobs, sending alerts, and executing scheduled tasks. It is essential for database maintenance and administration.

A primary key is a unique identifier for a record in a table. It ensures that no two rows have the same value and cannot contain NULL values.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It typically involves dividing large tables into smaller, related tables.

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row in another table, establishing a relationship between the two tables.

SQL Server Agent is a component of SQL Server that allows for automation of tasks like scheduling jobs, alerts, and notifications.

A transaction is a sequence of operations performed as a single logical unit of work. A transaction must either be completed (committed) or canceled (rolled back), ensuring data integrity.

• Atomicity: Transactions are all-or-nothing.
• Consistency: Transactions bring the database from one valid state to another.
• Isolation: Transactions do not interfere with each other.
• Durability: Once a transaction is committed, it remains so, even in the event of a system failure.

A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It helps in modular programming, reuse of code, and improved performance.

A view is a virtual table created by a query that selects data from one or more tables. It does not store data itself but provides a way to simplify complex queries.

Triggers are special types of stored procedures that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

• Use appropriate indexes.
• Optimize queries by avoiding SELECT *.
• Analyze execution plans.
• Update statistics.
• Consider partitioning large tables.

SQL (Structured Query Language) is a standard language for querying and manipulating databases, while T-SQL (Transact-SQL) is Microsoft's proprietary extension of SQL that includes additional features like procedural programming and error handling.

• DELETE: Removes rows from a table based on a condition. It can be rolled back if wrapped in a transaction.
• TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back and resets any identity columns.
• DROP: Deletes the entire table structure from the database. All data and associated indexes are lost.

• Read Uncommitted: Allows dirty reads.
• Read Committed: Default level; does not allow dirty reads.
• Repeatable Read: Ensures consistent reads; prevents non-repeatable reads.
• Serializable: Highest level; prevents phantom reads.
• Snapshot: Uses versioning to provide a snapshot of the data.

An execution plan shows the steps that SQL Server takes to execute a query. It helps in analyzing performance issues by detailing the operations performed, the order in which they occur, and the estimated cost of each operation.

• Clustered Index: Sorts and stores the data rows based on the index key. Each table can have only one clustered index.
• Non-Clustered Index: A separate structure that points to the data rows. A table can have multiple non-clustered indexes.
• Unique Index: Ensures that no two rows have the same values in the indexed columns.
• Full-Text Index: Used for full-text searches on character-based data.

Indexed views are views that have a unique clustered index created on them. They store the results of the view physically on disk, which can improve performance for complex queries but requires additional overhead during data modification.

The WITH (NOLOCK) hint allows SQL Server to read data without acquiring shared locks, which can improve performance and reduce blocking. However, it may lead to dirty reads, meaning uncommitted data may be read.

A deadlock occurs when two or more sessions are waiting for each other to release locks, causing a standstill. SQL Server automatically detects deadlocks and terminates one session to resolve it. To prevent deadlocks, use:

• Proper transaction management.
• Shorter transaction times.
• Consistent access order for resources.
• Implementing retry logic: In applications to handle deadlock exceptions and retry the transaction.
• Using the SQL Server Profiler: To monitor deadlocks and identify the causes.

TempDB is a system database used to store temporary objects such as temporary tables, table variables, and stored procedures. It is recreated every time SQL Server starts and is shared by all users.

SQL Server Jobs are tasks that can be scheduled to run at specific times or in response to certain events. Jobs can include various tasks such as running scripts, backing up databases, or performing maintenance tasks.

Error handling in T-SQL can be done using TRY...CATCH blocks. If an error occurs within the TRY block, control is passed to the CATCH block, where you can log the error or take corrective actions.

SQL Server supports various data types, including:

• Numeric: INT, DECIMAL, FLOAT, etc.
• Character: CHAR, VARCHAR, NCHAR, NVARCHAR, etc.
• Date and Time: DATE, TIME, DATETIME, DATETIME2, etc.
• Binary: BINARY, VARBINARY.
• Others: XML, JSON, UNIQUEIDENTIFIER, etc.

Normalization reduces data redundancy and ensures data integrity. It organizes tables and relationships to eliminate duplicate data, making updates and deletions more efficient.

SQL Server uses locking and isolation levels to manage concurrent access to data. It ensures that transactions are isolated from each other to maintain data integrity while allowing multiple users to access the database.

• Table Variable: Declared using DECLARE, its scope is limited to the batch or procedure. It is stored in memory but can spill to disk if it exceeds memory limits.
• Temporary Table: Created using CREATE TABLE #TempTable, it can be indexed, has a broader scope, and can be used by multiple sessions.

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and can be recursive.

Window functions perform calculations across a set of rows related to the current row. Common examples include ROW_NUMBER(), RANK(), and SUM() OVER(). They are used for analytics without collapsing the result set.

• Simple: No transaction log backups; only full backups are possible. Automatically reclaims log space.
• Full: Requires transaction log backups; point-in-time recovery is possible.
• Bulk-logged: Allows for minimal logging of bulk operations while providing point-in-time recovery.

A schema is a collection of database objects, such as tables and views, that logically groups them together. A user is an account that can access the database. Users can own schemas, and a single user can own multiple schemas.

• Indexing: Create appropriate indexes based on query patterns.
• Statistics: Ensure statistics are up-to-date for the query optimizer.
• Query Refactoring: Rewrite complex queries for efficiency.
• Partitioning: Split large tables into partitions to improve performance.
• Avoiding Cursors: Use set-based operations instead of cursors where possible.

• Poorly written queries.
• Missing or outdated statistics.
• Lack of proper indexing.
• High concurrency leading to locking/blocking.
• Hardware limitations (CPU, memory, disk I/O).

The query optimizer is a component of SQL Server that determines the most efficient way to execute a given query. It evaluates different execution plans and chooses the one with the lowest cost based on various factors, such as available indexes and statistics.

Parameter sniffing occurs when SQL Server creates an execution plan based on the parameter values provided during the first execution of a stored procedure. This plan may not be optimal for different parameter values in subsequent executions.

• CHAR: Fixed-length character data. Pads with spaces.
• VARCHAR: Variable-length character data. More storage-efficient.
• NCHAR: Fixed-length Unicode data (supports international characters).
• NVARCHAR: Variable-length Unicode data (more storage-efficient for Unicode).

Service Broker is a feature for asynchronous messaging and queuing in SQL Server. It allows applications to communicate and process messages reliably. Use cases include decoupled application components, long-running processes, and distributed transaction management.