Definitions

INDEXS

An index is an object that exists only within the framework of a particular table or view. An index works much like a book index. An index provides us ways of speeding the lookup of desired information, indexes are of two types

Clustered:
There is one clustered index per table. If an index is clustered, it means that the table on which the clustered index is based is physically sorted according to that index. For instance if you were indexing a book, the clustered index would be the page numbers, the information in the book is stored in the order of the page numbers.

Non Clustered:
There can be more than one Non-Clustered index in a table. This kind of index points to some other value that will let you find the data. So for instance this we can consider in terms of our book , the keyword index at the back of the book.

PS:Views which have indexes or indexed views, must have at least one clustered index before it can have any non clustered index.

----------------------------

Triggers

A trigger is an object that exists only within the framework of a table. Triggers are a pieces of logical code that are automatically executed when certain things such as inserts, updates or deletes happens to your table.

Triggers can be used for great variety of things, but mainly used for either copying data as it is entered or checking the update to make sure that it meets some kind of criteria.

Or

Triggers are a special kind of stored procedure which will be executed automatically based on the user events (DDL or DML).

----------------------------

Savepoint

Savepoint is a named point that allows you to rollback the transaction upto a particular statement. Savepoint does not commit the operations and a transaction can have one or more save points.

Ex:
Begin transaction
statement1
statement2
statement3 (The statements from 1 to 4 will not roll back)
statement4
save transaction p1
statement5 ((The statements from 5 to 6 will roll back))
statement6

Rollback transaction p1
commit transaction.

-----------------------

Deadlock and Blocking
When a transaction conflicts with other transaction, then its called as deadlock, its usually known as 1205 error number.

or

Deadlock are caused when one lock cant do what it needs to do in order to clear because a second lock is holding that resource and vice versa. when this happens someone has to win the battle, so SQL sever chooses a deadlock victim. The dead lock victims transaction is rolled back and is notified that this happened through the 1205 error. The other transaction can continue normally.

Blocking means a transaction is waiting for resource of other transaction, blocking may lead to a chain of transactions.

PS:Blocking or deadlocks degrades the system performance.

-----------------------------

Constraints
A constraint can be defined as an object that exists only within the confined of a table. constraints confine the data into our table to meet certain conditions. constraints, in a way compete with triggers as possible solutions to data integrity issues.

---------------------------------

Views

A view is a virtual table, a view for the most part is used just like a table except the fact that it does not contain any data of its own. Instead, a view is merely a preplanned mapping and representaion of the data stored in tables. The plan is stored in the database in the form of a query. This query calls for a data from some, but not necessarily all, columns to be retrived from one or more tables. The data retrived may or may not be have to meet special criteria in order to be shown as data in that view.

• Views that reference multiple tables generally perform much faster with an indexed view because the join between the tables is preconstructed.
• Aggregations performed in the view are pre calculated and stored as part of the index; again this means that the aggregation is performed one time and then can be read directly from the index information.
• Inserts and deletes have higher overhead because the index on the view has to be updated immediately; updates also have higher overhead if the key column of the index is affected by the update.

Or

A logical structure created based on 1 or more tables

• A view contains only one select statement [stored query].
• View does not contain any rows in it.
• View is also known as virtual table.

In order to create and use views we have two steps
Step 1: Creating a view
Create View viewname
[with encryption]
as select .............. statement.

Step 2: Performing DML Operations through view
select * from viewname
insert viewname(colnames)
values(list of values)

delete from viewname
where

update viewname
set col1=newval
set col2=newval
where

• while performing DML operations through view rows will be effected in or more base tables.
• Performing operations through view also verifies the constraints in the tables
• View may be updatable view or non-updatable view

Updatable View:
If successful DML operations are performed through a view, then its called as Updatable view

Non-Updatable View:
If select operations are performed successfull through a view is called as non-updatable view.

---------------------------------------

