Explore topic-wise InterviewSolutions in .

This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.

1.

CONTAINS and CONTAINSTABLE look for a _______ match for the phrase.(a) exact(b) similar(c) different(d) none of the mentionedThis question was posed to me by my school teacher while I was bunking the class.The question is from Full Text Search in chapter Beyond Relational of SQL Server

Answer»

The correct option is (a) exact

Explanation: In full-text SEARCH, a WORD (or token) is a string WHOSE boundaries are identified by appropriate word breakers, following the LINGUISTIC rules of the specified LANGUAGE.
Discussion
2.

The CONTAINS and FREETEXT predicates return a _________value.(a) TRUE or FALSE(b) Scalar(c) One(d) All of the mentionedThe question was posed to me by my college director while I was bunking the class.Question is taken from Full Text Search in portion Beyond Relational of SQL Server

Answer»

The CORRECT OPTION is (a) TRUE or FALSE

The explanation is: The CONTAINS and FREETEXT predicates return a TRUE or FALSE value. They can be used only to specify SELECTION CRITERIA for determining whether a GIVEN row matches the full-text query.
Discussion
3.

FREETEXTTABLE is a full text _____________(a) Predicate(b) Function(c) Procedure(d) All of the mentionedI got this question during an interview.The question is from Full Text Search in chapter Beyond Relational of SQL Server

Answer» RIGHT CHOICE is (b) Function

To elaborate: FREETEXTTABLE FUNCTIONS are referenced like a regular table NAME in the FROM clause of a SELECT statement.
Discussion
4.

Which of the following predicates is used for full text search?(a) TEXT()(b) FREETEXT()(c) TEXT()(d) All of the mentionedI got this question in an online interview.My doubt stems from Full Text Search in portion Beyond Relational of SQL Server

Answer»

The correct option is (b) FREETEXT()

EASIEST explanation: Full Text Index can be used to SEARCH words, phrases and multiple FORMS of WORD or phrase using FREETEXT() and CANTAINS() with “and” or “or” OPERATORS.
Discussion
5.

Full text indexing is a great feature that solves a database problem, the searching of _________ columns for specific words and phrases in SQL Server databases.(a) Textual data(b) Character data(c) String(d) All of the mentionedI had been asked this question in semester exam.I would like to ask this question from Full Text Search in section Beyond Relational of SQL Server

Answer»

The correct ANSWER is (d) All of the mentioned

The explanation is: Full Text Index can be USED to search words, phrases and multiple forms of WORD or phrase.
Discussion
6.

Point out the wrong statement.(a) To define full-text searches, SQL Server full-text queries use the full-text predicates(b) Full-text queries perform linguistic searches against text data(c) Before you can run full-text queries on a table, the database administrator must create a full-text index on the table(d) Full-text search is applicable to a shorter range of business scenariosThis question was posed to me in final exam.Enquiry is from Full Text Search in section Beyond Relational of SQL Server

Answer»

Right option is (d) Full-text search is applicable to a shorter RANGE of business scenarios

Explanation: Full-text search is applicable to a wide range of business scenarios such as e-businesses—searching for ITEMS on a web SITE; law firms—searching for case histories in a legal-data REPOSITORY; or human resources departments—matching job DESCRIPTIONS with stored resumes.
Discussion
7.

Full-Text Search in SQL Server lets users and applications run _________ queries.(a) simple queries(b) full-text(c) scalar(d) none of the mentionedThis question was posed to me during an interview.My question comes from Full Text Search topic in division Beyond Relational of SQL Server

Answer» RIGHT choice is (b) full-text

To explain I would say: Before you can run full-text QUERIES on a table, the database ADMINISTRATOR must CREATE a full-text INDEX on the table.
Discussion
8.

Full-text search is an ________ component of the SQL Server Database Engine.(a) slower(b) optimal(c) faster(d) none of the mentionedThis question was posed to me during an online interview.Question is taken from Full Text Search topic in chapter Beyond Relational of SQL Server

Answer»

