SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

jacobsmall SQL SERVER   Interview Questions and Answers   Guest Post by Jacob Sebastian   Day 30 of 31Jacob Sebastian is a SQL Server MVP, Author, Speaker and my personal friend. Jacob is one of the top rated expert in SQL Community. Jacob wrote the book The Art of XSD – SQL Server XML Schema Collections and wrote the XML Chapter in SQL Server 2008 Bible. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to participate in T-SQL Challenges.

I am very much impressed with the interview questions series Pinal has been doing since the beginning of this month. When he asked me whether I am interested to contribute, I readily agreed. I see that Pinal and various other contributors have done a good job covering several interesting SQL Server areas. I thought of adding a few question from the XML side for the sake of completeness.

The purpose of the questions and their answers given below is to ensure that the people who read them and learn them will get a basic understanding of the XML functionalities provided by SQL Server. SQL Server 2000 introduced a very interesting function OPENXML() which allowed us to read the content of an XML document from TSQL. SQL Server 2005 added more XML support by introducing XML data type, support for XQuery,  XML Indexes and XML Schema Collections. Together, they provide a very power set of functionalities that empowers you to perform most of the XML processing requirements you might come across in your day-to-day programming life.

Most common XML operations can be classified into 3 categories.

  1. Read the content of an XML document and produce a result set
  2. Generate XML documents from relational data
  3. Validate the content of an XML document against a pre-defined schema/structure

SQL Server 2005 introduced support for all these operations by exposing a number of XML related features. XQuery support in TSQL allows you to read the content of XML documents. XQuery functions along with OPENROWSET(BULK…) can be used to access XML documents stored in the disk.

SQL Server 2000 introduced “FOR XML” which allows you to transform the result of a SELECT query into XML format. This is further enhanced in SQL Server 2005 to allow you create XML documents with almost any structure and shape.

SQL Server 2005 introduced XSD support in the form of XML Schema collections which allows you to describe and validate XML data type variables and columns.

The short set of questions and answers given below intend to summarize the basic XML functionalities that SQL Server provides.

What is XSD?

XSD stands for XML Schema Definition Language, which is a W3C standard specification that allows you to describe and validate and XML document.  Once an XSD schema is created, a Schema Validator can verify an XML document against a schema and say whether the document follows the rules and structure defined in the XSD document.

What is XML Schema Collection?

XML Schema Collections are SQL Server database objects, just like objects like tables, views, stored procedures etc. An XML Schema Collection contains definition of one or more XSD documents. Once an XML Schema Collection is created, it can be used to validate XML data type values.

What is TYPED XML?

An XML data type value – variable or column – that is bound to an XML Schema Collection is known as TYPED XML. A TYPED XML variable or column should follow the validation rules defined in the XML Schema collection. When a TYPEX XML value is modified (or value is assigned), SQL Server will perform a schema validation and the assignment/modification will succeed only if the new value passes all the validation rules defined in the XML schema collection associated with it.

What is XQuery?

XQuery is a W3C recommended language for querying XML documents. In simple words, we could say, “XQuery is to XML, what SQL is to a relational database”. XQuery allows you to query and modify XML documents.

What are the advantages of using XQuery over OPENXML()?

XQuery can be used as part of a TSQL SET based operation. OPENXML can process only one XML document at a time where as XQuery can process more than one XML document in a single TSQL statement.

XQuery can be used to modify (insert/update/delete elements and attributes) or re-shape the XML document whereas OPENXML allows only reading XML data.

What is a FLWOR operation?

FLWOR stands for ‘For, Let, Where, Order By and Return’ which is an XQuery feature that allows you to perform complex operations on an XML document. The FLWOR operation allows you to iterate over the elements/attributes of an XML document and extract specific information from it or generate a completely new XML document.

How do you solve performance problems with XML operations?

There are no ‘correct’ answers to generic performance questions. A proper solution can be suggested only after examining the specific use case and environment.  However, a few recommendations are as follows:

If you are dealing with UNTYPED XML, try to convert them to TYPED XML. When using TYPED XML values, SQL Server has got better understanding of the structure of the XML document, data types of elements and attributes etc.  This allows SQL Server to process the XML document more efficiently.

XML Indexes can be used to speed up queries that read information from XML data type columns.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

Portrait3 SQL SERVER   Interview Questions and Answers   Guest Post by Feodor Georgiev   Day 29 of 31Feodor Georgiev is a SQL Server database specialist with extensive experience of thinking both within and outside the box. He has wide experience of different systems and solutions in the fields of architecture, scalability, performance, etc. Feodor has experience with SQL Server 2000 and later versions, and is certified in SQL Server 2008. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series.