STORED PROCEDURE
Stored procedures are ordered series of transact sql statments bundled up into a single logical unit. They allow for variables and parameters as well as selection and looping constructs. sprocs offer several advantages over just sending individual statements to the server in the sense that they

• Are reffered to using short names, rather than a long string of text, therefore less network traffic is required in order to run the code within sproc.
• Are pre-optimized and pre compiled, saving a small amount of time each time the sproc is run.
• Encapsulate a process, usually for security reasons or just to hide the complexity of the database.
• Can be called from other sprocs, making them reusable in a somewhat limited sense.

or

Stored procedures are a set of precompiled transact SQL statements stored in a database server.

There are two types of stored procedures

System stored Procedure:

• These are pre-defined and present in 'master' database.
  
• System procedures are used to perform operations with system tables as well as to perform administrative tasks.
• Every system procedure starts with 'Sp_' ex: sp_helpdb, sp_helptext,sp_depends.

User stored procedure:

User stored procedures are used to perform DML operations with user tables.
In order to work with user stored procedures we have two steps

Step 1: Create Stored Procedure
Create Procedure procname
(

)
as begin
-----------------------
-----------------------
end

Step 2: Calling stored procedure or executing the stored procedure.
exec procname parameters.

-----------------------------------

User Defined Functions

User defined functions is a set of pre complied transact SQL statement stored in the database server.

• User defined function contains only select statments to read data from tables.
• User defined functions does not contain insert,delete and update statements to perform operations with permenant tables.
• User defined functions takes only input parameters.
• Return statement can be used to return scalar value or set of rows.

There are two steps to work with user defined functions

Step1: Create Function name
Create function functionname
(

)
returns return type (which means, any data type except text, image, cursor/timestamp)
as begin
--------------------------
--------------------------
return (scalar value (or) set of rows)
end

Step 2: Calling the user defined function or executing the user defined function.

Ownername.functionname(parameters)
default owner ->.dbo

Types of User defined functions, They are of 3 types

1)Scalar UDF
A user defined function which returns single scalar values is called as scalar UDF

ex:Create a user defined function which returns 'empname','job' of a given employee number

Step1:
Create function get emp details
(
@empno int
)
returns varchar(36) or varchar (max)
as begin
declare @ details varchar(36)
select @ details=empname+','+job from employees where empno=@empno return(@details)
end

Step2: This can be handled in two ways
Method 1:
Declare @result varchar(36)
set @ result=dbo.getempdetails(120)
print @result

Method 2:
select dbo.getempdetails(120)

2)Inline UDF
A user defined function which returns set of rows is called inline udf. Inline UDF can have only one statement that should return statement (No being and no end)

Ex: Create function which returns empno, empname, sal and dname of given deptno employees.

Step 1:
Create function getempbydno
(
@deptno int
)
returns table
as
return (select empno, empname, sal,dname from employees inner join dept on employees.deptno=dept.deptno and employees.deptno=@deptno)

Step 2:
While a function returns a set of rows it can be called same as a table.

select * from dbo.getempbydno(10) ->This statement returns the table having set of (n number ) of rows.


3)Multi-statement table UDF
A user defined function returns a set of rows with multiple statements is called as multistatement table UDF
Return type of multistatement table UDF is table variable.
Ex:
Create a function which returns empno, empname,sal and deptname of a given deptno "employees".

Step1:Creating a UDF
Create function getemployees
(
@deptno int
)
returns @t1 table
(
empno int,
empname varchar(20),
sal money,
dname varchar(15)
)
as begin
-------Copy rows into table variable------------
insert into @t1 (empno,empname,sal,dname)
select empno, empname,sal,dname from employees inner join dept on employees.deptno=dept.deptno and employees.deptno=@deptno
return
end

Step2: Calling UDF function
While calling user defined function which returns set of rows can be called same as table

select * from dbo.getemployees(10)->This will return table variable where we have set of rows.


Table variable
Table variables are used to prepare one dimension arrays (list of values) and prepare two dimenstion arrays(set of rows)

