What is Indexed View in SQL Server

Indexed Views:

A view whose query result is stored in database is called as indexed View.

·       Indexed views dramatically improve the performance of some types of queries

Indexed views are created mainly for two reasons

1 To improve performance of aggregate operations

2 To create local copy for remote db.

If we want to create an Indexed View we need to do the following:

·       Create a View by using the with Schema Binding Option.(create view with schema binding)

·       Create a Unique Clustered Index on the View (create index on that view).

Step1:

Create view with schema binding

Create view indv1 with schemabinding

As

Select deptno,sum(isnull(sal,0)) sumsal, count_big(*) emps from dbo.emp group by deptno

Step2: create index on that view.

Now, I am creating unique clustered index on group by column i.e. deptno

Create unique clustered index i11 on index (deptno)

Select * from indx1

Update EMP set sal=sal+1000 where deptno=10

After creating indexed view if, we make any changes to base table the changes are reflected to index view automatically.

NOTE:

Once the index is created on the view it will internally the store the information of the View physicially in a location, any manupulation performed on the base table reflects to the the View also.

Difference between View and Indexed View

      

VIEW	INDEX VIEW
When view is created the query is stored in database.	When index view is created the query result stored in db
View does not contain any data.	Index view contains data.

Synonym in sql server

Synonym

A synonym is another name or alternative name of the table

They are created

     If table name is lengthy

     To access table without owner name

Syntax:

Create synonym<name> for table name

Example

createsynonym s1 for emp

select* from s1

Dropping synonym

dropsynonym s1

Difference between view and synonym

      

View	Synonym
View is a subset of a Table.	Synonym is a mirror of a Table
View can be based on more than one table.	It is always based on one table.

INDEX IN SQL SERVER

Indexes are used to make the data retrieval fast index in relational database can be compared to index available at the end of a book using which you can find the required topic in the book fast.

Sql server provides a system tables called sysindexes that contains details of index available on tables in the database

Every table in the database will have minimum one row in this table even if it has no index.

Internally the data will be stored in the database in pages where size of each page is 8 kb.

 A continuous 8 pages is called as an Extent. When a table is created, than one extent will be allocated for it, and when that extent is completely filled than another extent will be allocated that may or may not be continuous to the first Extent.

In database indexes are created on particular column.

The column on which the index is created is called “Index Key”.

In sql server searching for a particular record is of two types.

1 table scan

2 index scan

Table scan

Each and every record as to be compared than sql server scan the entire file.

It consumes more time.

Explanation:

When  you write a select statement on the table with a condition in “where condition” than first sql server will refer to “IndId” column of sysindexes table to verify wheather the column on which you write the condition has an index in the table.

When this column contains zero, than it indicates that the table has “No Index” and in this case it will refer to the column “firstIAM” of sysindexes table, to get the address of the first extent of the table and check the given condition with each and every row available in every page of every extent of the table and displays only the rows that satisfy the given condition. This process of checking the given condition with each and every row of the table is called as table scan.

Drawbacks of table scan

As no of rows increase in the table, the time taken to retrieve the data will increase and will effect the performance of the database.

Index scan

In index scan, an average sql server scans only half of the file which is must faster than table scan.

Sql server uses index in the following cases:

If where condition or order by clause is based on the column on which the index is created.

Note:

Sql server does not follow index in the following case.

1 If where clause or order by clause is based on the column on which the index is not available.

2 creating no of index on particular table degrades performance of DML operations.

To maintain the balance, so create indexes on columns frequently accessed in where clause and order by clause.

Index in sql server are classified into

·          Clustered index

·          Non clustered index

Clustered index:

Is the index that will arrange the rows physically in the memory in sorted order

Advantage of clustered index is searching for a arrange of values will be fast. You can create only one clustered index per table.

If order of the values in table and if order of values in the index is same this index is called as clustered index.

Explanation:

Clustered index is internally maintained with the help of B-Tree Data Structure and leaf node of B-Tree of clustered index contains table Data. When you write select statement on the table with condition in “where clause” then first sql server will refer to “INDID” column of sys index table to verify whether or not the column on which you write the condition has index.

When INID column contains the value 1 , than it indicates that the table has a clustered Index and in this case it will refer to a column called “Root “ to get the address of the Root Node of B-Tree of clustered index and searches in the B-Tree to find the leaf node  that contains the first row that satisfy the given condition and retrieves all the rows that satisfy the given condition , which will be continuous in the memory.

A Drawback for clustered Index is insert, update will be slow because the root must be inserted or updated in sorted order.

To make insert , update fast we use fillfactor option while creating the clustered index.

If you set the fillfactor to 70, than in every page of the table 70% will  be filled with Data and remaining 30% will be left free.

As “freespace” is available on every page inserted and update will be little bit fast.

Sql server by default creates clustered index on primary key column.

Example:

Create table xyz(x int, y int, z int)

Create clustered index i10 on xyz(z)

Insert into xyz values (1,2,10)

Insert into xyz values (4,3,12)

Non clustered Index

Is the index that will not arrange the rows physically in memory in sorted order.

