Stored Procedure vs Function
Thursday, June 13, 2024 in SQL Server Interview Questions
Stored Procedure vs Function Feature Stored Procedure Function Usage Use for complex processing (involve DML, transaction and exception handling) Use for computations Database Modifications Yes (INSERT, UPDATE, DELETE) No Syntax Use EXEC to run Used …
Join vs Subquery
Thursday, June 13, 2024 in SQL Server Interview Questions
Join vs Subquery JOIN Subquery JOIN is combining rows from two or more tables based on related columns A subquery is a SELECT query that is nested within another DML statement Generally, joins are highly optimized Generally, correlated subquery is …
DROP vs TRUNCATE vs DELETE
Thursday, June 13, 2024 in SQL Server Interview Questions
DROP vs TRUNCATE vs DELETE DROP TRUNCATE DELETE Remove an entire object from the database 1. Remove all rows from a table without logging individual row deletions or firing triggers. 2. Identity sequence reset to its seed value Remove specific rows …
WHERE vs HAVING
Thursday, June 13, 2024 in SQL Server Interview Questions
WHERE vs HAVING WHERE HAVING Filter individual rows based on conditions involving column values Filter groups of rows based on conditions involving aggregated values Must used after GROUP BY Contain aggregate function Used in SELECT, UPDATE, or …
Constraint
Thursday, June 13, 2024 in SQL Server Interview Questions
Constraint PRIMARY EKY Constraint A unique identifier (single col or composite cols) for a record in a database table Maintain ****entity integrity Automatically creates a clustered index Identity column is commonly used for primary key …
Normalization
Thursday, June 13, 2024 in SQL Server Interview Questions
Normalization Normalization is a database design technique Normalization vs Denormalization Normalization Will divide large tables into smaller and related tables Reduce redundancy Improve data integrity Denormalization Improve read performance by …
Entity Relationship Diagram
Thursday, June 13, 2024 in SQL Server Interview Questions
Entity Relationship Diagram An Entity Relationship Diagram (ERD) is a type of flowchart that illustrates how entities relate to each other within a system
Entity Relationship
Thursday, June 13, 2024 in SQL Server Interview Questions
Entity Relationship One-to-One Relationship where a record in one table is linked to exactly one record in another table eg. each student has a unique locker One-to-Many Relationship where a single record in one table can be associated with one or …
Transaction
Thursday, June 13, 2024 in SQL Server Interview Questions
Transaction A sequence of one or more operations performed as a single logical unit of work Modes to handle transaction in SQL Server Autocommit (default) Each individual statement is considered a transaction Implicit SQL Server starts the …
SQL Database vs NoSQL Database
Thursday, June 13, 2024 in SQL Server Interview Questions
SQL Database vs NoSQL Database SQL Database Suitable for applications requiring a fixed schema Support complex queries Strict data integrity ACID properties Atomicity A transaction is all-or-nothing Consistency Database remains in valid state before …