About a month ago I wrote a post Job interviewing – the right way and for the right reasons in which I explored the factors involved in a productive job search and the proper points of view involved in the process. This blog post can be considered as part 2 of the series, which in its turn extends the topic to a broader level: incentives and motives leading to mutual compromises during a job interview.

In short, the job interview is like an enchanted dance between a potential employee and a potential employer which plays a defining role in their entire collaboration. Of course, both sides have sometimes contrasting motivations, different priorities and are moved by different incentives, but in the end, the interviewing process is the host of all unlimited possibilities.

I do consider the incentives to be one of the most powerful energy sources in human history and as examples of incentives I would point to monetary (good salary or bonus), psychological (a good pat on the back), self-appreciation (the perception of self-overachievement) and so on. (Fear is also an incentive, but I sincerely hope that no company runs on this kind of evil fuel!)

I will mention motivation again later on, but for now, let’s look at some essential aspects of quality points of view during the interviewing and hiring process.

‘Absolute ends meet in the middle’ – some proverb, I think…

I am not really certain if this is a real ancient proverb or some colorful phrase I just came up with, but in either case it seems true.

During the interviewing process the main goal boils down to ‘defining the absolute qualities wanted, absolute qualities not wanted and all the gray areas in-between’.  And this is valid, of course for both sides – the interviewer and the interviewee. (I should be really calling both sides ‘interviewers’, since both sides are interviewing each other on equal grounds, unless one of them is really desperate.)

So, what are the absolute qualities wanted? Let’s say, in the specific case of interviewing a DBA for a developer position: the understanding of the Query Optimizer and the techniques of T-SQL development are essential. From the point of view of the potential employee an absolute quality expected would be most likely the proper pay.

How do the absolute ends meet in the middle? Let’s say that the job candidate in the example above has great understanding of the QO, great T-SQL development skills, but has never worked with VLDBs and is consciously willing to improve in the area, simultaneously wanting to get as close as possible to his own ‘absolute end’ – the proper pay.

Here we enter a gray area where the two absolute ends would (hopefully) meet: the potential employer will suggest that the new experience would be of great value, the potential employee will acknowledge the fact, but will point out that it is in a way a long-term investment, and eventually the absolute ends will meet due to mutual compromise.

This is a simplified example, but in general the mechanics of negotiation flow by these rules.

How is hiring an employee similar to buying a car

A while back we decided to buy a car and we started looking at the dealerships around the city. It really amazed me how similar the process is to interviewing.

The simple truth about all cars we saw is that ‘all of them will take you from point A to point B’, however, the different cars…

  • … could take shorter or longer time
  • … can or cannot go off-roading (sometimes even the ones looking like they were designed for off-roading are not meant for that!)
  • … are more or less expensive on average per kilometer (a fast sports car can get me faster to point B, but dividing the price of the speeding tickets to the kilometers travelled would be an additional cost)
  • … and so on

During interviewing it is also very important for each side to note and calculate the capabilities, costs and timing of the other side and decide whether the ROI (time and effort nowadays are serious investment considerations!) is valuable enough.

And, of course, the potential employee has to have a potential to fit the team in a way which does not disrupt the morale or the rhythm.


