Using INDEX in SQL Server

Question

I need to create am index in SQL however it needs to display the records by entering only a part of the name. The index should be created to retrieve the student information using part of the student name (i.e. if the name if Johnanesburg; the user can input John)

I used the syntax below but it wont work

create index Student ON Student(SName)
SELECT * FROM Student WHERE StRegNo LIKE A% 
go

Answer 1

I think your problem is here: A%

Try wrapping it in apostrophes.

SELECT * 
  FROM Student 
 WHERE StRegNo LIKE 'A%'

Also, you may want a GO statement after you create your index.

Answer 2

The index you are creating over SName will not provide as much benefit for the select statement you are running as one created over StRegNo. Assuming that StRegNo is the primary key on the Student table you could try:

CREATE CLUSTERED INDEX IX_Student on Student(StRegNo)

SELECT * 
FROM Student 
WHERE StRegNo LIKE 'A%'

However it appears that the SQL you have provided is at odds with your question. If you want to search based on student name then you might want the following instead.

CREATE NONCLUSTERED INDEX IX_Student on Student(SName)

SELECT * 
FROM Student 
WHERE SName LIKE 'A%'