Student Parent - StudentTelephoneNumber / StudentAddress Child
Student / StudyProgram Parent - StudentStudyProgram Child
Given the above structure, what would be the best way of constructing a query in which the input would be a text value, and the output would be a wildcard (or, ideally, a proximity) search across ANY of the above tables? In other words, if I wanted to search for "Macaroni", it would search for any value in Student.FirstName, Student.LastName, StudentTelephoneNumber.TelephoneNumber, StudentAddress.City, StudentAddress.PostalCode, and StudyProgram.Name, ideally as a proximity search (values that resemble "Macaroni", like "Macarony" or "Macaronee"), though if necessary, as a wildcard serch (values that contain "Macaroni", like "I like Macaroni" or "Macaroni is the best"), how would I best construct the query?
The obvious answer is to build it like follows:
DECLARE @Input VARCHAR(MAX)
SET @Input = 'Macaroni'
LEFT JOIN StudentTelephoneNumber ON StudentTelephoneNumber.StudentID = Student.ID
LEFT JOIN StudentAddress ON StudentAddress.StudentID = Student.ID
LEFT JOIN StudentStudyProgram ON StudentStudyProgram.StudentID = Student.ID
LEFT JOIN StudyProgram ON StudyProgram.ID = StudentStudyProgram.StudyProgramID
(Student.FirstName LIKE '%' + @Input + '%') OR
(Student.LastName LIKE '%' + @Input + '%') OR
(StudentTelephoneNumber.TelephoneNumber LIKE '%' + @Input + '%') OR
(StudentAddress.City LIKE '%' + @Input + '%') OR
(StudentAddress.PostalCode LIKE '%' + @Input + '%') OR
(StudyProgram.Name LIKE '%' + @Input + '%')
I worry about performance issues with constructing the query like that, though. While there will only be around 15000 active records in each table, these records will be supplanted every few months with a new list of records, with those that no longer constitute the list being set to "deleted", though still remaining in the table. Since I'm using wildcard searches on both sides, indexes would serve no purpose, so I worry that the query will start taking longer and longer to run. Also, as the project grows, it is likely that more columns will be requested to be searched, so I'd like to minimize both the extra work necessary to modify the query, and the extra time added to the searching. I'd also of course have to validate the input to avoid potential injection attacks.
I've heard that full-text search could work for this, but I'm not really sure how I would go about constructing the query to handle it. I'm also not sure if the performance of the full-text search would be up to par, or if it would suffer the same problems. Finally, I'm not sure how the full-text search maintenance would affect the database, IE, when importing new lists, or modifying the existing ones.
Anyone have any suggestions? I don't need someone to construct a full answer for me, just to point me in the right direction.
NOTE - if any potential solutions involve a version of SQL Server beyond 2008, these are also acceptable.