IGNOU BCA MCA Students - VIVA Question Answer for SQL Server Part I

IGNOU BCA MCA Students - VIVA Question Answer for SQL Server

SQL Server with .Net Selected Question Answer - PART I

Ques 1) What are the difference between clustered and a non-clustered index?

Ans) A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Ques 2) What's the difference between a primary key and a unique key?

Ans)  Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non- clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Ques 3) What is SQL Server Agent?

Ans)  SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.

Ques 4) What are differences between function and stored procedure?

Ans:

1) Function returns only one value but procedure returns one or more than one value.

2) Function can be utilized in select statements but that is not possible in procedure.

3) Procedure can have an input and output parameters but function has

Ques 5) How to find out which index is defined on table?

Ans: sp_helpindex tablename

Ques 6) What are the advantages of using Stored Procedures?

Ans - They help in reducing the network traffic and latency which in turn boosts application performance.
- They help in promoting code reuse.
- They provide better security to data.
- It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients.
- It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.

Ques 7) What do you mean by ACID?

Ans - ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here's the relevance of each quality:
- Atomicity is an all-or-none proposition.
- Consistency - it guarantees that your database is never left by a transaction in a half-finished state.
- Isolation - it keeps transactions separated from each other until they’re finished.
- Durability - it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.

Ques8) What is Trigger? What is Nested Trigger?

Ans) A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger

A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.