SQL Server and regular expressions

Question

Hello !

Which will be better among following option ?

1. Regular-expressions in SQL Server searches, as new versions support CLR objects ?
2. Full text search ?
3. Lucene.net combined with SQL Server ?

Our database will be having millions and millions of records and we will be providing Google-like search option, and like Google searches for anything, we will be searching some specific categories only.

Please help.

Answer 1

For searching large amounts of data, you want a full text index. Regular expressions are more flexible and can provide more power to your users to express their queries, but it will be slower.

Lucene is a fine choice, but you might find that the built-in features that SQL Server has already meet your needs.

Answer 2

Despite being a fan of all things SQL Server, I would favor lucene.net over SQL Server's Full Text Search.

Answer 3

1. Regular Expressions are fine as long as your data is small, very small.

2. Full text Search with SQL is good choice. I do not personally do not like this option because the search syntax isn't as expressive as Lucene.Net. But either way it is a good way to quickly get some full text search going, without going into a lot of details.

3. Lucene.Net gives your more control/responsibility of creating and maintaining the index, so if this doesn't scare you away then Lucene.Net gives your high quality results and you can do a lot with it. You can customize and tweak just about everything to get your search engine working the way you want it to work. I would personally choose Lucene.Net.

In sort.

1. don't use regular expressions.
2. SQL Server Full Text Search is a quick and easy way to get a decent search out of it, without being to technical.
3. Lucene.Net is the best for it's quality of results, but requires you to go through some learning (if your new).