by

SQL Server Interview Questions and Answers Complete List Download

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

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

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

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


Other popular Series
SQL SERVER Database Coding Standards and Guidelines Complete List Download
SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
DBA Database SQL Job List Search

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

About these ads

302 thoughts on “SQL Server Interview Questions and Answers Complete List Download”

  1. Hello Dave,

    Congratulations! Excellent Web Site!

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

    Alan

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

  6. 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)

  7. Your resume is impressive. Sent email with job opening. Please forward it to others, who can be good fit to position.

  8. Good work.
    Who are you? Every search related to SQL brings up your story or link to your story.
    Good storys.

  9. 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,

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

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

    Good work and keep writing…

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

  15. 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)

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

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

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

  21. 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)

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

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

  28. It’s really very informatics website and question and answered have been given in detail, thanks for providing us so nice materials.

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

    Keep it up and let us updated as well.

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

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

  43. …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.

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

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

    Thanks in Advance

  46. 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 !!!

  47. 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)

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

  51. Hi Dave,

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

    Please help
    Thanks and Regards,
    omprakash

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

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

  55. Pingback: SQL SERVER - Database Coding Standards and Guidelines - Introduction Journey to SQL Authority with Pinal Dave

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

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

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

  64. i have done software engineering and i m preparing for the interview so please send me some quetions which is related to my course.

  66. Pingback: SQLAuthority.com News - Journey to SQL Authority Milestone of SQL Server Journey to SQL Authority with Pinal Dave

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

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

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

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

  73. Hi Dave,

    As Mr.Wanderer question regarding Delete/Truncate difference,I cant able to understand,can u explain with example..
    Regards
    P.Praveenkumar

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

  75. 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)

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

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

  79. Hi dave,

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

    Regards,
    Prasanna S

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

  81. Hi Dave,

    Thats Really Great Job…..

    All the Best…………

    Parmod Bhardwaj
    Gurgaon

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

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

  85. Pingback: SQL SERVER - Data Warehousing Interview Questions and Answers - Introduction Journey to SQL Authority with Pinal Dave

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

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

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

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

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

  93. Pingback: SQL SERVER - What is SQL? How to pronounce SQL? Journey to SQL Authority with Pinal Dave

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

  96. HELLO SIR I’M FRESHER I WANT TO JOB BASED ON SQL SERVER.WITH .NET.SO PLS GIVE ME IMPORTANT QUESTIONS FOR INTERVIEW.

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

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

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

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

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

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

  106. Pingback: SQLAuthority News - JavaScript - Beginning Interview Questions Journey to SQL Authority with Pinal Dave

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

    Thanks Again….

  111. 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;

  112. 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 )

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

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

  116. Pingback: JavaScript - Beginning Interview Questions Joey JavaScript

  118. Pingback: SQLAuthority News - Job Opportunity in Ahmedabad, India to Work with Technology Leaders Worldwide Journey to SQL Authority with Pinal Dave

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

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

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

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

  125. Hi,

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

    Thanks,

    Pradeep

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

  128. Excellent site!!!
    It is gr8 deal of help for many!!!
    Thanks a lot for having such a site with all this information!!

  130. 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 …..

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

  139. 1) What is diff. between Sql server 200 and sql server 2005 .
    2) Which is best wesite for Downloading Questions and answer for certifications.

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

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

    Thanks,
    Raj.

  142. 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…

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

  145. Pingback: 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

  146. hi,

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

    Thanks again

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

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

  157. 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) ?

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

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

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

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

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

  165. Pingback: SQL SERVER - Most Frequently Asked Generic Interview Questions Journey to SQL Authority with Pinal Dave

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

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

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

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

  172. Pingback: SQLAuthority News - Find Job Related to SQL Server and Database Administrator and Interview Questions and Answers Journey to SQL Authority with Pinal Dave

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

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

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

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

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

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

  196. 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.,

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

    You’re awesome!

    Kind Regards,

    Omar

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

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

  208. 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).

  212. I downloaded file but it seems to be corrupted and does not open by adobe reader. Please correct it or email me.

  213. Thanks Mr. Dave

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

    I need only the queries.

  217. 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…

  218. Pingback: SQL SERVER - Popular Articles of SQLAuthority Blog Journey to SQL Authority with Pinal Dave

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

  222. hi……..
    thanks ……these r very helpful to so many members.v r getting more knowledge from u. thank you

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

  224. Good job mate, really appreciate the effort you have put in, to put this all together, good on you chief!!

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

  227. Pingback: SQL Server Interview Questions and Answers - Part 4 Journey to SQL Authority with Pinal Dave

  228. Pingback: SQL Server Interview Questions and Answers - Part 5 Journey to SQL Authority with Pinal Dave

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

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

  231. Hi Dave,

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

    Thanks a lot!

    Best Regards,
    Bala.

  232. 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)

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

  235. Hi

    You list is very useful for interview. Can u pls add some SSIS or SSRS Questions also.

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

  238. Hi,

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

    Thanks
    Tushar

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

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

  242. 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…….

  246. Pinal, thanks man. Great stuff. I really appreciate your effort and enjoyed reading your Blog.

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

  248. 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….

  249. i want to the upgraded interview question for ms sql server 2005.
    i need this urgently.
    thanks
    Animesh

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

  251. Pingback: SQL Authority News - SQL Server Interview Questions - SQL Related Jobs - DBA Job Description Journey to SQL Authority with Pinal Dave

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

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

  258. Hi,

    Information from this site is helping a lot ….

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

    Thanks,

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

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

    thanks.

    brijesh patel.

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

  262. 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 ?

  263. Hi

    Your website is very usefull. Can you please post some list of interview question on SSIS too.
    Thanks

  264. What is the difference in Procedure and Function in detail. Can we use output parameters in function?

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

  267. 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,

  268. 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,

  269. 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’)

  270. 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/

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

  272. @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.

  275. Hi DAVE

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

    regards
    Arindam

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

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

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

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

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

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

    Regards
    Prashant Shetake

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

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

    THANKS……………………..

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

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

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

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

  298. Pingback: SQL Server Interview Questions and Answers - Introduction Journey to SQL Authority with Pinal Dave

  299. Pingback: SQL Server Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  300. Pingback: SQLAuthority News - Best Downloads on SQLAuthority.com Journey to SQL Authority with Pinal Dave

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

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

Comments are closed.