SQL Server Interview Questions and Answers Complete List Download « Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Complete List Download

April 21, 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.

Download SQL Server Interview Questions and Answers Complete List

Thank you all for your appreciation about the my recent series of SQL Server Interview Questions and Answers. I enjoyed writing questions and answers. I have got many emails about complete series.

Top most request was to collect series in one big post so they can be easily used. I was asked to provide links to download them so they can be printed and referred.

Another question I received is do I ask the same questions in interviews which I administrate. Answer is YES. Though, I have few other questions, which I ask. All of them I came up myself and answers are unique to questions and not available on-line. Well, long story short, I have compiled list of questions in one PDF. Please download them and use them in your next interview or just for reading purpose.

Download SQL Server Interview Questions and Answers Complete List

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

Posted in Best Practices, Database, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, SQL Trigger, T SQL, Technology | 300 Comments

300 Responses

on April 21, 2007 at 8:34 pm Alan Murray

Hello Dave,

Congratulations! Excellent Web Site!

We are always looking for talented candidates. Contact us. Email or online application through website.

Alan
on April 22, 2007 at 9:57 am anonymous

Excellent! Worked for me!

Actually, my interviewer was using some other side where similar questions were listed but it did not have answers. I realize that he was writing down my answers. He was impressed with my knowledge.

Thanks.
on April 22, 2007 at 9:59 am pinaldave

Good Luck with Interviews. Best Wishes for your career.

Pinal Dave
(http://www.SQLAuthority.com)
on April 23, 2007 at 9:13 am Sachin Bhaat

Thanks it is very detailed questions and answers.
How long did you work on them?
on April 23, 2007 at 9:14 am anonymous

Wonderful. Thanks
on April 23, 2007 at 9:21 am pinaldave

Sachin,

I have come across many questions during my career. Many are from my experience or my team members asking them to me. I wrote down most of the question and answers for their reference.

In recent interviews in my company I used this question with applicants for Sr. DBA and I found that this are good set of questions and reveals true understanding of the applicants about the SQL subject. I enjoyed asking them question using my notes.

I decided to not to keep it with me only. I typed them up, converted them to PDF and here it is.

Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on April 23, 2007 at 6:00 pm MicroTech Solutions

Your resume is impressive. Sent email with job opening. Please forward it to others, who can be good fit to position.
on April 24, 2007 at 12:00 am Ansari Mohd

Good work.
Who are you? Every search related to SQL brings up your story or link to your story.
Good storys.
on April 25, 2007 at 9:13 am Wasim

Hi,

Good work, good collection of sql server interview, who are you? and where you working?

Keep writing more questions and aswers related to sql server DBA.

Thanks,
on April 26, 2007 at 1:50 am Sameer Buddha

Thank you very much.
Your articles are very informative.
Please keep on writing. I am subscribed to feed. Today I came back to site after long time, many things have changed.
good 4 u.
on April 26, 2007 at 3:14 am MicroTech Solutions

Please contact us either by email or website with your updated resume.
thanks
on April 29, 2007 at 3:43 pm MohammedU

Excellent work Mr. Dave…
Interview questions are really helpful for all DBAs :)

Good work and keep writing…
on April 30, 2007 at 1:13 am maria francis

Thank you ,

I need all the elements and concepts in architucural format .
can u send that all to my mail id. and send me all the question and answer.
thanks
on April 30, 2007 at 5:34 am KK SURYA PRAKASH

I require the SQL server Questions
on April 30, 2007 at 6:23 am pinaldave

Hello All the readers,

Those who have requested that you want my questions and answer. Please read the post above. Very top. I have posted all my questions and answers there. Please download them. They are open and free for everybody.

Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on April 30, 2007 at 7:22 pm Roger McDonald

Thank you Mr. Dave,

It is good collection. I enjoy your articles very much. I personally think you can still write more quality documents. I like your ideas very much. From your post, I can see that you are short of words, though you very well manage with simple words.

I appreciate your efforts, I want to congratulate you for your website and I willing to help you, if you need any help. Keep it up.

Your knowledge of sql server is excellent and your understanding of subject is almost perfect.

Great work.

Roger
on April 30, 2007 at 10:01 pm pinaldave

Thank You very much Roger for your opinion and advise.

Kind Regards,
(htttp://www.SQLAuthority.com)
on April 30, 2007 at 11:39 pm Mahendra Singh

Perfect Pinalbhai,

What about next series. Your articles are very interesting.

What magazine do you write for? What company do you work for? Do you have life outside this blog? You should write about your life also. Your SQL knowledge is respectable.

Mahendra
on May 1, 2007 at 10:35 am ram

Thanks for sharing :)
on May 2, 2007 at 12:40 am Annie

Thank you.
Really thank you thank you thank you. I own you one. I got job today. Interviewer knew you. He is from UCLA like you.
If you ever visit to Wind City, please please visit our user group.

I feel good. Finally got job.
on May 2, 2007 at 12:44 am pinaldave

Congratulations Annie,

When I read that you got job and it seems that you used this questions and answer, I am extremely happy. This just gives me satisfaction about helping community.
I will sure visit your user group if ever get chance. Which user group is it? SQL or any other programming language. I am willing to attend any.

Just to be clear I am from USC not UCLA :)) Fight ON!!!

Kind Regards,

