What is the best way to create and manage global Procs and UDFs in SQL Server?

Question

Suppose I have two separate databases on the same instance of SQL Server. DB1 is a database related to trade and placement. DB2 is a variable price market database.

Both databases have the concept of working with time / date objects, and I created some handy UDFs. Let's assume again that I am creating some convenient math functions that I would like to call from all databases. What is the best way to create and organize them?

i.e. Should I create UDF and SP in the main database? How can I group all of my CustomDateTime UDFs, is it better to create something in the schema to replace .dbo with .myDateTime or .myMath?

The suggestions were appreciated because I don't like the way I currently put most of my functions into DB1, and I know that many will be related to DB2?

Answer 1

Create a database project using Visual Studio (I believe that 2008 and 2010 support it). Store object scripts within the project. Check the project and its associated files in the form of version control software (SVN, git, hg, SourceSafe ...).

You must have a database administrator or a dedicated person who is responsible for deploying code changes to your work environment. You can customize the database project using scripts before and after deployment, which will simplify working with them.

I cannot recommend saving user objects to master ; IMHO, you are better off with copies in each database. If you manage your code as described above, it doesn’t matter that they are duplicated, since the source is in the same managed location.