5. Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
– With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
– TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
– Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
– DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP
More differences on Delete & Truncate: http://wp.me/p12rgl-7 | YouTube.

16. What should be the ideal combination with IN & UNION (ALL) in terms of performance?
a. SELECT *FROM
WHERE

IN (SELECT… UNION SELECT…)
OR
b. SELECT * FROM

WHERE

IN (SELECT… UNION ALL SELECT…)

17. What is an IDENTITY column and its usage in INSERT statements?
IDENTITY column can be used with a tables column to make it auto incremental, or a surrogate key.
Check my all blog posts related to IDENTITY: https://sqlwithmanoj.com/tag/identity/

Create a job with an optimal script that will update the Master table by the Feed table.Use MERGE statement to INSERT any new record form the Feed table, update any existing record in Master table and DELETE existing records in Master table that is not present in Feed table.

20. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.
More Info: https://sqlwithmanoj.com/tag/cube/

Actually if you put the LEFT/RIGHT JOIN table’s filter condition on WHERE clause then it could act like an INNER JOIN. So, try putting filter conditions before the WHERE clause with the LEFT/RIGHT JOIN’s ON clause.

A very good site for SQL server interview questions organised by topic. If you feel you need to brush up interview questions related to specific topics, this website will be of great use. God bless the person who has contributed to this site. Very useful.