Pinal Dave
(http://www.SQLAuthority.com)
on May 3, 2007 at 2:42 am sudhir

gOOd Yaar?
on May 4, 2007 at 2:23 am ajaykumar

interveiw question
on May 5, 2007 at 11:37 pm Saurav

Excellent Work Pinal!

In fact, there are many DBA’s around the world, but very few would share information like you.

Appreciate all your hard work in preparing this snapshot!

Thanks,
Saurav
on May 5, 2007 at 11:45 pm shrikanth

You are true magician. Thanks. I got job today. I worship you.
on May 6, 2007 at 12:04 am student

Dave,

Our school has blocked your site. We have to use it through proxy. my school IT dept sez that your list of sql question has few question which are in our course. i think your question are generic and are basic to any database course. It may be useful to us in future but at moment we can learn database course by reading it. few questions are very difficult and may be used in future.

My school IT dept is not smart as I read your site from either proxy or from home. my frends from dorm can not use it. See my email address above it is incorrect but if domain is correct. Please contact our IT dept so we can see your site from dorm. they need letter from CS dept so go to cs.schooldomain.edu and contact us to see the information.

good work. Like your questions.
on May 7, 2007 at 1:28 pm Kedarnath

Thanks Dave,
For helping me.
on May 14, 2007 at 10:17 am Ashutosh

It’s really very informatics website and question and answered have been given in detail, thanks for providing us so nice materials.
on May 16, 2007 at 3:17 am Parixit Parekh

A really nice work which would help in general Database related question in Interview.

Keep it up and let us updated as well.
on May 16, 2007 at 9:25 pm Dileep

Thanks for providing good and varied amount of q/a on SQL Server.
on May 18, 2007 at 4:43 am Sivashanmugam.p

Thanks Pinal Dave it is very helpful for my interview Preparations.
on May 19, 2007 at 3:51 am sanjeev

good
on May 20, 2007 at 6:58 am mamatha

thanks for queries and answers
on May 20, 2007 at 9:54 am pinaldave

Those who has asked link to digg. Here it is
DIGG
on May 20, 2007 at 11:38 pm Vivek

Thanks for regarding these information & quries
on May 21, 2007 at 12:24 am prem

hello sir, iam premkuar. i want sql query.
on May 21, 2007 at 12:25 am prem

Thanks for regarding these information & quries
on May 21, 2007 at 12:27 am pinaldave

Hello All,

Please go to first line of this blog where you can click on link and download the PDF.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on May 21, 2007 at 12:28 am pinaldave

Interview Tag of WordPress
on May 21, 2007 at 2:21 am Poonam

Its really nice and helpful for us

Thank u
on May 21, 2007 at 6:46 pm Anthony

Hi Mr. Dave,
Could you help me with multiple sub queries of SQL. It will be great help if you could send them to my mail id.

Anthony
on May 22, 2007 at 1:47 am Wanderer

Hi there Pinal,

Interesting set of questions. Must take quite a while to go through all of them in an interview – our set is aimed at jumping in at the deeper end, because we are consultants, and require a stronger level. That said, I sometimes worry that ’skipping’ some of the more basic concepts, means that we could allow people to ’slip through’.

Regarding the questions, some comments (possibly a few posts, rather than 1 long comment). these are given in the interest of sharing.

Truncate:
-You state truncate CANNOT be rolled back. That is incorrect when tested on SQL 2000 (SP4) and SQL 2005 (sp2a):

create table I (i int)
insert into I(i) values (1)
go
set nocount on
select i ‘BEforeTruncate’ from I
begin tran
truncate table I
select i ‘afterTruncateBeforeRollback’ from I
rollback
select i ‘AfterTruncate’ from I

–results:
BEforeTruncate
————–
1

afterTruncateBeforeRollback
—————————

AfterTruncate
————-
1

Note that after the rollback, the table again has the row.
on May 22, 2007 at 1:54 am Wanderer

…hmm, message seems to have been truncated. The full quote, again, for review:

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound
statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given
connection. When connection are closed, the data in the global temporary table disappears. However,
the table definition remains with the database for access when database is opened next time.
on May 22, 2007 at 7:58 am chauhan

Hi! All
Pls help me to resolve Following issue:

I want to drop contraint defind on a FIELD OF table AND WANT TO ALTER LENGTH OF THAT FILED in 15-20 database having same schema without every time changing constraint’S unique id w.r.t to database using following command

ALTER TABLE MYTABLE DROP CONSTRAINT CONS1

ALTER TABLE MYTABLE ADD CONS_COL VARCHAR(50)

It is not executing/error encountered
on May 22, 2007 at 1:41 pm Chakradhar

Can you please send me the complete collection of your SQL server Questions.

Thanks in Advance
on May 22, 2007 at 1:45 pm Chakradhar

Dave,

I have a question ?

when a dead loack occurs one process is choosen as dead lock victim against another process.

The process that is choosen as dead lock victim gets rolled back. Can we set the dead locked process to re-run automatically after waiting for certain time ?

Please help with an example if possible.

Thanks Again !!!
on May 22, 2007 at 1:54 pm pinaldave

Wanderer,

Thank you very much for all your suggestions and comments. All points made by you are correct. I will update my interview questions soon and reflect all the suggestions made here.

Again, thank you very much your analysis is truly appreciated.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on May 24, 2007 at 3:05 am Vandana

hi Pinal Dave

excelent job..u r a real database guru..keep it up..

thnaks for helping ..
on May 24, 2007 at 6:32 am hiren

Thanks Dave,
Very Helpfull for me
on May 24, 2007 at 9:51 am Rahul

Thank you,
These questions are extremely helpful for me beacause I am applying for different software companies. These questions will be helpful for me for giving interviews.
on May 27, 2007 at 1:26 am omprakash

Hi Dave,

how is it possible to use nested transaction with sql 2000.

Please help
Thanks and Regards,
omprakash
on May 30, 2007 at 8:07 am pinaldave

Hello,
Those who are looking for job or looking for good candidate for job. Please visit http://jobs.SQLAuthority.com for job posting and job listing.

Regards,
Pinal Dave
on May 31, 2007 at 4:47 am Appalaraju Kusumpudi

Hi Friends
We are very thankful to PInal Dave for the site, its really wonderful. Hope lots of people gets benifitted from the site. use as readymade information.
Thanks and Regards,
Appalaraju Kusumpudi
on June 3, 2007 at 4:32 am pavan

This is very much help ful to me. thank u.

Now iam complete my M.C.A and searching for a job. I have very much interested to working under SQL server.
on June 3, 2007 at 8:50 am SQL SERVER - Database Coding Standards and Guidelines - Introduction Journey to SQL Authority with Pinal Dave

[…] 3rd, 2007 by pinaldave I have received many many request to do another series since my series SQL Server Interview Questions and Answers Complete List Download. I have created small series of Coding Standards and Guidelines, as this is the second most request […]
on June 5, 2007 at 11:24 pm Ramakrishna Bandaru

Thank u very much i am satisfy to u r help on que and ans
on June 8, 2007 at 8:48 pm Narayan Satya

Hi Pinal,

Thank you very very very very much. I passed my final written interview exam of sql server today. Tomorrow I will start my new job. It was open book and I was allowed only one book. I used your q & a. You are good.

Narayan Satya
on June 10, 2007 at 3:59 am kishore

very good questions
on June 10, 2007 at 11:43 pm Chandan

Thanks
on June 13, 2007 at 2:46 am kalaiarasu

Nice website i learned more about SQL

with regards
kalai
on June 13, 2007 at 11:43 pm satish n.

These questions are extremely helpful for me beacause I am applying for different software companies. These questions will be helpful for me for giving interviews.

Its keep me upgrading myself.
Thanks and keep it up.
Its Really Very helpful site.
on June 16, 2007 at 11:57 am sekar

Hi
How to find the first 50 highest values
How to find the nth maximum value and minimum value
How to concatinate one integer value and string value
pls reply me
u r friend
on June 17, 2007 at 8:30 am ashwini

I need help for cursors and triggers in sql server with synatx and some sample example.
on June 18, 2007 at 12:31 am vinita

i have done software engineering and i m preparing for the interview so please send me some quetions which is related to my course.
on June 18, 2007 at 5:23 am Harendra

Nice job ;-)

very nice collection.
on June 19, 2007 at 8:23 am SQLAuthority.com News - Journey to SQL Authority Milestone of SQL Server Journey to SQL Authority with Pinal Dave

[...] I am very glad to write this 200th post of this blog. I would like to express my gratitude to all of YOU – my readers for continuously reading this blog. I receive many comments and emails with feedback, questions and suggestion everyday. I enjoy meeting few of you during this journey as well. Please do send me feedback and your request to make this blog better. Following is milestone of Journey to SQL Authority. SQL Server Interview Questions and Answers Complete List Download (PDF) [...]
on June 22, 2007 at 7:59 pm santu

Hi to Pinal Dave and every one,
Do u have complete notes and Interview questions on SSRS and SSIS?
can u plz give me a reply

Thanks
santu
on June 26, 2007 at 1:43 pm sampath

Hi Dave

i am working as sql DBA in one company having 1 yr exp , please post me some gud monitoring scripts & suggest me to improve my knowledge in sql server as a DBA
on June 27, 2007 at 3:18 am Maheshwari

i found many answers without taking risk.I worship you

thanks a lot..
on June 28, 2007 at 8:08 pm Bob Lafore

Mr. Dave,

This is great collection of questions and answers. Today I passed interview. All questions are from your list.
1) Difference between Truncate vs Delete
2) Normalization advantages
3) Having condition
4) Unique constraint
5) Constraint and rules

You just got this thing right. I passed interview because of YOU. YOU are the man!

Bob Lafore
on July 5, 2007 at 12:05 pm Gayatri

Hello,

Great work! Certainly gonna help with my interviews :)
Thank you!
on July 7, 2007 at 4:34 am Ajeesh

Hi Pinal,

Thank you very much for this document. It really helps me a lot..
But one thing still confusing me now…
in question
What is difference between DELETE & TRUNCATE commands?

in answer it is also given that

TRUNCATE can not be Rolled back.

is it true ? i think we can rollback it. please give me details about this…

thanks
Ajeesh
on July 10, 2007 at 5:07 am P.PraveenKumar

Hi Dave,

As Mr.Wanderer question regarding Delete/Truncate difference,I cant able to understand,can u explain with example..
Regards
P.Praveenkumar
on July 10, 2007 at 5:10 am Prasanna S

Hi Dave

I got a question in SQL SERVER. How can v delete a row form a table without using delete keyword. Is it possible?

If so, tell me the way?

Regards,
Prasanna S
on July 10, 2007 at 9:29 pm pinaldave

Prasanna,

To achieve what you asked follow this directions.
1) Create new Table.
2) Insert all the rows except the row you want to delete
3) Drop old table.
4) Rename this new table to old table name.

Regards,
Pinal Dave (SQLAuthority.com)
on July 10, 2007 at 9:51 pm S.Prasanna

Hi dave
All ready the table contains some records, my question is i want delete the particular row from the table

Ex:
If table XYZ contains..

S.NO NAME DEPT …..
1 XXXX CSE
2 YYYYY IT
3 zzzzz MECH

From the above mentioned,I want to delete the record which contains dept IT. But without using DELETE keyword.
on July 10, 2007 at 10:09 pm P.PraveenKumar

Hi Dave,

As Mr.Wanderer I worked with his example He is correct Truncate gets Rollback
But I am not getting your explanation which u given for Wanderer
Kindly clarify me with example
on July 10, 2007 at 10:10 pm haini

very useful to us please me
on July 10, 2007 at 11:14 pm Prasanna

Hi dave,

I got cleared for what u say for delete a record without using delete keyword. thanks.

Regards,
Prasanna S
on July 11, 2007 at 11:28 pm Mahdi

hello
i have a question the work to #tempTable is better or the work To dataTable In VB.net(2003) Which Is Faster?
thank you
on July 13, 2007 at 9:38 am Parmod Bhardwaj

Hi Dave,

Thats Really Great Job…..

All the Best…………

Parmod Bhardwaj
Gurgaon
on July 15, 2007 at 10:36 pm vijay