For a moment I would like to get back to my previous article on Interviewing (http://blog.sqlauthority.com/2011/06/20/sqlauthority-news-job-interviewing-the-right-way-and-for-the-right-reasons-guest-post-by-feodor-georgiev/). In that article I made a point that the most valuable quality in a potential employee is the level of curiosity.

Curiosity indeed is a base quality, which – if mixed well with the proper incentives – can bring great results.

Again, this is valid for both employees and employers since, as I mentioned above, the absolute ends meet somewhere in the gray area, and eventually the gray area will have to get re-evaluated after some time. In this aspect curiosity is a very well paying investment.

Take this example: as I mentioned above, the DBA who has great database developer skills landed a great job despite their lack of experience with VLDBs and the agreement with the employer was that the initial pay would be a bit below the standard for the industry, but both parties agreed that re-evaluation will be performed in 6 months and adjustments will be made accordingly.

The employee is expected to invest time and effort in feeding their curiosity and, if the results are good, a shifting of incentives will happen at the end of the 6 month period.

Incentives vs. motivation

As I mentioned above, incentives have many implementations and are generally a very powerful force. The problem with incentives is though that they are always projected onto the individual person’s self-motivation and tend to manipulate it.

People (hence, employees) always have the free will to prioritize, according to the incentives.

The point is that a quality interview should cover the most important aspects of incentives-motivation relation.

So, to recap:

To wrap everything up and to make sense of what I have said so far I would like to do two things:

  • give a quick summary of the theoretical points and
  • write a list of suggestions to both sides of the interview table

Here are the points:

  • Incentives are the fuel of the interviewing, hiring and work processes;
  • The (accurate) relative evaluation of the weight of incentives vs. motivations determines the long-term relationship between both sides;
  • The curiosity is the investment, fed by the incentives;
  • The motivation is the final result of the mix between incentives and curiosity;

And hopefully the results are corresponding to the investments made during the interviewing process.

List of suggestions: (most of them are valid for both sides)

  • Remember, that the other side needs you as much as you need them, otherwise you would not be sitting there talking;
  • The interview is not a ‘one-way’ street, i.e. each side should be interviewing the other
  • A job interview is simply a negotiation of potential collaboration based on mutual incentives
  • There are no wrong questions, there are just questions asked at the wrong time and in the wrong way (well, except the illegal questions)
  • Money is just one form of incentive; try to negotiate with your potential employer / employee other incentives as well, for example: unlimited access to books on the work-related topic, funds for training, funds for certifications, conferences. The best part of other incentives besides money is that the employer will write it off taxes and the employee will not pay taxes on it (unlike when you pay / get money)
  • There is nothing wrong with saying ‘No’
  • There is nothing wrong with rejection (unless it is based on inadequate terms, i.e. racist, age, gender and other forms of discrimination);
  • Do not apply for jobs which are far below your level of expertise, unless you know that both sides are really desperate and there is no other choice
  • Do not hire employees which are far too qualified, unless you know that both sides are really desperate and there is no other choice
  • Promote yourself – add your achievements to your resume: blogging, public speaking, answering questions on forums; potential employers tend to appreciate any public activities and will tend to pick a candidate over another based on public value (given that both candidates have the same experience, same education, same skills etc. )
  • Promote your company and its current employees – create a company blog space and ask your employees to blog or express opinions freely; potential employees tend to see this as a positive sign
  • Don’t lose sleep over interviews; nervousness and your own imagination is your worst enemy
  • Never accept a job which is not clearly defined; ask questions about the role until it is completely clear or run as fast as you can (unless you are desperate)
  • Try to find out the reasons why the position is available (check professional social network sites for previous employees and do a quick trending of the employee flow through the position / department: how long do the employees stay in the position, what are their backgrounds, where are they working now and so on )
  • Don’t count on references; people tend to cheat on those. Instead, try to socialize or, if possible, try to get introduced to a current employee which can put a good word for you at the company. Most companies (the smarter ones) have company events, where employees are allowed to bring guests; there is no better opportunity than this to headhunt or to be headhunted
  • Try to find out what kind of people you will be working with: how many of them are experienced, curious and motivated, and how many of them are there ‘just for the ride’. I have noticed that the latter kind will just drain your energy with their repetitive questions, whereas the former kind of coworkers will most likely stimulate your professional advancement

List of all the Interview Questions and Answers Series blogs

Reference: Rick A. Morelan, Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interview Questions and Answers – Guest Post by Nakul Vachhrajani – Day 28 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

Nakul Vachhrajani SQL SERVER   Interview Questions and Answers   Guest Post by Nakul Vachhrajani   Day 28 of 31

Nakul Vachhrajani is a Technical Lead and systems development professional with iGATE Patni having a total IT experience of more than 6 years. He has comprehensive grasp on Database Administration, Development and Implementation with MS SQL Server and C, C++, Visual C++/C#. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series.

Interviews – A Definition

The Merriam-Webster English dictionary defines an “Interview” in two ways.

  • A formal consultation usually to evaluate qualifications (as of a prospective student or employee)
  • A meeting at which information is obtained (as by a reporter, television commentator, or pollster) from a person

Interviews are a reality of life, and we all have been facing them ever since we started understanding the world around us. Way back when we were little children, we faced our first interview when we used to be asked – “What’s your name?” For frequent fliers, there is nothing new if they are asked the same set of questions (“What is your final destination?”, “Where will you stay?”, “Are you carrying any food products?”) again and again by different people at different legs of their journey. We face interviews almost every day of our life.

Interviews are therefore nothing new, but yet there is always an anxiety when we, as an interviewee, are a prospective student or an employee. And it is not wrong because Darwin’s rules on evolution are kicking in and “survival of the fittest” is seen in its pure form. As a general rule, as defined above, if your qualifications are the best, you will be selected.

To evaluate qualifications, a meeting is required – the meeting can be a face-to-face meeting, a telephonic communication, or even a written evaluation (not necessarily in the same order). Without a meeting of some sort, no interview is complete.

Einstein’s theory of relativity establishes that everything is relative. There has to be a reference. and based on the reference, every evaluation has to be done. This leads us to the next question – what is the definition of the best qualification, and how do you evaluate one?

Defining the best qualification – understanding expectations

Unfortunately, there is a lot left to interpretation, and therefore, the reference changes with each interview. It is important to define and understand the right qualification requirements and the expectations that the interviewer has for the job. Let me explain.

On the part of the interviewee, it is the prime challenge to understand what is required by the prospective employer. Job requirement postings are typically developed by HR professionals, who guided by the technical team sometimes obfuscate the core requirement. Doing  the same serves two purposes.

Indicates the “tolerance” margin of the organization

Most requirements are very specific. However, finding the candidates matching the exact requirement is almost always very difficult. Hence, most organizations have a “tolerance” margin – wherein if the candidate falls within the tolerance margin, they can be trained and ramped up to the specific requirement.

Often I hear from new recruits that they are not happy with a job simply because they were interviewed for one technology (e.g. Web-technologies), but have been placed into another (e.g. client-server technologies). I would say that for the most part, the recruit is to blame. Interviews are the best places to clear out any questions that one may have about the work that would be assigned once the interview is cleared.

It is not wrong to ask the interviewer about the tentative projects that they would be placed in. I have had experienced candidates ask me – “If you decide to select me, what will I be required to work on? I can work on ABC technologies and can learn something if you want me to.” The reason is that as experienced professionals, they know, understand, and have accepted the tolerance margin.

Sometimes, the first assignment may be exactly what one is interviewed for, but the next one may not. It becomes very easy to work in such situations if one understands that in a service or consultation-based industry, it is almost impossible to be extremely specific about the technology and domain.

Fresher graduates (and in some cases, experienced resources as well), however, have a misconception that because they are trained in a technology, they should work in the same technology. Not getting the desired work because of their misconceptions causes undue stress to both – the individual and to the employer. After an initial high-speed growth by jumping around from one organization to another, such individuals often end up in stagnation.

Educational Qualification is never the only requirement

In an employment interview, the focus of the interviewer is generally on the job candidate’s work experiences, general and specific attitudes, and occupational goals.

Aside from professional work experience, others are soft skills. I have at many times chosen candidates with a very positive drive towards innovation, customer satisfaction, learning, and strong work ethics v/s a higher academic qualification.

Having a wide requirement defined during an interview call ensures that such candidates will take a chance and come over.

Responsibility of the interviewer

Interviews are not only about the candidates – they are about the interviewers as well. When the career of another person is at stake, it is a huge responsibility of the interviewer that they remain unbiased by the factors of mood, personal differences, and carry-overs from the previous interview.

My philosophy for a career

The movie 3 idiots demonstrated to the common man something I have always believed in – strive for excellence. Even if you are the Jack of all trades, if you excel at it, there is always a requirement for you.

It pains me to see people rushing after IT one year and after core-engineering the next year. If we think of it, every course of study has its own importance. IT organizations need buildings to carry out their work. Buildings are designed by architects and structural engineers and built by civil engineers. To design lifts, we need mechanical engineers and to ensure that computers and HVAC systems remain powered up 24×7, we need electrical engineers. So you see, there is no such thing as “there is no demand for anything else but IT.”

What I want to emphasize is that as students, focus on what you like best. Excel in it, make sure the world knows about it, hone your soft skills, and then leave the rest up to fate. Even the karma theory in the Mahabharata has this as one of its most important teachings.


I could fill up pages on the topic of interviews;, however, summarizing, I would like to present a bulleted list of some of the most important points that needs to be taken care of during an interview:


  • Be honest. Always
  • Don’t stress out
  • Don’t be afraid to ask questions
  • Do not use salary as a negotiation tool


  • Check your biases at the door
  • Ask questions keeping the experience level of the interviewee in mind
  • Answering interviewee’s questions and making the interviewee feel at home goes a long way
  • The Interviewee is a human and is allowed to commit a few mistakes due to stress and anxiety of the interview


  • You may be the dream company for a lot of people attending your interview. Ensure that you extend the same level of comfort and services that you do so to your employees (facilities, catering services, security, etc.)

I would like to thank Pinal for running this month-long series on Interviews & Interview questions. To all those who read this post, I wish you all the best for your interviews in the future!

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Latest expressor Data Integration Platform Posts

I continue to frequently post new articles on expressor and would like to share with you my latest three posts:

Introduction to expressor Datascript Modules

5 Tips for improving your data with expressor Studio

expressor 3.2 Release Review

I will soon be blogging about their upcoming 3.4 release to keep you informed about the latest developments around their product. If you haven’t tried yet, consider downloading and test-driving their Studio product – it’s absolutely free.

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

SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

RM01 SQL SERVER   Interview Questions and Answers   Guest Post by Rick Morelan   Day 27 of 31Rick A. Morelan is finest SQL Expert. He is very much known for his excellent book series Joes 2 Pros. His books are not only inspiration to many who wants to learn SQL Server properly but a MUST read for any SQL enthusiast. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to read his MUST read books Joes 2 Pros.

After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in the interview but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. Most of these questions were not even programming questions but short queries with deep strategic thought. These questions separated the junior SQL query writers from the advanced SQL query writers. That is because most advanced interview questions combine two or more intermediate level skill sets into 1 solution.

Realizing years ago that just writing and training people for the Microsoft test was not always enough to get them where they wanted to go. Maybe half the time they would get the job so I vowed to do something extra to increase those odds. Perhaps needed to create an entire course on the how to ace SQL query interviews.

Since it seemed 80% of the interview questions were about the advanced queries that is where my focus must be directed. The end result is my SQL Query book on certification (SQL Queries Joes 2 Pros:  SQL Query Techniques for Microsoft SQL Server 2008ISBN 1-4392-5318-) doubled in size to nearly 600 pages. In fact this book now adorns more interview prep then certification prep. It’s proven to be a big payoff and my students almost always get the job offer after the interview.

Let’s get on to one of the common interview questions. Most people are fully aware of how the TOP keyword works with a SELECT statement. For example if you wanted to look at the 3 top most expensive products from the [Grant] table of the sample database JProCo then this query would do the trick (note: you can download my sample database from Joes2Pros.com). Watch the video here.

FROM [Grant]

The real interview question assumes this is already easy to you and instead they want to find just the 3rd highest Grant. In other words just the record that is in 3rd place by amount. To do this you need rank the records and then predicate for 3rd place. Let’s start off by sorting all the records of the table by highest price.

FROM [Grant]

The ORDER BY clause picks what records are listed first based on the field and sort order you pick. In this case we choose amount from highest to lowest. Now we want to use the power of the ORDER BY to pick the rank. Follow the RANK() with OVER()  and the put  the ORDER BY inside and not the end of the query. Your query and output look like the sample below.

FROM [Grant]

GrantID GrantName EmpID Amount Rank












Thank you @.com





Norman’s Outreach NULL




BIG 6’s Foundation%





TALTA_Kishan International










K_Land fund trust





Call Mom @Com





92 Purr_Scents %% team




This is very nice but not very neat. It a good idea to alias expression fields to let’s call it GrantRank.

FROM [Grant]

Almost done right? You can see the 3rd highest grant is Thank you @.com for $21,500. Just 1 critera way from victory. You might think it would be as simple as saying GrantRank = 3. If you try to predicate on a field alias you will discover that the WHERE clause runs before the field expression and you get the following error.

FROM [Grant]
WHERE GrantRank = 3

Msg 207, Level 16, State 1, Line 5

Invalid column name ‘GrantRank’.

GrantRank is not a column until after the query has run so the where clause can’t find what you’re talking about. What can you do? Simply materialize the query like a table by using a Common Table Expression or a Derived Table. Both examples are below.

--Use a Common Table Expression
FROM [Grant] )
WHERE GrantRank = 3
--Use a Derived Table
FROM [Grant] ) AS dt
WHERE GrantRank = 3

