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.
11. What is the difference between VARCHAR and CHAR data types?
Answer:
VARCHAR (Variable Character) stores variable-length strings. It only uses as much space as the data requires.
CHAR (Character) stores fixed-length strings. If the string is shorter than the defined length, it pads the remaining space with spaces.
12. What is a Subquery?
Answer:
A subquery is a query nested inside another query. It can be used in the SELECT, INSERT, UPDATE, or DELETE statements to retrieve data that will be used in the outer query. There are two types of subqueries:
Single-row subquery: Returns one row of data.
Multiple-row subquery: Returns multiple rows of data.
13. What is an Index in SQL Server?
Answer: An index is a database object that speeds up data retrieval operations on a table at the cost of additional storage space and slower write operations. SQL Server supports clustered and non-clustered indexes. A clustered index determines the physical order of data in the table, while a non-clustered index creates a separate structure to store pointers to the data.
14. What are Stored Procedures?
Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures help improve performance by reducing the amount of information sent to the database, and they can also be used for security purposes and code reusability.
15. What is a Foreign Key?
Answer: A Foreign Key is a column (or set of columns) in one table that links to the primary key of another table. It enforces referential integrity by ensuring that a value in the foreign key column must match a value in the referenced primary key column or be NULL.
16. What is a Primary Key?
Answer: A Primary Key is a column or a set of columns in a table that uniquely identifies each row in that table. A table can have only one primary key, and it cannot contain NULL values.
17. What are the differences between DELETE, TRUNCATE, and DROP?
Answer:
DELETE: Deletes rows from a table based on a condition but does not remove the table structure. It can be rolled back (if used within a transaction).
TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster than DELETE but cannot be rolled back (except in a transaction).
DROP: Deletes the entire table or database, including the table structure and all its data. It cannot be rolled back.
18. What is normalization?
Answer: Normalization is the process of organizing data in a relational database to minimize redundancy and dependency by dividing large tables into smaller, manageable ones. The main goal is to eliminate duplicate data and ensure that relationships between data are maintained. There are several normal forms (1NF, 2NF, 3NF, etc.), each building on the previous one to improve data integrity.
19. What are the different types of joins in SQL?
Answer: The main types of SQL joins are:
INNER JOIN: Returns rows when there is a match in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table. If no match, NULLs are returned.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table.
FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table.
CROSS JOIN: Returns the Cartesian product of both tables.
SELF JOIN: Joins a table with itself.
20. What is SQL Server?
Answer: SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store and manage data in databases and provides tools for database management, data retrieval, and processing, as well as business intelligence, reporting, and analytics.