Interview Questions

Interview Questions

Introduction: 
                 In this module we are covering more FAQ's by gathering from different different Sites and top most MNC companies we will be updated periodically. So you have to go through this module every day for improving your real time knowledge in DBA.

1: As a database is recovering, after which phase will the database be available?

     (SQL Server 2000): After the Undo phase.

     (SQL Server 2005): In all editions but Enterprise, after the Undo phase (if running FULL 

             recovery model). With Enterprise edition, after the Redo phase. Fast recovery is possible 

             because transactions that were uncommitted when a crash occurred reacquire whatever 

             locks they held before the crash. While these transactions are being rolled back, their locks 

             protect them from interference by users.

2:  What is an Index?
      Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.

3:  How to change default SQL Server backup folder path?
            When you install SQL Server the path for the installation is generally something such as 

the following:
           C:\Program Files\Microsoft SQL Server\MSSQL10_50.Pavan\MSSQL

      We can change through RegistryEditor (REGEDIT)

•  Open Run
•   REGEDIT
•   HKEYLOCALMACHINE
•   SOFTWARE
•   Microsoft
•   Microsoft SQL Server
•   MSSQL10_50.Pavan
•   MSSQLServer
•   BackupDirctory
•   Right Click BackupDirectory and Modify the Path

4: Which database will effect if we install service pack in SQL Server?

   The Resource database is a read-only database that contains all the system objects that are included 

with SQL Server. SQL Server system objects, such as sys.objects are physically persisted in the 

Resource database, but they logically appear in the sys schema of every database. The Resource 

database does not contain user data or user metadata.

   The Resource database makes upgrading to a new version of SQL Server an easier and faster 

procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. 

  Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

5: What is the use of SQL Server Browser ?

     SQL Server Browser contributes to the following actions:

         1.  Browsing a list of available servers.

         2.  Connecting to correct server instance.

         3.  Connecting to Dedicated Administrator Connection (DAC).

6: When is the use of UPDATE_STATISTICS command?

     This command is basically used when a large processing of data has occurred. If a large 

amount of deletions or any modification or Bulk Copy into the tables has occurred, it has to 

update the indexes to take these changes into account.
     UPDATE_STATISTICS updates the indexes on these tables accordingly.

7: What is Dedicated Administrator Connection (DAC)?

      SQL Server provides a special diagnostic connection for administrators when standard 

connections to the server are not possible. This diagnostic connection allows an 

administrator to access SQL Server to execute diagnostic queries and troubleshoot 

problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security 

features of SQL Server. The DAC only allows changing the user context to another admin 

8: Is it possible to create index on more than one column?

     Yes, Its possible. We can include maximum 16 columns as key columns while creating 

index. Sum of bytes should not be more than 900bytes.

9: In SQL Server, on a primary key column, is it possible to create Non-Clustered index?

     Yes. Its possible to create the Non-Clustered Index on a primary key column. We can use 

below syntax to create Non-Clustered Index on primary key column while creating table itself.

--Create table
CREATE TABLE
(ID INT NOT NULL PRIMARY KEY NONCLUSTERED, 
 Name varchar(50) NOT NULL)
GO

10: What is the advantage of CLR integration?

       SQL Server has lack of certain features like Regular expressions. Its also not efficient 

in string processing etc also. Microsoft .NET framework is more efficient in processing 

strings,Regular Expressions etc. By integrating CLR integration, SQL Server can use the 

features of Microsoft .NET framework.

1: What are the requirements for setting up database mirroring? 

     Requirements for database mirroring:

          1. Principal and mirror are running SQL Server 2005 or newer (Standard or Enterprise)

          2. Principal and mirror have enough space for the database.

          3. For automatic failover, witness must be running.

          4. Principal database must be in FULL recovery model.

          5. Mirror database must be prepared prior to mirroring setup - one full backup and one 

              transaction log backup need to be taken on principal and restored WITH NORECOVERY