GrantID GrantName EmpID Amount GrantRank
9 Thank you @.com 11 21500 3

List of all the Interview Questions and Answers Series blogs

Reference: Rick A. Morelan, Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

malathim SQL SERVER   Interview Questions and Answers   Guest Post by Malathi Mahadevan   Day 26 of 31Malathi Mahadevan who is known SQL Server Expert has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to read her excellent blog and follower her on twitter.

One of the questions i was asked – and a regular at most interviews where i work is

‘What is the toughest challenge you have faced at your present job and how did you handle it’?
Before looking at various responses I will explain some reasons why this question is asked…#1 reason is that we don’t believe in grilling a person technically…it is the one thing most people dread in interviews, and many times people who even know the subject well get totally flustered and upset if they cannot answer something.On the other hand it is vitally important to test an individual’s technical skills, especially when the company cannot afford a big learning curve, and person needs to get started on the job right away on production system. The easiest way to gauge a person’s expertise is to get them talking about what they did…they may not know all that they are needed to know but as an interviewer one can clearly understand how much and what this person is capable of.
Always try to be honest about this question. There is definitely a risk involved, particularly if you are a junior DBA trying to transition into a more senior level..and the company is looking for senior level expertise already. Experience shows – in the technical jargon we use, how comfortable we are describing what we did, in everything. Sometimes just the way you answer can impress the interviewer and get you the job. But never try to pass off something you read or someone else’s experience as your own. You may not be able to get very far with that, and it rarely fools a smart interviewer.

