I have a Visual Studio solution which mostly consists of unit and automated tests. All tests are developed in C#, using NUnit and our own library for database tests.
All my T-SQL code is also included in the same VS solution. Because Windows search frequently fails to find files, I am using VS search, which always finds all the right files as long as they are included in my solution. This also allows me to find both a module's source code and the corresponding unit tests, which is very convenient.
Like most other developers I know, I am using Subversion, which is a wonderful tool. For better integration between VS and Subversion I am using an excellent plugin, VisualSVN, and I am very happy with it.
My T-SQL modules are stored in separate files; when I build, I invoke an Iron Python script which combines all individual objects into a single SQL script, which can build my database from scratch and insert some static data, such as countries and currencies. Also I utilize C++ preprocessor, so that I can use macros:
#define YyyyMmDd(A) (YEAR(A)*10000 + MONTH(A)*100 + DAY(A))
These macros are as convenient to use as scalar UDFs, and as perfromant as inline ones. In my source code, I write this:
and this line expands into this in the script:
SELECT ( YEAR(SaleDate) * 10000 + MONTH(SaleDate) * 100 + DAY(SaleDate) ) AS SaleDateAsInt
without any runtime performance penalty whatsoever. This is great for reusing code. I wish scalar UDFs were supplemented by something like this.
This single SQL script is also checked into Subversion, and other solutions list it as their dependencies, and use it to automatically create a test database from scratch.
One more thing: the expected results for my unit tests are not hardcoded in my tests, they are stored separately in XML files. This dramatically simplifies the maintenance of my unit tests. These XML files are also included in the solution, so that I can search in them using VS search.
How do you store your source code?