Advantages of Non Clustered Index

Searching for the values that are not in the arrange will be fast.

You can create a maximum of 999 Non Clustered Index per Table.

Non Clustered Index is also maintained with the help of B-Tree data structure, But leaf Node of B-Tree of Non Clustered Index contains pointer to the pages containing data and Not Table Data.

Explanation:

When you write a select statement with a condition in “where clause” then first sql server will refer to “InDId” column of  sysindexes table to verify whether  or not the column on which you write the condition has an Index.

When “IndId” col contains value in the range of 2 to 1000 then it indicates that the column on which you write the condition has a

“Non Clustered Index” and in this case  it will refer to the column Root to get address of the root Node of B-Tree of Non Clustered Index and then searches in the B-Tree to find the leaf Node corresponding to the value you are searching for and retrieves all rows containing that value based on pointer available in the leaf Node.

With non clustered index there will be no effect on Insert and Update.

Clustered index	Non Clustered Index
It will arrange the rows physically in memory in sorted order	It will not arrange the rows physically in the memory in sorted order
The leaf node of B-Tree of clustered index contains the table data.	The leaf node of B-Tree of Non Clustered Index contain pointer to the pages containing data.
It will effect insert and update	It will not effect insert and update
It will be fast in searching for a arrange of values	It will be fast in searching for values that are not in a range.
You can create  only one clustered index per table	You can create a maximum of 999 Non clustered Index per table.

Based on No of columns on which index was created, Index are classified into

1 simple

2 composite

Simple:

When an index is created on single column than that index is called as simple index.

Composite:

When an index is created on combination of multiple columns than that index is called composite Index.

Create index i2 on EMP(deptno, job)

Select * from emp order by deptno, job

Select * from EMP where deptno=10 and job=’clerk’

Sql server does not perform composite index on OR operator.

Unique Index:

When an index is created using the keyword unique than that index is called as unique index.

When a unique index is created on a column than along with index unique constraint will be created on that column, which will not allow duplicate values. When you try to create a unique index on a column that contains duplicate values than you will get error.

Primary key- clustered index

Unique- non clustered index

Create a index

To create a index use the create index command that has the following syntax

