I use Freefind for my site search. There are other free site search software packages out there too. The problem with Google or Yahoo is that they will only return links on your site that are indexed in their search results index.
I have a dynamic site where new pages are added every few days (the site is about 1500 pages now and growing). Visitors looking for information added in recent days may not be able to find it if I counted on it being in Google's or Yahoo's search results. It might take several days to a week to be included in their index. With Freefind, I can rebuild the search index of my site's web pages anytime I want.
I would recommend the Google services, free for non-commercial applications and very easy to implement. Plus: Major search engines offer probably the most sophisticated SE technology (=best search results).