interview questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

interview questions

Author

Topic

victory
Starting Member

1 Post

Posted - 2007-02-14 : 10:48:12

hi,
i have been attending interviews and jotting down a couple of questions that were asked to me. could someone please answer to these..it would be of gr8 help..
Q 1> give example of situation when & why u would use clustered and unclustered index. not the definition a practical example.
Q 2> a website has a lot of users using it and the pages are loading very slowly what would you check..
i said would look for deadlock , the next question how would you look for dead lock?
i answered would check on the stored procedure on that page, question what if there are multiple stored procs then what would you do?
Q 3> what if u have a dts package and execute SQL task and there is a stored proc that is doing inserts on a table and that table has triggers on
it for insert, delete etc.so would the triggers also be executed with the dts package run?

thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-14 : 11:23:01

Do you think that maybe you would be better deserving of a job (and also you'd be better at it) if you researched these yourself and learned about them?

What SQL Server books have you read? What experience do you have with SQL?

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-14 : 11:24:48

Let me try:

1. hmmm...let's make a deal, you give me the job, I will show you practical example of how to implement proper indexes

2. interesting, may be webserver is loaded with other apps as well. I will try to isolate my app and then check whether this is really problem with Back-end or Front-end

3. I heard somewhere that trigger stuff is really scary So before running any DTS package I will disable all triggers. Problem resolved!

Wasn't that simple !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-14 : 12:21:03

If you can't answer those questions, you are probably unqualified for the job.

CODO ERGO SUM

gongxia649
So Suave

344 Posts

Posted - 2007-02-14 : 22:59:43

i will try to answer you. But i dont know if they are right. Best luck!

Q 1> give example of situation when & why u would use clustered and unclustered index. not the definition a practical example.
non-clustered is used on columns involved in search conditions of a query (where clause).
clustered index is used on columns that contain a large number of distinct values.

Q 2> a website has a lot of users using it and the pages are loading very slowly what would you check..
Check if the table has indexes or use dbcc to recreate the index.

Q 3> what if u have a dts package and execute SQL task and there is a stored proc that is doing inserts on a table and that table has triggers on
it for insert, delete etc.so would the triggers also be executed with the dts package run?
Yes

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-14 : 23:16:29

I am going to play the part of the interviewer since that seems like the fun thing to do in this thread.

quote:
Originally posted by gongxia649
non-clustered is used on columns involved in search conditions of a query (where clause).
clustered index is used on columns that contain a large number of distinct values.

why is this? Does this mean that all unique indexes should be clustered indexes?

quote:

Check if the table has indexes or use dbcc to recreate the index.

how do you know what tables are in use? how do you know if an index will even help? how can you tell if rebuilding the index will do anything for you?

quote:

Yes

Are you sure about that? what about if you bcp data into a table. Do triggers fire then?

-ec

Westley
Posting Yak Master

229 Posts

Posted - 2007-02-15 : 04:49:16

haha, ec, let me try as well.

1. Clustered index would be great for date range, or range search. Where non-clustered would be use for any given values for quesies at the where cause.

2. if the site is slow, can either use profiler to capture the queries that is running slow and tune it (either rebuild index, create/drop/update them).
Also check if there are any blocking, (sp_who, sp_who2) etc etc.....

3. triggers, hated it.....but yes it will fire, since its an insert to the table. even if you use bcp, it will still fire, you will need to disable it before you bcp the data in.

Do I get the job? haha

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-15 : 09:23:20

quote:
Originally posted by Westley

haha, ec, let me try as well.
...3. triggers, hated it.....but yes it will fire, since its an insert to the table. even if you use bcp, it will still fire...

So, you're certain of that?

CODO ERGO SUM

gongxia649
So Suave

344 Posts

Posted - 2007-02-15 : 10:54:13

eyechart,

quote:Originally posted by gongxia649
non-clustered is used on columns involved in search conditions of a query (where clause).
clustered index is used on columns that contain a large number of distinct values.

why is this? Does this mean that all unique indexes should be clustered indexes?
yes, all the unique index should be clustered. cause when u create a primary it creates a unique clustered index automatically.

quote:
Check if the table has indexes or use dbcc to recreate the index.

how do you know what tables are in use?
I can ask the front end developer to tell me which SP is he calling. Go to that SP and see what tables is using and then see if the tables have indexes.
how do you know if an index will even help?
If recreating the index doesn't help, then it could be hardware problem or we could edit the code and write a better statements.
how can you tell if rebuilding the index will do anything for you?
its just a suggestion. i dont know if its going to work but we also have to see how many records are in the tbale.

quote:
Yes

Are you sure about that? what about if you bcp data into a table. Do triggers fire then?
no sure about this one.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-15 : 12:42:03

quote:
Originally posted by Westley
3. triggers, hated it.....but yes it will fire, since its an insert to the table. even if you use bcp, it will still fire, you will need to disable it before you bcp the data in.

Please see http://msdn2.microsoft.com/en-us/library/aa196743.aspx

now, does DTS use bcp or bulk inserts behind the scenes?

quote:

Do I get the job? haha

short answer is no. sorry :(

-ec

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-15 : 23:03:13

quote:
Originally posted by gongxia649
yes, all the unique index should be clustered. cause when u create a primary it creates a unique clustered index automatically.

Okay...then what about this?

Create Table Sample
(
    SomeKey int primary key nonclustered
    col2 varchar(10),
    ....
)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Westley
Posting Yak Master

229 Posts

Posted - 2007-02-15 : 23:54:55

oh, I though bcp does fire triggers....:( learn something new :)
but when you use a DTS that calls a stored proc that insert something in a table, where the table got a trigger on, it will fire the tigger, as it just like you calling the sp anywhere else, right?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-16 : 00:02:52

quote:
Originally posted by Westley

when you use a DTS that calls a stored proc that insert something in a table, where the table got a trigger on, it will fire the tigger, as it just like you calling the sp anywhere else, right?

Not if you use Fast Load option.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Westley
Posting Yak Master

229 Posts

Posted - 2007-02-16 : 00:11:12

Fast load option is only for data transformation task, you can't put that on a SQL task object, and since its calling an SP, don't think it have that options?
but yeah, on transformation task, does fast load using bulk insert? just like to confirm, as i can't see that on BOL.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-16 : 00:27:40

The answers on this thread remind me of answers I got when I interviewed candidates.

They didn’t get the job either.

CODO ERGO SUM

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-02-16 : 00:59:57

You all Fail...

When asked any database question the answer is always....

IT DEPENDS!!!!

DavidM

Production is just another testing cycle

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-16 : 01:10:52

quote:
Originally posted by byrmol

You all Fail...

When asked any database question the answer is always....

IT DEPENDS!!!!

DavidM

Production is just another testing cycle

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-16 : 05:20:18

It is ok to say you don't know the answer to a question when you don't. it is far worse to bullshit an answer and have it be totally wrong.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources