Thanks heaps for the suggestions!

Full Text Indexing - To be honest its not a technology I've used a lot before, It does seems to improve the query time significantly if the search string is over 5 letters long eg like = '%123456%' takes nearly 8 minutes where as contains('"*123456*") takes 10 - 30 seconds so that’s a definite help. Only issue is the fact the ORM I support doesn't handle the full text transparently 🙁

XML - I’ve been using sql 2000 a lot and found XML to be hard to work with. we are now using 2005 so that could be an option, but wouldn’t putting all that data in a field and parse it out cause a performance overhead? XML might be a better option if I wanted to extended the metadata. I will do some research into this idea.

Unfortunately I'm still getting really slow response times even when using the full text index. out of the two options i posted which do you think is a better design?