Wednesday, March 7, 2012

Maximum Allowed Stored Procedure in SQL Server

Here is something that perhaps a lot of you out there wonder about..

My company is on a large Enterprise Project.
The database plans for this to be fully supported predict 1000+ stored procedures.

Question:
Will there be any limitation for an SQL Server 2000 to handle so many stored procs?

and if there is a limitation what will we have to do in order to work around it?The limitation for the total number of objects in a database is 2,147,483,647. This includes all the objects (Tables, views, functions, stored procedures etc) ...|||A fellow programmer said that there would be a problem with this number of procs. However he isn't famous for his experience :)

Thanks for the answer, it figures that there isn't any actual limitation of the number of objects you can handle. This guy was probably wrong.|||The practical limit is too high given the situation to be reached. The only issue I see is the maintenance of such huge number of stored procedures ... If you have a proper process to keep track, you are safe for sure building such solutions ...|||Agreed, you should not hit any physical limitation. I think you should carefully plan out naming conventions for your stored procedures so that you can keep things organized. One convention I like is:
sp
module name
underscore(_)
action (lower case)
noun (proper case)

For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory

Also, you should strongly consider using standard documentation at the top of the stored procedures if you are not doing so already. I like to list the author, date created, purpose, where the sproc is used, any notes, and a revision history (with date, author, and summary of change).

Terri

No comments:

Post a Comment