Key Concepts in MS SQL

Introduction

In the world of SQL, several key concepts and terminologies can sometimes be confusing. In this blog post, we’ll explore the differences between some of the most critical SQL concepts in Microsoft SQL Server (MS SQL) by comparing them in a tabular format. This approach will help clarify how these concepts relate to each other, making it easier to understand their distinct functionalities and use cases.

Primary Key vs. Foreign Key

Primary Key: A primary key uniquely identifies each record in a table. It cannot contain NULL values and must contain unique values.
Foreign Key: A foreign key is used to link two tables together. It is a field (or collection of fields) in one table, that refers to the primary key in another table.

Clustered Index vs. Non-Clustered Index

Clustered Index: A clustered index sorts and stores the data rows of the table based on the key values. There can only be one clustered index per table.
Non-Clustered Index: A non-clustered index stores a separate structure from the data rows, with pointers back to the data rows. Multiple non-clustered indexes can be created on a table.

INNER JOIN vs. OUTER JOIN

INNER JOIN: Returns rows when there is at least one match in both tables.
OUTER JOIN: Returns rows that have matching values in one of the tables, along with the unmatched rows from one or both tables, depending on whether it’s a LEFT JOIN, RIGHT JOIN, or FULL JOIN.

WHERE vs. HAVING

WHERE: Filters records before any groupings are made in the result set.
HAVING: Filters records after the GROUP BY clause is applied.

UNION vs. UNION ALL

UNION: Combines the result sets of two queries, removing duplicate records in the final result set.
UNION ALL: Combines the result sets of two queries, including all duplicates.

DELETE vs. TRUNCATE

DELETE: Deletes rows one at a time and logs each deletion.
TRUNCATE: Removes all rows from a table by deallocating the data pages. It is faster and uses fewer system resources than DELETE.

DROP vs. DELETE

DROP: Removes a table or database from the system.
DELETE: Removes rows from a table but does not delete the table itself.

CHAR vs. VARCHAR

CHAR: Fixed-length data type that always uses the same amount of storage space regardless of the length of the data.
VARCHAR: Variable-length data type that uses storage space based on the actual length of the data.

TRANSACTION vs. SAVEPOINT

TRANSACTION: A sequence of SQL operations executed as a single unit of work. It ensures that either all operations succeed, or none do.
SAVEPOINT: Sets a point within a transaction to which you can roll back without affecting the entire transaction.

SUBQUERY vs. JOIN

SUBQUERY: A query nested inside another SQL query.
JOIN: Combines columns from one or more tables based on a related column between them.

To make it easy to understand and remember for a long time. Will go with our educational way of tabular format, it should remind your school days.

Concept 1	Concept 2	Comparison
Primary Key	Foreign Key	Primary Key: Uniquely identifies each record in a table. Foreign Key: Establishes a relationship between two tables. The foreign key in one table points to the primary key in another table.
Clustered Index	Non-Clustered Index	Clustered Index: Determines the physical order of data in a table and only one can exist per table. Non-Clustered Index: This does not alter the physical order of the data. Multiple non-clustered indexes can exist per table.
INNER JOIN	OUTER JOIN	INNER JOIN: Returns records that have matching values in both tables. OUTER JOIN: Returns all records when there is a match in either left (LEFT JOIN), right (RIGHT JOIN), or both tables (FULL JOIN).
WHERE	HAVING	WHERE: Filters records before any groupings are made. HAVING: Filters records after the GROUP BY clause is applied.
UNION	UNION ALL	UNION: Combines the result sets of two queries and removes duplicate records. UNION ALL: Combines the result sets of two queries without removing duplicates.
DELETE	TRUNCATE	DELETE: Removes rows one at a time and logs each row deletion. TRUNCATE: Removes all rows from a table without logging individual row deletions.
DROP	DELETE	DROP: Removes a table from the database entirely. DELETE: Removes rows from a table based on a condition. The table structure remains intact.
CHAR	VARCHAR	CHAR: Fixed-length data type. Always occupies the specified length. VARCHAR: Variable-length data type. Occupies only the space needed to store the data.
TRANSACTION	SAVEPOINT	TRANSACTION: A sequence of operations performed as a single logical unit of work. SAVEPOINT: Allows setting a point within a transaction to which you can later roll back.
SUBQUERY	JOIN	SUBQUERY: A query nested inside another query. JOIN: Combines columns from one or more tables based on a related column between them.

Conclusion

Understanding the differences between these key SQL concepts is crucial for effective database design and query optimization. By comparing these concepts side by side, we hope to have clarified their distinct roles and use cases within MS SQL. Whether you're optimizing performance, ensuring data integrity, or managing complex queries, these comparisons will serve as a handy reference.