CODEWITHSUNDEEP
sql-serverindexing
Yoosuf
Yoosuf

Reputation: 892

Using INDEX in SQL Server

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

Upvotes: 3

Views: 261

Answers (3)

VdesmedT
VdesmedT

Reputation: 9113

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 !

Upvotes: 1

Born2BeMild
Born2BeMild

Reputation: 544

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%'

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48537

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.

Upvotes: 4

Related Questions