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.

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.

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.

• Left Join
• Right Join
• Inner Join
• Full Join

SELECT is a DML command used for fetching one or more tables. It queries for information which usually returns a set of results.

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.

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.

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.

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.

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.

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.

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.

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.

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;

• 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.

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.

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).

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