Sunday, January 31, 2010

Interview Questions On Sql Server -Part 2

1. What is the difference between the Union and Union All?

1) Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.

2) The second difference between Union and Union All can be given on the basic of their uses. Union All can be used to insert multiple rows in the table in a single query whereas Union can not be used to insert multiple rows in the table.

3)Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


2. what is the stored procedures?
Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task.There are many benefits of  using a stored procedure. The main benefit of  using a stored procedure is that it increases the performance of the database


3.What are the benefits of using stored procedures?
1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server.It can become more important benefit when the bandwidth of the network is less.Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not  used between Transaction statement and rollback statement is not used.

2) Compilation step is required only once when the stored procedure is created.Then after it  does not required recompilation before executing unless it is modified and reutilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.
3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure. 
5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

4. What are the difference between Stored procedures and Functions?
1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement. 
3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

Read more about difference between stored procedures and Functions 

5.What is the Group By Clause .......... Having Clause?
Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-
  Select column1, column2, column3, aggregate_function(expression ) 
           From TableName Where (condition) 
           Group By Column1, column2, column3
Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-
       Select column1, column2, column3, aggregate_function(expression ) 
       From TableName Where (condition) 
       Group By Column1, column2, column3
       having aggregate_function(expression )  operator value


6. What are the difference between Having and Where Clause?
1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas  we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

 3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

Read more on difference between Having and Where Clause 

7.How we can find out the duplicate values in a table?
We can find out the duplicate values in a table with the help of the Having clause in conjunction with the Group By Clause.

Read more about how to find out the Duplicate values in a table 

8.What are the Views?
Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server.

Read more about Views  

9. What are the difference between Query, Subquery, Nested Subquery and the Corelated Subquery?
Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.




Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.


10.Suppose we have a table whose structure is given below
      Create table addcounter( counterid int identity(1,1))
Now how can we insert values in the table

By using the following Insert Statement
Insert into addcounter defalut values
 








 

  

Interview Questions on sql server -Part-1


1. What is the Normalization?
Normalization:-Normalization can be defined as the process of organization the data to reduce the redundant table data to the minimum. This process is carried out by dividing the database into two or more than two tables and defining relationship between them so that deletion, updation and insertion can be made to just one table and it can be propagated to other tables through defined relationships.


Read more about Normalization 


2. What are the normal forms and what are the different normal forms of Normalization?


Normal Forms: - The normal form can be refers to the highest normal form condition that it meets and hence indicates the degree to which it has been modified. The normal forms are:-

  1. First Normal Form (INF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Boyce- Codd Normal Form
  5. Forth Normal Form (4NF)   
  6. Fifth Normal Form(5NF)
Read more about Normal forms




3. What is the Denormalization?
Denormalization:- Denormalization can be defined as the process of moving from higher normal form to a lower normal forms in order to speed up the database access.

4. What is the Primary key?
Primary Key: - Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. The data help across the primary key column must be unique. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.
Syntax for Primary Key


(ColumnName) (DataType) ((Size)) Primary Key 
Read more about Primary key 


5. What is the Unique key?
Unique key:-Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.


Read more about Unique key 

6. What is the difference between Primary key and Unique Key?

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.
2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message.
3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.
 
Read more about difference between Primary key and Unique key 


7. What is the Foreign Key?
Foreign Key:- Foreign key is used to prevent destroying the link between two tables. In foreign key, the table (Child table) in which the foreign key is defined points to the primary column of another table (Master table). A foreign key can points to the primary column of the same table. In this Foreign key relationship is said to be defined within the same table. Due to foreign key relationship, a value from the primary column of the master table can not be deleted until its all references from the child tables are deleted. Also a new value in the column in which primary key is defined can not be inserted until the value is already existed in the primary column of the master table.

Read more about Foreign Key 



8. What is the check constraints?
Check constraints:- Check constraints are the user defined business rules which can be applied to the database table column. For example a check constraint on the column “Salary” of the table Employee salary can be defined which state that no employee can have salary less than 5000.


Read more about Check constraints

9. What is the difference between Delete command and Truncate command?
 1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.


Read more about difference between Delete and Truncate Command

10. What is the Identity property?

Read about the Identity Property

Related articles


Interview questions on Sql server - Part 2 


Interview questions on Sql server - Part 3



Group by.....Having Clause

Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-
  Select column1, column2, column3, aggregate_function(expression ) 
           From TableName Where (condition) 
           Group By Column1, column2, column3

For Example, suppose we have a table named EmpInfo which contains the information about the Id of the employee and the Id of the project and it contains the following data

Table:- EmpInfo
EmployeeId  Projectid
--------------------------
1                          1
2                          2
3                          2
4                          2
5                          3

Now suppose we want to know about the number of employee belonging to each project then we can use the group by clause as given below:-

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId

This query will give us the following result:-
ProjectId     NumberOfEmployee     
1                    1
2                    3
3                    1

Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-
       Select column1, column2, column3, aggregate_function(expression ) 
       From TableName Where (condition) 
       Group By Column1, column2, column3
       having aggregate_function(expression )  operator value

For example, suppose we want to know which project has more than 2 employee and the total no of their respective employees, we can use the following query

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId having count(employeeid) >2


The query will give us the following result:-
 ProjectId  NumberOfEmployee
2                    3

In the above example, the Having clause further filter the result return by the Group By clause by imposing a condition that only those projectid will be shown who has more than 2 employee.



DMCA.com

Difference Between Having and Where Clause in Sql Server


Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:-

To show the difference between the Where Clause and the Having clause we will going to use the table EmployeeDeptInfo whose create query statement  is given below :-

Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)

