We use TFS 2010 for all source control, including SQL scripts. TFS by itself works just fine for storing and tracking versions, but things really work well if you have Visual Studio 2010. Certain editions of 2008 let you create and maintain database projects, which worked but were really slow, somewhat buggy, and not quite feature-complete. 2010 improved on all three dimensions, and it is now good enough that we are able to use it for all of our projects. With the database project, you can edit a TFS build definition to deploy database projects, so you can also have database deployment and changes as part of an automated build process. Admittedly, this was a bit daunting at first, especially because it's hard to test changes, but there are websites which can help you out with that.
I have used both TFS and Red Gate SQL Source Control. They are different tools with different philosophy's behind them. However, the best advice I can give you is to get a copy of the [Red Gate SQL Server Team-based Development] book. It's free as an e-book. I wrote the articles on database deployment and source code management and tried to put as much of what I knew about both these tools into those chapters. I think it will help. The only thing I will add, you, everyone, should be getting their dtabase into source control. I'd prefer you did it using Red Gate tools, but regardless of how you do it, you should do it. [Here's a blog post] on why I believe this. :
We also use TFS to control all our development projects. It does work really well for us - we are a software house that have a team of developers and there are times when more than one of use needs access to the same project at the same time. TFS makes it easy as you can "branch" off projects and then merge them all together again at the end.