SQL Server Interview Questions and Answers – Part 2 « Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 2

April 16, 2007 by pinaldave

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.

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

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Download, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Trigger, Software Development, T SQL, Technology | Tagged SQL Collation | 54 Comments

54 Responses

on June 27, 2007 at 11:10 pm Sudha

It si really good
on July 9, 2007 at 11:49 pm praveen

its superb , but add some more interview tips and faq
on July 18, 2007 at 12:08 pm Mohan

It’s really superb
on July 20, 2007 at 2:34 am Mallika

Answers provided are really clear and concise.
on August 1, 2007 at 5:36 am kumar

Hi Dave,
its very easy to understand the topics.

Thanks
on August 8, 2007 at 6:57 am veena

ohhhhhhhhhhh!!!!!!!!!!!
wow!!!!!!!
fantastic!!!!!!!lovely………..
so easy and simple…………
thanks a lot…..
can u give questions for vb etc……..
on August 23, 2007 at 11:19 pm Prasad

Hi Dave,
its simple to understand.

many thanks ……………..
on September 6, 2007 at 9:53 pm RAM PRASAD

It’s Awesome …

It’s really helpful…

It has useful stuff…

Thanks…
on September 7, 2007 at 4:10 am Manoj

Hi,

Itz really usefull and very simple defined.
on September 18, 2007 at 10:23 pm Vimal Naik

Dave,

Its really nice.

Can we have some advanced questions and answers added into this series. like fetching the ntext column etc
on October 13, 2007 at 1:36 am Raj

It’s good….
on October 22, 2007 at 12:38 am deepak

excelent but it sholud contain some
realtime related question and answer
on October 23, 2007 at 10:44 pm Pratibha

very nice…
easy to read and often asked
can u give some complicated sql queries example
Thanks
on October 30, 2007 at 11:06 pm HARI

Really this is a good stuff for beginners and medium level Developers to brush up the SQL knowledge
on October 30, 2007 at 11:08 pm HARI

Need help how to create and maintain dynamic databases
for web applications
on November 5, 2007 at 9:06 pm Rahber

Realy very very goog and help full for interviews …
on November 17, 2007 at 3:19 am A

DBCC may stand for the following …

DBCC Database Concurrency Control
DBCC Database Consistency Check
DBCC Database Console Command
on January 16, 2008 at 1:06 pm priya

its very simple to understand…..thank u
on January 24, 2008 at 9:18 am Anwar

its nice and superb.ainclude some logical question for sql dba.
on January 25, 2008 at 12:29 pm naresh

its good and easy to understand…provide some other questions about sql dba..
on January 25, 2008 at 12:30 pm naresh

its good…
on February 3, 2008 at 1:42 am Your Reader

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
on February 5, 2008 at 5:01 pm Santanu

Hi Dev,

It is very fantastick Q&A. But I want to know How connect to the SqlServer to C++. PLZ send me code.

Thanks
on February 5, 2008 at 5:08 pm Santanu

It is very fantastick Q&A. But I want to know How connect to the SqlServer to C++. PLZ send me code.

Thanks
on February 16, 2008 at 6:21 pm Arulraj

Hi

It is very easy to understad the Q & A
on February 18, 2008 at 9:55 am Vinay.R

halo sir,

So helpful material on the occasion of interviews.Thanks
on March 5, 2008 at 8:33 pm Alexis

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
on March 10, 2008 at 5:01 pm Faisal Qureshi

Nice Link
on March 11, 2008 at 3:17 pm Tarun

Thanks a lot its Really gr8
on March 19, 2008 at 7:41 am saravana

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..
on March 28, 2008 at 6:30 am aruna

hi,

its really superb.very clear.

thanks
aruna
on March 28, 2008 at 9:34 am sanika

Its great stuff…simple to understand…keep it up!!!
on April 8, 2008 at 5:07 pm Manoj

It’s really gud sir….
on April 11, 2008 at 1:04 am Imran

@ Dave

I am not sure if Cursor is a database objects, can we call cursor a datatype.
on April 29, 2008 at 3:31 am paras

Good snapshot!
on April 30, 2008 at 5:35 pm Joe

Very Nice !!!
on May 3, 2008 at 4:59 pm ravi

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.
on May 7, 2008 at 9:37 pm Kanchan Gupta

good helping approach to candidates
on May 8, 2008 at 11:18 pm Prashanthi G

Its Good,it is very advantage to us
on May 10, 2008 at 6:34 pm Kavitha

Hi

Really very usefull for interviews .This is very nice .
on May 16, 2008 at 10:44 am Michael

Excellent!!!!!
on May 28, 2008 at 5:44 am prahadhir

Gr8 work man…

appreciated…

Thank u so much
on June 14, 2008 at 3:40 pm shafi

its nice
but answer the questions in broadway
means please answer them in efficient& globalway
thank you
on June 21, 2008 at 1:33 am shravs

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
on June 24, 2008 at 12:44 pm MIS

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
on July 16, 2008 at 8:03 pm ash

hey pinal,

all the questions were answers with simplicity better than even wikipedia…. thanks a lot man….
on July 18, 2008 at 7:54 pm Rajiv

Hey Pinal,

This is really helpful.. And the way you arranged the questions and answers are simply superb…
on July 30, 2008 at 10:31 am Abid

Hey Pinal

Realy valueable material. thanks
on August 7, 2008 at 2:00 pm Mohan

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

Thanks in Advance
Mohan
on August 21, 2008 at 7:56 pm romit

it is very simple language to learn
on August 31, 2008 at 1:28 pm SATYA

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
on September 8, 2008 at 11:49 am Bhajharam prasad dhyani

very nice!
on September 20, 2008 at 11:36 am pinaldave

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.
on February 24, 2009 at 6:22 pm SQL SERVER - Database Interview Questions and Answers Complete List Journey to SQL Authority with Pinal Dave

[...] 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 – [...]