SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2012 - Interview Questions (Sample)


SQL 2012 - Interview Questions (Sample)

Author
Message
subramaniam.chandrasekar
subramaniam.chandrasekar
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 235

Hi Guyz,
Warm greetings !
I'm very happy to post some interview questions today which I'd faced recently via a Skype.
Could you please help me to note down the answers ?

Question 1 :
Using SQL Server 2012 features build two select statements that will calculate the difference in sales between a month end and the last and next month values.

Please use pseudocode if uncertain of exact syntax.

Monthe_end Sales
31/01/2016 23,070
29/02/2016 12,236
31/03/2016 24,056

Question 2 :

What are row and column store indexes, explain ?

Question 3 :

Build a SSIS diagram / pseudo code showing the steps with comments from a raw data in a CSV format to a fact table in a data warehouse.
The process needs to cope with the dimension data that may not be in the dimension tables ?

Thanks
Subbu.


anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62754 Visits: 8598
As these are interview questions, please post what your answers where and the people here will guide you on the rights and wrongs, we do not give the answer to interview questions as its all about your learning and showing your knowledge, so please detail your answers.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


subramaniam.chandrasekar
subramaniam.chandrasekar
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 235

Question 2 :

What are row and column store indexes, explain ?

Row store indexes are used in OLTP tables in DB, where the indexes are created on transactional tables and the indexes are stored as pointers in RAM like rows.

Column store indexes are used in OLAP tables In data warehouse, where the indexes are created on analytical / non transactional tables.

Question 3 :
SSIS for loading data into fact / dim table

Data flow task ------------------------------------------------------ > Flat file source
| ( precedence constraint) |
Execute sql task Data conversion
|
Oledb source


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221683 Visits: 33561
subramaniam.chandrasekar - Thursday, December 14, 2017 2:19 AM

Question 2 :

What are row and column store indexes, explain ?

Row store indexes are used in OLTP tables in DB, where the indexes are created on transactional tables and the indexes are stored as pointers in RAM like rows.

Column store indexes are used in OLAP tables In data warehouse, where the indexes are created on analytical / non transactional tables.


But what about using nonclustered indexes on a clustered columnstore? What about using nonclustered columnstores on a clustered row store index? I think the answer here is quite a bit more nuanced and detailed.


----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
subramaniam.chandrasekar
subramaniam.chandrasekar
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 235
Hi,
that's right.
While working with In memory tables in SQL Server 2014 or later, We can get this queries raised, But what I'd mentioned is the basic difference between row store and column store indexes in SQL. If some one has better answers, I'd like to note down.

Besides, Can you please help me with flow chart of SSIS loading CSV file data to a fact table by coping dim table data.
Your response will be helpful.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221683 Visits: 33561
subramaniam.chandrasekar - Thursday, December 14, 2017 5:12 AM
Hi,
that's right.
While working with In memory tables in SQL Server 2014 or later, We can get this queries raised, But what I'd mentioned is the basic difference between row store and column store indexes in SQL. If some one has better answers, I'd like to note down.

Besides, Can you please help me with flow chart of SSIS loading CSV file data to a fact table by coping dim table data.
Your response will be helpful.


But your answers doesn't address what the actual differences are, just general usage and not even why that general usage is better? Aren't columnstore indexes compressed better than row store indexes? Why wouldn't we use the columnstore then in place of row store in an OLTP situation?

I love technical interviews.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
subramaniam.chandrasekar
subramaniam.chandrasekar
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 235
Hi,
Thank you for your reply.
When Column store indexes are created in an OLTP / OLAP tables, the tables will become read only. So in SQL 2012 version, this index was used for analysis services.
I heard someone said like column store indexes will store the pointers in Pages not like OLTP indexes.(tree map concept)
Any have I would like to get in depth knowledge of knowing the difference between row store and column store indexes.
Anymore responses will help !!!
Scott Coleman
Scott Coleman
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14843 Visits: 1700
Simplistic answer for Question 3: Import CSV data into data warehouse.
Import the CSV file into a staging table.
For each dimension:
Find all new dimension values in the staging table, insert them into the related dimension table.
Import the staging table into the fact table once all dimension values are defined.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)SSC Guru (506K reputation)

Group: General Forum Members
Points: 506932 Visits: 44273
subramaniam.chandrasekar - Thursday, December 14, 2017 5:12 AM
Hi,
that's right.
While working with In memory tables in SQL Server 2014 or later, We can get this queries raised, But what I'd mentioned is the basic difference between row store and column store indexes in SQL. If some one has better answers, I'd like to note down.

Besides, Can you please help me with flow chart of SSIS loading CSV file data to a fact table by coping dim table data.
Your response will be helpful.


To be brutally honest, these questions were asked by people that expect YOU to know the job they're trying to hire someone for. They're not expecting US to know the answers for you because we're not the ones that are going to be interviewed for the position you're applying for nor are we the ones they're going to have to put up with if you somehow get the job but can't do it. You need to hucker down and demonstrate that you know at least the first and most basic skill for any job in IT and that's how to search the web for information, find it, and then study it. None of these questions are so difficult that you can't do that.

The problem is that when you get to the interview, you won't be allowed to use the internet to answer the questions. You need to look at the description of the job and anticipate the questions they will ask and then prepare to be able to talk at length about any of the subjects listed on the job description.

If you can't do that, then your interview will be unsuccessful, or at least is should be if the interviewers are worth their salt.

And asking people to answer your interview questions for you is a despicable act.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
subramaniam.chandrasekar
subramaniam.chandrasekar
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 235
Jeff Moden - Saturday, December 16, 2017 12:27 PM
subramaniam.chandrasekar - Thursday, December 14, 2017 5:12 AM
Hi,
that's right.
While working with In memory tables in SQL Server 2014 or later, We can get this queries raised, But what I'd mentioned is the basic difference between row store and column store indexes in SQL. If some one has better answers, I'd like to note down.

Besides, Can you please help me with flow chart of SSIS loading CSV file data to a fact table by coping dim table data.
Your response will be helpful.


To be brutally honest, these questions were asked by people that expect YOU to know the job they're trying to hire someone for. They're not expecting US to know the answers for you because we're not the ones that are going to be interviewed for the position you're applying for nor are we the ones they're going to have to put up with if you somehow get the job but can't do it. You need to hucker down and demonstrate that you know at least the first and most basic skill for any job in IT and that's how to search the web for information, find it, and then study it. None of these questions are so difficult that you can't do that.

The problem is that when you get to the interview, you won't be allowed to use the internet to answer the questions. You need to look at the description of the job and anticipate the questions they will ask and then prepare to be able to talk at length about any of the subjects listed on the job description.

If you can't do that, then your interview will be unsuccessful, or at least is should be if the interviewers are worth their salt.

And asking people to answer your interview questions for you is a despicable act.

Hi,
Knowledge means sharing. You should know this basic thing.
Irrespective of the working experience on a domain / technology , If a person with less experienced wants to know something and posting a query / questions in general forum, it'll get notified first and answered by the techies. this is a general thing I've seen so far.
From your latest post, I can understand you're not ready to share your thoughts on the queries. If so let others can share their answers here, So that the knowledge can be shared among ours.
Its not like I know something and I don't wanna disclose with you, If you're with this mindset, Please dont comment here and dont reply to my post.
If you're not ready to share your thoughts, I even post this queries and get the answers somewhere else, SQL has millions blogs around the world not like Oracle.
If I've posted some queries in this SQLServerForum, that doesn't mean that I dont know the answers.
It is like sharing knowledge and growing.
Hope you're able to understand what I mean Jeff Moden.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search