SQL Server-Interview Questions

RDBMS:

1. What is normalization and what are its pros and cons?
2. What is demoralization and what are its pros and cons?
3. What are ACID properties and how SQL server is maintaining these?
4. What are DML and DDL commands?
5. What is difference between a view and a table?
6. What are synonyms?

Developer:

1. What is the difference between index rebuilding vs index reorganizing and in which scenarios each can be used?
2. What is the default transaction isolation level used in SQL server?
3. What is the difference between user and login?
4. What  all permissions are required to execute scalar UDF and table valued UDF ?
5. Where does the full text catalog created in the SQL server 2008?
6. How to rename a table column?
7. What is the difference between coalesce and isnull functions?
8. What is the difference sp_executesql and exec?
9. What are magic tables?
10. How can you delete duplicate records from a table?
11. How to find the third highest salary for an employee?Ans
12. Which all recovery models are available in SQL Server?
13. List all system DBs with their usage.
14. What are the different isolations in SQL server and what’s the difference between them?
15. What is the difference between intersect v/s except?
16. Temp Table vs Temp variables.
17. What is CTE?
18. What will happen if rollback is executed in innermost transaction?
19. What will happen if commit is executed in innermost transaction?
20. What is OUTPUT clause in TSQL?
21. What is the max size of varchar(max)?
22. What is fill factor and how it is important?
23. List few of  DMVs with their usage.
24. What is difference between delete and truncate?
25. What is sparse column and how is it useful?
26. By default SQL server listens on which port?
27. What is a heap?
28. What is the difference between clustered and nonclustered indexes?
29. What’s the latest version of SQL server?
30. What’s row level compression and the page level compression?

DBA:

1. Can you take transnational or differential backup of master database?
2. How to start SQL Server single user mode and when do you need it?
3. What’s the use of browser service?
4. How to make DAC connection?Ans
5. What is the difference between index rebuilding and index reorganizing and in which scenario these can be used?
6. What are the filtered indexes and why are they useful in some scenarios?
7. What is a covering index?
8. What can cause an index scan instead of index seek?
9. Which port does DAC use?
10. What all type of backups are available in SQL Server?
11. What will you do to improve the performance of the tempdb?
12. What is snapshot isolation level and what are its pros and cons?
13. What will you do if log-files are over growing?
14. Find the running queries and the resources it is waiting on.
15. What are VLFs and why are they important to watch?
16. How to find a backup is valid without restoring it?
17. How exactly does SQL server cater a SELECT Query?
18. How exactly does SQL server cater an UPDATE query?
19. What is TDE and how you can set it up?
20. What type of indexes are available in SQL server?

 ETL/SSIS:

1. What is difference between Control flow and Data Flow Engine?
2. List the ways of deployment available in SSIS 2012?
3. How to configure checkpoint in SSIS?
4. How to debug a SSIS package?
5. What’s new in SQL server 2012?
6. What is fast parse?
7. List 5 ways to speed up your SSIS package.
8. List the ways you can execute a SSIS package.
9. If you want to delete duplicate records that what option would you use in SSIS?
10. Explain SCD task and its working.
11. Explain Lookup task.
12. Explain SCD type 1,2 and 3.
13. How you would handle errors in SSIS?
14. How to check the execution status of  a SSIS package,deployed in SSISDB, using system tables.Ans
15. What is factless table?
16. What is the difference between dimension table and fact table?
17. What is the difference between star and snowflake schema?