SQL Server DBA Interview Questions

Hi All
 Jr. SQL server DBA

Q.1. What are system databases into SQL server (2005/2008)
Ans. TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource,
Q.2. What stored by the TEMPDB ?
Ans. Row versions, cursor, temp objects.
Q.3. What Stored by the MODEL?
Ans. Templates of new database objects, like tables and column.
Q.4. What Stored by the MASTER?
Ans. Server’s configurations and logins.
Q.5. What Stored by the MSDB?
Ans. Scheduled jobs, Backup/Restore and DTA information.
Q.6. Can we Perform Backup Restore operation on TEMPDB?
Ans. NO
Q.7. What is stored in the mssqlsystemresource database?
Ans. Definition of sys objects, which logically shows into all database and DMVs.
Q.8. Where the SQL Logs gets stored?
Ans. It’s stored into root folder SQL server, LOG folder.
Q.9. What do you mean by Ded lock and Live lock in SQL server?
Q.10. What Backup strategy you will keep for one TB (Tera Bite) database for minimal data lose.
Ans. http://www.sqlservercentral.com/Forums/Topic1019063-357-2.aspx#bm1020308
Q.11. What is the DAC connection?
Ans. http://msdn.microsoft.com/en-us/library/ms189595.aspx
Q.12. What is a Linked Server?
Ans. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements.
Q.13. What are the Joins in SQL server?
Ans. Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.
Q.14. Describe the Left Outer Join & Right Outer Join.
Ans. Left Outer join Retrieves the all records from LEFT table and matching from the RIGHT table, and null values where is no match.
Right Outer Join just opposite.
Q.15. How to find the Version of SQL server?
Ans. Select @@version
Q.16. How to find the Service pack installed?
Ans. Ans. Select @@version Or select serverproperty (‘productlevel’)
Q.17. What are the Deference between Primary Key and Unique Key?
Ans. An unique key cant not be referenced as foreign key. And it may allow on null.
Q.18. What is mean by Clustered Index and Non clustered index, give syntax of creation?
Ans. create clustered index index_name on empmst(card)
Q.19. What is Scan Table/View and Seek Table/View When its Occurs?
Ans. A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.
Q.20. What is Scan Index and Seek index. When its Occurs?
Ans. http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/
Q.21. What is SQL Profiler. What are the default templates with it?
Ans. SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
http://msdn.microsoft.com/en-us/library/ms190176(v=SQL.100).aspx
Q.22. What are the DMVs?
Ans. Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Q.23. What is the syntax to execute the sys.dm_db_missing_index_details?
Ans. Select * from sys.dm_db_missing_index_details
Q.24. How to Change the SQL server Authentication mode.
Q.25. What is the New in SQL server 2008.
Ans. File stream data type, Activity Monitor, Resource governor, data compression, compressed backup