Interview Questions and Answers are now updated with the latest questions and answers. New Location: SQL Server Interview Questions and Answers.
Click here to get free chapters (PDF) in the mailbox
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 gotten many emails about complete series.
Top most requests were 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 done I ask the same questions in interviews which I administrate. The answer is YES. Though, I have a 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.
An interview Question and Answer discussion can be very helpful to both these individuals. It is simply a way to go back over the building blocks of a topic. Many times a simple review like this will help “jog” your memory, and all those previously-memorized facts will come flooding back to you. It is not a way to re-learn a topic, but a way to remind yourself of what you already know.
Download SQL Server Interview Question and Answers Complete List
Complete Series of SQL Server Interview Questions and Answers
Introduction, Part 1, Part 2, Part 3, Part 4, Part 5, Part 6
SQL Server Interview Question 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
Connect with me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
282 Comments.
HELLO SIR I’M FRESHER I WANT TO JOB BASED ON SQL SERVER.WITH .NET.SO PLS GIVE ME IMPORTANT QUESTIONS FOR INTERVIEW.
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
this very useful site for sql learners.
send me any news about SQL SERVER.
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
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
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.
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.
Dear, u have done a very excellent job,
It help me alot.
thanks
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
Thanks! It’s very very Gooooooooood information.
Very Nice and Extrodinary Stuff provided and I expect more such kind of stuff from Pinal.
Thanks Again….
Very Nice and Extrodinary Stuff provided but Some more examples are needed and I expect more exemplified stuff from Pinal.
Thanks Again….
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;
Thanks a lot Pinaldave for your response.
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
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
THello sir,
send me sql server FAQ list please
regards
vijay
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
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
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