I am on the cusp of finishing use of SQL2000 here shortly, but I still needed to set up a full text index for searching on a table. Upon running some of the 1st queries I was extremely disappointed with performance, almost to the point where the solution was not a viable option. I set up the same full text index on SQL2008 R2 and ran the identical query. The performance difference? Astounding. Take a look:
Full Text Index Search results:
SQL 2000: 431 Seconds (7minutes 11 seconds)
SQL 2008 R2: 2 seconds
Wow… (Rows #’s are different because of different database backups). I know SQL2000 relied on the Windows Search Service and this functionality and related catalogs became integrated once SQL2005 was released which helped immensely.
So this is probably not any big news for 90% of folks out there, but if you are still on SQL2000 and need something to look forward to, well here it is.
Full-Text Indexing Overview