Enterprise SQL Projects (1000+ Stored Procedures)

Enterprise SQL Projects


---------------------------


When Design is replaced with an Architectural Plan





The following post is intended as a starting point of some main concepts to consider when dealing with ent. sql projects. While it is not a direct question of any kind, it would interest people that are/or was involved in ent. projects and therefore have been troubled with similar problems.





Here is a quick overview of a couple main concepts when you have to deal with a Ent. Projects with 1000+ stored procedures.





DOCUMENTATION:


It is an absolute must to include 100% explanatory code on top of the sps.





FUNCTIONS:


Use functions to the maximum extent to reduce overal stored procedure complexity


a rule of thumb is to have 1 to 10, functions to sps ratio or simmilar.





TRIGGERS:


A lot to say about them that cannot be covered in this context





NAMING CONVENSION:


Your naming convension should be 100% pre-thought and designed, no mistakes allowed in this context as it will cause all stored procedures to be extremely difficult/impossible to browse.





a quick template could look as this:





sp


module name


underscore(_)


action (lower case)


noun (proper case)





For example:


spOrders_putOrderDetail


spMaintainUsers_deactivateUser


spReports_getZeroInventory





(quoted by: tmorton)





my addition to this would be something like:


sp< as a prefix is surtently an overkill when dealing with 1000+ sps and is not needed.





however a lot more complex naming strategies can be used, that will cause the project to be a lot more easy to maintain.