SQL Server questions for interview

SQL Server questions for interview(List of Question):

Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.

It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to

CREATE PROCEDURE Abc

WITH ENCRYPTION

<< SELECT statement>>

What is a linked server in SQL Server?

It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server access and has the ability to issue distributed queries, updates, commands and transactions.

What is SQL Server English Query?

English query allows accessing the relational databases through English Query applications. Such applications permit the users to ask the database to fetch data based on simple English instead of using SQL statements.

What is the purpose of SQL Profile in SQL server?

SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.

What is XPath?

XPath is an expression to select a xml node in an XML document.

It allows the navigation on the XML document to the straight to the element where we need to reach and access the attributes.

Features and concepts of Analysis Services:

Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data One can create data mining models from data sources and use it for Business Intelligence also including reporting features.

Some of the key features of Analysis Services are:

Ease of use with a lot of wizards and designers.
Flexible data model creation and management
Scalable architecture to handle OLAP

Provides integration of administration tools, data sources, security, caching, and reporting etc.Provides extensive support for custom applications.

What is Analysis service repository?

Every Analysis server has a repository to store metadata for the objects like cubes, data sources etc. It’s by default stored in a MS Access database which can be also migrated to a SQL Server database.

What is SQL service broker?

Service Broker allows internal and external processes to send and receive guaranteed, asynchronous messaging. Messages can also be sent to remote servers hosting databases as well. The concept of queues is used by the broker to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the broker. Service Broker uses the concepts of message ordering, coordination, multithreading and receiver management to solve some major message queuing problems. It allows for loosely coupled services, for database applications.

What is user defined data types and when you should go for them?

User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and null ability.