Sql server interview questions and answers-Part-2

QUESTION - What is a transaction and what are ACID properties?          

ANSWER -  Transaction encapsulates SQL commands and work as a single unit.          

All the SQL statements defined in the transaction should work successfully.
Every transaction follow tranaction properties ACID i.eAtomicity, Consistency, Isolation, Durability to qualify as valid transaction.

 

QUESTION - Explain different isolation levels defined in SQL Sever.          

ANSWER - Isolation levels  determine the degree of isolation of data during concurrent access.          

Read Uncommitted, Read Committed, Repeatable Read, Serializable are the different isolation levels defined in SQL Server.          

The default SQL Server isolation level is Read Committed.          

SET TRANSACTION ISOLATION LEVEL allows to define the isolation level at the connection level.

 

QUESTION - What is lock escalation?          

ANSWER - Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks).          
Each lock is a memory structure. Too many locks mean more memory being occupied by locks.          

SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.          

 

QUESTION - Define SQL Profiler.          

ANSWER - SQL Profiler is used to diagnose a problem by capturing events in a trace file.
It is useful to step through queries to find the reason of slow performance.
You can monitor SQL server performance in order to tune workload.
SQL profiler also supports auditing of actions on the server.

 

QUESTION - Explain SQL Server Service Manager.          

ANSWER -  SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server.
The service components are as follows:
SQL Server service
SQL Server Agent service
Microsoft Search service
MSDTC service
MSSQLServerOLAPService service

QUESTION - What is SQL Query Analyzer?          

ANSWER - SQL Query Analyzer is a tool that are used for various purposes such as

Creating and executing queries.
Creating database objects.
Executing stored procedure.
Analyzing query performance.
Inserting, updating, or deleting rows in a table

 

QUESTION - Define DTS service in SQL Server.          

ANSWER -DTS, Data Transformation Services is used to import and export data between heterogeneous data source.
A DTS package can not only transfer data from a table to another, but can also specify query as source of data.

 

QUESTION - What is Replication in SQL Server?          

ANSWER -Replication allows creating copies of data in separate databases along with keeping all databases synchronized by replicating modification in one copy to all the copies.
Data can be effectively distributed over network using replication.

 

Question - Define Bulk Copying and Distributed Queries of SQL Server.

Answer - Bulk copying is used to transfer large amount of data.
Distributed Queries in SQL server are used to reference heterogeneous data source such as a Microsoft Access database or Oracle database.

 

Question - Define SQL Server Agent.
Answer - SQL server agent is important service of the server where instances of SQL server are running.
The agent is responsible to run tasks that are scheduled to occur at specific times or intervals.
The agent is also responsible to run replication task set by the administrators.

QUESTION - Difference between DELETE and TRUNCATE commands in SQL Server.          

ANSWER - DELETE TABLE is a logged operation, it is a slow process.          

TRUNCATE TABLE deletes all the rows, but it won't log the deletion, conquently it is fast process.          

TRUNCATE TABLE can't be rolled back.

QUESTION - What are constraints in SQL Server?          

ANSWER - Constraints enforce the data integrity to the database and protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server.

QUESTION - What is an index?          

ANSWER - Indexes of SQL Server are similar to the indexes in books.          

They help SQL Server retrieve the data quicker.          

Indexes are of two types. Clustered indexes and non-clustered indexes.          

Rows in the table are stored in the order of the clustered index key.          

There can be only one clustered index per table.          

Non-clustered indexes have their own storage separate from the table data storage.          

Non-clustered indexes are stored as B-tree structures.          

Leaf level nodes having the index key and it's row locater.          

Disadvantages of the Indexes are

Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).          

Every time data changes in the table, all the indexes need to be updated.          

Indexes need disk space, the more indexes you have, more disk space is used.

QUESTION - What is RAID?          

ANSWER - RAID stands for Redundant Array of Inexpensive Disks.          

It provide fault tolerance to database servers.          

There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.

QUESTION - Reasons of poor performance of query.          

ANSWER - No indexes          

Excess recompilations of stored procedures.          

Procedures and triggers without SET NOCOUNT ON.           

Poorly written query with unnecessarily complicated joins,          

Highly  normalized database design.          

Excess usage of cursors and temporary tables.