SQL Server Interview Questions and Answers – Part 2

Interview Questions and Answers are now updated with the latest questions and answers. New Location:  SQL Server Interview Questions and Answers.

Click here to get free chapters (PDF) in the mailbox

Interview Questions and Answers

SQL Server Interview Questions and Answers - Part 2 interview

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 (https://blog.sqlauthority.com)

, , , , , , , , , , , ,
Previous Post
SQL Server Interview Questions and Answers – Part 1
Next Post
SQL Server Interview Questions and Answers – Part 3

Related Posts

53 Comments. Leave new

  • Excellent!!!!!

  • Gr8 work man…

    appreciated…

    Thank u so much

  • its nice
    but answer the questions in broadway
    means please answer them in efficient& globalway
    thank you

  • 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

  • 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

  • hey pinal,

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

  • Hey Pinal,

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

  • Hey Pinal

    Realy valueable material. thanks

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

    Thanks in Advance
    Mohan

  • it is very simple language to learn

  • HI

    I WANT TO CALLICULATE THE DATE OF BIRTH LIKE 19 YEARS 4 MONTHS 2 DAYS
    CAN U SUGGEST ME A QUERY
    HOU CAN I CONSIDER THE LEAP YEARS

  • Bhajharam prasad dhyani
    September 8, 2008 11:49 am

    very nice!

  • 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.

    Please continue with your questions and answers at new location.

Menu