SQL Server interview questions -set 01

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

AS

<<    SELECT statement>>

GO

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.

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 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 datatypes 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 nullability.

Parameters for user defined datatype:

Name

System data type on which user defined data type is based upon.

Nullability.

For example, a user-defined data type called post_code could be created based on char system data type.

What is bit datatype?

A bit datatype is an integer data type which can store either a 0 or 1 or null value.

Describe the XML support SQL server extends.

SQL Server (server-side) supports 3 major elements:

1. Creation of XML fragments: This is done from the relational data using FOR XML to the select query. 
2. Ability to shred xml data to be stored in the database.
3. Finally, storing the xml data.

Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of

• XML Views:  providing bidirectional mapping between XML schemas and relational tables.
• Creation of XML Templates:  allows creation of dynamic sections in XML.

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 Profiler 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 expressions 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.

What are the Authentication Modes in SQL Server?

a.       Windows Authentication Mode  (Windows Authentication): uses user’s Windows account

b.      Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server

Explain Data Definition Language, Data Control Language and Data Manipulation Language.

Data Definition Language (DDL):- are the SQL statements that define the database structure.

Example:

a.       CREATE

b.      ALTER

c.       DROP

d.      TRUNCATE

e.      COMMENT

f.        RENAME

Data Manipulation Language (DML):- statements are used for manipulate or edit data.

Example:

a.       SELECT - retrieve data from the a database

b.      INSERT - insert data into a table

c.       UPDATE - updates existing data within a table

d.      DELETE

e.      MERGE

f.        CALL

g.       EXPLAIN PLAN

h.      LOCK TABLE

Data Control Language (DCL):-statements to take care of the security and authorization.

Examples:

1. GRANT
2. REVOKE

What are the steps to process a single SELECT statement?

Steps

a.       The select statement is broken into logical units

b.            A sequence tree is built based on the keywords and expressions in the form of the logical units.

c.             Query optimizer checks for various permutations and combinations to figure out the fastest way using minimum resources to access the source tables. The best found way is called as an execution plan.

d.      Relational engine executes the plan and processes the data

Explain GO Command.

Go command is a signal to execute the entire batch of SQL statements after previous Go.

What is the significance of NULL value and why should we avoid permitting null values?

NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.

What is the difference between UNION and UNION ALL?

UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.

UNION: SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2

UNION All: SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2

What is use of DBCC Commands?

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as:

Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.            

Please Share it! :)