1. What is the difference between HAVING and WHERE?
Answer:
WHERE: Filters rows before any grouping is done (i.e., it is used to filter individual rows in the result set).
HAVING: Filters groups after the GROUP BY clause has been applied. It is used to filter the results of aggregate functions.
2. What is a deadlock in SQL Server?
Answer: A deadlock occurs when two or more transactions are blocking each other from completing because they are waiting for resources locked by the other transactions. SQL Server automatically detects deadlocks and terminates one of the transactions to resolve the situation.
3. What are the different types of indexes in SQL Server?
Answer:
Clustered Index: Defines the physical order of the data in the table. A table can have only one clustered index.
Non-clustered Index: Creates a separate structure from the table that stores pointers to the data rows. A table can have multiple non-clustered indexes.
Unique Index: Ensures that the values in the indexed columns are unique.
Full-text Index: Used for full-text search operations, enabling complex word-based queries.
4. What is the difference between UNION and UNION ALL?
Answer:
UNION: Combines the result sets of two queries and removes duplicates.
UNION ALL: Combines the result sets of two queries without removing duplicates.
5. What is a Cursor in SQL Server?
Answer: A cursor is a database object used to retrieve, manipulate, and navigate through rows in a result set one row at a time. Cursors can be used when row-by-row processing is required, but they are generally slower than set-based operations.
6. What is a Trigger in SQL Server?
Answer: A trigger is a special type of stored procedure that automatically executes when certain events occur in a database, such as INSERT, UPDATE, or DELETE. Triggers are used for enforcing business rules, maintaining audit logs, or handling cascading actions.
7. What is the purpose of the GROUP BY clause?
Answer: The GROUP BY clause is used in SQL to arrange identical data into groups. This is often used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group.
8. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
INNER JOIN: Returns only rows that have matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table. If no match is found, NULL values are returned for columns from the right table.
9. What is a View in SQL Server?
Answer: A View is a virtual table in SQL Server that is defined by a query. It does not store data physically but retrieves data from one or more underlying tables. Views are used to simplify complex queries, enhance security by restricting access to specific data, and provide a level of abstraction.
10. What are the ACID properties in SQL?
Answer:
ACID stands for Atomicity, Consistency, Isolation, and Durability. These are properties of database transactions that ensure data integrity:
Atomicity: Ensures that all operations in a transaction are completed or none are.
Consistency: Ensures that a transaction brings the database from one valid state to another.
Isolation: Ensures that concurrently executing transactions do not interfere with each other.
Durability: Guarantees that once a transaction is committed, its changes are permanent.