2:  What is the difference between Push and Pull Subscription in Replication?

     Push - A push subscription pushes data from publisher to the subscriber. Changes can be 

                 pushed to subscribers on every transactions or on a scheduled basis.

     Pull - A pull subscription requests changes from the Publisher. This allows the subscriber to 

               pull data as needed. This is useful for disconnected machines.

3:  What is SQL Server replication?

      Replication is subset of SQL Server that can move data and database objects from one 

      database to another database in an automated way.

      This allows users to work with the same data at different locations and changes that are 

       made are transferred to keep the databases synchronized.

4:What are the magic tables in SQL Server?

      There are two tables Inserted and deleted in the SQL Server, which are popularly known 

      as the Magic tables. These are not the physical tables but the SQL Server virtual tables 

      usually used with the triggers to retrieve the inserted, deleted or updated rows. These 

      tables contain the information about inserted rows, deleted rows and the updated rows. This 

      information can be summarized as follows:

                 1.  Action Inserted Deleted

                 2.  Insert Table contains all the inserted rows Table contains no row

                 3.  Delete Table contains no rows Table contains all the 

                 4.  Update Table contains rows after update Table contains all the rows 

5: What are the difference between Stored Procedure and Trigger in SQL Server?

       1.   We can execute a stored procedure whenever we want with the help of the exec 

           command, but a trigger can only be executed whenever an event (insert, delete and update) 

           is fired on the table on which the trigger is defined.

       2.  We can call a stored procedure from inside another stored procedure but we can't 

            directly call another trigger within a trigger. We can only achieve nesting of triggers in 

            which action (insert, delete and update) defined within a trigger can initiate the execution of 

           another trigger defined on the same table or different table.

        3.  Stored procedures can return values but a trigger cannot return a value.

        4.  We can use the Print commands inside the stored procedure to debug purpose but we 

           can't use the print command inside a trigger.

        5.  We can use the transaction statements like begin transaction, commit transaction and 

            rollback inside a stored procedure but we can't use the transaction statements inside a  trigger.

        6.  We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but 

            we can't call a trigger from these files.

6: How to recover a Database in suspect mode?

          EXEC sp_resetstatus ''

          ALTER DATABASE SET EMERGENCY

          ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

          DBCC CheckDB ('', REPAIR_ALLOW_DATA_LOSS)

          ALTER DATABASE SET MULTI_USER

7: How can you achive Recurrsion in SQL Server 2008?

        SQL Server 2008 has got a real nice feature called CTE (Comman Table Expressions)

      through which you can achive recurrsion

8: How do you find the Index Fragmentation level in SQL Server?

    The Index fragmentation level in SQL Server can be determined using 2 ways. 

              1.  DBCC SHOWCONTIG

              2.  sys.dm_db_index_physical_stats .

     Generally if the Fragmentation level of a Index in less than 30% then it is advised to
  Re-Organize an Index and If the Fragmentation level of an Index is More than 30% then it is 

  recommended that you ReBuild the Index.

     

9:  What is the difference between Delete command and Truncate command?

         1.  Delete command maintained the logs files of each deleted row but Truncate command do 

     not maintain the logs files for each deleted row but maintains the record for deallocation of 

     the datapages in the log files.The deallocation of the data files means that the data rows still 

     exists in the data pages but the extents have marked as empty for reuse.

         2.   Truncate command is much faster than delete command.

         3.   You can use Where clause in case of Delete command to delete a particular row but in 

     case of Truncate command you have to delete the data from all the rows. since Where clause 

     will not work with Truncate command.

         4.   Triggers are fired in case of Delete command and they are not fired on Truncate 

         5.   Truncate command resets the Identity property to its initial value whereas Delete 

     command do not resets the Identity property of the column.

         6.   Delete is a DML command and Truncate is a DDL command.