Dear dave,
1. How to i drop all the trigger at a time ,
is there is any way as single T-SQL statement give some breif example.

2. How can i excute trigger with sql statement

with regards
Vijay.B
on July 19, 2007 at 12:31 am saravan

Hi pinal dave

Your site is really got useful infos. Please put some more
technical faq in depth in sql server questions on execution plan
and the performance and tuning questions.

Thanks man

Saravan
on July 19, 2007 at 12:55 am harsh saxena

Interview Question of SQL SERVER
on July 25, 2007 at 7:00 am SQL SERVER - Data Warehousing Interview Questions and Answers - Introduction Journey to SQL Authority with Pinal Dave

[...] Series: SQL Server Interview Questions and Answers Complete List Download SQL SERVER Database Coding Standards and Guidelines Complete List [...]
on August 2, 2007 at 2:26 pm Vijay

Excellnt website
on August 5, 2007 at 9:46 pm chandrashekhar

Hi Dave,

your sql server interview ques and answers are really very very helpful to who is hunting for job….

All The Best …..

Thanks …….

- Chandrashekhar & Smita
on August 6, 2007 at 7:52 pm saq

i had a query which was working fine in 65 and 80 but suddenly it just stopped working in 80 and I have no idea what caused it?
The commented code was working in 65 but for 80 we wrote the other part (uncommented one).

select distinct
rt.Description,
– FullName = case c.FirstName when null then null else c.FirstName + ‘ ‘ end
— + case c.MiddleName when null then null else c.MiddleName + ‘ ‘ end
— + c.LastName,

FullName = ltrim(isnull(c.FirstName, ‘ ‘) + ‘ ‘ + case c.MiddleName when null then ltrim(”) else c.MiddleName + ‘ ‘ end + c.LastName),
s.ContactNum,
s.ReportType
from ReportType rt
inner join Subscription s on (rt.ReportType = s.ReportType)
inner join Contact c on (s.ContactNum = c.ContactNum)
where rt.OnDemand = 1
on August 7, 2007 at 2:29 am Jitender

Hi Pinal Dave,

I am having over 100 databases in an enterprise.
For some reason i need to have some data from some of the tables from almost every database into a centralized database. I was thinking of using triggers “On update” on all those tables for this purpose, which will update the central DB as soon as a new record is created in any DB. But In most of the tables i m using composite keys. So i m not getting the last created primary key in the table. I have tried SCOPE_IDENTITY() and IDENT_CURRENT(”). Also it is not a feasible solution to use triggers in every database.

Can u please tell me how i can do that. If possible can u suggest any other integration method, but it should be a real time solution.
on August 13, 2007 at 10:58 pm Sadanand Singh

Congratulations! Excellent Web Site!

I am new to sql server 2005, after gone to the entire questionnaire. I have got a very good knowledge of sql server 2005.

Excellent Job done!

Regards
Sadanand
on August 14, 2007 at 3:06 am Parvinder Arora

Excellent Work Pinal!

In fact, there are many DBA’s around the world, but very few would share information like you.

Appreciate all your hard work & done a great Job.

Regards

Parvinder
on August 14, 2007 at 7:29 am Praveen_Boy

Thank you Sir………………….
on August 14, 2007 at 8:28 am SQL SERVER - What is SQL? How to pronounce SQL? Journey to SQL Authority with Pinal Dave

