SQL Server Interview Question

September 13th, 2010 by aabhaanjan

What is DBMS ?

A Database Management System (DBMS) is a set of programs that controls the creation, maintenance, and the use of a database. A DBMS provide a systematic way to organize and retrieving the data from database.

What is RDBMS ?

A RDBMS is a database management system that is based on the relational model .RDBMS store the data into multiple table and the table are related to each other . RDBMS provides the concepts of relationship integrity . RDBMS describe how the table are related to each other .

What is collation in SQL Server ?

Collation are a set of rules that determine how the data is stored and compared .

Each SQL Server collation specify three properties :-

What is authentication mode ?

SQL Srever have two authentication mode . These are :-

  1. Windows Authentication Mode :-

When a user connects through a Windows NT 4.0 or Windows 2000 user account, SQL Server revalidates the account name and password by calling back to Windows NT 4.0 or Windows 2000 for the information. Windows Authentication provide following benefits such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests. Windows Authentication utilizes Kerberos security protocol

2.Mixed Mode (Windows Authentication and Sql Server Authentication Mode ) :- When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

What is Normalization ?

Normalization is a way to organize the data into table in good manner. Database normalization is the process of removing redundant data from your tables to improve the database efficiency. Normalization provides the data integrity and scalability.

First Normal form (1NF): A relation is said to be in 1NF if it has only single valued attributes, neither repeating nor arrays are permitted.

Second Normal Form (2NF): A relation is said to be in 2NF if it is in 1NF and every non key attribute is fully functional dependent on the primary key.

Third Normal Form (3NF): We say that a relation is in 3NF if it is in 2NF and has no transitive dependencies.

Boyce-Codd Normal Form (BCNF): A relation is said to be in BCNF if and only if every determinant in the relation is a candidate key.

Fourth Normal Form (4NF): A relation is said to be in 4NF if it is in BCNF and contains no multi valued attributes.

Fifth Normal Form (5NF): A relation is said to be in 5NF if and only if every join dependency in relation is implied by the candidate keys of relation.

Domain-Key Normal Form (DKNF):  We say that a relation is in DKNF if it is free of all modification anomalies. Insertion, Deletion, and update anomalies come under modification anomalies.

SQL Server 2005 default database

By default there are four database created in sql server .

  1. Master Database :- The Master database hold all the information of database located into sql server instance or created .the database includes information such as system logins , system configuration , linked server and general information regarding the other system and user database .
  2. Model Database :- model is essentially a template database used in creation of new user database . All the information related to stored procedure , views , users etc stored into template database .
  3. Tempdb Database :- Tempdb holds temporary objects such as global and local temporary tables and stored procedure . This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database. When you are executing any queries or stored procedure, every times the temporary table or views is created and stored into tempdb table.
  4. MSDB Database :- The MSDB Database stores information regarding of backup , Sql agent information , DTS package ,Sql Server jobs and some replication information such as for log shipping.

What is DBCC commands ?

DBCC stands for Database Consistency checker . We use these commands to check the consistence of the database. i.e maintenance , status checks , validation task .

Database consistency checker statements are grouped into the following categories :-

  1. Maintenance: – Maintenance task on database , file group ,index .
  2. Validation :– Validation operation on database , file group , index ,catalog etc.
  3. Informational : Task that gather and display various types of information .
  4. Miscellaneous :– Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

How do you read transaction logs?

To Read transaction log , we use DBCC command and pass database name as a parameter

DBCC LOG (Your_Database_Name, 2)

What Command do you use to rename db ?

Sp_renamedb “oldname” ,”newname “

Tags: , , , , , , , , , , , , , , ,