The interview is a very important event for any person. A good interview leads to good career if the candidate is willing to learn. I always enjoy interviewing questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is a very large subject and not everything is usually asked in interview. In interview what matters the most is the learning attitude.
Interview Questions
An interview Question and Answer discussion can be beneficial 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.
I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the questions, please send me a mail or write a comment about interview questions.
Click here to get free chapters (PDF) in the mailbox
You can also connect with me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
160 Comments. Leave new
Hi Panal,
Great going, We are benefiting a lot form your site.
Thanks,
SK
Actually, I am .NET developer. But most of the compnies requires that we have strong knowledge of SQL too. You article helped a lot.
One thing I want to share with you. Some of my friends gave interview in Patni Computers, Gandhinagar for .NET. They were asking questions about SQL only (for .NET positions). They were asking question of Database administrator level (Database mirroring, SSIS, DTS packages etc.) Do you think, all .NET developer should have DB Admin level of SQL knowledge?
I need some help in such a way that, what is the difference between
“Casting” And “Convert.ToXXX()” In SQL Server.
Refer this post. It describes the difference between the two
I am calling sp_oaCreate to add DTS package in SQL Server 2008 64 bit environment.
I am getting an error “Class not registered”. When I googled I found that MS do not support sp_oa*** system stored procedures in SQL Server 2008 64 bit environment.
My requirement is to execute the DTS package from Stored proc and get the data. I am using sp_oa*** sp to do this.
Now I cannot use sp_oa*** in SQL Server 2008 64bit envi. Is there any other solution?
Hello Pinal
please check if you can help about the below topic
This is just an interview question i got from one of interviewer. for which I did not knew answer.
Any insight on this welcome..
If we have dimension tables loaded and used surrogate keys for processing. Now what happens is the we loaded fact table too. What happens if the dimension tables gets corrupted and we do not have any backup of the dimension tables. So will the fact table still funstional and will give us correct results. Is there any we can get out dimension tables back from the fact table (keep in mind there is surrogate key used).
Do we need to rebuild the fact table by rebuilding the dimension table. Will it cost much time reuilding fact table. Is there any approach besides this.
Thank you, Pinal! Much appreciated.
Hi Pinal,
Can you please help me with a script to automate the backup process and archive the old backups which also includes the transaction log backups and differential.
Thank you
Here is the one for automating the backup of the database
Hi Pinal,
I have learnt lot of thing from your site.
I want to know more about performance tuning in sql server 2005. What is the possible questions on performance tuning.
You can identify the possible issues using Microsoft’s SQL Server 2008 Upgrade Advisor tool.
Please let me know if it is possible
Hi Pinal Dave
I downloaded your “SQL Server 2008 Interview Questions and Answers”. It’s fanatastic.
Many Thanks.
Regards,
Ganesan.
Hi there,
I created a publication using sql 2008 serve. the publication was deleted. I want to know if there is a way to discover the computer that connected to the server and deleted this publication. Any Help?
In the following table, RESPONSES counts how many times SEGMENT has responded on CHECKED date.
CREATE TABLE #TEST (ID INT, SEGMENT CHAR(1),RESPONSES INT,CHECKED SMALLDATETIME)
INSERT INTO #TEST VALUES (1,’A’,0,’2009-05-01′)
INSERT INTO #TEST VALUES (2,’B’,1,’2009-05-01′)
INSERT INTO #TEST VALUES (3,’C’,0,’2009-05-01′)
INSERT INTO #TEST VALUES (4,’A’,0,’2009-05-02′)
INSERT INTO #TEST VALUES (5,’B’,2,’2009-05-02′)
INSERT INTO #TEST VALUES (6,’C’,1,’2009-05-02′)
INSERT INTO #TEST VALUES (7,’A’,1,’2009-05-03′)
INSERT INTO #TEST VALUES (8,’B’,0,’2009-05-03′)
INSERT INTO #TEST VALUES (9,’C’,2,’2009-05-03′)
Write a query to summarise total RESPONSES for each SEGMENT and each CHECKED date, shown in following format:
CHECKED A B C
2009-05-01 0 1 0
2009-05-02 0 2 1
2009-05-03 1 0 2
Do NOT hard-code segment names (i.e. “A”, “B”, “C”) into your solution, so the solution remains functional if more segments are added (e.g. “D”) or segments are renamed (e.g. “A” “X”).
Refer this dynamic PIVOT post which does what you want
I want to get certification for SQL Server 2005/2008. Can you please provide me the reading material for the same.
Thanks.
Thanks Pinal Dave , You are doing Very Good work for everyone.
its grt
Hi Pinal,
I got question in my interview what he ask me like if he want to fetch 1 million records what type of query will need to write?
Please give me this answer I am confused what should I answer for this question I asked other friends also but they don’t give me the write answer and I have hope I get it from you.
Regards
Khan
truncate can be rollback,but delete can’nt rolback.
This is absolutely wrong. Where did you read this?
Hi Rakhi,
From where did you get this knowledge that “truncate can be rollbacked, but delete can’t”, so untrue?
I’ve also heard people speaking contrary that “delete can be rollbacked, but truncate cannot”.
But the real thing is that both can be rollbacked, when applied inside the transaction.
~Manoj
Adding more to my comments above:-
“Without or Outside Transactions”, When DELETE/TRUNCATE is applied outside the transaction the changes are committed as soon as you execute sql statements. Thus nor DELETE neither TRUNCATE can be rollbacked. Thus in order to retrieve DELETED/TRUNCATED records you have to restore the last backup and run the transaction logs until the time DELETE/TRUNCATION happened.
Friends
in case of outside the transaction ..you can only rollback delete not truncate.
thnx
@Ankur, have to tested this at your end or are you just spreading word of mouth.
Can you show us how can do do that? Like an example.
Thanks you so… much . I’m appreciating your work .
Keep doing
Hi Sir
please send me interview question (frequently asked).