Stored Procedure

• A procedure can have both input and output parameters.
• We can use DML(Insert/Update/Delete) statements inside a procedure.
• Stored Procedure can’t be utilized in a Select statement.
• Stored Procedure can’t be used in SQL statements anywhere in WHERE/HAVING/SELECT section.
• We can use Try-Catch block inside a Stored Procedure

Function

• A function can only have input parameter.
• We can’t use DML statements inside a function.
• Function can be used in a Select statement.
• Function can be used anywhere in SQL statements
• We can’t use Try-Catch block inside a function

DELETE

• Delete Command is used to remove rows from a table based on WHERE condition
• Delete is executed using a row lock, each row in table is locked for deletion.
• Delete is a DML command.
• Delete retains the identity of the column value
• To use Delete command, DELETE permission is required on the table.

TRUNCATE

• TRUNCATE command removes all rows from a table
• Truncate is executed using a table lock and the entire table is locked for removal of all records.
• Truncate is a DDL command.
• In Truncate, the identity column is reset to its seed value if the table contains any identity column
• To use Truncate on a table, atleast ALTER permission is required on the table

WHERE

• WHERE clause can be used with a Select, Update and Delete statement clause
• WHERE is used before the GROUP By clause
• WHERE clause applies to each and every row
• The data that is fetched from memory based on a criterion is specified in the WHERE clause.

HAVING

• HAVING clause can only be used with Select statement
• Following the GROUP BY phrase in the query, the HAVING clause is used to impose a condition on the GROUP function.
• HAVING clause applies to summarized rows(summarized with GROUP BY)
• In HAVING, the completed data is first fetched and then divided according on the condition.

Primary Key

• There is only one Primary key in the table
• We can’t have NULL value in Primary key
• Primary key is a Clustered Index by default
• An auto increment value is supported by a Primary key

Unique Key

• We can have more than one unique key in the table
• Unique key may have only one NULL value.
• Unique key is unique non clustered index by default
• Unique key don’t support an auto increment value

DDL stands for Data Definition Language. It is used to define the structure of the table, that holds the data. SQL commands like CREATE, ALTER, DROP and RENAME comes under this.

DML stands for Data Manipulation Language. It is used for the manipulation of the data. SQL commands like SELECT, INSERT and UPDATE comes under this.

DCL stands for Data Control Language. It is used to control the visibility of data like Granting or revoking access on database and setting different privileges to different user roles. SQL commands like GRANT and REVOKE comes under this.

A composite key in SQL can be defined as a combination of several columns that are used to uniquely identify all the involved rows. A combination of more than one column can uniquely identify any record even though a single column cannot uniquely identify any row. In other words, the combination key is a primary key that is produced by combining many columns. Yet, the data types of several columns could be dissimilar from one another.

For e.g. In a table with Employee information, “EmployeeId” and “EmployeeName” is combined to uniquely identify a row and called as a Composite key.

Data is saved in various linked tables to prevent data duplication. To retrieve the data from a related table, Join keyword is used. When there is at least one match in both tables, a join returns rows.

Based on a shared column between the tables, it is used to combine rows from two or more tables.

Types of Joins are:

Right Join: It returns all the rows from the right table, even when there is no match in the left table.

Left Join: It returns all the rows from the left table, even when there is no match in the right table.

Full Join: It returns those rows when there is a match in one of the tables.

Primary Key

• In a relational database, a primary key constraint serves as a unique identifier for each row in the table.
• The Primary key columns cannot have NULL values
• Each table can have only one primary key.
• Because a primary key is unique and non-null, no two rows can have the same values for the primary key.

Foreign Key

• In order to uniquely identify a row of the same table or another table, a foreign key constraint creates a relationship between two different tables.
• The Foreign key can have more than one NULL value.
• We can have multiple foreign keys in the table.
• A Foreign key can store duplicate values.

A small unit of any given program made up of numerous low-level operations is referred to as a transaction. Every DBMS transaction must uphold ACID, which stands for Atomicity, Consistency, Isolation, and Durability. To guarantee the completeness, accuracy, and integrity of data, ACID must be maintained.

Atomicity: Any given transaction must be regarded as an atomic unit, according to the property of atomicity. It implies that all of its functions must be carried out, or none at all. Make sure the database doesn't contain any states where a transaction has been left unfinished. Either the states before or after the transaction's execution, failure, or abortion must be defined.

Consistency: The consistency property stipulates that following any transaction, the database must always be consistent. A transaction must therefore never have any negative impact on the data and information stored in the database. If the database is consistent before a transaction is done, it must continue to be consistent even after the transaction has been completed.

Isolation: According to the property of isolation, when many transactions are being carried out and performed in a database system concurrently and in parallel, it would appear as if that transaction were the only one present in the system. None of the transactions would have an impact on the existence of any other transactions.

Durability: According to the durability property, each particular database must be strong enough to withstand all of its most recent updates and must continue to function even if the system unexpectedly restarts or fails. If a transaction modifies and commits some data in the database, the database would save the modified data. If a transaction is committed but the system crashes before the data is written to disc, the data will really be updated once the system restarts.

Clustered Index

• A clustered index is used, much like a dictionary, to define the order, sort the table, or organise the data in alphabetical order.
• Compared to a non-clustered index, it is quicker.
• Less memory is needed to execute the operation in Clustered Index.
• A single cluster index may be present in a single table.
• It comes with a built-in capacity for disk storage.

Non Clustered Index

• A non-clustered index records data in a different location than where it is collected.
• Compared to the clustered index, it is slower.
• In order to execute the operations, more memory is needed in Non-Clustered Index.
• It might include several non-clustered indexes.
• It lacks the inherent capability to save data on the disk.