[...] Reference : Pinal Dave (http://www.SQLAuthority.com) , SQL Server Interview Questions and Answers [...]
on August 16, 2007 at 12:00 am Vinit

Hi , Thanks a lot for doinog this job
on August 17, 2007 at 6:07 am Prakash Nandwana

Hi Pinal,
Wonderfull Job . I appreciate your hard work and intelligency. You had understood the requirement of the person thrust of Job.

Thank You Very Much.

Regards:

Prakash Nandwana
on August 17, 2007 at 6:40 am NEERAJ JAIN

HELLO SIR I’M FRESHER I WANT TO JOB BASED ON SQL SERVER.WITH .NET.SO PLS GIVE ME IMPORTANT QUESTIONS FOR INTERVIEW.
on August 18, 2007 at 8:21 pm Murali

hi Dave,
I’m doing a course on database n windows application development.I found your website very interesting Thank you for sharing all the info about Sql.I would like u to post useful information on other topics such as XML,ASP.NET,VB.NET,C#,GUI,ADO.NET….etc

Regards,
MK
on August 21, 2007 at 7:39 am Madhav

this very useful site for sql learners.
send me any news about SQL SERVER.
on August 22, 2007 at 5:03 am Ananth.K

Hi Mr.Pinal,

Very informative Blog. You have really put in a great effort. Thanx a lot for sharing your knowledge with us, which many dont mind to do.

Keep sharing and keep growing.
All the very best.

Regards,
Ananth.K
on August 28, 2007 at 2:07 am jatinder singh

Hello sir,
I am fresher and I have done asp.net in c# using sqlserver please give me important interview question.i have used sqlserver only for connectivity but in interview they asked me lots of questions related to sql server pls give me important quetions
on August 29, 2007 at 5:31 am Sanmukh

Hi,

Good collection of questions and answers. Pretty much useful in interviews. Following are some questions, which I had faced in interview and don’t know the answers. Please help me.

- What is the difference between Primary Key and Candidate Key.

- In a stored procedure i m adding a row into a table which contains a Identity column. How to know the value of IDENTITY column for this inserted row.

- Data in my database is mainly used for Reporting purposes. So what normal form is suggested and why.

Thanks in advance,

Sanmukh.
on August 29, 2007 at 10:09 pm Muralidhar

Can I have a questionnaire of SQL Server 2005?
(Related only to SQL Server 2005)

If anyone has the same then i would appreciate if you can eMail me the same.
on August 31, 2007 at 5:55 am Faiq

Dear, u have done a very excellent job,
It help me alot.

thanks
on August 31, 2007 at 10:12 am Mohammad Musleh

Very nice work Pinal

Just a quick comment on sub-queries

“Sub-queries cannot contain an ORDER BY clause”, that’s true unless you use TOP phrase

Example
This code does not work

select firstname, lastname, username
from tbluser
where intgroupingid in (
select intStudentGroupingID
from tblStudentGrouping
order by vchStudentGrouping
)

But this code works

select firstname, lastname, username
from tbluser
where intgroupingid in (
select top 100 percent intStudentGroupingID
from tblStudentGrouping
order by vchStudentGrouping
)

So if you want to use ORDER BY in your sub-query you can fake it by using “top 100 percent”, which will return all records in your sub-query.

Hope this helps

Thanks
on August 31, 2007 at 10:13 am pinaldave

Thanks Mohammad Musleh,

Reader likes you makes a day.

Good work.
Pinal Dave ( http://www.SQLAuthority.com )
on September 1, 2007 at 7:27 am SQLAuthority News - JavaScript - Beginning Interview Questions Journey to SQL Authority with Pinal Dave

[...] of feedback and I enjoyed responding to my readers additional questions. You can read my series SQL Server Interview Questions and Answers Complete List Download [...]
on September 1, 2007 at 10:01 am killy

Thanks! It’s very very Gooooooooood information.
on September 3, 2007 at 8:11 am Siv

Very Nice and Extrodinary Stuff provided and I expect more such kind of stuff from Pinal.

Thanks Again….
on September 3, 2007 at 8:13 am Gopal

Very Nice and Extrodinary Stuff provided but Some more examples are needed and I expect more exemplified stuff from Pinal.

Thanks Again….
on September 3, 2007 at 12:59 pm pinaldave

Thanks SivGopal,

I promise that I will soon come up with latested updated version of this post.

Regards,
Pinal
on September 3, 2007 at 11:18 pm Rajendra Shetty

Hi Pinaldave,
Good Morning.

First of let me thank you for your good nature of sharing knowledge with others. This is very rare to see nowadays.

I am facing a pecular problem related to Inserting records into a table using OpenXML syntax.

We are getting some deadlock situations if calling the same Stored Procedure Multiple times from multithreaded environment. The below SQL scripts creates deadlock situations. But if we use a table variable for temporary insert and then use do the insert into the main table from the table variable the script/sp runs properly. I think this is not a best practice for million of records to be processed in batch. It is a part of batch requirement and needed some expert comments on this.

Stored Procedure Code (This causes dead-lock)
—————————-
ALTER Procedure [ebp].[isp_BatFinancialTransDetail]
(
@TransactionOid int,
@DataXml XML
)

AS
BEGIN TRY
SET NOCOUNT ON;

– DECLARE INT VARIABLE TO HOLD XML DOCUMENT HANDLE
DECLARE @intDoc int

EXEC sp_xml_preparedocument @intDoc OUTPUT, @DataXml

INSERT INTO [ebp].[FinFinancialTransactionDetail]
(TransactionOid,
AccountOid,
EntryTypeCd,
Amount,
SourceFundBasedFl,
UnitizedFl
)

SELECT * FROM OPENXML(@intDoc, ‘/Allocations/Allocation’, 2)
WITH
(AccountOid int,
EntryTypeCd int,
Amount money,
SourceFundBasedFl bit,
UnitizedFl bit
);

EXEC sp_xml_removedocument @intDoc;

END TRY

BEGIN CATCH
— Execute the error retrieval routine.
EXECUTE ebp.ssp_EScapeErrorInfo;

END CATCH;
on September 3, 2007 at 11:42 pm pinaldave

Rajendra Shetty,

You are facing the same issue which many times many users with OpenXML. It is true that multi threads creates issues for this.

We faced lots of issues due to that. We have limited the our XML processes to run on single server with great amount of RAM, instead of multi cpu running this at the same time.

Since we have done that we are seeing great performance improvement, instead of expected performance degradation due to single CPU.

I noway claim this as best solution but it is working fine with our servers which are performing millions of XML transactions in an hour.

Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 4, 2007 at 10:42 pm Rajendra Shetty

Thanks a lot Pinaldave for your response.
on September 6, 2007 at 2:15 am Mahesh chudamani

Hi Pinal,
The following script whic his used to say overall job status Information..

select a.name as JobName, a.date_modified as LastModified,
case when a.enabled = 1 then ‘Enabled’
when a.enabled = 0 then ‘Disabled’
else convert(varchar,a.enabled)
end as IsJobEnabled,
case when e.enabled = 1 then ‘Enabled’
when e.enabled = 0 then ‘Disabled’
when e.enabled is NULL then ‘Schedule Info not available’
else convert(varchar,e.enabled)
end as IsScheduleEnabled,
case when f.enabled = 1 then ‘Enabled’
when f.enabled = 0 then ‘Disabled’
when f.enabled is NULL then ‘Alerts Info not available’
else convert(varchar,f.enabled)
end as IsAlertsEnabled,
case when c.LastRunDate is NULL then ‘LastRunDate Info not Available’
when c.LastrunDate is not NULL then convert(Varchar,c.LastRunDate, 101)
end as LastRunDate

from msdb..sysjobs a
left outer join (
select max(convert(varchar,cast(convert(varchar,run_date,112) as Datetime),101)) as LastRunDate, Job_id
from msdb..sysjobhistory
where step_id = 0
group by Job_id) c
on a.job_id = c.job_id
left outer join
(select p.enabled, p.job_id from msdb..sysjobschedules p join
(select job_id, max(date_created) as dt_Created from msdb..sysjobschedules
group by job_id) q
on p.date_created = q.dt_created
and p.job_id = q.job_id) e
on a.job_id = e.job_id
left outer join msdb..sysalerts f
on a.job_id = f.job_id
order by 1
on September 6, 2007 at 2:16 am Mahesh chudamani

The following script is used to find the Latest Database Backup

– Find the latest backup of a database
– Input Parameter:
– @DatabaseName – Name of the database
– Output Parameter: None
CREATE PROCEDURE uspFindLatestBackup(@DatabaseName VARCHAR(255))
AS
BEGIN
SET NOCOUNT ON
SELECT BS.Database_Name,
BS.Backup_Finish_Date,
BS.Type,
CONVERT(INTEGER, BS.BACKUP_SIZE/(1024*1024)) AS ‘Size in MB’,
BS.Machine_Name,
BM.Physical_Device_Name
FROM MSDB..BackupSet BS
INNER JOIN MSDB..BackupMediaFamily BM ON BS.Media_Set_ID = BM.Media_Set_ID
WHERE BS.DATABASE_NAME = @DatabaseName
AND BS.TYPE = ‘D’ — D Database, L Log
ORDER BY BACKUP_FINISH_DATE DESC
END
on September 7, 2007 at 11:30 am JavaScript - Beginning Interview Questions Joey JavaScript

[...] For a similar article on SQL interview questions, I highly recommend SQL Interview Questions and Answers from the SQL Authority, Pinal [...]
on September 9, 2007 at 6:58 am Vijay

THello sir,
send me sql server FAQ list please
regards
vijay
on September 10, 2007 at 7:33 am SQLAuthority News - Job Opportunity in Ahmedabad, India to Work with Technology Leaders Worldwide Journey to SQL Authority with Pinal Dave

[...] selected for interview I suggest you go over SQL Server Interview Questions and Answers Complete List Download, as there is great chance I may be participating in [...]
on September 12, 2007 at 4:37 am seema

hi

im doing oracle- 9i course from niit but still im not happy with my knowledge in sql . plz suggest me and send some gud notes.
thanks

rgds
seema
on September 20, 2007 at 3:23 am Mukesh

Hi Mr.Dave

First I would like tell you thanks you for idea and questions and answers list

I think this list is so important and so simple that it givis enough confidence to the candidate who is going to attend interview or who want to know about the sql server very rapidly and easily

Regards,

Mukesh
on September 20, 2007 at 5:19 am santosh

Hi Mr. Dave,

Pls provide me these doc, i would be thankful to you.

->SQL Server Interview Questions and Answers Complete List Download

->SQL SERVER Database Coding Standards and Guidelines Complete List Download

->SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
on September 21, 2007 at 12:35 am prashantha v

Hi Pinal Dave
Oh God , how can i thank u….it was so helpfull, and i can’t forget u in this needfull Q and A. once again thank you very much…i will call u as SQL-GOD!!!!

Thanks and regards
prashanth venkataswamy
on September 24, 2007 at 9:53 am Dipak

Can you please let me know how many thereads allow SQL Server to execute DTS packages?
on September 27, 2007 at 12:17 pm vinod

Hi Dave,
Excellent work
on September 27, 2007 at 12:30 pm Pradeep Adiga

Hi,

Excellent site. Thanks a lot. Please don’t forget to update it. Please come up with questions on SQL Server 2005.

Thanks,

Pradeep
on September 27, 2007 at 2:01 pm Mallampatu

Can’t Truncate statement be rolled back?
I was under the impression that Truncate works at pages, and removes the reference, however it can be rolled back.

Please clarify this.
Thanks in advance
on October 2, 2007 at 5:29 pm pinaldave

Hi Mallampatu,

Truncate can not be rolled back after it is committed. It can be rolled back if it is not committed.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on October 3, 2007 at 11:37 pm Ruturaj

Excellent site!!!
It is gr8 deal of help for many!!!
Thanks a lot for having such a site with all this information!!
on October 4, 2007 at 8:54 am kris

Can u send me the SQL Server Faqs.
on October 4, 2007 at 11:07 pm kazi Jahanheer Alam

i am verry verry thanks for the Mr.P.Dave.that have sharirng of good collection of his exillent knowladge,once again verry verry thanks …..
on October 12, 2007 at 4:03 am anyniomus

NOTE :

To have a non custered index the t able must have a clustered index.

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
on October 17, 2007 at 11:54 pm Venu

Thanks very much.

Great

Thanks
Venu Jakku
Developer
on October 18, 2007 at 5:19 am Anil Chauhan

Hi,

Its Realy Nice question with answer.

Regards,
Anil CHauhan
on October 27, 2007 at 12:13 pm Fatima

Just found your site today while preparing for an interview. Thank you for the wonderful resource.
on October 28, 2007 at 12:40 am tannu

hi ,
i should say the best website for finding a lot on sql and rdbms thanks …
regards
tannu
on October 29, 2007 at 5:13 am ashesh

a great collection of questions( thanks a lot for answers were also there)..
really help full for all..
on November 1, 2007 at 11:14 pm zain

Thanks for the questions, they are of immense help to a fresher like me!!
on November 3, 2007 at 3:11 pm Raghu

Please Send Me Sql Server Interview Faqs ASAP.
on November 4, 2007 at 5:17 pm sandy

1) What is diff. between Sql server 200 and sql server 2005 .
2) Which is best wesite for Downloading Questions and answer for certifications.
on November 5, 2007 at 10:22 am shashi kanta

hello,
Thanks for your good nature for sharing knowledge.You are nice person.
please give me brief description on View command. Can we change data in base table through view .
can we bound view so that a person cannot change base table through view.
on November 5, 2007 at 11:42 am Raj

hi,
This sql server interview question page is very useful.
pls send me Sql Server Interview Faqs ASAP.

Thanks,
Raj.
on November 7, 2007 at 5:07 pm Karan Prabhakar

hi dave,
I am a sql freak like you are,and i have a query for you,am trying to find out a way to find top most parent of each child in the following table using one sql query and not any batch query,although oracle provides a way by using connect by prior,i dont see anything like that in sql server 2005 can u help me out ?????i hope you can…..cheers!!!!!

