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.
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.
Rick 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.
SELECT TOP (3) *
FROM [Grant]
ORDER BY Amount DESC
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.
SELECT *
FROM [Grant]
ORDER BY Amount DESC
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.
SELECT *,
RANK() OVER(ORDER BY Amount DESC)
FROM [Grant]
| GrantID | GrantName | EmpID | Amount | Rank |
| 7 | Ben@MoreTechnology.com | 10 | 41000 | 1 |
| 8 | www.@-Last-U-Can-Help.com | 7 | 25000 | 2 |
| 9 | Thank you @.com | 11 | 21500 | 3 |
| 4 | Norman’s Outreach | NULL | 21000 | 4 |
| 5 | BIG 6′s Foundation% | 4 | 21000 | 4 |
| 6 | TALTA_Kishan International | 3 | 18100 | 6 |
| 3 | Robert@BigStarBank.com | 7 | 18100 | 6 |
| 2 | K_Land fund trust | 2 | 15750 | 8 |
| 10 | Call Mom @Com | 5 | 7500 | 9 |
| 1 | 92 Purr_Scents %% team | 7 | 4750 | 10 |
This is very nice but not very neat. It a good idea to alias expression fields to let’s call it GrantRank.
SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS 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.
SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
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
WITH CTE AS
(SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] )
SELECT * FROM CTE
WHERE GrantRank = 3
--Use a Derived Table
SELECT *
FROM
(SELECT *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] ) AS dt
WHERE GrantRank = 3
| GrantID | GrantName | EmpID | Amount | GrantRank |
| 9 | Thank you @.com | 11 | 21500 | 3 |
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.
Summary
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:
Interviewee
- Be honest. Always
- Don’t stress out
- Don’t be afraid to ask questions
- Do not use salary as a negotiation tool
Interviewer
- 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
Organization
- 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!
Feodor 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.
Curiosity
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
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.
- Read the content of an XML document and produce a result set
- Generate XML documents from relational data
- 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.
Please read the Introductory Post before continue reading interview question and answers. Complete List of all the Interview Questions and Answers Series blogs.
We have spent the last 30 days going over questions and answers you may come up against when you are being interviewed. Of course, I am only human and I can’t provide you with the answer to every question, or even the answer to every situation – because sometimes acing an interview is more than getting all the answers right.
Sometimes acing an interview is more about impressing the interviewer with your personality or with your reasoning abilities. Remember that you are probably being hired to work at a company with many other employers, so your interviewer is not only concerned with how much you know but how well you will work in a team.
Even if you are totally prepared and are a great team player, you might also just have the bad luck to have a cranky interviewer, accidentally say the wrong thing, or just have an “off” day. Here is my best advice for situations like that.
Bad Interviews
We all have bad days, it is part of life. Usually we are just unproductive or make a mistake that can be corrected later, and we can go on with our life. But if your bad day happens to fall during an interview, you may feel like you have ruined your entire life.
Having a bad interview can mean you don’t get the job, and this might have seemed like your dream job. You may beat yourself up afterwards about all the wrong things you did or said, and how nothing will ever go right for you.
The best advice for a situation like this is: treat it like just another bad day. Go on with your life. In the first place, the interview might not have gone as badly as you think. Sometimes the words might not come out of your mouth the way you want them to, but that doesn’t mean you sounded stupid to the interviewer. Keep your head up and think positive thoughts.
If the interview truly went badly and you know you did not get the job, then there is no reason to beat yourself up! The position might have seemed like your dream job – but remember the saying “God’s greatest gift is unanswered prayers.” We can’t see the future, and it may have turned out that you would have been miserable at the job anyway. Above all: do not let one bad interview make you lose your confidence. Keep going on interviewers – the worst that can happen is that you improve your interviewing skills.
When It’s Out of Your Control
Sometimes a bad interview happens because you weren’t prepared. Well, if you have been keeping up with our 30 days of Questions and Answers, then you will probably be the most prepared candidate! That means that sometimes an interview goes wrong and it’s not your fault.
The biggest thing that can be out of control is nervousness. Everyone says “just relax!” but sometimes it is not that easy. If you think you are going to be nervous about an interview, take time before that day to figure out how to calm your nerves. You might need to take a long walk, or just relax with a book or movie to take your mind off of it. Make sure you are well prepared beforehand so that you won’t be trying to cram information into your brain 10 minutes before the interview. Practice relaxing before the interview day, so that you can calm yourself down while you wait in the office or lobby. And, of course, make sure you know how to get to the interview place so that you don’t get lost!
Another thing that is completely out of your control – other people. The interviewer may ask you a question you just don’t know the answer to. This can be something out of your expertise, or a detail about something that you have forgotten. If this happens, do not panic. If you have no experience with the subject in question, don’t lie! Answer truthfully, but also add that you would be willing to learn the procedure, or are planning on training. If it a small detail you can’t quite remember, offer to look up the information and let them know at a later time. Many interviewers will give you a business card or at least a phone number, so you can provide this information to them when you leave the interview, and also look like a dedicated employee.
Not only can you not control the interviewer’s questions, you also cannot control their personality. Sometimes you will simply meet a person who is not very nice. The best thing to do in this situation – be polite and get out of there as quickly as you can! The interviewer may be mean because they are just that kind of person – or they could be testing you. Don’t lose your temper, but answer calmly and demonstrate your ability to deal with unpleasant people. If you are good at diffusing situations with humor, try that. The interviewer might be trying to see how you work under pressure.
Interview Tips and Tricks
Acing an interview can be about more than just knowing the answers to all the technical questions. No matter what your field is, there are certain things you should know before you go into an interview.
- Know the Company. If you stroll into an interview not knowing one thing about what the company does or anything more than just their name, you might be in for a nasty surprise. Learn all you can about the company, this can help you decide if you’d like the job, and even help you come up with questions for the interviewer, which will impress them.
- Do Not Lie. Your mother always taught you that lying is bad, and this is still true today! Not only will lies get you in trouble when they are found out, but research shows that people can tell when you are lying. Especially if you are being interviewed by someone in Human Resources, they have a lot of experience with people and will probably be able to read your body language to know you just made something up.
- Politely Brag! Bragging is not the same as lying. Interviews are the one time in your life where you will get to talk about how great you are without someone thinking you are a jerk. Don’t be afraid to volunteer information about your talents and strengths, or details about a project you really did well on.
- Be Memorable. The sad truth about the job market is that when you apply for a job, you might be competing against hundreds of similar applicants. If you are lucky enough to get an interview, you will want to stand out from the crowd. The best way to make yourself memorable is with a funny story or personal details. Try to find a way to slip your favorite hobby into the conversation, or a funny joke. Just make sure that it is something you wouldn’t be ashamed to say in front of your grandmother.
- Don’t Rush. Sometimes when we are nervous, we might start talking too fast. This can happen to anyone. You might end up sounding very nervous, or even unknowledgeable. The best way to combat this is to take a deep breath before you answer every question. Don’t be afraid to think about your answers for a second – this will make you seem like a very thoughtful employee. If you don’t understand the question, don’t be afraid to ask for clarification. It will be better than giving a very silly answer.
The “Difficult” Question
The technology giants hire a lot of people, and by all accounts they are a great company to work for. Their employees are often very smart, highly motivated, and can think outside the box. So how did they end up with such great employees? They ask very strange interview questions!
Imagine going into an interview for a simple engineering position, and being asked why manhole covers are round. You won’t be working with manholes, so why does this question matter? It is a test of how well you can think logically – and under pressure. The answer is: a round cover will not fall through into a round hole, no matter the configuration, while any other shape could twist and fall into the sewer and possibly injure workers below. The answer seems simple when you hear it, right?
More and more companies are learning that these kinds of questions can weed the good employees from the bad. If you are asked a weird interview questions, what is the first thing you should do? Not panic!
Remember, the interviewer does not expect you to immediately know the answer, this isn’t a trivia quiz it is a test of rational thinking. If you blurt out the answer, they will probably just come up with another one. Also, the question might not have a real answer. The best thing to do is sit and think about the question logically, and try to come up with an answer that will show your mental process. It is like in mathematics – show your work!
Conclusion
So – do you feel ready for your interview? I hope your answer is “mostly.” Just remember that interviews are kind of like a test about life. Unlike tests in school, you can never be 100% prepared, because you cannot control life. But if you are 99% prepared, you will be a great interview candidate.