and it contains the following data 

Employeeid              Departmentid
1                                  1
2                                  2
3                                  2
4                                  3
3                                  2
2                                  2
5                                  4
2                                  2

1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

For example, the sql query
         Update EmployeeDeptInfo Set departmentid =7 Where employeeid=4 
will work fine but the query
         Update EmployeeDeptInfo Set departmentid =7 Having employeeid=4
will not work

2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas  we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

For example,  the sql query
select * from EmployeeDeptInfo where count(employeeid)>1 
will not  work but the query
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1)
will work fine

3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

For Example, in the below sql Query
 select employeeid, departmentid from EmployeeDeptInfo where employeeid=5

the where clause will  search the table EmployeeDeptInfo for the record whose employeeid is 5 and then show the output.

but in the below query,
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having  employeeid=5

the result are  first grouped by the Group By Clause and then they become again filtered by the condition defined in the having clause. Sometime , like above both queries, we get the same result with the help of Where clause and having clause but which way is best is determined automatically by the optimizer and it select the best way of executing it.



   

DMCA.com

How to find duplicate values in a table

Sometimes duplicate values in tables can create a major problem when we do not make a primary key or a unique key on a table. In these cases we need to find out the duplicate records and need to delete them. We can use the Having Clause to find out that duplicate records.we show this with the help of an example.

Suppose we have a table named EmployeeDeptInfo which have the column Employeid and Departmentid. The query for creating this table is given below:-

Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)

Employeeid contains the Id of the employee and Departmentid contains the Id of the department to which he/she is belonged. This table do not have any primary key defined on it and also it doesnt have any unique key constraint defined on any column.

Suppose the table contains the following data

Employeeid              Departmentid
1                                  1
2                                  2
3                                  2
4                                  3
3                                  2
2                                  2
5                                  4
2                                  2
In this table, entries for the employee having employeeid 2 & 3 are get repeated.In this example the data in the table is not much for the example point of view but in real time scenario it can be billions of rows and duplication of rows can be a very big concern. Therefore it is necessary to find out the duplicate rows in the table. We can use the Having Clause to find out the duplicate rows:-

Query for finding out the duplicate rows in the table:-

Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)

This query will give us the following results:-
Employeeid         Departmentid
2                              2
3                              2

We can also find out that how many times a record is repeated in the table. For example, The following query gives us the result of how many times a given record is repeated

Select Employeeid, Departmentid, count (Employeeid) as NoOfRepeat from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1 and count (Departmentid) >1)


This query will give us the following results