The CORRECT OPTION is (B) optimal

The explanation is: Full-text queries perform LINGUISTIC searches against text data in full-text INDEXES.
Discussion
9.

We can create a full-text index on _____________(a) table(b) view(c) indexed view(d) all of the mentionedThe question was asked in an online quiz.I want to ask this question from Full Text Search in portion Beyond Relational of SQL Server

Answer» RIGHT answer is (d) all of the mentioned

Easy EXPLANATION: Full text INDEX can contain up to 1024 columns.
Discussion
10.

Point out the correct statement.(a) The full-text index includes only one character-based column in the table(b) The full-text index includes more than one character-based columns in the table(c) The full-text index includes one or more character-based columns in the table(d) None of the mentionedI got this question by my school principal while I was bunking the class.This is a very interesting question from Full Text Search in portion Beyond Relational of SQL Server

Answer»

The correct answer is (c) The full-text INDEX includes one or more character-based COLUMNS in the table

Best explanation: Each full-text index INDEXES one or more columns from the table, and each column can use a SPECIFIC language.
Discussion
11.

Atomization is process of extracting ________ value of an item.(a) XML value(b) Non numerical value(c) Typed(d) None of the mentionedThe question was asked in an interview for internship.This intriguing question originated from Xquery topic in division Beyond Relational of SQL Server

Answer»

The correct option is (c) Typed

For EXPLANATION: Some of the XQuery OPERATORS, such as arithmetic and COMPARISON operators, DEPEND on ATOMIZATION.
Discussion
12.

With XML DML, which of the operations can be performed on nodes?(a) Insert nodes(b) Change nodes(c) Delete nodes(d) None of the mentionedThis question was addressed to me in a national level competition.My enquiry is from Xquery in chapter Beyond Relational of SQL Server

Answer»

Correct option is (c) Delete nodes

To ELABORATE: To change or remove a PORTION of the content in an XML VARIABLE or column, the MODIFY() METHOD can be used.
Discussion
13.

Functions on numerical values in Xquery are ___________(a) ceiling(b) concat(c) contains(d) numberI had been asked this question in quiz.My question comes from Xquery in chapter Beyond Relational of SQL Server

Answer»

Correct ANSWER is (a) CEILING

For EXPLANATION: Ceiling function returns the smallest number without a fractional PART and that is not less than the VALUE of its argument.
Discussion
14.

Different types of errors raised during Xquery operation are ______________(a) Static(b) Dynamic(c) Type(d) All of the mentionedI had been asked this question in final exam.My question is based upon Xquery topic in section Beyond Relational of SQL Server

Answer» RIGHT option is (d) All of the mentioned

Easiest explanation: The W3C specification ALLOWS TYPE errors to be raised statically or dynamically, and defines static, DYNAMIC, and type errors.
Discussion
15.

Point out the correct statement.(a) XQuery statements are case sensitive and xml is case sensitive(b) XQuery statements are case insensitive andxml is case insensitive(c) XQuery statements are case sensitive and xml is case insensitive(d) XQuery statements are case insensitive since xml is case sensitiveI got this question in examination.I'm obligated to ask this question of Xquery topic in chapter Beyond Relational of SQL Server

Answer»

Correct OPTION is (a) XQUERY statements are case sensitive and XML is case sensitive

To ELABORATE: In Sql Server XQuery statements are case sensitive since xml is case sensitive. Hence while query to xml data remember this THING.
Discussion
16.

What is every identifier in an XQuery called?(a) Name(b) QName(c) Qidt(d) QnamThe question was asked in a job interview.This question is from Xquery in division Beyond Relational of SQL Server

Answer» CORRECT CHOICE is (B) QName

The best EXPLANATION: A QName is made up of a namespace prefix and a local NAME. In this implementation, the variable names in XQuery are QNames and they cannot have prefixes.
Discussion
17.

Every database you create has which collection by default?(a) sys(b) sym(c) syx(d) none of the MentionedThe question was posed to me in examination.This intriguing question originated from Xquery in section Beyond Relational of SQL Server

