SQL Server interview questions -PART-2

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.

QUESTION - Ways to troubleshoot performance problems in SQL Server.

ANSWER - SET SHOWPLAN_ALL ON

SET SHOWPLAN_TEXT ON

SET STATISTICS IO ON

SQL Server Profiler

Windows NT /2000 Performance monitor

Graphical execution plan in Query Analyzer