Employeeid           Departmentid        NoOfRepeat
    2                                 2                           3
    3                                 2                           2

Here NoOfRepeat shows the number of times the records are duplicated in the table
Summary
This article shows that we can find the duplicate records with the help of the Group By and Having Clause.

Sql Server View


Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server. For example, suppose there is table called Employeeinfo whose structure is given below:-

Create table EmployeeInfo(EmpId int, EmpName nvarchar(200),EmpLogin nvarchar(20), Emppassword nvarchar(20) , EmploymentDate datetime )

And it contains the following data

EmpId        EmpName         EmpLogin       Emppassword     EmploymentDate 
1                 Vivek Johari         Vivek                  VikJoh           29/01/2006
2                Virender Singh      Virender              Virender         06/02/2007
3                Raman Thakur       Raman                Raman          14/05/2007 
4                Uma Dutt Sharma   Uma                   Uma             30/03/2008
5                Ravi Kumar Thakur  Ravi                    Ravi            30/06/2007

Now suppose that the Administrator do not want that the users have excess to the table EmployeeInfo which contains the some critical information (Emplogin, EmpPassword etc) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and give the permission for the view to the user. In this way the administrator do not need to bother about giving the access permission for the table to the user.

The syntax for creating a View is given below:-

Create View Viewname As
 Select Column1, Column2  From Tablename
 Where (Condition)
Group by (Grouping Condition) having (having Condition)

For Example,
  Create View View_Employeeinfo As 
        Select EmpId, EmpName, employmentdate 
          From EmployeeInfo
Now user can use the view View_EmployeeInfo as a table to get the empid , empname and employmentdate information of the employees by using the giving query

Select  *   from  View_EmployeeInfo  where empid=2

It would gives the following result
    EmpId        EmpName             EmploymentDate
       2              Virender Singh         06/02/2007

We can also use Sql Joins in the Select statement in deriving the data for the view

For Example, Suppose there is a table named EmpProjInfo which contains the information about the employee project. the structure of the EmpProjInfois given below:-

Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))
and it contains the following data
EmpId          Projectname
1                   Abcbank
2                   AtoZfinancialsol
3                   learningsystem
4                   ebooksystem
5                  AtoZfinancialsol

Now we can create a view Vw_EmployeeProj which gives the information about the Employees and its projects

Create view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo 
   inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId

Altering an View

  If we want to alter the view, then we can use the Alter View command to alter the view
For Example,
Alter view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId where EmployeeInfo.EmpId in (2,3,4)

Getting Information about the Views:-
   We can use the System Procedure Sp_Helptext to get the definition about the views
For example,  we can use the sp_helptext command to get the information about the view Vw_EmployeeProj
          sp_helptext Vw_EmployeeProj

Renaming the view:-
    We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:-
  SP_Rename 'Old Name', 'New name'
 For example if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:-
sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping  a View
We can use the Drop command to drop a view 
  For example, to drop the view  Vw_EmployeeInfo, we can use the following statement
Drop view Vw_EmployeeInfo
 
Summary :-
  This article tells us that view can be described as a virtual table which can derived its data from one or more than one table.We can create a view with the help of the Create View command,can alter its definition with the help of Alter view command, get its definition with the help of Sp_helptext command, rename a view with the Sp_rename command and drop a view the Drop view command.



            
DMCA.com

Tuesday, January 26, 2010

Second Normal Form (2NF)


Second Normal Form (2NF) :-A table is said to be in its Second Normal Form if it satisfied the following conditions:-
1) It satisfies the condition for the First Normal Form (1NF),
2) It do not includes any partial dependencies where a column is dependent only a part of a primary key.

For example suppose we have a table EmpProjDetail, which contains the employee details and its project details like projected, project name and durations in terms of days on which he/she is allocated to the project.

Table Name:-EmpProjDetail
Primary Key :- EmpId + projectid

EmpId
ProjectId
EmpName
ProjectName
Days
1
1
Vivek
Abc Bank
35
2
2
Sudeep
AbeBook
10