Some answers I have given and heard:

1 I had to do an in-place upgrade on a critical production system overnight. It was an active active cluster on SQL 2005 and was upgrading to 2008. I had to read clearly the differences between clustering installation in both and had to be very sure i understood.  After going through test installs we decided this was too risky and went with new hardware, new os, it was still an in place install since they required us to have the same server name. So had to extensively document the older system and rebuild the new system from scratch overnight.

2 Had two fellow dbas leave in quick succession, had to handle work of 3 dba’s. Learnt how to multi task and automate monitoring, buy and use tools…Also automated the dba daily checklist to the extent possible so that i dont have to manually check processes running, event logs, job failures and so on. Used powershell/vbscript to achieve this.

3 Had to convince application team to learn and use SSIS instead of TSQL for several ETL processes. Ther was lot of resistance as SSIS was not easy for them to learn and they were very used to writing TSQL code. But i had to arrange several demos and show them advantages – particularly in terms of performance with data transforms, eliminating the need for intermediate tables, filtering bad data and exception handling. Had to talk to the manager and migrate couple of packages initially, did it all myself..when they saw how it performed they started learning it and came on board totally. The main lesson i learnt was to understand their resistance and deal with it maturely instead of forcing opinions.

4 Had a database that was using GUIDs heavily and suffering performance issues due to high fragmentation. Tried to mitigate the issue by reindexing more often – to some extent it helped. Then we moved to sequential GUIDs and it got little more better. But the main issue i had was to force them to adopt integer keys – they kept insisting it would result in i/o hotspots and this and that..but proved that wrong too by recreating the database using integer keys and using multiple files to avoid contention. When they saw performance improvement they were sold!

