Microsoft SQL Server
Top 40 SQL Interview Question and Answers
1. What is Index in SQL?
With the help of Indexes, information retrieval from the database happens faster and with greater efficiency. Thus, indexes improve performance. There are three types of indexes:
Clustered: Used for reordering tables and searching information with key values.
Non-clustered: Used for maintaining the order of the tables.
Unique: They ban fields from having duplicate values.
There can be many non-clustered indexes in a table, however, there can be only one clustered index.
2. What are Scalar subqueries and Correlated subqueries?
A Scalar subquery is when a query returns just one row and one column of data. A Correlated subquery occurs when a query cannot process without information from an outer query. In such cases, table aliases define the scope of the argument and the subquery is parameterized by an outer query. Thus, there is a correlation between the inner and outer queries. As a result, back and forth execution takes place where a single row of results from the outer query passes parameters to the inner query.
3. What do you mean by ‘auto increment’?
With the auto-increment command, one can generate unique numbers when new records are added to a table. This function is especially useful when one wants to automatically generate the primary key field values upon inserting new records. This command comes in handy on several platforms. The auto-increment command for the SQL servers is “identity”.
4. What is the main use of ‘recursive stored procedure’?
The main use of the recursive stored procedure is to make the code calls till the time certain boundary conditions are reached. This helps programmers enhance productivity by using the same code multiple times.
5. What is the difference between SQL and MySQL?
Structured Query Language is utilized for handling and modifying data in relational databases. With SQL, you can generate and alter databases, tables, and other related objects, alongside executing various data operations, including record insertion, updates, and deletions.
MySQL, on the other hand, is a specific relational database management system (RDBMS) that uses SQL as its primary language for managing data. MySQL is an open-source RDBMS that is widely used for web applications.
6. List the type of SQL statements or subsets.
Below are the popular subsets used in SQL:
DDL (Data Definition Language) - It is used to define and structure tables. Users can CREATE, ALTER, and DELETE the database tables.
DCL (Data Control Language) - Administrators use it to give users privileges to GRANT or REVOKE permissions to the database.
DML (Data Manipulation Language) - It allows users to either UPDATE, INSERT, RETRIEVE, or DELETE information from the database.
7. Define what joins are in SQL.
Joins is a statement used to join two or more rows based on their relationship. There are four types of Join statements:
- Left Join
- Right Join
- Inner Join
- Full Join
8. What is a Foreign Key?
A foreign key is used to link two or more tables together. Its values match with a primary key from a different table. Foreign keys are like references between tables.
9. What is a Primary Key?
A primary key is used to identify unique rows or tables in a database. Primary keys must always contain unique values. Null or duplicate values are not considered primary keys.
10. What is a unique key?
A unique key ensures a table has a unique value not found or contained in other rows or columns. Unlike the primary key, the unique key may have multiple columns. You can create a unique key using the keyword "UNIQUE" when defining the table.
11. What is a SELECT statement used for?
SELECT is a DML command used for fetching one or more tables. It queries for information which usually returns a set of results.
12. Name the clauses used in the SELECT statement.
WHERE - filters the rows according to their criteria
ORDER BY - Sorts the tables/rows according to the ASC clause (ascending order) or DESC clause (descending order)
GROUP BY - groups data from different tables that have similar rows in the database
13. List the types of relationships found in SQL.
One-to-one relationship - This relationship exists between two tables when a single row in one table corresponds to a single row in another table. This relationship is usually established using a foreign key constraint.
One-to-Many/Many-to-One - This relationship exists between two tables when a single row in one table corresponds to multiple rows in another table. This relationship is also established using a foreign key constraint.
Many-to-Many - This relationship exists between two tables when multiple rows in one table correspond to multiple rows in another table. This relationship is usually implemented using an intermediate table that contains foreign keys to the two tables being related.
14. What are CHAR and VARCHAR?
CHAR is a fixed-length string character, whereas VARCHAR is a variable-length string data structure. VARCHAR is preferred over CHAR because it is more space-efficient when storing strings with variable lengths.
15. What is the difference between TRUNCATE and DELETE?
The truncate command is used when you want to delete all rows and values from a table. It is a DDL type of command which is faster. While the DELETE command is used when you want to delete a specific row in a table. It is a DML command type and less efficient than the truncate statement.
16. What is a cursor?
A cursor is a temporary memory allocated by the server when performing any DML queries. They are used to store Database Tables. Basically a cursor in sql is an object in database code that allows processes to process rows one by one. While in other programming languages sets of data is processed individually through a loop, in SQL, data is processed in a set through a cursor.
Two types of cursors are Implicit cursors and Explicit cursors.
Implicit Cursors:
They are Default Cursors of SQL SERVER. Allocated when the user performs DML operations.
Explicit Cursors:
They are created by users in need. They are used for Fetching data from Tables in Row-By-Row Manner.
17. Define normalization.
Normalization is a method of breaking down larger, complex data into smaller tables. It helps in filtering unnecessary, redundant data and leaves only unique values.
18. What is the difference between Local and Global variables?
Local variables are used inside a function and can’t be reused by other functions, whereas global variables can be accessed and used throughout the program.
19. What is a subquery?
A subquery is a query that is found in another query. Usually referred to as an inner query, its output is typically used by another query.
20. What is ETL?
ETL is an acronym for Extract, Transform, and Load. It is a process where you extract data from different sources, transform the data quality, and finally load it into the database.
21. What is ACID?
ACID in SQL refers to a set of properties that guarantee the reliable and consistent processing of database transactions. It is an acronym where each letter stands for one of the properties:
Atomicity: Ensures that a transaction is either fully completed or not executed at all. If any part of a transaction fails, the entire transaction is rolled back, and the database remains unchanged.
Consistency: Guarantees that the database transitions from one consistent state to another upon the completion of a transaction. All data must adhere to predefined rules and constraints.
Isolation: Provides a degree of separation between concurrent transactions, ensuring that they do not interfere with one other. It helps maintain data integrity by controlling the visibility of changes made by one transaction to another.
Durability: Guarantees that after a transaction has been committed, the modifications made to the database become permanent, even if a system failure or crash occurs.
ACID properties are vital in maintaining data integrity and consistency in relational database management systems (RDBMS) and ensuring the robustness of transactions.
22. What are triggers in SQL?
Triggers are special stored procedures that run when there's an event in the database server, such as changing data in a table. A trigger is different from a regular stored procedure as it cannot be directly called like a regular stored procedure.
23. Define an Entity Relationship Diagram.
An Entity Relationship (ER) diagram is a visual representation of the relationship tables found in the database. It displays the table structures and primary and foreign keys
24. Define Check Constraints.
Check constraints are used for checking and ensuring that values in a table follow domain integrity. Users can apply Check constraints to single and multiple columns.
25. Differentiate between HAVING and WHERE clauses.
These conditions are used for searching values except that the HAVING clause is used with the SELECT statement accompanied by the GROUP BY clause. The HAVING clause is used in combination with the GROUP BY clause to filter the data based on aggregate values, while the WHERE clause is used to filter the data based on individual values.
26. What is a SCHEMA?
A schema in SQL is a collection of database objects, including tables, indexes, sequences, and other schema objects. It defines how data is organized in a relational database system. It is used to manage database objects and control access to them by different users.
27. What is CDC?
CDC means change data capture. It records the recent activities made by the INSERT, DELETE, and UPDATE statements made to the tables. It is basically a process of identifying and capturing changes made to data in the database and returning those changes in real time. This capture of changes from transactions in a source database and transferring them to the target, all in real-time, keeps the system in sync. This allows for reliable data copying and zero-downtime cloud migrations.
28. What is a SQL Injection?
SQL injection is a flaw in a code that allows attackers to take control of back-end processes and access, retrieve, and delete sensitive data stored in databases. This strategy is widely utilized using data-driven apps to get access to sensitive data and execute administrative tasks on databases.
29. Explain Database mirroring
Database mirroring is a disaster recovery technique used in SQL Server to provide redundancy and failover capabilities for critical databases. It involves maintaining two copies of a database, known as the principal database and the mirror database, on two separate servers. Database mirroring provides a highly reliable and robust solution for critical databases, with the ability to maintain high availability, reduce downtime, and provide a quick recovery in case of a failure.
30. What is an ALIAS?
An ALIAS command is a name given to a table. It is used with the WHERE statement when users need to identify a particular table. They are often used to increase the readability of the column names. An alias exists only for the time the query exists and is created with the AS keyword.
Alias Syntax for column
SELECT column_name AS alias_name FROM table_name;
Alias syntax for table
SELECT column_name(s)
FROM table_name AS alias_name;
31. What does STUFF() do?
The STUFF() function deletes a string section and inserts another part into a string starting from a specified position.
Syntax
STUFF (source_string, start, length, add_string) Where:-
source_string: This is the original string to be modified.
start: It is the starting index from which the given length of characters are deleted, and a new sequence of characters will be added. length: The number of characters to be deleted from the starting index in the main string.
add_string: The new set of characters (string) to be inserted in place of deleted characters from the starting index
32. What is a deadlock in SQL, and how can you prevent them?
A deadlock occurs when two or more transactions are waiting indefinitely for each other to release resources such as locks on rows or tables.
To prevent deadlocks:
- Access objects in a consistent order, reducing the chances of cyclic dependencies.
- Reduce lock time, ensuring minimal time between acquiring and releasing locks.
- Apply appropriate isolation levels to limit the locking scope
- Use transactions with a smaller scope and avoid running large, long-running transactions.
- Implement a deadlock detection and handling mechanism, such as timeouts or retry logic.
33. What is the difference between COMMIT and ROLLBACK?
COMMIT is a statement executed to save the changes made to a database. It ensures that the changes made within a transaction are permanent and cannot be rolled back. On the other hand, a ROLLBACK statement is executed to revert all the changes made on the current transaction to the previous state of the database.
34. What is the difference between a temporary table and a table variable in SQL Server?
Temporary Table: A temporary table is created using CREATE TABLE statement with a prefix #, and it is stored in the tempdb system database. Temporary tables support indexing, statistics, and can have constraints.
There are two types of temporary tables: local (visible only to the session that created it) and global (accessible to all sessions).
Table Variable: A table variable is declared using DECLARE statement with @ prefix, and it is also stored in the tempdb system database. Table variables don't require explicit dropping, have no statistics, limited constraints, and are scoped to the batch or stored procedure in which they are declared.
35. Define isolation in SQL transactions.
In SQL transactions, isolation refers to the degree to which a transaction is separated from other transactions taking place concurrently within a database management system (DBMS). It is one of the four key properties of database transactions - known as ACID (Atomicity, Consistency, Isolation, Durability). Isolation levels determine the extent to which changes made by one transaction are visible to other simultaneous transactions, and each level provides different performance and side effects
36. What is the use of a Graph Database?
The main advantage of using graph databases is their ability to efficiently handle interconnected data, making them suitable for use cases where relationships between entities are deep, complex, or frequently changing. They excel at tasks such as performing complex graph analysis, traversing hierarchical relationships, or finding patterns in connected data.
37. What is the difference between GETDATE and SYSDATETIME.
GETDATE function returns the date and time of a location. While on the other hand, the SYSDATETIME function returns the date and time with a precision of 7 digits after the decimal point.
38. What is a live lock?
A live lock is a situation where two or more processes are actively trying to make progress but are blocked and unable to proceed. In live lock, the processes are not blocked on resources but are actively trying to complete the tasks causing them to interfere with each other. It is difficult to detect and resolve Live locks because the system does not crash or give an error message.
