SQL SERVER – Interview Questions and Answers

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.