SQL Server Interview Questions You'll Most Likely Be Asked is a perfect companion to stand a head above the rest in today's competitive job market. Rather than going through comprehensive, textbook-sized reference guides, this book includes only the information required immediately for job search to build an IT career. This book puts the interviewee in the driver's seat and helps them steer their way to impress the interviewer.
Table of Contents
Includes:
a) 202 SQL Server Interview Questions, Answers and Proven Strategies for getting hired as DBA
b) Dozens of scenario based examples to respond to interview questions
c) 51 HR Questions with Answers and Proven strategies to give specific, impressive, answers that help nail the interviews
d) 2 Aptitude Tests Software download available
Sample from the book
58: How does columnstore index improve the query speed?
Answer:
The columnstore index improves the query speed by using the following approach:
a) Columnstore index uses VertiPaq compression algorithm with which column data is compressed and high level compression of data can be achieved
b) With columnstore index, less space is required to store data in memory. Because of this, data transfer from disk to memory is reduced which improves the query speed.
59: Explain Batch-mode processing.
Answer:
Batch-mode processing is an advanced technology used to process queries that retrieve columnstore index data. The column data is processed in batches using the vector technology that is highly efficient which works well with columnstore index. This batch-mode processing utilizes Hash Join and Hash Aggregation algorithm which optimizes the hardware architecture and improves parallelism.
60: Explain about segments in columnstore index.
Answer:
Each column data is a segment which contains data up to 1 million rows. Each row is called a row group. Each segment is stored internally in a LOB (Large Object). So, when SQL Server reads data, each segment processes data between disk and memory.
61: What are the restrictions in columnstore index?
Answer:
The restrictions in columnstore index are:
a) Can’t enable
b) If tables and its columns use ‘Change data capture’ (To monitor if data has been modified), then it is not possible to use columnstore index
c) If columnstore index is applied on a table, then data replication over server is not possible
d) Columnstore index can’t be created on indexed view
119: Explain custom proximity operator with an example.
Answer:
Custom proximity operator is used to find a particular word if that exists near another word. For example to find the word ‘query’ that exists before the word ‘insert’ in a particular document we use the custom proximity operator.
For example; Select * from MyTable where contains (*, ‘near((query, insert), 6, false)’)
In the above query we are using near to identify if the word query exists exactly 6 tokens before insert.
120: Explain statistical semantic search.
Answer:
The statistical semantic search is a new feature in SQL Server 2012 which extends full text search and provides the exact meaning of the actual phrase. This search also identifies the meaning of the particular search phrase and provides the exact match for the particular phrase from the unstructured documents that exists in the database engine.
121: How will you identify if semantic search is configured for a database?
Answer:
Identify whether semantic search is configured using the below query:
Select serverproperty(‘IsFullTextInstalled’)
GO
If the value is returned as 1, then semantic search is configured. If the value is returned as 0, then semantic search is not configured.
154: What databases are installed during installation of Data Quality Server?
Answer:
The Data Quality Server will install the following databases:
a) DQS_MAIN: The primary database containing the published knowledge and stored procedures that support DQS engine
b) DQS_PROJECTS: For internal use to store data related to data quality projects and managing knowledge bases
c) DQS_STAGING_DATA: Used for intermediate data storage to perform DQS operations and to store processed data
155: What roles must be mapped for each user before using DQS_
Answer:
The following roles must be mapped to each user during installation of DQS_
a) dqs_kb_editor: This role is used to create and edit the knowledge base
b) dqs_kb_operator: This role is used to edit and execute data quality projects and to monitor data
c) dqs_administrator: This role can perform the operations of an editor and operator; they can add new users, stop any activity, stop a process within an activity, and perform any configuration using Data Quality client
156: What are the tasks available in Data Quality Client?
Answer:
There are three types of tasks available in Data Quality Client:
a) Administration: View the status of data quality projects and knowledge base management that are used in Integration Services
b) Knowledge Base Management: Create a new knowledge base or edit the existing knowledgebase
c) Data Quality projects: Create and execute data quality projects to perform data-cleansing and data matching tasks
179: Explain the Master Data Manager in MDS.
Answer:
Master Data Manager is a web based application for managing the master data. MDS administrators can manage the object model and configure security settings using this web manager application. The Explorer and Integrated management functions in the web application are available as a Silverlight component enabling the management process to be easier and faster.
180: How an Entity Member is managed in
Answer:
The Explorer tool in Master Data Services web application allows adding/modifying the members of an entity. Master Data Services in SQL Server 2012 allows configuring an entity to automatically generate the code value. This automatic generation of code value occurs only when the code value is empty. This code value can be always overridden.
181: What is the new improvement in SQL Server 2012
Answer:
SQL Server 2012