Answer»

The correct OPTION is (a) SYS

Explanation: sys stands for SYSTEM and it reserves these SCHEMAS so they can be ACCESSED from any user-created XML schema collection.
Discussion
18.

SQL Server provides how many Xquery methods?(a) 3(b) 4(c) 5(d) 6I have been asked this question during an online exam.Question is from Xquery topic in portion Beyond Relational of SQL Server

Answer»

Correct option is (c) 5

To elaborate: SQL SERVER PROVIDES FIVE XQUERY methods to query xml FILE or xml data.
Discussion
19.

Which of the following function is contained by both full text search and Xquery?(a) search()(b) contain()(c) contains()(d) query()This question was posed to me in a national level competition.My doubt stems from Working With XML Data topic in section Beyond Relational of SQL Server

Answer»

Right option is (c) contains()

For explanation: A full-text SEARCH that uses contains() and XQUERY contains() has DIFFERENT SEMANTICS.
Discussion
20.

Which of the following function returns an instance of the XML data type?(a) query()(b) value()(c) nodes()(d) none of the MentionedThis question was posed to me in a national level competition.I'd like to ask this question from Xquery in division Beyond Relational of SQL Server

Answer»

Correct choice is (a) QUERY()

The EXPLANATION: xml.query() method TAKES an XQuery statement and returns an instance of the XML data type.
Discussion
21.

Point out the correct statement.(a) If ALTER INDEX ALL is specified, it applies to both non-XML and XML indexes(b) The rebuild and set option IGNORE_DUP_KEY is not valid for XML indexes(c) The option DROP_EXISTING is permitted in the ALTER INDEX statement(d) The modifications of the primary key constraint in the user table are not automatically propagated to XML indexesThe question was asked in final exam.The question is from Working With XML Data in division Beyond Relational of SQL Server

Answer»

Correct answer is (c) The option DROP_EXISTING is PERMITTED in the ALTER INDEX statement

For explanation I would say: The USER MUST DROP the XML indexes FIRST and then re-create them.
Discussion
22.

Different xml modes available for xml clauses are __________(a) RAW(b) AUTO(c) PATH(d) All of the mentionedI have been asked this question during an interview.I need to ask this question from Working With XML Data in portion Beyond Relational of SQL Server

Answer»

The CORRECT OPTION is (d) All of the mentioned

The best explanation: WITHIN the FOR XML clause,we SPECIFY an XML mode: RAW, AUTO, EXPLICIT or PATH.
Discussion
23.

Sample applications to illustrate XML functionality in SQL Server are _____________(a) Implementing XML in SQL Server(b) XmlOnlineCatalog(c) XmlSurvey(d) All of the mentionedThis question was posed to me by my school principal while I was bunking the class.Enquiry is from Working With XML Data topic in division Beyond Relational of SQL Server

Answer»

The correct CHOICE is (d) All of the mentioned

Easiest EXPLANATION: Sample application are mostly WRITTEN in Production.ProductModel table.
Discussion
24.

Following content of XML elements are indexed in full text?(a) Content of the XML value(b) Non numerical value in content of XML attributes(c) The content of XML elements.(d) None of the mentionedThe question was asked in a job interview.My query is from Working With XML Data in chapter Beyond Relational of SQL Server

Answer» CORRECT choice is (b) NON numerical value in content of XML ATTRIBUTES

To explain I would say: The content of XML attributes of the top-level element only, UNLESS those values are numeric values are INDEXED only.
Discussion
25.

Statement for dropping XML index is __________(a) DROP INDEX(b) DELETE INDEX(c) REMOVE INDEX(d) None of the MentionedThis question was addressed to me at a job interview.Asked question is from Working With XML Data topic in chapter Beyond Relational of SQL Server

Answer»

Right answer is (a) DROP INDEX

For explanation: The DROP INDEX (Transact-SQL) Transact-SQL statement can be USED to drop existing primary or secondary XML and non-XML INDEXES.
Discussion
26.

