Index
Thursday, June 13, 2024 in SQL Server Interview Questions
Index Index is a structure that speeds up data searches Pros Faster data retrieval Efficient data sorting Cons Increase storage space Slower data modification operations B+ Tree is a common data structure used to implement index Non-leaf level Nodes …
Join Algorithm
Thursday, June 13, 2024 in SQL Server Interview Questions
Join Algorithm Nested Loops Join Merge Join Hash Join Definition For each row in the first table (outer loop), the database system searches through the second table (inner loop) to find matching rows Used when both input tables are sorted on the join …
Conditional Logic
Thursday, June 13, 2024 in SQL Server Interview Questions
Conditional Logic SELECT name, [status], CASE [status] WHEN 'active' THEN 'Currently in use' WHEN 'inactive' THEN 'No longer in use' WHEN 'pending' THEN 'Awaiting approval' WHEN 'obsolete' THEN …
Pagination
Thursday, June 13, 2024 in SQL Server Interview Questions
Pagination TOP Return a specified number of rows from the beginning of the result set Better used with ORDER BY Can be like TOP 5 or TOP 5 PERCENT OFFSET n ROWS FETCH NEXT m ROWS ONLY Skip a specific number of rows and then take a fixed number of …
Window Function
Thursday, June 13, 2024 in SQL Server Interview Questions
Window Function Used to perform calculations across a set of rows related to the current row in a query Can use aggregate function SUM(col), AVG(col), COUNT(col), MIN(col), MAX(col) Can use ranking function ROW_NUMBER(), RANK(), DENSE_RANK(), …
Common Table Expression (CTE)
Thursday, June 13, 2024 in SQL Server Interview Questions
Common Table Expression (CTE) A Common Table Expression (CTE) is a temporary result set in SQL Server that you can reference within a DML statement Pros Improve readability Can be recursively defined WITH TotalSales AS ( SELECT salesperson_id, …
Subquery
Thursday, June 13, 2024 in SQL Server Interview Questions
Subquery A subquery is a SELECT query that is nested within another DML statement Subquery used in FROM clause is called Derived Table Simple subquery Simple subquery is independent of the outer query Simple subquery executed before the main query …
Combine Or Exclude Result Sets
Thursday, June 13, 2024 in SQL Server Interview Questions
Combine Or Exclude Result Sets UNION Combines the result set of two or more SELECT statements Contain only distinct values Will sort the first col automatically UNION ALL Combines the result set of two or more SELECT statements Will includes …
Combining Rows From Two Or More Tables Based On Related Columns
Thursday, June 13, 2024 in SQL Server Interview Questions
Combining Rows From Two Or More Tables Based On Related Columns JOIN is combining rows from two or more tables based on related columns Type of join LEFT JOIN (Left Outer Join) Returns all rows from the left table and matched rows from the right …
Filtering Group Results
Thursday, June 13, 2024 in SQL Server Interview Questions
Filtering Group Results SELECT col1_name, SUM(col2_name) FROM table_name GROUP BY col1_name HAVING condition; -- the condition here will be involed the aggregate function -- eg. HAVING SUM(revenue) > 1500 182. Duplicate Emails /* Write your T-SQL …