SQL Server Interview Questions and Answers – Part 2

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

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.

What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC – To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Complete Series of SQL Server Interview Questions and Answers
SQL Server Interview Questions and Answers – Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download

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

55 thoughts on “SQL Server Interview Questions and Answers – Part 2

  1. ohhhhhhhhhhh!!!!!!!!!!!
    so easy and simple…………
    thanks a lot…..
    can u give questions for vb etc……..


  2. Dave,

    Its really nice.

    Can we have some advanced questions and answers added into this series. like fetching the ntext column etc


  3. DBCC may stand for the following …

    DBCC Database Concurrency Control
    DBCC Database Consistency Check
    DBCC Database Console Command


  4. Its very good and easy to understand. It would be great if you add examples to the topics..like for normalization ..some table and breaking down that into multiple tables by navigating through normalization rules… For Joins : one example


  5. It’s so helpfull!! Thanks . But I think if you would translate this blog to spanish a lot of people in Latin America will be so glad. I hope you listen my request.

    sorry for bad English


  6. Who!!!!! Its really superb!!!!!! Its very intresting things and easy to understand.

    And it will be help full for Job seekers….PLease add more question so that we can no more information..


  7. its good but sm practical example should be thr.Everything is in theory.not much helpful in implementation.smthing like
    when to use this concept should be there.


  8. hi Pinal,
    This site is very helpful…am a begginner to SQL server…thank you for posting the interview questions…could you please mail me all the interview questions…and any useful books for the begginers…

    Thank You


  9. i see many nice and greate quastions but i cant see any answers while they said nice naswer how can i see the answer’s
    so every one can share the nice and greate info.

    best regards


  10. hey pinal,

    all the questions were answers with simplicity better than even wikipedia…. thanks a lot man….


  11. Hey Pinal,

    This is really helpful.. And the way you arranged the questions and answers are simply superb…


  12. hi all
    How to escape special character when using query like update and insert queries???

    Thanks in Advance


  13. HI



  14. Pingback: SQL SERVER - Database Interview Questions and Answers Complete List Journey to SQL Authority with Pinal Dave

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Comments are closed.