Best practice?

Question

Best practice?

Henrik Staun Poulsen

SSCertifiable

Points: 6552
More actions
January 24, 2004 at 1:40 pm

#107366

Dear All,
Which do you consider the best practice when writing a function for general use on many databases?
1) store it local, one copy per database
2) store it in the master database like this:
use master
exec sp_configure 'allow updates', 1
go
reconfigure with override
go
if exists (select * from dbo.sysobjects where name = N'fn_date' and xtype in (N'FN', N'IF', N'TF')) begin
print 'dropping function'
drop function system_function_schema.fn_date
end
go
Create function system_function_schema.fn_date (@ADate datetime) returns datetime -- not a varchar(10)
as begin
-- 2004-0-24 hsp -
-- cuts off the time part from a datetime variable.
-- as a system function by idea of K. Delaney, Using MS SQL Srv pg 630
return convert( datetime, (convert(varchar(10), @ADate, 120) ), 120)
end
go
exec sp_configure 'allow updates', 0
go
reconfigure with override
go
TIA
Henrik Staun Poulsen
Stovi Software, Denmark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Henrik,

I guess it depends on your "setup" and "usage" of the DBs & Server(s). In my case, individule DBs get moved from server to server alot, and / or backups of DBs get restored to different servers. For this reason I think it is best for me to have the DB objects contained within each DB. IMHO

Once you understand the BITs, all the pieces come together

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I agree with Thomas.

It makes more sense to me, to include them in every database and develop your own rollout routines. Storing in SQL Server's master db is not a good idea, because IIRC in that case you are not supported by Microsoft anymore.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Alamir SSCertifiable Points: 6085 More actions · Answer 3

if you put it in Master ... then you can use in any database worked on this server .. and of course you need to backup the Master DB frequently (Note: the backup of master is very important anywhere) .. but if you want to use it on one database .. then put it on the DB objects contained within this DB.

Alamir Mohamed
Alamir_mohamed@yahoo.com

noeld SSC Guru Points: 96590 More actions · Answer 4

Adding to that you can:

1. Put your Routines in the Model and every time you create a newDB you will get your stuff Automatically.

2. If you are getting into consulting then it may make sense to use a Separate "DBAUtils" DB for Portability issues

Just my 2 cnts

* Noel

shendricks-122100 SSC Veteran Points: 254 More actions · Answer 5

I NEVER (well, hardly ever) put anything into the Master database. I want to keep it as pristine as possible. The same goes for MSDB.

I will put things into Model; not so that I can invoke them from there but so they get propogated into new databases.

The choice then comes down to:

1) Put the Function into every DB

2) Find one DB to hold these "generic" routines.

Putting them into each database was a couple of drawbacks:

1) Duplication of code causes increased space usage. This should be a very minor concern unless done to the extreme.

2) Change Control and Update Rollout - As changes are made to the functions you'll need some mechanism for updating each database.

If you decide to put the routine(s0 into one DB, I would agree suggest creating a DB dedicated to this.

HTH

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 6

Not in master. too easy to forget on restore, rebuild of new server, DR, etc.

Store it in each db, build a script to redeploy to multiple databases if you have to.

Henrik Staun Poulsen SSCertifiable Points: 6552 More actions · Answer 7

What an active newsgroup. Thank you all, for your replies.

I do store things in each database, but I'm reading "Using MS SQL Srv" by K. Delaney, and it looks as if she thinks that storing things in Master is acceptable. I do not.

It leaves my problem outstanding; "how to manage global functions and procedure", and their changes.

How do you build a script to redeploy to multiple databases? Or rather, whats in it? I have the function above, and other similar functions, and a script to check available disk space. I have a calendar table, that I might put in this global script too.

I wish I could do a "#Include MyFunctions.SQL" in a T-SQL script. That would solve most of the problems.

Henrik

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Maybe this would help

http://www.abaris.se/abaperls/

IIRC, if you are familiar with C or C++ you should be able to do something like

common_code.h:

CREATE PROCEDURE get_tbl
SELECT * FROM tbl

get_customers.sql:

#define tbl customers
#include common_code.h

</pseudocode mode off>

Although I don't really think that the C preprocessor would actually expand 'tbl' in the procedure name.

http://www.abaris.se/abaperls/doc/preppis.html might also be interesting.

Note, the above information I got from Erland Sommarskog ( http://www.sommarskog.se ) Ouch, once again I reference him

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]