In some cases you may be allowed a few minutes to describe the situation like this and answer – in some cases interviewee would just cut you short and say ‘ok, what did you do for this or that’, or ‘what if they still refused to moved to integer keys instead of guids’ and so on…the best way is to practice the answers at home repeatedly, perhaps have a friend or family member stop you at every sentence and ask questions. Also try to talk easily and fluently in technical terms, stammering with technical terms is often times a clue to what the person knows and does not know.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

paras SQL SERVER   Azure Interview Questions and Answers   Guest Post by Paras Doshi   Day 25 of 31Paras Doshi is known SQL Azure Expert has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series. I encourage all the readers to read his excellent blog and follow him on twitter.

1.What is SQL Azure?

SQL Azure is a cloud based relational database as a Service offered by Microsoft. Conceptually it is SQL server in the cloud.

2.What is cloud computing?

National Institute of standards and computing definition of cloud computing:

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.

[TIP: Remember Key words On Demand, Scalable, Self-service, and Measurable. Now take the first word from each key word which gives us OSSM which can be pronounced as Awesome. Thus remember cloud computing is Awesome! Tip courtesy: Dave Nielsen]

3.How is SQL Azure different than SQL server?

SQL Azure is a cloud based service and so it has own set of pros and cons when compared to SQL server. SQL Azure service benefits includes on demand provisioning, high availability, reduced management overhead and scalability. But SQL Azure abstracts some details from the subscriber which can be good or bad which depends on the context of the need.

4.How many replicas are maintained for each SQL Azure database?

For each database, three replicas are maintained for each database that one provisions. One of them is primary replica. All read/write happen on primary replica and other replicas are kept in sync with primary replica. If for some reason, primary goes down, other replica is promoted to primary. All this happens under the hood.

5.How can we migrate from SQL server to SQL Azure?

For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.

6.Which tools are available to manage SQL Azure databases and servers?

We can manage SQL Azure database using SQL server management server 2008 R2. Also, we can manage SQL Azure databases and servers through a Silverlight app integrated in Azure management portal.

7.Tell me something about security and SQL Azure.

SQL Azure service allows blocking a request based on its IP address through SQL Azure firewall. It uses SQL server Authentication mechanism to authenticate connections. Also connections to SQL Azure are SSL-encrypted by default.

8.What is SQL Azure Firewall?

SQL Azure firewall is a security mechanism that blocks requests based on its IP address.

9.What is the difference between web edition and business edition?

SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB. The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB whereas the size of a business edition can be increased in the increments of 10 GB.

10.How do we synchronize On Premise SQL server with SQL Azure?

We could use a No code solution called DATA SYNC (currently in community technology preview) to synchronize on-premise SQL server with SQL Azure. We can also develop custom solutions using SYNC framework.

11.How do we Backup SQL Azure Data?

SQL Azure keeps three replicas of a database to tackle hardware level issues. To tackle user level errors, we can use COPY command that allows us to create a replica of a SQL Azure database. We can also backup SQL Azure data to local SQL server using BCP, SSIS, etc. but as of now, point in time recovery is not supported.

12.What is the current pricing model of SQL Azure?

Charges for SQL Azure consumption is based on 1) Size 2) Data Transfer.