table1
child || Father
A || B
B || C
C || D
D || E
E || null
F || H
G || null

output
table1
child || Father
A || E
B || E
C || E
D || E
E || null
F || H
G || null

or anything which can be close to the recursion like finding out the topmost parent…
on November 16, 2007 at 10:51 am Suganthi

Hi dave,

I am a beginner in Sql Server do guide me in understanding the concepts in depth.

U work is really amazing keep doing for us.

Thanks a lot
Suganthi
on November 19, 2007 at 5:43 pm Manish Jain

Hi its really good site for professionals
on November 19, 2007 at 7:19 pm SQLAuthority News - Job Opportunity in Ahmedabad, India to Work with Technology Leaders Worldwide - SQL Server, ColdFusion, ASP.NET Journey to SQL Authority with Pinal Dave

[...] selected for interview I suggest you go over SQL Server Interview Questions and Answers Complete List Download, as there is great chance I may be participating in [...]
on November 23, 2007 at 10:39 am Jegadeesan

hi,

Thanks for sharing nice piece of collection. Anyone can get through the interview with these Question and Answers.

Thanks again
on November 24, 2007 at 2:25 pm Vasi

Dear sir,

This is very helpful website, our team and my self also using this site

Thank You sir

Please Update daily Updation
on December 1, 2007 at 4:40 pm Harendra Malik

Hi Dave

Please send me some complex sql queries

Thankx
on December 2, 2007 at 1:44 pm ANWAR

Thanks for help in SQL Interview Questions
on December 4, 2007 at 11:14 am pradeep

Excellent….. I appreciate your tendency of sharing.
on December 4, 2007 at 2:53 pm rishikesh sarma

hello sir,

this is a very helpful site. please give some complex queries in sql server 2000 that are frequently asked in technical interviews.

with regards
rishikesh
on December 5, 2007 at 11:01 am Riya

Hi Dave

Can u plz send me the interview quetions with objective type

Regards
Riya
on December 9, 2007 at 11:56 am Tarun jain

it is too much helpful for new comers who enters newly in it field and as possible as all topics gives help to provide job to them..

thank a lot
Tarun jain
on December 9, 2007 at 12:07 pm Tarun jain

how to add new column in existing table? pls answer me with code on my mail-id.. i will be very thankful to you.

Thank you
Tarun jain
on December 15, 2007 at 10:33 am Madhav Reddy

Tahnks Dave

Your Articles Are Very Useful To Me And I Got So Much Of Information From Your Website. Thank U Very Much.

Regards
———-
Madhav Reddy
on December 15, 2007 at 2:22 pm ekarimnagar.com Team

Hello Pinal Dave,

You are Great… Your information is very valuable an easy to understanding.
ekarimnagar.com Team
on December 15, 2007 at 5:10 pm Bhabani

Hello Sir,

Suppose i dont take latest backup the databse.
and the log file may be corrupted.

The database is now on Suspect Mode.

So how can i Recover my database(I have no backup) ?
on December 15, 2007 at 5:11 pm Bhabani

pls tell me the procedure
on December 18, 2007 at 12:45 pm Ravi Shankar

Thanks for u r work .I need SSRS interview Questions also if u have any thing realated please mail me .I m preparing for interview
on December 19, 2007 at 12:37 am Mark

Hi Pinal,

You wrote in your resume that you “… wrote Performance Tuning guidelines…”

Could you please e-mail me this document, or in case if it is placed on this site could you please point me to its link ?

Thanks,

Mark
on December 19, 2007 at 4:37 am Nivedita

Hi,
I really appreciate the hard work of yours by putting the effort in collecting the the important questions and providing the solutions also.
All the best and good luck
on December 19, 2007 at 11:37 am yogesh

Resp, Sir

I want to create RSS for the site
So, How can i create, publish, and maintain on the site.

please give me the code if possible, or references of site name
which gives code.

Yogesh
on December 19, 2007 at 11:40 am ankur

hi, i am ankur MCA student from ahmedabad.
i have a problem releted with rss.
how to make Rss and how to put into this Rss to our site.
plze give me the coding to make Rss.and tell me the easy way how to make it easy to Rss

plze send any answer then send me my email me..ok
bye
ankur
on December 24, 2007 at 4:47 pm Anurag Sharma

Hello Dave,
can you send me the frequently asked sql queries in interviews.
thanks
Anurag
on January 6, 2008 at 11:05 pm SQL SERVER - Most Frequently Asked Generic Interview Questions Journey to SQL Authority with Pinal Dave

[...] SQL Server Interview Questions and Answers Complete List Download [...]
on January 7, 2008 at 4:06 pm Ashu

Dear Pinal,

I have visited your web-site today itself and have found it very helpful.

My problem is that I have experience of 10+ years (in DOS-based applications like Clipper, Foxpro etc) and all logics are
clear and I have developed application softwares for almost
every field and are being used in the market. But I donot have any degree or diploma.

I have updated myself recently with new languages and now i am working with a company as a ERP programmer using SQL-Server 2000 and VB-2005. Please guide me accordingly so that I can see my future brighten. I belong to a small city but close to NCR.

I will be very thankful.

Best Regards,
ASHU
on January 9, 2008 at 2:19 pm naveen

hai nice website
on January 10, 2008 at 3:54 am informer

Hi Pinal,
I looked at your website, it really is nice. In addition to fundamentals of database. Do you in case have a list of sql excercises we can try for say Employee,Department example.

Things where someone needs to think hard. It could be a good excercise in problem solving.

For instance, how to delete duplicate rows from a table. Questions like that..

Let me know what you think.

Thanks

Regards,
informer
on January 10, 2008 at 8:31 am reddy

hi dave,

i am new to sql server 2005.
can you please tell how to display error message in the result set when the data is not available in the table.
when i trying select data of one perticular column from db table if there is no record i want to display error message saying that this recored is not available so how to write query…

eg:if there are 10 emp_id from emp tb if i select emp_id from emp where emp_id=11

in the result set i need display 11 not available

thanks in advance
on January 10, 2008 at 2:15 pm Manoj

Send me the SQL server Qans.
on January 10, 2008 at 10:14 pm Srinivasa Rao

This is simply Superb to give such an information for the beginers as well as experts.

I have updated myself recently with new languages and now i am working with a company as a ERP programmer using SQL-Server 2000 and VB-2005. Please guide me accordingly so that I can see my future brighten. I belong to a small city but close to NCR.

I will be very thankful

Regards

SRINIVASA RAO
BHANUTEJ
on January 13, 2008 at 7:00 am SQLAuthority News - Find Job Related to SQL Server and Database Administrator and Interview Questions and Answers Journey to SQL Authority with Pinal Dave

[...] in email : SQL Authority Job Site, SQL Authority Interview Questions and Answers, SQL Authority Generic Interview [...]
on January 14, 2008 at 9:30 pm Jayakumar

How can we write a trigger so that before any insert is occured on a table it must check for certain values to be supplied for a table.If coorect insert, else not.
on January 22, 2008 at 7:13 am Divya

Plz Send me SQL Interview Questions
on January 23, 2008 at 4:59 pm vijai

hi

can you pls send me SQL server interview question & answers

thanks
on January 24, 2008 at 9:23 am Anwar

superb—————–
on January 27, 2008 at 5:42 am jansi

u r gr8,thnks a lot.
on January 30, 2008 at 11:01 pm ganatra

are there any interview questions for reporting services
on February 1, 2008 at 7:33 pm vasudha

could u plz send me interview questions asked in HR round regading urself, strenth & weaknesses etc
on February 6, 2008 at 11:22 am Ashan

Thanks man. You are great.
on February 11, 2008 at 2:43 am John

Great links, questions and anwsers thanks for the info.

john
on February 11, 2008 at 2:36 pm Vineeta

Hi Dave,

I always come to this site whenever i stuck somewhere.
You have taken gud initiative. Its realy helpful not only for persons who are looking for job also for others.

Just 1 week back i started to give some interviews. I find they are more concern with Database performance issues. I’m having 3 years of total it experience. So they didn’t ask me general database questions.

So can you please give some Q/A regarding DB performance.

Regards
Vineeta Agarwal
on February 14, 2008 at 6:02 pm Srinatha

It is realy great, excellent web site.

Srinath.
DBA
Bangalore
on February 23, 2008 at 8:06 pm Ziangi Jones

Hi Pinal,

Thanks a lot for your interview questions. :)
on February 25, 2008 at 7:51 am rams

your answers are good
on February 25, 2008 at 8:54 am Neil

Hi Pinal

Ur suggestions are really very good.

Great collection.

Thanks.
on February 25, 2008 at 12:13 pm Ponsubbiah

