Sql server DBA Interview Questions and Answers Part 3

1) What are sql server versions and editions available ?


      1) SQL Server Enterprise Edition

      2) SQL Server Standard Edition

      3) SQL Server Developer Edition

      4) SQL Server Express Edition


       SQL Server 2017 (Latest One)

       SQL Server 2016

       SQL Server 2014

       SQL Server 2012

       SQL Server 2008 R2

       Azure SQL database (initial release

       SQL Server 2008

       SQL Server 2005

2) What is SQL Server Instance? 

   Sql Server installation provides you two options while installing on any machine.

    1) Sql Server Default instance (MSSQLSERVE)

    2) Sql Server Named instance (MachineName\sqlserverinstanceName)

3) How many SQL Server Instances can be installed on one machine ?

     You can  install over all 50 instances including default

4) What is collation ?

   A collation in sql server is set of rules that govern the proper use of characters and alphabtes of any language that you select during installation.

5) Why collation is important in SQL Server?

   collation reflects and follows the rules depending upon the language,region and pattern.

      We have devided in to two parts

       a) Sql server default collation

       b) Sql server database collation

6) How can you find SQL Server Default Collation?

   Run the following command

    SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

    ANS: SQL_Latin1_General_CP1_CI_AS

7) How to find Databases collation?

     SELECT name, collation_name FROM sys.databases

  Ans: name collation_name
       master SQL_Latin1_General_CP1_CI_AS
       tempdb SQL_Latin1_General_CP1_CI_AS
       model SQL_Latin1_General_CP1_CI_AS
       msdb SQL_Latin1_General_CP1_CI_AS

8) What are the best practices to place data files, log files and tempdb on Storage?

     a) Data Files and Log files should not be in same Drive:

         If you place data and log files on the same drive, and drive run out of space, there is a definite chance of data corruption or your database might go in          suspect mode and production outage will surely take place

     b) Data Files and Log files should not be on C:\ Drive :

          C:\ drive is secondary memory for Operating system, incase system is running out of memory, Operating system will use Part of C:\ drive space as a virtual           memory without impacting the current running processes - with that said, you will deal with great performance lose from SQL server as well as applications               attached to SQL Server if C:\ drive didn't have enough space available.

    c) TempDB should have it's own dedicated drive i.e T:\

        Tempdb is the most busy database when it comes to SQL Server, SQL Server Engine will use tempdb for every transaction possible, and this behavior is by design         in order to achieve fast processing

9) Is it best practice to have Auto Shrink enable on Database in Sql Server?

     Straight Answer is : NO

     Reason:  It increases fragmentaion level of Indexs due to this preformance go down

10) What is TDE (Transparent Data Encryption) and why do we use it?

     Transparent Data Encryption is designed to protect data by encrypting the physical files of the database,rather than the data itself.Its main purpose is to prevent unauthorized access to the data by restoring the files to another server

11) What is Locking in sql server ?

    Locking is important concept in SQL Server, As a DBA it is important to understand the depth of Locking in SQL Server to perform daily operations

    As well all know multiple users need to access databases concurrently,so locks comes into picture to prevent data being corrupted or invalidated when multiple     users try to do operations such as read,write, and update on database

12) Where locks are put in Database ? it means on which resource it locks or not?

     a) ROWID : RowId used to lock a single row with in a table.

     b) Table : Complete table including all data indexes

     c) Key : Row lock with an Index .It means primary key etc..

     d) Database : Entire database can be lock for some type of users which have read permission on database

13) What are Different Models of locks ?

    a) Shared(s)

    b) Exclusive(X)

    c) Update(U)

    d) Intent

    e) Schema

    f)Bulk update (BU)

14)  What is File Group ?

      In SQL Server filegroup is a logical structure which contains objects like data file, tables and indexes.

15)  Why Filegroups ?

       Filegroups make administration easier for a DBA. Using multiple filegroups we can gain following benefits;

         We can separate user data with internal system data using multiple filegroups.
         We can overcome with maintenance overhead by putting archive (or even read-only) data onto their own filegroups and dedicated set of disks.
         We can gain performance improvement by putting larger tables/indexes on their own filegroup and/or dedicated set of disks.
         We can bring some parts of the database online quickly ( piecemeal restore ).

16) How many Types Of File Groups ?

     There are Two types of file groups

        a) Primary File Group

        b) Secondary File Group / User defined File Group

17) What is Primary File Group ?

     The filegroup which contains the primary data file and any other files that are not associated to another filegroup is termed as Primary filegroup.

18) What is Secondary File Group / User defined File Group ?

       The Filegroups which we create from FILEGROUP keyword using CREATE DATABASE or ALTER DATABASE is termed as user-defined filegroups. This file is created by user       or later modifies database by a user.

19) How to view file groups ?

      Use Database
      select * from sys.filegroups

20) How to Change default filegroup ?


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. Hiya,

    Great info! I recently came across your blog and have been reading along.
    Does Log shipping configuration truncate the transaction log?
    If we perform a trn backup manuvally in Primary database (it is automatically selected the truncate transactional log as shown in figure),
    it breaks the log shipping and becomes out of sync and and db's need to be replayed. Is there something that can be done to continue to use log shipping without having the trn's grow out of control

    I thought I would leave my first comment. I don’t know what to say except that I have

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Irene Hynes