DATABASE INTERVIEW QUESTIONS.

1)    What is Database? What are the files created while creating a Database?

                Database is nothing but an organized form of data for easy access, storing, retrieval  and managing of data. This is also known as structured form of data which can be accessed in many ways.  While creating a Database in SQL Server there are two data files are created called master data file (.mdf), log data file (.ldf) and we can add one or more optional N-Dimensional data files (.ndf).

2)Explain about Data files (.mdf, .ndf, .ldf) (File structure)?

a)  Primary Data file (.mdf):- Starting point of the database and it points to other files.every data base has one primary file and extension is .mdf

b)  Secondary Data file (.ndf):-make up all the data files other than primary data file extension is .ndf

c)    Log Data file (.ldf):- It holds the all log information & used for recovery of database. log file is never part of any file group. 

3)What is Page and Page size?
The Fundamental data storage unt in sql server is nothing but page.page size is 8kb.128 pages for 1 Mb

4)What is physical architecture of page.?
Each page starts with 96 bytes header .it store system information such as type of page .object id owned by particular page.

5)What is Extent and Extent size and Extent types?

      extents are basic unit which space is managed.extent size is 64 kb .64 extents for 1mb.

 There are 2 types of extents 1) mixed extents 2) uniform extents

 1)mixed extents : mixed extents are shred by different objects.

2) uniform extents: uniform extents are owned by single object .

6)    What is Fill factor? How to assign Fill factor? (i)

A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.

Assign Fill Factor:- Right Click on Server > Properties > Database Settings > Default Index Fill Factor > Provide the value

7)   Tell me about System Database and User Databases in 2000 and 2005?

SQL Server 2000:- 1) Master 2) Model 3) MSDB 4) TempDB

SQL Server 2005:- 1) Master 2) Model 3) MSDB 4) TempDB 5) Resource DB

8)   Explain about System Databases in SQL Server 2005? (i)

MASTER DATABSE :

 It contains set of tables that serves as a central repository for entire instance.masterdatbase maintains login accounts,file allocation,system configuration,disk space,resource configuration,endpoints,linked servers and other database information.

MODELDATABASE: it's acts a template for new user data base.when user create new data base it will inherits the properties for master database.

MSDB DATABASE: the MSDB database used by sql server agent for scheduling jobs,alerts.msdb maintains backup and restore history .

TEMPDATABASE: it stores user objects,versions,internal objects.we can't back up the temp db because when sql server start r stop the temp can be deleted  permanently or refresh the temp db.

RESOURCE DTABASE: The resource database is a hidden database .system tables are physically store in resource database and logically stored in master database .it stores objects and stored procedure,extended stored procedures.

9)    How to move Model, MSDB, TempDB? (i)

a.     Check the Path of TempDB through sp_HelpDB TempDB

b.     ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');

c.     Stop the Services

d.     Move the files to desired location

e.     Restart the Services

f.      Check the Path of TempDB through sp_HelpDB TempDB

10)   How to move Master Database? (i)

a.     Check the Path of Master DB through sp_HelpDB Master

b.     Stop the Services

c.     Change the path at Startup parameters

SQL Server Configuration manager > Services > Right click on Service > Properties > Advanced > Add new_path at Start up parameters

d.   Move the files to desired location

e.   Restart the Services

f.    Check the Path of Master DB through sp_HelpDB Master 

11)   What are the Database States and explain them?

The main database states are online, offline, restoring, recovering, resource pending, suspect and emergency.

 12)   What is Database Snapshot? Give the Syntax? (i)

           A Database snapshot is a read-only static view of the database. Snapshots must be located on the                  same server. Snapshots doesn't contain UN-committed transactions at the time of snapshot was taken.             It is very useful for report queries.

13)    What is Transaction? What is Transaction (ACID) Properties? (i)

Transaction is a set of logical unit of work and it contains one or more database operations. A valid transaction should be met (ACID) Atomicity, Consistency, Isolation, Durability properties.

ATOMICITY:  all the operations in the transaction must be completed successfully and be committed. if any  one operation fail then all the transactions must be rolled back in their previous state.

CONSISTENCY: the transaction must be consistent state this means that the transaction must correctly    take the charge of the state of the system for a particular operation .
ISOLATION:it means that one transaction in the transaction can't see the result of another operation with  in the transaction.

Durability: it means that anything is committed to the managed resources must survive or failure.and it   can't be done damages to the resources in case of operation fails.

14) What are Transaction Isolation levels? (i)

An Isolation level affect the way locking behavior for read operations.

a)  Read uncommitted:-

b)  Read committed:-

c)   Repeatable read

d)  Serializable:-

e)  Snapshot isolation:-

f)   Read committed snapshot (Database option):-

15) What is Transaction?

Transaction is a set of logical unit of Work and it contains one or more database operations.

16) What is Checkpoint and when it occurs?

Check point is nothing but Committed Transactions Are roll forward to the data file.

Checkpoint occurs in the following cases:-

a)    Manual CHECKPOINT command

b)    When SQL server shutdown

c)     When ALTER DATABASE command is used to add or remove database file.

d)    When recovery model change from Full/Buck-logged to simple.

e)    Before a database backup is performed.

f)    Checkpoints run periodically on Full/Bulk-logged recovery model databases as specified by the recovery interval server setting.

g)    In simple recovery model log is truncated after checkpoints occurs

Assigning Checkpoint

CHECKPOINT 60;   (60 is duration in seconds)

17) What is purpose of LSN numbers?

18)Explain about RAID and RAID levels?

RAID stands for Redundant Array of Independent Disks. RAID is a disk system that contains arrays to provide greater performance, reliability, storage capacity, and lower cost. Typically 0, 1, and 5 RAID levels are used in SQL Server.

19) How to set a Database in a single user mode and Multi-user mode?

a)    Single user mode:- Only admin can access the database (users cannot access)

                      i.        Alter database <database name> set single_user

                     ii.        Sp_dbooptions, ‘database name’, ‘single user’, ‘true’

b)    Multi-User mode:- All users can access the database

                      i.        Alter database <database name> set multi_user

                     ii.        Sp_dbooptions, ‘database name’, ‘multi user’, ‘true’

20)How to set SQL Server in Single user mode and Minimal Mode?

a)    Single user mode:- Only admin can access the server (users cannot access)

Start ->run -> command prompt > SQLSERVER.EXE –m

b)    Minimal mode:- All users can access the Server

Start ->run -> command prompt > SQLSERVER.EXE –f

21)Orphaned Users? How to find Orphaned Users and Resolve them?

Orphaned user is nothing but If User is there but associate login might not be available.

    To detect Orphaned users:- sp_change_users_login 'Report'

To Resolve Orphaned Users:- sp_change_users_login 'Update_One', 'db_user', 'login_name'

22)What is Row size of a Database?

Row size of a database is 8060 bytes.

23)What is Heap table?

A table without a clustered index is called as heap table.

24)How many ways you can move the data or databases between servers and databases?

   SSIS, DTS, Attach & Detach, Bulk copy program, Bulk Insert & Import and Export wizard

23) Syntax for find out the Database ID?

24) Select db_id (‘db_name’)

25) Syntax for find out Logins? Current User?

To find out logins: - select * from sys.syslogins

To find out current user:- Select current_user

26) Is it possible to take Backups and Restores of System Databases?

Except TempDB all the System database can take Backup’s