[For contemporary pricing model, read: http://www.microsoft.com/windowsazure/pricing/ ]

13.What is the current limitation of the size of SQL Azure DB?

Maximum size of a SQL Azure database is 50 GB.

14.How do you handle datasets larger than 50 GB?

As of now, we have to build custom solution at application level that can handle scale out of underlying SQL Azure databases. But Microsoft has announced, SQL Azure Federations that will assist scaling out of SQL Azure databases. And scale out means that we are splitting the data into smaller subsets spread across multiple databases.

15.What happens when the SQL Azure database reaches Max Size?

Read operations continue to work but create/insert/update operations are throttled. You can drop/delete/truncate data.

16.How many databases can we create in a single server?

150 databases (including master database) can be created in a single SQL Azure server.

17.How many servers can we create in a single subscription?

As of now, we can create six servers under a single subscription.

18.How do you improve the performance of a SQL Azure Database?

We can tune a SQL Azure database using information available from execution plan and statistics of a query. We could use SQL Azure’s Dynamic Management views to monitor and manage SQL Azure database.

Also, SQL Azure performance is affected by network latency and bandwidth. Considering this, code near application topology gives the best performance.

19.What is code near application topology?

Code near application topology means that the SQL Azure database and the windows azure hosted service consuming the data are hosted in the same Azure datacenter.

[FYI: in the code far application topology, the app connects to SQL Azure from outside the Microsoft data center]

20.What were the latest updates to SQL Azure service?

Latest SQL Azure updates include multiple servers per subscription, SQL Azure co administrator support, creating Firewall rules for servers with IP detect.

[A constantly updated list of SQL Azure features is available at http://beyondrelational.com/whatisnew/sqlserver/azure.aspx]

21.When does a workload on SQL Azure get throttled?

When database reaches its maximum size update/insert/create operations get throttled. Also there are policies in place that does not allow to a workload to exploit a shared physical server. In other words, the policies make sure that all workload get a fair share of shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also got hard throttled which means that a SQL Azure machine is out of resources and it does not accept new connections. We can know more about what happened by decoding reason codes.

These were SQL Azure specific interview questions but remember that SQL server specific interview questions that we have seen so far can also appear in SQL Azure context since SQL Azure is “SQL server in the cloud”. Also questions specific to supported/unsupported features can be asked. I have not included the questions specific to unsupported features because it keeps on changing. Read general guidelines and limitations. Also note that SQL Azure is evolving very fast and it is important to keep a tab on the features that are added in service releases.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is Hybrid Slowly Changing Dimension?

Hybrid SCDs are combination of both SCD 1 and SCD 2. It may happen that in a table, some columns are important and we need to track changes for them, i.e. capture the historical data for them, whereas in some columns even if the data changes, we do not care.

What is BUS Schema?

BUS Schema consists of a master suite of confirmed dimension and standardized definition of facts.

What is a Star Schema?

Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What Snow Flake Schema?

In Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Differences between the Star and Snowflake Schema?

Star schema: A single fact table with N number of dimensions; all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.

Snow schema: Any dimension with extended dimensions is known as snowflake schema; dimensions maybe interlinked or may have one-to-many relationship with other tables. This schema is normalized, and results in complex join leading very complex query (as well as slower results).

What is Difference between ER Modeling and Dimensional Modeling?

ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is Degenerate Dimension Table?

If a table contains values, which are neither dimension nor measures, then it is called a degenerate dimension  table.

Why is Data Modeling Important?

Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end users.

In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. In data modeling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure.

Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video. (Reference: Wikipedia)

What is a Surrogate Key?

A surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it should be unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.

What is Junk Dimension?

A number of very small dimensions may get lumped together to form a single dimension, i.e. a junk dimension – the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.

What is a Data Mart?

A data mart (DM) is a specialized version of a data warehouse (DW). Like data warehouses, data marts contain a snapshot of operational data that helps business people to strategize based on analyses of past trends and experiences. The key difference is that the creation of a data mart is predicated on a specific, predefined need for a certain grouping and configuration of select data. A data mart configuration emphasizes easy access to relevant information (Reference: Wiki). Data Marts are designed to help the manager make strategic decisions about their business.

What is the Difference between OLAP and Data Warehouse?

Data Warehouse is the place where the data is stored for analysis, whereas OLAP is the process of analyzing the data, managing aggregations, partitioning information into cubes for in depth visualization.

What is a Cube and Linked Cube with Reference to Data Warehouse?

Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values. The linking in cube ensures that the data in the cubes remain consistent.

What is Snapshot with Reference to Data Warehouse?

You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data.

What is Active Data Warehousing?

An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively.

What is the Difference between Data Warehousing and Business Intelligence?

Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart, including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management and backup/recovery planning. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions. Typically, the term ’business intelligence’ is used to encompass OLAP, data visualization, data mining and query/reporting tools. (Reference: Les Barbusinski)

What is MDS?

Master Data Services or MDS helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. (Read more here)

Explain the Paradigm of Bill Inmon and Ralph Kimball.

Bill Inmon’s paradigm: Data warehouse is one part of the overall business intelligence system. An enterprise has one data warehouse, and data marts source their information from the data warehouse. In the data warehouse, information is stored in the 3rd normal form.

Ralph Kimball’s paradigm: Data warehouse is the conglomerate of all data marts within the enterprise. Information is always stored in the dimensional model.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is ETL?

ETL is abbreviation of extract, transform, and load. ETL is software that enables businesses to consolidate their disparate data while moving it from place to place, and it doesn’t really matter that that data is in different forms or formats. The data can come from any source. ETL is powerful enough to handle such data disparities. First, the extract function reads data from a specified source database and extracts a desired subset of data. Next, the transform function works with the acquired data – using rules or lookup tables, or creating combinations with other data – to convert it to the desired state. Finally, the load function is used to write the resulting data to a target database.

What is VLDB?

VLDB is abbreviation of Very Large Database. For instance, a one-terabyte database can be considered as a VLDB. Typically, these are decision support systems or transaction processing applications serving a large number of users.

Is OLTP Database is Design Optimal for Data Warehouse?

No. OLTP database tables are normalized, and it will add additional time to queries to return results. Additionally, the OLTP database is small; it does not contain data from a long period (many years), which needs to be analyzed. A OLTP system is basically an ER model and not a Dimensional Model. If a complex query is executed on an OLTP system, it may lead to heavy overhead on the OLTP server that will affect the normal business processes.

If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?

The foreign keys of facts tables are primary keys of Dimension tables. It is clear that the fact table contains columns which are a primary key to another table that itself make a normal form table.

What are Lookup Tables?

A lookup table is the table placed on the target table based upon the primary key of the target; it just updates the table by allowing only modified (new or updated) records based on the lookup condition.

What are Aggregate Tables?

An aggregate table contains the summary of existing warehouse data, which is grouped to certain levels of dimensions. It is always easy to retrieve data from aggregated tables than visiting original table which has millions of records. Aggregate tables reduce the load in the database server and improve the performance of the query, and they also can retrieve the result quickly.

What is Real-Time Data-Warehousing?

Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly.

What are Conformed Dimensions?

Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. They are common to the cubes.

What is a Conformed Fact?

Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly.

How do you Load the Time Dimension?

Time dimensions are usually loaded by a program that loops through all possible dates that may appear in the data. 100 years may be represented in a time dimension, with one row per day.

What is a Level of Granularity of a Fact Table?

Level of granularity means the level of detail that you put into the fact table in a data warehouse. Level of granularity implies the detail you are willing to put for each transactional fact.

What are Non-Additive Facts?

Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table. However, they are not considered as useless. If there are changes in dimensions, the same facts can be useful.

What is a Factless Facts Table?

A fact table that does not contain numeric fact columns is called a factless facts table.

What are Slowly Changing Dimensions (SCD)?

SCD is the abbreviation of slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time. There are three different types of SCD.

  1. SCD1: The new record replaces the original record. Only one record exists in database – current data.
  2. SCD2: A new record is added into the customer dimension table. Two records exist in the database – current data and previous history data.
  3. SCD3: The original data is modified to include new data. One record exists in database – new information is attached with old information in same row.

List of all the Interview Questions and Answers Series blogs

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

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

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

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is OLTP?

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is the Difference between OLTP and OLAP?

Data Source

OLTP:         Operational data is from original data source of the data
OLAP:        Consolidation data is from various sources.

Process Goal

OLTP:         Snapshot of business processes which do fundamental business tasks
OLAP:        Multi-dimensional views of business activities of planning and decision making

Queries and Process Scripts

OLTP:         Simple quick running queries ran by users.
OLAP:        Complex long running queries by system to update the aggregated data.

Database Design

OLTP:         Normalized small database. Speed will be not an issue because of a small database, and normalization will not degrade performance. This adopts the entity relationship (ER) model and an application-oriented database design.

OLAP:        De-normalized large database. Speed is an issue because of a large database and de-normalizing will improve performance as there will be less tables to scan while performing tasks. This adopts star, snowflake or fact constellation mode of subject-oriented database design.

Back up and System Administration

OLTP: Regular Database backup and system administration can do the job.
OLAP: Reloading the OLTP data is considered as a good backup option.

What are Normalization Forms?

There are different types of normalization forms such as

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is ODS?

ODS is the abbreviation of Operational Data Store ‑ a database structure that is a repository for near real-time operational data rather than long-term trend data. The ODS may further become the enterprise-shared operational database, allowing operational systems that are being re-engineered to use the ODS as their operation databases.

What is ER Diagram?

Entity Relationship (ER) Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has enabled the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner.

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

List of all the Interview Questions and Answers Series blogs

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