SQL- ssis - powerBI Interview questions

 

About Ranks

CTE's

1. What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN?

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table, and matched rows from the right table.
• FULL OUTER JOIN: Returns all rows when there is a match in one of the tables.

2. How do you optimize a slow-running query?

Answer:

• Analyze execution plan.
• Add appropriate indexes.
• Avoid SELECT *.
• Use WHERE filters early.
• Avoid correlated subqueries.
• Use JOIN instead of subqueries when possible.
• Partition large tables.

3. What is a CTE and when would you use it?

Answer: A Common Table Expression (CTE) is a temporary result set used for:

• Recursive queries
• Improving readability
• Breaking complex queries into manageable parts

WITH SalesCTE AS (

  SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales

   FROM Sales

  GROUP BY SalesPersonID

)

4. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

• RANK(): Skips ranks if there are ties.
• DENSE_RANK(): No gaps in ranking.
• ROW_NUMBER(): Assigns a unique number to each row.

5. How do you handle deadlocks in SQL Server?

Answer:

• Identify using SQL Profiler or Extended Events.
• Minimize locking time.
• Access tables in the same order.
• Use SET LOCK_TIMEOUT.
• Optimize transactions to be short and efficient.

6. What is the difference between TRUNCATE and DELETE?

• DELETE: Logs each row, can have WHERE clause, triggers fire.
• TRUNCATE: Faster, minimal logging, cannot use WHERE, no triggers.

What is an execution plan and how do you read it?

Answer: An execution plan shows how SQL Server processes a query. Look for:

• Table scans vs. index seeks
• Cost percentages
• Join types
• Missing indexes