--------
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.Just a note on your last comment - I would also like to submit that I have thought for several years now that prefacing stored procedure names with "sp" and / or table names with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but I don't think I've seen a compelling enough reason in years to continue this practice.
Of course, this is just my opinion.|||Russem:
Personally, it's all about readability and how the programmer feels which coding paradigm is easier for them to read. I personally love the Hungarian naming convention. For example, once you get into huge projects, i.e. > 1M lines of code, it gets more difficult to read the code, so by utilizing these prefixes, it sure helps the eyes (and brain)!|||::Just a note on your last comment - I would also like to submit that I have thought for
::several years now that prefacing stored procedure names with "sp" and / or table names
::with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but
::I don't think I've seen a compelling enough reason in years to continue this practice.
Naturally, though, this "compelling reason" some people seem to see has NOT included going to the documentation.
There you would find out that the official documentation says that whatever you name a stored procedure, you do NOT start it with "sp_".
Because contrary to what people that have not read the documentation do think, this means "System Procedure" (not Stored Procedure).
And it DOES make a difference. Let me quote:
::It is strongly recommended that you do not create any stored procedures using sp_ as a
::prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
::
::The stored procedure in the master database.
::
::The stored procedure based on any qualifiers provided (database name or owner).
::
::The stored procedure using dbo as the owner, if one is not specified.
::
::Therefore, although the user-created stored procedure prefixed with sp_ may exist in the
::current database, the master database is always checked first, even if the stored
::procedure is qualified with the database name.
::
::Important If any user-created stored procedure has the same name as a system stored
::procedure, the user-created stored procedure will never be executed.
Prefx if you want, but people following this should have the dignitiy to read the documentation.
Funnily, a lot of "sql gurus" in companies just prefix all "stored procedures" with "sp_" as this is "how ms does it, too".|||I'll prefix variables, sure. But I won't do it with stored procedures (and a standard prefix for all of them) and especially not with tables. I definitely agree with adding a prefix to variable names, though :)
