SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

1) General Questions on SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)

What are the Properties of the Relational Tables?

Relational tables have the following six properties:

  • Values are atomic.
  • Column values are of the same kind.
  • Each row is unique.
  • The sequence of columns is insignificant.
  • The sequence of rows is insignificant.
  • Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How is ACID property related to Database?

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they are finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. (Read more here)

What are the Different Normalization Forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key. (Read more here)

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

27 thoughts on “SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

  2. Hi Pinal,

    Discussion of technical question is always helpful, but Discussion about some behavioral question also important in almost all cases.

    Question like,

    You were working as Database Developer and Why you choose DBA role?

    Whats your plan in next comming 5 years for career?

    I think discussion of that kind of question will be very helpful.

    Thanks, Virul

  3. Hi Pinal,
    Your links are a great help. thanks a lot for making things simple. Can you please provide some SQL Queries with their solutions asked in interviews. Thanks for being such a help

  4. fantastic description and simple that easy to understand.then i need the
    clear definition of the all normal form with example.please send the file to
    my mail .pls sir .
    thanks and records
    muthu

  5. Basically normalization is a process in realational databse design to organise the data and avoid data redundancy. It usually involves dividing database into two or more tables and then define a relation between them. One of the table is the main table and rest are the transaction tables. Updations ,Deletions and additions are made in the main table and then are propagated to transaction tables which are related to it

  6. dear sir,

    I want to learn ms sql 2008 but I have no knowledge about database and programming as well.

    Kindly guide me how to learn it.

  7. Good job interview questions and answers on SQL server. Today, though job seeker wants to seek job in programming or in database field, these basic questions are asked.

  8. Dear Pinal,
    Thanq u so much for this blog….its realy help full for not only me even all those student and developer which have not deatail knowledge about SQL..
    thaq again….

  9. It’s an interview question – Do you heard about execution plan of stored procedure?
    What is the diff between truncate and delete?
    How to copy the data from one table to another?
    Do U heard about index? Diff types of index? What is cluster index?
    Why do we have only one cluster index per table?
    and so on…

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  11. Hi All,
    I am new in technical field want to learn SQL functional with advance ms-excel and access, anyone could help me to choose better place and material.

    With Regards
    Kamal Tyagi
    [phone no removed]

  12. Pls help me find Differences between oracle and SQL server (transaction monitoring and concurrency control, backup and recovery, and security)among these two . I would be most grateful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s