Which of the following indexes can be created on XML columns?(a) Primary(b) Secondary(c) Full-text(d) All of the MentionedI have been asked this question in an internship interview.I'd like to ask this question from Working With XML Data in portion Beyond Relational of SQL Server

Answer» RIGHT choice is (d) All of the Mentioned

To explain I would say: You can ALSO create full-text INDEXES on XML columns. However, the indexes IGNORE the XML markup and include only element content.
Discussion
27.

SQL Server lets you define how many types of secondary XML indexes?(a) 3(b) 4(c) 5(d) 6I have been asked this question in an online quiz.The doubt is from Working With XML Data in division Beyond Relational of SQL Server

Answer» CORRECT ANSWER is (a) 3

To EXPLAIN I would say: Secondary indexes are PATH, VALUE, and PROPERTY.
Discussion
28.

What is the purpose of the primary XML index?(a) Persisted representation of data(b) Shredded representation of data(c) Structured representation of data(d) None of the MentionedI have been asked this question in a national level competition.The above asked question is from Working With XML Data topic in portion Beyond Relational of SQL Server

Answer»

Right option is (d) NONE of the Mentioned

Explanation: Primary indexes all TAGS, paths, and values, along with such details as node TYPE and document order INFORMATION.
Discussion
29.

Which of the following is not a xml data type method?(a) query()(b) value()(c) nodes()(d) none of the MentionedThe question was posed to me in unit test.Question is from Working With XML Data topic in portion Beyond Relational of SQL Server

Answer»

Right option is (d) NONE of the Mentioned

The best I can explain: The XML data type comes with four METHODS that ASSIST with querying and MANIPULATING the XML data stored in the column or variable. Those methods are query(), value(), nodes() and modify().
Discussion
30.

SQL Server 2008 has made several extensions to the XML Schema support which includes?(a) Support for lax validation(b) Added support for list and union types(c) Improved handling of xs:time(d) All of the mentionedI had been asked this question in an interview.Question is taken from Working With XML Data topic in portion Beyond Relational of SQL Server

Answer»

Correct answer is (d) All of the mentioned

The EXPLANATION: Upgrade steps automatically OCCURS when you attach your SQL Server 2005 database to SQL Server 2008 for XML extension FEATURE support.
Discussion
31.

Point out the wrong statement.(a) Typed XML type and Untyped XML both have XML schema to validate the Data(b) Typed XML type has advantage of storage and query optimizations where as Untyped XML type does not have this advantage(c) Typed XML types can be validated at both client side and Server side where as Untyped XML types does not have option to validate on server side as they do not have any schema(d) None of the mentionedI had been asked this question at a job interview.I would like to ask this question from Working With XML Data topic in section Beyond Relational of SQL Server

Answer»

Correct answer is (a) Typed XML type and Untyped XML both have XML schema to validate the Data

To explain I WOULD say: Untyped XML type is preferred in SCENARIO when you have schema, but you do not WANT the server to validate the data.
Discussion
32.

Can we use untyped XML data type in the following situations?(a) You have schema for your XML data and you want the server to validate your XML data according to the XML schema(b) You want to take advantage of storage and query optimizations based on type information(c) You want to take better advantage of type information during compilation of your queries(d) You do not have a schema for your XML dataI got this question in final exam.Enquiry is from Working With XML Data topic in division Beyond Relational of SQL Server

Answer»

The correct option is (d) You do not have a SCHEMA for your XML data

The best EXPLANATION: Untyped XML TYPE does not have schema.
Discussion
33.

Which operation cannot be performed on XML schema collection?(a) DROP(b) DELETE(c) CREATE(d) UPDATEI got this question during an interview.This interesting question is from Working With XML Data topic in chapter Beyond Relational of SQL Server

Answer» RIGHT CHOICE is (b) DELETE

To EXPLAIN I WOULD say: XML schema collection is a metadata entity like a table in the database.
Discussion
34.