In this table, the primary key is composition of two columns EmpId and ProjectId. Now this table is in 1NF but it is not in the 2NF since the column EmpName can be depended on the column EmpId and the column ProjectName can be depended on the column ProjectId which violates the second condition for the 2NF.

We can break this table into three different tables to convert it into the 2NF. These tables are given below:-

Table name:- EmpDetails
Primary Key: - EmpId

EmpId
EmpName
1
Vivek
2
Sudeep

Table name:- ProjDetails
Primary Key: - ProjectId

ProjectId
ProjectName
1
Abc Bank
2
AbeBook



Table name:- EmpProjdetails
Primary Key: - empId + ProjectId

EmpId
ProjectId
Days
1
1
35
2
2
10

Now all the three tables are in 1NF and all the columns of these tables are fully depended on their respective primary keys.

Third Normal Form (3NF)

Third Normal Form (3NF) :- A table is said to be in the Third Normal form (3NF) if it satisfy the following conditions:-
1) It should be in the 2NF
2) It should not contain any transitive dependency which means that any non key column of the table should not be dependent on another non key column.

For example, let consider a table EmpProjDetails which contains the details about the employee and its project.

Table Name:-  EmpProjDetails
Primary Key: -  EmpId

EmpId      EmpName       EmpRegDate       Projectid        ProjectName
1                  Vivek               01/01/2006            1               Bankingexpress
2                  Neha                06/06/2007            2               BankingReport


Now, the table EmpProjDetails is in Second Normal Form (2NF), but it is not in the Third Normal Form because the non key column projectName is dependent on another non key column ProjectId. So to convert this table in the Third Normal Form, we need to decompose this table into the two tables EmpInfo and ProjInfo whose structure is given below:-

Table Name:- EmpInfo
Primary Key: - EmpId

EmpId       EmpName          EmpRegDate         Projectid
1                  Vivek                   01/01/2006               1
2                  Neha                    06/06/2007               2


Table Name:- ProjInfo
Primary Key: - ProjectId

ProjectId          ProjectName
  1                   Bankingexpress
  2                   BankingReport


Now the above tables are in the Third Normal Form since they satisfy all the conditions for the Third Normal Form.

First Normal form (INF)

First Normal Form (INF):- A table is said to be in a First Normal Form (1NF)if it satisfy the following conditions:-
1)If the columns of the table only contain atomic values (Single, indivisible).
2)Primary key is defined for the table
3)All the columns of the table are defined on the primary key.

The first condition also implies that no column should contain the set of values. For example, suppose we have a table EmpDetails

Table Name :-EmpDetails
Primary Key:- EmpId

EmpId     EmpName              EmpRegdate         ExpertiseDomain
1                Raman                      23/04/2006              eLearning
2               Vivek Johari               02/01/2006           {Banking, financial, eLearning}

Now, in the above table the column ExpertiseDomain contains a set of values for the EmpId 2. Therefore this table is not in the 1NF form. To make this table in the 1NF, we need to break this table into two tables, one contains the employee information EmpInfo (EmpId, EmpName, and EmpRegdate) and the other table contains the employee expertisedomain information EmpexpertDomain (EmpId, ExpertiseDomain). In both the tables, EmpId will be the primary key.

Table Name:- EmpInfo
Primary Key:- EmpId

EmpId     EmpName            EmpRegdate
1               Raman                   23/04/2006
2               Vivek Johari           02/01/2006

Table Name:- EmpexpertDomain
Primary Key:-( EmpId , ExpertiseDomain)

EmpId           ExpertiseDomain
1                         eLearning
2                         Banking
2                         financial
2                         eLearning

Now these tables is said to be in the 1NF since all the columns of these tables contains the atomic values and all the values of the columns are dependent on the primary keys.

Database Normalization


Normalization :- Normalization can be defined as the process of organization the data to reduce the redundant table data to the minimum. This process is carried out by dividing the database into two or more than two tables and defining relationship between them so that deletion, updation and insertion can be made to just one table and it can be propagated to other tables through defined relationships.

Normalization can be done for the following reason:-
  1. To simplify the database structure so that it is easy to maintain.
  2. To retrieve the data quickly from the database.
  3. To reduce the need of restructuring the database when enhancement of the application required in future.

