Sql server DBA Interview Questions and Answers Part 1

1) What are System Databases in to sql server  ?

     a) TEMPDB

     b) MSDEB

     c) MASTER

     d) MSDB

2) What  will store in TEMPDB Database ?

   In TEMPDB database will store following

   a) Row versions

   b) Cursor

   c) Temp objects etc....

3) What  will store in MODEL  Database ?

      In MODEL Datbase Templates of new database objects like we have tables and cloumns etc.....

4) What  will store in Master Database ?

      In MASTER Database User logins and Server configurations

5) What will store in MSDB database ?

    a) Scheduled jobs

    b) Backup/Restore

    c) DTA information

6) Is it possible to Perform Backup Restore Operation On TEMPDB

    No, Not possible.

7) What information will Store in the Mssqlsystemresource Database?

     Mssqlsystemresource Database will store Definition of sys objects, which logically shows into all database and DMVs.

8) Where will Sql Logs Gets Stored?

      Sql logs are stored into root folder SQL server, Log folder.

9) What are all the different types of joins ?

       1) Inner Joins

       1) An Equality condition

       2) Equality condition

       3) Self join

   2) Cross joins

   3) Outer joins

       1) Left outer join

       2) Right outer join

       3) Full outer join

10) What is Left outer join ? Give an Example ?

        In this case we will be getting the matching records from both the two tables as well as the unmatched records from left hand side table.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E left outer join Department D on E.Deptno=D.Deptno

11) What is Right outer join ? Give an Example ?

       In this case we will be getting the matching records from both the two tables as well as the unmatched records from right hand side table.

     Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E right outer join Department D on E.Deptno=D.Deptno

12) How to know/find the Version Of Sql Server?

       Using following command :   Select @@Version

13) How to Know/find the Service Pack Installed in sql server?

       Using following command :   Select @@Version

14) What is the Difference Between Primary Key And Unique Key?

      a) Unique Key:  An unique key cant not be referenced as foreign key. And it may allow on null.

      b) Primary key : An primary key can be referenced as foreign key and it will not allow null

15) What is an Index?

     Sql server without searching into each extent and each page will be first searching in to the index page of the table.if present to locate the address of the           records which have to be retrived and jumps directly to there address locations and retrives the information.if sql server has to use an index scan first the           index must be present for the table.Indexes are created for a table basing on a cloumn or columns of the table.

16) What are all the different types of indexes?

    In sql server we have two types of Indexes

     a) Clustered Index

     b) Non clustered index

17) What Is Mean By Clustered Index And Non Clustered Index ?Give Syntax and Example ?

       Syntax of Index : create clustered index Name_of_index on Employee(salary)

      a) Clustered Index

            In this case the arrangement of the data in the index page will be exactly same as the arrangement of the data in the datapage for the table
           Example : An Index in the start of the Book

      b) Non Clustered Index

          In this case the arrangement of the data in the index page will not be same as the arrangement of the data in the actual data page for the table
          Example : An index in the end of a book

18) What is Sql Profiler. What are the Default Templates in It?

       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.

19) What is DMVS(Dynamic Management Views) ?

      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.

20) What is the Syntax To Execute the Sys.dm_db_missing_index_details?

        Syntax : Select * from Sys.dm_db_missing_index_details
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

1 comment

  1. .Hello Mate,


    Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.


    is there any alert that i can create by scanning through sql error log and find if there is any error related to always on health and if there is any, alert through email?
    I assume all always on related issues will be reported in sql server errorlog and WSFC related issues will be in event log. But again if WSFC health is not good it will impact Alwayson group status and it will ultimately show up in the sql error log.


    But great job man, do keep posted with the new updates.


    Gracias
    Irene Hynes

    ReplyDelete