|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
 Posted - 04/30/2010 : 02:29:30
|
some guidelines
1.use version control tool where you organise the objects as per projects->types,etc groupings
2. Add a comment inside objects describing project for which they are created, purpose and owner along with a brief description on logic
3. Put reusable code in a repository area created on version control. In any case you want to implement a logic check here to see if aythings already present. you may also have a Knowledge library where you put documentation on reusable ones and circulate the links across team.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/30/2010 : 13:38:32
|
We mechanically generate SProcs for CRUD (Get, Save, Delete, Find and Match [a function]) which, on an 80:20 basis, do for most of our tables particular the stupid little tables for This and That lookups that are only ever edited once in a blue moon. For the rest they get hand edited to "improve" them for the other logic that is needed.
We use SVN / Tortoise for Version Control. We don't attempt to integrate it with Visual Studio or SSMS etc (and we use it across a range of tools and filetypes).
Each Sproc, Trigger, View is help in a separate file in the filesystem (we don;t do Right-Click and Edit to change an Sproc in the database, we edit a script, save it to a file, and run that to change the DEV database - and thus we can easily repeat that on Test, Staging and Production databases)
If you are stuck for a system of knowing what your "inventory" is put an Sproc call at the top of each script file so that the relevant information is saved into a table in your database. We do this for Sproc Name + Version Number - so we can easily compare what version a client database has compared to our current DEV machine; therefore we can check what version the client has when they report an error (we might have deployed a couple of "improved" SProcs outside normal procedures), and also to check that everything was correctly deployed during an upgrade.
EXEC dbo.MySP_LogScript @strName='MySprocName', @strVersion='100430',
@strDescription='This is what it does', @strTagList='WIDGET,FOOBAR'
GO
CREATE PROCEDURE MySprocName
AS
...
GO
you can then interrogate your Script Logging table based on TAGS / Description / date / version / etc.
We also use a naming convention universally. It has three parts: Application, Module, Object. Each of these is a short 3/4 letter acronym. For example in the Accounts Application we have a Sales-Order_processing Module and that has tables for things like OrderHeader and OrderItem.
Those tables will be ACC_SOP_ORDH_OrderHeader and ACC_SOP_ORDI_OrderItem
All columns within those will be acc_sop_ordh_ID, acc_sop_ordh_Date and then where there is a join names like acc_sop_ordi_acc_sop_ordh_ID - which is the Order Header ID link in the Order Item record.
Then all SProcs related to Order Header are named ACC_SP_SOP_ORDH_XXX where "XXX" is prescribed for tasks that are common between tables - "Get", "Save" etc.
All variable names in the application use the same name as the column, as do all parameters and working variables in the Sprocs. Thus if we need to change acc_sop_ordh_ID from, say, INT to BIGINT we can do a global FIND of "acc_sop_ordh_ID" across Sprocs, Views, Application Code, Reports, TAGs in CMS, Javascript, etc. and find every related use - which will including finding FKey fields like acc_sop_ordi_acc_sop_ordh_ID
That doesn't directly help with remembering that I have written something before  , but it does mean that all SProcs relating to Order Header table are all grouped alphabetically under ACC_SP_SOP_ORDH_* so they are easy to see in a diary listing.
Just my 2-pennyworth but maybe food-for-thought? |
|
|
Store Procs, Views and Tables For WHAT?
