1) What is mean by VIEW?
Ans: View is virtual table based on the result set of an SQL Statement.
2) Types of View?
Ans: They are two types of Views
a) User Defined View:
1. Simple View: When we access required data from a single base table
known as simple view. It is also known as updatable view.
2. Complex View: When we access required data from multiple base tables
known as Complex view. It is also known as Non-updatable view.
b) System Defined View
3) What is mean by Stored Procedure?
Ans: It is a Pre compiled SQL Statement which can be saved and Re used.
Stored Procedure accepts input, output and optional parameters.
a) Advantages: It supports faster execution. It reduces network traffic and
provides better security to the data.
b) Dis-Advantages: It cannot be called as part of select/Insert/Update/Delete.
4) What is mean by Function?
Ans: Function is a database object that contains set of SQL statements to
perform a specific task. Function accepts input parameters, Perform actions
and return result. Function always returns either a single value or a table.
5) Types of Functions?
Ans: They are two types of functions
a) User Defined Function:
1. Scalar Function: A function which returns a single value is called scalar
function.
2. In-Line Table valued Function: A function which contains a single T-SQL
statement and returns a table set.
3. Multi-Statement Table valued Function: A function which contains a
multiple T-SQL statement and returns a table set.
b) System Functions:
1. String Functions
2. Date & Time Functions
3. Aggregate Functions
4. Ranking Functions etc.
6) What is mean by Trigger?
Ans: Trigger is a special kind of stored procedure that automatically runs
when an event occurs in the database server.
7) Types of Trigger?
Ans: There are three types of Triggers which are
a) DDL Trigger: DDL triggers are automatically fired when a CREATE, ALTER
or DROP event occurs.
b) DML Trigger: DML triggers are automatically fired when an INSERT,
UPDATE or DELETE event occurs. They are two types of DML Triggers
1. After DML trigger
2. Instead of DML trigger
8) What is mean by Cursor?
Ans: It is a temporary memory or work station allocated by database server at
the time of performing DML operations on table by user.
9) Types of Cursor?
Ans: There are two types of cursor which are
a) Implicit Cursors: These are Default cursors
b) Explicit Cursors: These are created by user and are used for fetching data
from table row by row manner.
10) What is mean by Index?
Ans: Indexes are used to increase search performance. Indexes are used for
query tuning purpose.
11) Types of Index?
Ans: There are five types of index which are
a) Clustered Index
b) Non Clustered Index
c) Covering Index
d) Filtering Index
e) Column store Index
12) Exception Handling?
Ans: Whenever an error occurred while executing program is known as
exception. The mechanism used to resolve the error called Execption handling.
13) What is mean by variable?
Ans: Variables are used to carry data. They are two types of variables
a) Local variable: Local variable always store single value and are created by
using @symbol.
b) Global Variable: These are pre-defined system variables.
14) What is the difference between Stored Procedure and Function?
Ans:
1. The procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE)
statement in it whereas Function allows only SELECT statement in it.
2. Procedures cannot be utilized in a SELECT statement whereas Function can
be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section whereas Function can be.
4. Functions can have only input parameters for it whereas Procedures can have
input or output parameters.
5. Functions can be called from Procedure whereas Procedures cannot be called
from a Function.
15) What is the difference between Temp Table and Table variable?
Ans: Temporary Tables are physically created in the tempdb database and act as the
normal table. Table Variable acts like a variable and exists for a particular batch of query
execution. It gets dropped once it comes out of the batch.
16) Types of Isolation Levels in SQL Server?
Ans: SQL Server provides 5 Isolation levels to implement with SQL Transaction to maintain
data concurrency in the database.
1) Read Uncommitted
2) Read Committed
3) Repeatable Read
4) Snapshot
5) Serializable
17) Query Performance tuning / How can you update slow running query to
get faster?
Ans:
1) Efficient schema design
2) Query optimization
3) To create indexes
4) Build Efficient stored Procedures
5) Understand and analyse execution plan