Which of the following statement would import additional schema or schema components into an existing collection object?(a) IMPORT XML SCHEMA COLLECTION(b) ALTER XML SCHEMA COLLECTION(c) UPDATE XML SCHEMA COLLECTION(d) ADD XML SCHEMA COLLECTIONI have been asked this question in an interview.My question is based upon Working With XML Data in chapter Beyond Relational of SQL Server

Answer»

The CORRECT answer is (b) ALTER XML SCHEMA COLLECTION

Explanation: ALTER XML SCHEMA COLLECTION ADDS new schema COMPONENTS to an existing XML schema collection.
Discussion
35.

The space use of XML indexes can be found in the table-valued function named?(a) sys.dm_db_index_physical_stats(b) sys.dm_db_index_phy_stats(c) sys.dm_db_index_plan_stats(d) sys.dm_db_index_table_statsThis question was addressed to me in exam.The origin of the question is Working With XML Data topic in chapter Beyond Relational of SQL Server

Answer»

Right option is (a) sys.dm_db_index_physical_stats

The explanation is: sys.dm_db_index_physical_stats provides information, such as the NUMBER of DISK pages OCCUPIED, average row SIZE in bytes, and number of records, for all index TYPES.
Discussion
36.

Contents of sys.xml_schema_collections are _____________(a) Number of disk pages occupied(b) Indexing Capabilities(c) Predefined namespaces(d) None of the MentionedThe question was asked during an interview for a job.The above asked question is from Working With XML Data in portion Beyond Relational of SQL Server

Answer»

The CORRECT OPTION is (C) Predefined namespaces

The best I can explain: sys.xml_schema_collections contains the namespaces for XML, xs, XSI, fn, and xdt.
Discussion
37.

What is index type for XML index entries?(a) 3(b) 4(c) 5(d) 6This question was addressed to me by my college professor while I was bunking the class.The query is from Working With XML Data in portion Beyond Relational of SQL Server

Answer»

The CORRECT CHOICE is (a) 3

The EXPLANATION: XML index entries appear in the catalog view, sys.indexes, with the index “TYPE” 3.
Discussion
38.

Which of the following following is used for retrieving XML Schema Collections?(a) XML_SCHEMA_SPACE(b) XML_SCHEMA_NAMESPACE(c) XML_SCHEMA_NAMES(d) None of the MentionedThe question was asked during an online interview.I would like to ask this question from Working With XML Data in portion Beyond Relational of SQL Server

Answer»

The CORRECT choice is (b) XML_SCHEMA_NAMESPACE

For EXPLANATION: Built-in function XML_SCHEMA_NAMESPACE YIELDS an xml DATA type INSTANCE.
Discussion
39.

What are Catalog Views for XML?(a) Schema collections(b) View Collections(c) Unstructured data(d) None of the MentionedThe question was posed to me in examination.Question is from Working With XML Data in chapter Beyond Relational of SQL Server

Answer» RIGHT option is (a) SCHEMA collections

For explanation I would SAY: CATALOG views exist to PROVIDE metadata information about XML use.
Discussion
40.

Which of the following feature of SQL Server was used before XML technology for semi structured data?(a) Stored Procedure(b) Dynamic management views(c) In memory database(d) None of the mentionedI have been asked this question in a job interview.Enquiry is from Working With XML Data topic in division Beyond Relational of SQL Server

Answer» RIGHT CHOICE is (a) Stored Procedure

The EXPLANATION: You need to create a stored procedure to process INCOMING XML from a partner before SQL Server 2005.
Discussion
41.

XML datatype was introduced in which of the following versions of SQL Server?(a) SQL Server 2000(b) SQL Server 2005(c) SQL Server 2008(d) SQL Server 2012The question was asked in final exam.This interesting question is from Working With XML Data in section Beyond Relational of SQL Server

Answer»

Right option is (B) SQL Server 2005

To EXPLAIN: XML data type allows variables and columns to be created to specifically hold xml data, whether it be an entire XML document or just some XML CONTENT.
Discussion
42.

