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
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.
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%'
Ardman got it right regarding your query %A => '%A'. Now as for the index, that's another story that no index can help you with at the time, neither can full text search. If you want to look for names starting with @A (i.e. John%), an ordered index could help but otherwise (i.e. %bur%), you will go for a full table scan !