Create [unique] [clustered| Non clustered Index] index <indexname>  on <tablename>(<columnname> [asc|desc]

[include(<collist>)]

[with fillfactor=<n>]

The following example creates a non-clustered index on department no col of emp table.

Create index dnojobidx on emp(deptno,job)

Altering an Index

To alter an index use Alter Index Command that has the following syntax

Alter Index <idxname> on <tablename>

Rebuild| Reorganise|Disable

Rebuild:

It willrecreate the entire Index.

ReOrganize:

Will rearrange only leaf nodes of B-Tree of the Index

Disable:

Will disable the Index.

When you want to enable the disable Index, than you must alter the Index using Rebuild option.

Example

The following example rebuild the Index dnoldx available on deptno col of emp table

Alter index dnoldx on emp Rebuild

Getting List Of Index

To get the list of index available on  a table used the stored procedure that has the following syntax

Example

The following example gets the list of index available on emp table

Sp_helpindex ‘emp’

Deleting the Index

To delete an Index use the Drop Index command that has the following syntax

Drop Index <idxname> on <tablename>

The following example deletes the Index DnoIdx available on Dept No col of emp table

Drop Index DnoIdx on Emp

 Using Index

It is not possible to explicitly specify to use an index while retrieving the data, and sql server will automatically determine whether or not to use an Index.

Sql server will use an Index to retrieve the data only in the following cases

When the index is simple index, than the column on which the index is created must be in the condition in “where clause”

When an index is a “composite Index” then all the columns on which combination the index was created must be in the condition in where clause.

Important Points

The column on which index was created are called key column for that index.

Maximum no of key columns per an index is 16 and total size  of key column must not exceeds 900 bytes

Key columns   16               900 bytes

Deptno                int                4

Job                      varchar(20)  20

                                           24 bytes

When you have to create an index on combination of more than 16 columns or combinations of columns whose size exceed 900  bytes than specify the additional columns as include columns for the index.

Maximum no of include columns per an Index is 1023(less than 1 of Total no of columns to create table)

Include columns will be included in the Index but there size will not be counted towards the size of the Index.

A column can be either a key column or include column but not both.

Observing the difference between data with and without index.

To observer the difference in retrieving data without index and with index follow the following steps

1 create a copy of the table emp with the name emp1 as follow

Select * into emp1 from emp

2 write the following insert statement on table emp1 to take the rows from table emp and insert them into emp1 again and again repeatedly execute this statement until you get 1000 of rows on the table emp1

 Insert emp1 select * from emp

Write the select statement on table emp1 to find the employees working in dept no 20

Select that statement and use the shortcut (Ctrl+l) to get estimated execution plan , place the mouse pointer over the table scan icon and note down the estimated c.p.u cost.

Select * from Emp where Deptno=20 

Constraints in sql server

Are the conditions specified on the column in table to maintain data integrity within the database?

Making sure only valid data is stored in the database is called as data integrity.

Constraints in sql server are classified into

1 declarative integrity constraint

2 procedural integrity constraint

1 procedural integrity constraint

Are the constraint that cannot be specified while creating the table and can be specified only by using triggers?

Declarative integrity constraint

Are the constraint that can be specified while creating the table?

Declarative integrity constraint can be specified either at column level or table level.

Note:

 When we have to specify a declarative integrity on a single column then it can specified either at column level or table level, but when you have to specify declarative integrity constraint on combinations of  multiple columns then you must specify at table level.

Declarative Integrity Constraint

Not Null

When we insert rows into the table without providing values for one or more columns in a table then the values in those columns is called as NULL

When we don’t want to allow Null into a columns then specify Not Null constraint on that column.

Not null constraint can be specified only on a single column and it cannot be specified on combination of multiple columns hence it can be specified only on single column, and it cannot be specified at table level.

Syntax : <Column Name> data type Not Null

It allows duplicate values.

Default: Is used to provide default value for a column that will be automatically inserted into the column when user insert a row into that table without providing a value for that column.

DEFAULT constraint can be specified only on single column and it cannot be specified on combination of multiple columns and hence it can be specified only at column level and it cannot be specified at table level.

Syntax:

<Column name> <data type>

Default <default value>    

Unique:

Is used for not allowing duplicate values into a column

Note:

When we specify unique constraint on a column or combination of columns then an index will be automatically created on that column or combination of columns which is by default “Non Clustered Index”

Super key:

A column or combination of any columns in a table that can be uniquely identify the rows in a table is called as super key.

 If we take a table with the name departments with the columns deptid, deptname, and city as example

Then the super keys available in that table are as follows:

Deptid

Deptname

Deptid, deptname

Deptid, city

Deptname, city

Deptid, deptname, city

Candidate key

Among the available super keys with minimum no of columns is called as candidate key.

In the above example of department table there are two candidate keys

Deptid,

Deptname

Primary Key:

A column declared within the primary key doesn’t allow duplicate and Null Values.

When we want to use a column or combination of columns in a table to uniquely identify the rows then specify the primary key constraint on that column or combination of column.

Syntax:

<Colname> <data type>

[Constraint <constraint name>]

Primary key

Table level

[Constraint <constraint name>

Primary key (<column name>)

On primary key column also an index is automatically created, which is by default “clustered Index”

Foreign Key or Referential Integrity Constraint

Is used to restrict the user from inserting only those values that are available in a referenced column

A column to be referred by a foreign key must have primary key.

Foreign key allows Nulls and duplicates values.


Rules

Delete Rules

It specifies how child record is effected if the parent record is deleted

On delete no action

On delete cascade

On delete set null

On delete set default

On delete no action

If foreign key is declared without any delete rule , then parent record can’t be deleted if

along with child records

matching child records exists

On delete cascade

If foreign key is declared with  on delete cascade, if we delete the parent record the parent is deleted

On delete set null

If foreign key is declared with on delete  set null, then if you delete the parent record , parent record is deleted without deleting the child records, but the child record foreign key value is set to Null

On delete set Default

If foreign key is declared with on delete set Default. If parent record is deleted then child record foreign key value set to default value available on foreign key column

Update Rules

On update no action

Parent cant be updated , if matching child records exists

On update Cascade

If the foreign key is declared with on update cascade, then if the primary key is updated matching foreign key also updated automatically

On update set null

The foreign key declared with on update set null, then if the primary key is updated the matching foreign key value set to null

On update set Default

If primary key is updated so matching foreign key value set to default value

To specify Default Constraint In GUI

To specify Check Constraint

Computed Columns in SQL SERVER

Computed Columns

Are the columns for which values will be automatically computed having Insert /Update?

To create a column as computed column , while creating a table next to the column name use the keyword as followed by the formula based on which value has to be computed for that column.

While inserting rows into the tables having computed columns, you must use the following insert syntax of insert statement by excluding columns.

Insert table name (col, col2) values (va1, val2)

The following example creates a table with name Marks by making total, average and grade as computed columns

Create table marks(sid int, c int, cpp int,sql int, total as c+cpp+sql, Average as (c+cpp+sql)/3.0,

Grade as case

When (c+cpp+sql)/3.0 >=90 then 'Distinction'

When (c+cpp+sql)/3.0 >=70 then 'First Class'

When (c+cpp+sql)/3.0>=55 then 'Second class'

When (c+cpp+sql)/3.0>=35  then 'Third Class'

Else 'Fail'

End)

insert marks(sid,c,cpp,sql) values(100,40,30,20)

insert marks(sid,c,cpp,sql) values(100,40,40,40)

insert marks(sid,c,cpp,sql) values(100,70,70,70)

select * from marks

100 40  30  20  90  30.000000 Fail

100 40  40  40  120      40.000000 Third Class

100 70  70  70  210      70.000000 First Class

Creating Computed Column in GUI

While creating a table in GUI, If you want to make a column as computed column click on column name

Go to column properties available at the bottom Expand the property computed column specification and by using property formula specify the formula based on which value has to been computed for that column and press Enter

.