Hi
Pls Could u Send Me SQL Server Interview Questions and Answers
on February 25, 2008 at 5:12 pm sandeep

I am facing a problem of transaction lock in sql server2000. The situation is when two persons work together on same record at same time transaction lock problem comes. How to avoid this. v have developed the software in vb.net(visual studio2005) running on LAN. kindly help.
on February 26, 2008 at 2:00 pm veda

Can u send me the SQL Server Faqs.
on February 27, 2008 at 6:46 pm anon

Hi Pinal,

Great set of questions, but I think there are a couple that may not be entirely correct.

A Heap is a table with no CLustered Index. If a Heap has a non-clustered index on it, its still a Heap (Kalen Delaney, Inside SQL Server 2000, Ch9, Data Modification Internals).

The use of NOLOCK should be used with extreme care and IMHO it isn’t considered good practice to use it generally. Itzik Ben Gan demonstrated some issues when using NOLOCK in the UK back in 2006 at Microsoft (see http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx, http://www.sqlservercentral.com/articles/Performance+Tuning/2764/, and http://www.sqlservercentral.com/Forums/Topic330889-192-1.aspx ).

I hope you don’t mind me mentioning these, and if you disagree thats fine, but if you could supply some references that would be great – then I can learn too :)

Regards
Anon
on February 28, 2008 at 10:26 pm SEMI

plz guide me about the queries put in interview for sql-2000.
on March 6, 2008 at 6:11 pm Monika Thotha

Hi Pinal,
I’m working on SQL Server 2005, I want to know how can jobs and schedules be run on SQL server.
I wrote a job as below:

#HEADER
Job_Name=MD0898
Server=NT0SUCP1
#
STEP1
module=isql
db_server=NT0SUCP1
userid=planogbatch
sql=MD089801.sql
mode=no_report
#
FOOTER

this job executes a stored procedure and execute cmd for SP is in MD089801.sql file.
I wan’t to know how to run this job and can I create a daily schedule to run this job in SQL server. We are using mainframe as well in our project, will these jobs be executed by mainframe.
Thanks in advance..

Regards
Monika
on March 9, 2008 at 4:22 am Sr

Can You please send me those links of SQL Interview Questions
on March 11, 2008 at 1:43 am Sudeepta

Hi Mr. Dave,

I have some experience with Databases but I didn’t spend a lot of time working on the same. My job was to install a Database server, creating Replication, taking Backups/ Restore database in development, testing and support environment, which I did mostly using SSMS (Management Studio / Enterprise Manager for SQL 2000).

I want to switch my career into Database Administration and to be specific in T-SQL. Could you please help me in that? I want to know, if you have any series of articles describing the basics of T-SQL. It will be great if you could forward the links to me along with any related document.

Waiting for your response.

Thanks & Regards,
Sudeepta.
on March 12, 2008 at 8:00 am Shanti

Hi Pinal,

The question and answers are very helpful. Thank you!!

Shanti.
on March 12, 2008 at 3:25 pm ramesh

hi dave,

past ten days i saw your website, very helpful for me….. i studying Sql server2005 in beginner stage…. and can you quide me…….

thanks verymuch

With Regards
S.Ram.,
on March 12, 2008 at 4:51 pm saravanan.p

Respected sir

i learning more advanced concepts from you(your site)

thank lot
…………………..saravanan
on March 12, 2008 at 4:55 pm saravanan.p

Respected sir,

can u tell me how to create index and how we use that index

give one example
on March 12, 2008 at 4:57 pm saravanan.p

Respected sir

is ti possible to import a Excel file by writing the sql script…then tell me the script..please
on March 17, 2008 at 3:55 pm Omar

Many thanks Sir! Most appreciated, your sound advise and interview prep material has helped me a lot.

You’re awesome!

Kind Regards,

Omar
on March 18, 2008 at 6:02 pm selvakumar

Hi Mr. Dave,

How to use in PIOVT in SQL server 2000.
on March 18, 2008 at 9:59 pm Ankit Bhurat

Hello Pinal

I just downloaded your interview Question collection. Its really good to help all.

It is really appreciating.
But i want to say something , there is erro in answer of one question.
You wrote difference between truncate and delete command. There is written that truncate can not be rollback, but i implement on my system that trucate can also be rollbacked.

So please update it.

thanks
on March 18, 2008 at 10:29 pm pinaldave

Ankit,

Truncate can not be rolled back you can search about this at
http://search.sqlauthority.com and you will find that I am correct.

Kind Regards,
Pinal
on March 19, 2008 at 7:56 am saravana

Hai dave,

Really a excellent work……

By seeing the information i understand many things……

I am searching job in Oracle filed, just now only i had finished orcale DBA, But i feel difficulty in seeking the Job in this filed, bcoz many of the organisation asking fo experienced person only and not for the fresher..

If you give me some tips., It may be helpful to me….

Thanks & Regads
Saravana
on March 19, 2008 at 10:09 am buvana

hi,

can u tell me ,

how to retrieve the nearest value from a table in sql?
on March 24, 2008 at 6:21 pm Atul

hi i like this site plz send me FAQ in sqlserver 2000 and 2005.Thanks
on March 25, 2008 at 2:21 pm NANDA

hi this is very useful to all
on March 25, 2008 at 11:00 pm Sureshkumar

Hi Pinaldave,
thanx for your this kind of help.these are very helpful to me to attend Interview.really very very thank u.

Suresh(Technical Support Engineer,Singapore).
on March 26, 2008 at 3:48 am Munshi Verma

Your articles on SQL Server are simply adorable!
Keep the good work up Pinal!

Cheers!
on March 26, 2008 at 4:46 pm Ashok

hi pinal,

pls send me interview query and some important query that mostly used ..

Thanks
Ashok
on March 27, 2008 at 1:25 pm gg

Very useful information is given, Thank u so much
Pinal kumar
on March 28, 2008 at 6:04 pm yg

I downloaded file but it seems to be corrupted and does not open by adobe reader. Please correct it or email me.
on March 31, 2008 at 11:43 am Sharad

Thanks Mr. Dave

Good Work !!!
Have you some notes at advance queries of sql server.

I need only the queries.
on April 3, 2008 at 10:32 am Bilam

Thank you Dave sir.

I is lot of help to me.

Thanks
on April 5, 2008 at 9:24 pm rajeev

it is so nice……
on April 5, 2008 at 11:14 pm saurav

hi pinal its so nice 2 see this site……..really amazing
on April 6, 2008 at 11:13 am Jamaldeen

Hi,

Thanks a lot..This is the very best collection for the freshers,also it bestows an overview over sql server..

Carry on….

More kisses from me…
on April 6, 2008 at 2:07 pm SQL SERVER - Popular Articles of SQLAuthority Blog Journey to SQL Authority with Pinal Dave

[...] SQL Server Interview Questions and Answers Complete List Download [...]
on April 6, 2008 at 2:07 pm Deven

hi , Pls send me objective quetion on SQL and Data base design
on April 10, 2008 at 9:25 am Sharad

HII Mr. Dave,

Sir i have a question which was asked to me in an interview.
plz telll me the solution :

if i fire a set of queries or a single query in database, and after that want to know the status of database means, how much rows are affected.how much aredeleted how much are updated and how much are untouched.and how much are inserted.

I think thr is a property in sqlserver by which we can know that staus of database.

Sir Plz help me.

Thank you

Sharad Agarwal
on April 11, 2008 at 4:02 pm Sharad

Hello All,

Is there any person who can help me and can give the solution for my problem.
on April 16, 2008 at 2:21 am Srinivas

hi……..
thanks ……these r very helpful to so many members.v r getting more knowledge from u. thank you
on April 16, 2008 at 2:50 pm Sivakumar

Hi Dave,
This is an excellent collection. It helped me alot to fresh up for my interviews. Great collection. Thanks for your help.

Thanks
Sivakumar
on April 18, 2008 at 7:44 am Ankur

Good job mate, really appreciate the effort you have put in, to put this all together, good on you chief!!
on April 18, 2008 at 3:23 pm vetrivel.M

i need Oracle 9i Forms Interview Questions and Answers
on April 21, 2008 at 10:36 am Sharad Agarwal

Hiiii All

i have a qustion which was asked in an interview. i really want to know the answer of this qustion.

Is there no one who can give the answer of this qustion……. apart from these congratulations………and Good job wishes.
on April 23, 2008 at 11:41 am SQL Server Interview Questions and Answers - Part 4 Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on April 23, 2008 at 11:43 am SQL Server Interview Questions and Answers - Part 5 Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on April 23, 2008 at 1:35 pm raman

hi all
Sir i have a question. i make a page in windows application .net.and i required to pass the path of back file .and then restore database at client side

Sir Plz help me.

Thank you

Regards
Raman kumar
on April 26, 2008 at 8:13 pm waseem