10.  What is the difference between Unique Key and Primary key?

          1.  There can be only one Primary key possible in a table but there are many unique keys 

          2.Primary key does not allow NULL values but a Unique key allows one NULL value.

          3.When a Primary key is created, a clustered index is made by default but if an Unique key 

        is created, a non-clustered index is created by default.

11. To list all the databases and their details in a instance

                sp_helpdb

12: What are isolation levels and use of isolation levels?

            While executing queries, SQL Server often required to acquire locks on resources to 

prevent dirty reads,phantom reads etc. These locking behavior will be depends on isolation 

levels. Based on isolation level of transaction, SQL Server will acquire locks on resources.

13: What is there difference between clustered index and non-clustered index?

          Important difference between these is, Clustered index consists of table data at leaf 

nodes, where as Non-Clustered index consists of pointer to the table row.

Another difference is regarding allowed indexes count, there will be only 1 CI per table. We 

can have up to 255 Non-CI until SQL Server 2005, From SQL Server 2008, limit increased 

14: How can you get last backup date of a database through t-sql query?

           In MSDB database, backup set table consists of all the details of backup. By querying this 

1.select backup_start_date,backup_finish_date from msdb..backupset

15: Do you have any idea about database normalization and its various normal forms?

 Normalization :- Normalization can be defined as the process of organization the data to 

reduce the redundant table data to the minimum. This process is carried out by dividing the 

database into two or more than two tables and defining relationship between them so that 

deletion, updation and insertion can be made to just one table and it can be propagated to 

other tables through defined relationships.

Normalization can be done for the following reason:-

To simplify the database structure so that it is easy to maintain.

To retrieve the data quickly from the database.

To reduce the need of restructuring the database when enhancement of the application 

Normal Forms: - The normal form can be refers to the highest normal form condition that 

it meets and hence indicates the degree to which it has been modified. The normal forms 

Note: - Normalization into 5NF is considered very rarely in practice.

First Normal Form (INF):- A table is said to be in a First Normal Form (1NF) if it satisfy the 

1) If the columns of the table only contain atomic values (Single, indivisible).

2) Primary key is defined for the table

3) All the columns of the table are defined on the primary key.

Second Normal Form (2NF):- A table is said to be in its Second Normal Form if it satisfied 

1) It satisfies the condition for the First Normal Form (1NF),

2) It do not includes and partial dependencies where a column is dependent only a part of a 

Third Normal Form (3NF):- A table is said to be in the Third Normal form (3NF) if it satisfy 

2) It should not contain any transitive dependency which means that any non key column of 

the table should not be dependent on another non key column.

Denormalization:- Denormalization can be defined as the process of moving from higher 

normal form to a lower normal forms in order to speed up the database access.

16: Suppose you have Employee_mst table now how to find store procedure uses this 

         SELECT sys.objects.name, sys.objects.type, sys.objects.type_desc,

sys.objects.schema_id, sys.syscomments.text

INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id

where sys.syscomments.text like '%Employee_mst%' And type ='P'

17: In SQL Server, how do you find un-used stored procedures and un-used views?

             There is no in-built mechanism to identify the un-used stored procedures. When an stored 

procedure is called, an entry will be created in "sys.dmexecquery_stats". Based on this 

information, we can find un-used stored procedures by using below query. However, this 

information will be deleted once the server re-starts.

LEFT OUTER JOIN  ( SELECT x.objectid FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS x

WHERE OBJECTPROPERTYEX(x.objectid,''IsProcedure'') = 1 T on P.object_id = T.objectid

For views, it will store the execution plan in DMVs. Below query can be used to get un-used 

views. When the server is re-started, this information will be cleared. Also, when the server 

has more run out of procedure cache, it will dump the old execution plans. so, this query 

SELECT v.name [ViewName] FROM sys.views v

WHERE v.is_ms_shipped = 0 EXCEPT SELECT o.Name [ViewName]

FROM master.sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t

INNER JOIN sys.objects o ON t.objectid = o.object_id [here][1]