Normal Forms: - The normal form can be refers to the highest normal form condition that it meets and hence indicates the degree to which it has been modified. The normal forms are:-

  1. First Normal Form (INF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Boyce- Codd Normal Form
  5. Forth Normal Form (4NF)   
  6. Fifth Normal Form(5NF)

Note: - Normalization into 5NF is considered very rarely in practice.

First Normal Form (INF):- A table is said to be in a First Normal Form (1NF) if it satisfy the below three conditions:-

1)      If the columns of the table only contain atomic values (Single, indivisible).
2)      Primary key is defined for the table
3)      All the columns of the table are defined on the primary key.

Second Normal Form (2NF):- A table is said to be in its Second Normal Form if it satisfied the following conditions:-

1) It satisfies the condition for the First Normal Form (1NF),
2) It do not includes and partial dependencies where a column is dependent only a part of a primary key.

Third Normal Form (3NF):- A table is said to be in the Third Normal form (3NF) if it satisfy the following conditions:-

 1) It should be in the 2NF
 2) It should not contain any transitive dependency which means that any non key column of the table should not be dependent on another non key column.

Denormalization:- Denormalization can be defined as the process of moving from higher normal form to a lower normal forms in order to speed up the database access.



Sunday, January 3, 2010

Indexes in Sql server


Indexes-Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.

In Sql Server there are two types of Index

1) Clustered Index
2) Non Clustered Index


How to create Index in the table:-

Non Clustered Index:-



Suppose we have a table tbl_Student and the Sql script for creating this table is given below:-

CREATE TABLE tbl_Student
(
    [StudentId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](150) ,
    [LastName] [nvarchar](150),
    [Phone] [nvarchar](20),
    [Email] [nvarchar](50),
    [StudentAddress] [nvarchar](200),
    [RegistrationDate] [datetime],
    [Enddate] [datetime]
)  


Suppose it contains the following data-




Now First we can check whether the table contains any index or not.For this we can use the following queries:



sp_helpindex tbl_student

select name from sysindexes where id=object_id('tbl_student')



Now, since we don't make any index on the table tbl_student yet, so when we run the sql statement "sp_helpindex tbl_student" , it will give us the following result:-


The object 'tbl_student' does not have any indexes, or you do not have permissions.


Now , we can make the index on this table by using the following Sql  Statement:- 

 Create Index  (Index name) ON (Table name)(Column Name)



Create Index Index_Firstname on tbl_student(FirstName)  
This sql statement will create a non clustered index "Index_Firstname" on the table tbl_student. We can also make a index on a combination of the columns of the table.This can be done by using the following Sql Statement:-    Create Index  (Index name) ON (Table name)(ColumnName1, ColumnName2)
  For Example:- 
 
Create Index Index_StudentName on tbl_student(FirstName, Lastname)   

This Sql Statement will create a non clustered Index Index_studentname on the combination of the two columns FirstName and LastName. 
 
Clustered Index:-  We can also create a Clustered index by using the given Sql Statement:-
 
Create Clustered Index (Index name ) on Table Name (Column Name)

For Example, 
 
Create clustered Index Index_Studentid on tbl_student(Studentid)     
The above Sql statement will create a Clustered index Index_Studentid on the table tbl_student. Now we can use the Sql Statements, which I described earlier to find out all the index made on the table tbl_student If we execute the statement "sp_helpindex tbl_student" , it will give us the following results:-   
 Droping an Index We can drop an Index by using the following Sql Statement:- Drop Index (Index Name) on (Table Name)  
  For Example,
 
Drop Index Index_Firstname on Tbl_student
 
The Above Sql Statement will delete the Index  Index_Firstname on the table tbl_student. Now if we execute the statement "sp_helpindex tbl_student" , it will give us the following results:-    
   There are some difference between the Clustered Index and Non Clustered Index.You can read these difference in my article "Difference between Clustered Index and Non Clustered Index".You can also click on the given url to read this article   http://vivekjohari.blogspot.com/2010/01/difference-between-clustered-and.html