hi,
could you help me resolve this
“select ARABIC name from the table using openrowset”
leaves me with ‘???????????????????’ where as a normal select produces the readable arabic,
any clues other thatn NVARCHAR,

Regards,
May god increase your knowledge.
Waseem Ahmed.
on April 28, 2008 at 5:33 pm Bala

Hi Dave,

This is site is being really very much useful for preparing SQL Server.

Thanks a lot!

Best Regards,
Bala.
on April 29, 2008 at 4:01 pm suresh vishwakarma

hi sir thanks so much very good y articls..
i have one proble related to sql.
i have one table employee (Some Fields) are aslo in employee table..
but cant see my table how many are fields in table.
problem-
i want see the any Row records from the employee table.but not used to condition.(because User not saw the table details)
but user want see the any row records.
how to its possible.
so plz rep me…

regards
suresh kumar
(New Delhi)
on April 30, 2008 at 12:05 pm Kavita

Hello sir,your stuff is very useful for interveiws as well as normal knowledge gaining purpose.Is it that u have interview questions and answers for SQL Server only….can u plz add some .net inteview ques and ans also…I really need them along with SQL Server ques & ans.
Thanks
Kavita
on April 30, 2008 at 6:15 pm devi

hello sir
please provide guidence in sql server to gain knowledge
on May 1, 2008 at 8:29 pm tarun

Hi

You list is very useful for interview. Can u pls add some SSIS or SSRS Questions also.
on May 1, 2008 at 10:36 pm Rocky

Hey Pinal, This is an awesome site and you are the GURU. Do you also have more insight on SSIS ? or Can you suggest some websites where I can start learning. I have worked on SQL Server 2000 a lot with DTS packages and stuff and did the same in 2005. But 2005 offers much more than DTS packages.

I small suggestion. Can you sort these messages in reverse so we get latest posts on top?

~ Rocky
on May 3, 2008 at 10:19 am Mayank

hello sir really a very gud question..
on May 9, 2008 at 12:54 am Tushar

Hi,

Can you please tell me some interview questions on SSRS & SSIS. If you can then it will be very helpful.

Thanks
Tushar
on May 10, 2008 at 2:20 am Faisal

increase the price of all desks (in the item table) by 50% (use update)
on May 13, 2008 at 12:37 pm Dan Ferland

on March 18, 2008 at 10:29 pm203 pinaldave

Ankit,

Truncate can not be rolled back you can search about this at
http://search.sqlauthority.com and you will find that I am correct.

Kind Regards,
Pinal

With all due respect, that is incorrect. When you issue a TRUNCATE command, the pages used by the table are simply deallocated. This deallocation is actually logged and can be rolled back.

Here is a very simple demonstration;

CREATE TABLE T1(RID int identity(1,1) PRIMARY KEY,Col1 int NOT NULL)

INSERT INTO T1(Col1) SELECT 100
INSERT INTO T1(Col1) SELECT 200

SELECT * FROM T1 –2 rows

BEGIN TRANSACTION

TRUNCATE TABLE T1

SELECT * FROM T1 –0 rows

ROLLBACK TRANSACTION

SELECT * FROM T1 –2 rows

Regards,

Dan Ferland
on May 14, 2008 at 3:27 pm Sangeetha

hai,

How to retrieve one last row from the single table, in Sql server.

I tried with the query as follows:

Select * from(Select top 1 * from order Order By orderdate DESC) AS last

Select Top 1 * from oder order by orderdate DESC;

But it is showing error like:

“You cannot use the orderby clause in the derived tabel”

Can you please clear the ambiguty”

Thank You.
on May 15, 2008 at 1:16 pm Dilip

thanks alot man…………..it really helped me a lot………fine, keep doing things 4 ever so,it helps so many persons like me……….thanks once again………bye…….
on May 16, 2008 at 9:42 pm Satyasheel

Mind Blowing..It really helped me out in my project work.
on May 18, 2008 at 10:09 pm Gopal varma

Hi Mr.Pinal

First of all thanks a lot
Really it worked for me

Gopal
on May 20, 2008 at 12:38 pm anks

Add more content !!
on May 23, 2008 at 1:57 am Suresh Ganapathy

Pinal, thanks man. Great stuff. I really appreciate your effort and enjoyed reading your Blog.
on May 26, 2008 at 10:27 am Edgar

Help please..?

i tried to code that enable to transfer from the data in my local drive database to the network database to make network database updated to the new inputs but i got error i used this statement below..

here is my codes….

Dim conn As ADODB.Connection
Dim SQL As String
conn = New ADODB.Connection
conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\pc1\TSmobileData\CopyTSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1″
conn.Open()

SQL = “Select into CopyTSMobile.dbo.TTMtransaction” & _
“Select FROM (’Provider=Microsoft.Jet.OLEDB.4.0;’,’Data Source=c:\TSMobile.mdb;’,’User Id=;’,’Password=h1lt1;’,’SELECT FROM TTMtransaction where sync=0’)”

conn.Execute(SQL)
conn = Nothing

please help please how to fix this error..

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
on May 26, 2008 at 3:44 pm Edgar

i have also tried this one but still not…

Pinal please help…

Dim strSQL As String
Dim cn As New OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph-suman\TSmobileData\CopyTSMobile.mdb;Persist Security Info=true;Jet OLEDB:Database password=h1lt1″)
Dim cmd As New OleDb.OleDbCommand
cn.Open()

strSQL = “select into [CopyTSMobile.mdb.dpo.ttmtransaction] from [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1].[TSMobile.mdb.dpo.ttmtransaction],cn”
cmd.CommandText = strSQL
cmd.Connection = cn
cmd.ExecuteNonQuery() ‘this should create a copy of the table called Opportunity in the Access database
cn.Close()

here is the error in this batch of codes…

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

i tried a lot i never make this run…
Pinal Dave, please help me….
on May 28, 2008 at 4:01 pm Animesh Mishra

i want to the upgraded interview question for ms sql server 2005.
i need this urgently.
thanks
Animesh
on May 29, 2008 at 7:05 am Sunny

Hi, is there a way to find the two salaries with the biggest difference amount between them, and then display both the salaries and the amount difference between them, all in one query( without using FETCH or TOP (Unfortunately I can’t use them, or any other newer MS Server SQL syntax for this project for some lame reason). Thanks for any help.
on June 6, 2008 at 6:54 am SQL Authority News - SQL Server Interview Questions - SQL Related Jobs - DBA Job Description Journey to SQL Authority with Pinal Dave

[...] SQL Server Interview Questions and Answers Complete List Download [...]
on June 10, 2008 at 1:13 pm alexis

Hi Pinal,

I’m your latest fan. I’ll surely make a point to visit this blog again and again. I have my interview tomorrow and I’ve just found what I was looking for.

A great site, a great insight in your answers.

Thanks a billion for your efforts!!

Wish there are more people like you :)

Cheers,
Alexis
on June 17, 2008 at 8:05 pm sanjeet

dear pinal bhai
iam working as a business analyst, iam a functional guy, i want to learn sql, please help me how can i lean in short period, reply me

regards
sanjeet
on June 18, 2008 at 2:00 pm kao

Question about user

Could you help me find all user in sql database by using script.

kao
on June 18, 2008 at 2:53 pm hari

thanks a lot!!!
on June 20, 2008 at 6:38 pm Shreyas

Thnx man.. nce wrk.. :)
on June 22, 2008 at 8:39 pm suresh

sir,
This is suresh. can u tell about data modelling.
on June 25, 2008 at 6:00 pm Tushar

Hi,

Information from this site is helping a lot ….

can you please describe the new programming features from Sql Server 2008.

Thanks,
on June 26, 2008 at 12:07 am George George

Hi Dave,
I have a question. How can I pull the area codes from the following list of phone numbers in a table?

1-630-832-3215
(567)453-1234
546-234-2456
342234567

Your help will be greatly appreciated

Thanks
George
on June 28, 2008 at 7:21 pm Brijesh Patel

hi Pinal,
its been very good knowledge sharing .
its realy a good update for me in SQL.

thanks.

brijesh patel.
on June 29, 2008 at 2:31 pm Vinit gaur

Hi Dave,
This is really good site for all sql server users.

I have questions what is good Cursor or while loop.

I read cursor is beter how can u help me?

Thanks.

Vinit gaur
on July 5, 2008 at 3:28 pm Rajesh

Hi friend,

This is Rajesh . I am looking for job only in SQL Server… Is it possible to get job in SQL server.. I am working in SQL for 1 + year. s it possible ?
on July 10, 2008 at 2:34 am Saritha

Hi

Your website is very usefull. Can you please post some list of interview question on SSIS too.
Thanks
on July 10, 2008 at 4:15 pm Abhishek Srivastava