• Table variables can store list of values or set of rows temporarily.
• Table variable memory is allocated in RAM

ex:Prepare one dimension array to store collection of integers temporarily.

declare @arr table
(
col1 int
)
insert @arr values(10)
insert @arr values(20)
insert @arr values(30)
select * from @arr

ex: Prepare a table variable to store collection of rows temporarily
Declare @t1 table
(
col1 int primary key,
col2 char(10)
)
insert @t1 values(10,'abc')
insert @t2 values(20,'def')
select * from @t1

------------------------------

Properties of table variables

1. Table variables existed in the memory as long as script is executed.
2. Table variables cannot be defined with relationships.
   

----------------------------------

Temporary Tables

• Temporary tables are used to store set of rows temporarily.
• Temporary tables memory is allocated in tempdb database.

Properties of temporary tables

1. Temporary tables exist untill we close the connection.
2. Temporary tables can be defined with relationships also.
3. Temoprary tables can be dropped explicitly also by the following command. Ex: Drop table #emp or ##emp.
   

Two types of temporary tables

1)Local Temporary table
#Tablename-> Accessible from only current connection.

Ex
Create table #emp1
(
empno int primary key,
empname varchar(20)
)
insert #emp1 values(10,'lmno')
insert #emp1 values(20,'pqrs')
select * from #emp1

2)Global Temporary tables
##Tablename->Accessible from all connections.

Ex:
Create table ##emp2
(
empno int primary key,
empname varchar(20)
)
insert ##emp2 values(10,'yedj')
insert ##emp2 values(20,'nhtr')
select * from ##emp2

-------------------------------

Dropping a function
Drop function functionname
Ex:drop function getemployees

---------------------------------

Transaction and locks
Its a logical unit of work which contains one or more operations, every transaction follows ACID properties

ATOMICITY
It guarantees that if all the operations are completed and then the transaction is committed, if any of the operations fails then transaction is rolled back.
or
Transaction is all or nothing

Consistency
Its about providing a correct data to the transactions, it can be implemented with locking mechanism.
or
All constraints and other data integrity rules have been adhered to, and all related objects (data pages, index pages) have been updated completely.

Isolation
One transaction failure should not effect the other transactions in the system
or
Each transaction is completely isolated from any other transaction. The actions of one transaction cannot be interfered with by the actions of a separate transaction.

Durability
It Guarantees that result of every committed transaction should be permenantly saved in database tables.
or
After a transaction is completed, its effects are permanently in place in the system. The data is "safe", in the sense that things such as a power outage or other non-disk system failure will not lead to data that is only half written.

---------------------------------

Types of transactions

SQL Server supports two types of transactions
1)Implicit transactions:
One statement creates one transaction implicitly, implicit transactions are automatically committed.
To control the behaviour of implicit transactions, set implicit_transactions ON/OFF
->OFF - Implicit transactions automatically committed and we cannot rollback (or) undo->its by default.
->ON - Implicit transactions are not committed automatically and we can roll back.

Ex:
Set implicit _transactions ON
GO
Delete from employees
Now, in order to retrive the lost data the command is roll back.

**In order to set implicit transactions ON for every connection by default follow the steps as given below:

Open Object explorer->Connect to a Database engine->select server and then right click properties->select connections page->check the option implicit transaction->Click OK.

2)Explicit transactions
These transactions are used to execute group of operations inside a single transaction.
In order to prepare explicit transactions transact SQL provides TCL commands (Transaction control language)
TCL Commands:

1. Begin Transaction: It Creates transaction process.
2. Save Transaction:It creates save transaction.
3. Commit Transaction:Transaction operations result is permanently saved in actual database tables.
4. Rollback Transactions:Transaction operation results not saved in actual database tables (Undo).

Advantages of Explicit Transactions:

• Explicit transactions are used to execute group of operations in single transaction
• E transaction allows you to verify the the buisness conditons.