Which of the following part of the XML data stored in an XML column is very important for locking?(a) Granularity(b) Degree of Structure(c) Hierarchy(d) None of the mentionedThe question was posed to me in homework.This intriguing question comes from Working With XML Data in division Beyond Relational of SQL Server

Answer» CORRECT choice is (a) Granularity

Explanation: When the granularity is large, LOCKING large XML INSTANCES for updates CAUSES throughput to DECLINE in a multi user scenario.
Discussion
43.

Reasons to use native XML features in SQL Server instead of managing your XML data in the file system would be:(a) You need language support for query and data modification for cross-domain applications(b) You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schema(c) You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer(d) All of the mentionedThis question was addressed to me in unit test.This interesting question is from Working With XML Data topic in portion Beyond Relational of SQL Server

Answer» RIGHT answer is (d) All of the mentioned

To elaborate: If none of these conditions is satisfied, it may be better to store your data as a non-XML, large object type, such as VARCHAR(MAX) or VARBINARY(max).
Discussion
44.

What is the hybrid model in SQL Server?(a) Combination of relational and XML data type columns(b) Combination of relational and non relational data type columns(c) Using XML with views(d) Using XML with triggersI got this question at a job interview.This is a very interesting question from Working With XML Data topic in portion Beyond Relational of SQL Server

Answer»

Correct answer is (a) Combination of RELATIONAL and XML data type columns

Best explanation: HYBRID model may YIELD BETTER performance in that you have more control over the indexes CREATED on the relational columns and locking characteristics.
Discussion
45.

XML View Technology is useful in the following situations?(a) Your application needs system catalog views to administer your XML data and XML schema(b) Indexing Capabilities(c) You want to bulk load XML data and decompose them into the underlying tables by using the XML view(d) None of the MentionedI got this question by my school principal while I was bunking the class.This intriguing question comes from Working With XML Data in section Beyond Relational of SQL Server

Answer»

Right option is (c) You WANT to bulk load XML DATA and decompose them into the underlying tables by using the XML VIEW

Explanation: XML bulk load can be used to POPULATE the underlying tables by using the XML view.
Discussion
46.

In which of the following scenario, using XML native storage would be inappropriate?(a) Fixed schema(b) You want to query and modify your XML data(c) You want to index the XML data for faster query processing(d) Your application needs system catalog views to administer your XML data and XML schemaI had been asked this question during an interview.This key question is from Working With XML Data topic in division Beyond Relational of SQL Server

Answer»

The correct choice is (a) FIXED schema

For EXPLANATION I would SAY: XML is appropriate when we may or may not have a schema for your XML DATA.
Discussion
47.

The choice of XML technology, native XML versus XML view, generally depends upon the following factors?(a) Storage options(b) Query capabilities(c) Indexing(d) All of the MentionedI had been asked this question in an online interview.Query is from Working With XML Data in division Beyond Relational of SQL Server

Answer» CORRECT choice is (d) All of the Mentioned

The explanation: Different XML CHOICES may ALSO have different PERFORMANCE characteristics.
Discussion
48.

Which of the following is not a XML storage option?(a) Native storage as XML data type(b) Mapping between XML and relational storage(c) Small object storage(d) None of the MentionedI got this question in exam.Query is from Working With XML Data in chapter Beyond Relational of SQL Server

Answer»

The correct answer is (c) Small OBJECT storage

Easiest EXPLANATION: XML USES LARGE object storage which uses varchar(max) and varbinary(max).
Discussion
49.

Which of the reasons will force you to use XML data model in SQL Server?(a) Your data is sparse or you do not know the structure of the data(b) Your data represents containment hierarchy(c) Order is inherent in your data(d) All of the MentionedI had been asked this question in an online interview.Enquiry is from Working With XML Data in section Beyond Relational of SQL Server

Answer»

Correct answer is (d) All of the Mentioned

Explanation: XML is a good choice if you WANT a platform-independent MODEL in order to ENSURE the PORTABILITY of the data by using STRUCTURAL and semantic markup.
Discussion