indexes

Data access: SQL server uses following two methods to access data

1). Table scan
2). By using index

Table scan: it has to start from the beginning of the table and scan each and every page for the required data.

By using index: it will traverse through the index tree structure to find the required data and extract the data that satisfy the query criteria.

Indexes: indexes in sql server are similar to index in a book. Indexes are used to improve the performance of quires.

Indexes are generally created for following columns.
1). Primary key column
2). Foreign key column: Frequently used in join conditions
3). Column which are frequently used in where clause
4). Columns which are used to retrieve the data in sorting order

Indexes are cannot be created for following columns
The columns which are not frequently used in where clause.
Columns containing the duplicate and NULL values.
Columns containing images, binary information, and text information.

Types of indexes
1). Clustered index
2). Non-clustered index
3). Unique index
4). Composite index

1). Clustered index: Only one clustered index is allowed for a table. The order of values in a table. Order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.

Syntax:
Create clustered index an (columns)
Create clustered indew emp_clindex an emp (empno)

2). Non-clustered index:
It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.

Max no of non – clustered indexes allowed for table is 249.

Syntax:
Create non-clustered index on table_name
Create non-clustered index emp_sal on emp (deptno, sal)

3). Unique index:
An index with unique constraint. It will not allow duplicate values.

Syntax:
Create unique index on (column)
Create unique index dept_index on dept (dname)


Transactions:
1). Auto commit Transaction
2). Explicit Transaction
3). Implicit Transaction

1). Auto commit Transaction: Every individual transaction is an Auto commit transaction

Example: Update emp set sal = sal + 100 where deptno.

2). Explicit transaction: Transaction begins with Begin Tran and ends explicitly with either commit or rollback
Begin Tran
insert into dept values (10, ‘ACCOUNTING’, ‘NEWYORK’)
rollback
insert operation is cancelled

3). Implicit Transaction:
 Set Implicit_transactions on/off
 update emp set sal = sal + 1000 where job = ‘MANAGER’
 delete from emp where deptno = 10
 commit

Update and delete operations are cancelled

Save point: Save points are like paper markers used to marker the transactions.

Syntax: save begin from
insert into emp (empno, ename) values (100, ‘RAM’)
Save point from A
Update emp set sal = sal + 100 where empno = 7839
Save from B
Insert into emp (empno, ename) values (200, ‘***’)
Delete from emp where empno = 7369
Rollback from B
delete and INSERT will be cancelled
Rollback from A
update will be cancelled.
Commit/rollback
Insert will be saved/int will be cancelled