30 Sep 2012

Missing the option of creating table dependent objects within separate files

I have been developing database for over 12 years for use with applications and business intelligence projects. One of the challenges I used to face was how to put the SQL Server database under source control.  Over the years I came across several methods: Direct Database development with nightly backup,  Script(s) place under source control, SSMS Projects. 
Microsoft help to solved the problem when they release Visual Studio Data Database Professional (DataDude), when Visual Studio 2005 was around,  which add a project structure, development methods, build and deployment methods.   Recently Microsoft has release a new replacement database development tool: SQL Server Data Tools, which has some improvements and some missing features. 
One of the missing features is the folder structure choice that which I talk about in another post :  http://zogamorph.blogspot.co.uk/2012/06/how-to-create-old-database-project.html.  Another change is the dropping of the development method of one object to each file, which was started to be relaxed within Visual Studio 2008 release. The option is still there, however it's not easy to apply it as self imposed rule when using the new table designer.   
Here are a few reason while I think having one object per-file is a good idea:
  • Forces all the objects to be named instead of have system created names.  This would help finding build problems within the database project.  Having all the objects name is a good coding practices.  Also make comparing database easier with 3rd party products.
  • Reduce the risk of code being lost as there are less chances of  check-in conflicts.  This  can also help to improve productivity by not having to waste time resolving them.
  • Can help relate / audit deployment changes with source control check-ins.  As if the file has been changed then the object would have been changed.  Meaning you can compare the file changes list within source control to deployment script to make sure that all generated changes within the deployment script are valid.
  • Also can help projects which chooses data model to be the is the version of truth for the database.  As tables can be under data model tool control and database developers can added operational indexes without them being lost each time the data model is synchronise to database project. 
  • Reduce the number for developer to do deployments.  When doing a get latest and if source control only brings down is indexes files then a developer might not have to waste time in deploying the database.
  • It the same style method that some code developers applied to their coding standards.  .Net developers, with the help of Jet Brains Reshaper, put all there objects in single files for some the same reason as I mention above.
I have raise a Microsoft Connect ticket to get a option add to the table designer to allow linked objects to be added as separate file to it easier when sticking to the rule one object per-file.  If you agree with me and would like to see the feature added please vote for it with your feedback against the following ticket: https://connect.microsoft.com/SQLServer/feedback/details/748552/create-table-dependent-objects-within-separate-files