What is the difference in Procedure and Function in detail. Can we use output parameters in function?
on July 10, 2008 at 11:49 pm keerthi

Hi guys
how to retrive the prvious year to current year(for example today date is mach-2007 then it retrive march2007 to march 2006) please help me
on July 11, 2008 at 3:23 pm Swati

Hi Keerthi
Check this,
SELECT DATEADD(Year,-1,GETDATE())
on July 11, 2008 at 3:51 pm Swati

Hi Abhishek Srivastava,

Procedure is a saved block of statements that can take and return user-supplied parameters.
Function is block of statements (routine) that only returns a value.

SQL function returns a value, where as a procedure does not

‘OUTPUT’ option can be used with ‘create procedure’ to specify return parameter

It cannot be used with ‘create function’ query

Regards,
on July 11, 2008 at 4:02 pm Swati

Hi Kao,
Try this to find all user in sql database by using script in sql server 2005(Select Master database)

Select name from sys.login_token

In SQL Server 2000 u need to check syslogin table in Master database

Regards,
on July 11, 2008 at 4:21 pm Swati

HI Kao,
U can also get it using
select name from sys.sql_logins
This will give u list of users (excluding SQL users like ‘public’)
on July 12, 2008 at 8:34 pm Gaurav Arora

Hi Pinale!

First of all I congrates to you for your achievement as MVP. May be am very laet to reach here but I thought I have reached here.

There is no doubt this is a good site and its really helpful to peoples like me.

Basically, I am .Net Developer but like to work with SQL using its stuff. And today, I have found your site which meets my requirements. I have opted some posts to my site – http://stuff4mdesktop.blogspot.com/

I think as a Technical guy, I need to add more stuff in my Technical skills.

Your comments awaited towards my thinking and may be I was wrong?

Keep it up dear!

Thanks,
Gaurav Arora
http://stuff4mdesktop.blogspot.com/
on July 14, 2008 at 4:40 pm Swati

Guys I have few queries
A.
Here is the code

1. if exists(Select count(*) from emp where deptno=20)
2. goto sales_pro
3. else
4. goto notexists_pro
5.
6. sales_pro:
7. begin
8. print ‘ I am in sales_pro !’
9. select * from emp where deptno=20
10. end
11. notexists_pro:
12. begin
13. print ‘ I am in notexists_pro’
14. print ’sorry no employee exists’
15. end

Query:
if the control goes in ‘6. sales_pro:’ label, after executing upto line 10, it continues with the ‘11. notexists_pro:’ label staments. How to break / stop the execution at line 10?

B.
Can I alter the existing column of the table, and add Identity(1,1), if there is no row inserted in the table?

Plz let me know
on July 15, 2008 at 2:38 am Imran Mohammed

@Swati

Your first question.

I have no idea why you are using that terminology, when you can use a simple while loop with break. This would solve the problem. Again I think I did not understand your question properly.

Your second Questions.

YOU can add identity column to a table after you create a table, which has no records or which has some records. Yes You can still add a identity column if your table has records ( data ) in it.

For sure you cannot do this through query analyzer or code.
You can only do this through Enterprise manager or object explorer in SSMS ( 2005).

Select the table in which you want to create an identity column, right click table – > click modify -> create a new column, select the datatype , int, smallint, bigint… ( no characters) and below in column properties scroll down a little bit and you can see identity specifications, expand that and click on the cell and you should be able to see an option YES. Select it and click save and you are good to go.

This will create an identity column in the table, but if there exists some previous data inside the table… identity column will select its values automatically, depending upon your seed.

Hope this helps.
Thanks,
Imran.
on July 15, 2008 at 5:31 pm Swati

Thanks Imran!

Regards,
Swati
on July 18, 2008 at 5:04 pm Giribabu

Very good to read the articles written by you, thanks once again.
on July 24, 2008 at 4:55 pm Arindam Bose

Hi DAVE

can u send me the technical interview question for MS SQL Server.

regards
Arindam
on July 26, 2008 at 12:17 am UMA SHANKAR GUPTA

Dear Sir

I am currently working in Network Ltd (RETAIL COMPANY) as a Sr.MIS Executive, & Previously worked in Vishal Retail Ltd as a Sr.MIS Executive, I was handling the Project for RXL (Retail Excel ) POLARIS RETAIL INFO TECH LIMITED(PRIL) Discover, RXL life 5th Show Room. & SAP MM as End User.

Total Experience – 3 Year 5 Month .

Qualification :— BScIT & PURSING MCA

09910643567

i m interested SQL so just i am starts no any idea book , can you mail link or .pdf formate book

Thanks
Uma Shankar
on July 30, 2008 at 11:11 am Krishna

hi sir,

can u send me the technical interview question for MS SQL Server2005.

And my Question is

I have 4 tables.. T1, T2, T3, T4.. these tables have the same structure and they store the information entered in different years.. T1 stored 2002, T2 stored 2003, T3 stored 2004 and T4 stored 2005.. i want to copy contents in T1 to T2, T2 to T3, T3 to T4 and T4 to T1.. how do i do that? Temp tables cannot be used..

Please send me the Answer to the above question

ThankQ
on August 5, 2008 at 12:15 pm sambasivarao

thank you very much sir, i am a programmer and i have 6+ years experience i want to change my position to DBA kindly suggest me how can I start my career as a SQL DBA. Thanks.
on August 11, 2008 at 8:39 pm mira_ro

Thanks, Dave

I am new in the DBA world and your work help me a lot to improve my abilities. Thank you again. Great job.

Mira
on August 12, 2008 at 7:12 pm Vinod Sharma

Sir ,

Thanx a lot for this sort of question bank.It has all what was I looking for.Now I am pretty confident that I’ll grabe an wonderful Opportunity very soon ……

Finally ….Thanx again
on August 13, 2008 at 9:22 pm mkcprasad

what is the use of anonymous
blocks in pl/sql where wu use in real time senireo
on August 19, 2008 at 3:51 pm Jags

Nice Job Dave,

can u send me the technical interview question for MS SQL Server2005.
on August 21, 2008 at 1:28 pm Briju

want to prepare for interview and test my knowledge
on August 22, 2008 at 5:51 am Imran Mohammed

@Birju,

Try checking these free exams online,

http://www.mssqlcity.com/TestExams.htm

Hope these helps.
Imran.
on August 25, 2008 at 8:29 am Prashant Shetake

Dear Pinal
This Blog is very helpful to increase the knowledge.
Thanks

Regards
Prashant Shetake
on August 25, 2008 at 3:03 pm Gaurav Trivedi

Hie Pinal,

Thanks alot!!!
on August 27, 2008 at 3:29 am Manasa

Hi Dave,

Excellent Job!!
I wish u al success in ur upcoming endeavours.

Dave,Could you pls send me technical interview questions for SQL Server 2005

Thanks & Regards
Manasa
on September 1, 2008 at 6:09 pm Pakash

Dear Sir,
Could you pls send me MBA (Human Resource $ Marketing Mnagement) interview questions and latest topics of GD.

THANKS……………………..
on September 9, 2008 at 3:57 pm SG

Hi Pinal

Nice questions set… Do you have the same on SQL Server 2005? I am going to attend some interviews next week. If possible, kindly post the questions related to SQL Server 2005.

Thanks
SG
on September 11, 2008 at 10:36 pm Sathish

Hi Dave Sir,
Sathish here.Sir, Can u plz tell me the link or else send me the link of Sql Interview Appitiude Questions Sir,plz Sir.

Regards ,
Sathish Ganesh.T
on September 12, 2008 at 11:17 pm Manoj

Hey Pinal,

Good Piece of Information.
Thanks for sharing :)
on September 15, 2008 at 12:33 am Ajay

it’s gr8 help
on September 16, 2008 at 10:50 am Manju

THANKS SWATI,

Ur sharing good info..

Regards

Manju
on September 16, 2008 at 3:31 pm nayana kolhe

Nice one Thank you
on September 19, 2008 at 2:06 pm Erdal Findik

Hi,

I installed recently SQL2008 on a new server. I moved my databases from the old server(SQL 2005) to the new one.
Everything is woeking fine…except Full text search.

FTS is very slow on SQL 2008, I deleted and recreated the FTS from scratch but still very slow.

Can somebody has a similar issue?

Thx.
on September 20, 2008 at 11:26 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:23 pm SQL SERVER - Database Interview Questions and Answers Complete List Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on February 26, 2009 at 5:22 pm SQL Server Interview Questions and Answers - Introduction Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on February 26, 2009 at 5:24 pm SQL Server Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

[...] 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 [...]
on April 30, 2009 at 11:44 am SQLAuthority News - Best Downloads on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL Server Interview Questions and Answers Complete List Download [...]