Differences between Clustered index and No clustered index:

Clustered Index                                             Non-Clustered index

1) We can have only one-clustered index in a table	1) We can have 249 non-clustered indexes in a table
2) To create clustered index Sql server required more memory. Because the leaf pages in the tree structure will maintain actual data.	2) To create non-clustered index Sql server requires less memory. Because the leaf pages will contain pointers to actual data
3) By using clustered index retrieving data is faster, when we compare with non-clustered index.	3) By using non-clustered index retrieving data is slower than clustered index.

Advantage of Indexes

·         The database engine can use indexes to boost performance in a number of different queries. Sometimes these performance improvements are dramatic.

Disadvantages:

·         If we create more indexes on a small table it will degrade the performance.

·         If the table is having some indexes on it when we are performing insertions into that table, it will be time consuming.

Select Command syntax:

Select <column names>

From <table name>

Where <condition>

Group by <group name>

Having <condition>

Order by <key>

Select statement contains 6 clauses :

Ø  Select clause specifies column names

Ø  Specify table name in from clause

Ø  Where clause specifies condition, which ever rows in the table satisfy the condition are display other rows are ignored.

Ø  Group by clause form groups on these selected rows

Ø  Having clause selects some groups and ignores others, those groups which satisfy the condition present in the having clause is selected and others ignored.

Ø  Order by clause display data in ascending or descending order

Ø  After group by only column name is allowed

Ø  Having clause can contain aggregate functions

Ø  Where clause cannot contain aggregate function

Aggregate functions or group functions:-

1) Max() It returns the maximum value

2) Min() It returns the Minimum value

3) Sum() It returns sum of the values

4) Avg() It returns average of the values

5) Count(*) It returns the number of rows in a table

Example:

create table emp2(empid int,ename varchar(20),sal int,deptno varchar(20))

Insert into emp2 values(1,'Philips',10000,10)

Insert into emp2 values(2,'John',16000,10)

Insert into emp2 values(3,'simth',12000,20)

Insert into emp2 values(4,'David',18000,20)

Insert into emp2 values(5,'Mary',12000,10)

1) Display the Total salary,average salary,highest salary, lowest salary,number of records in emp table?

Ans:     select sum(sal),max(sal),min(sal),count(*),avg(sal)

from emp2

2) Display the Total salary,average salary,highest salary,lowest salary,number of employess working in department 10?

Ans:     select sum(sal),max(sal),min(sal),count(*),avg(sal)

from emp2

                                    where deptno=10

3) Display the department wise total salary ?

Ans: select deptno,sum(sal) from emp2 group by deptno

4) Diplay the department wise total salary,that is greater than 30,000?

Ans: select deptno,sum(sal) from emp2 group by deptno having sum(sal)>30000

5) Display the emp table according acceding order based on salary?

Ans:  select * from emp order by sal

6) Descending order?

Ans: select * from emp2 order by sal desc

Joins in SQL Server 2005