In all my years experience with SQL and being a developer before transitioning to become a DBA I will answer for me personally NO. Each developer has their own niche or style that they use when writing code. I’ve seen some pretty wild and insane ways people develop. I worked with one guy several years ago who would name his counters off t.v. shows so every now and then you might have a skipper or a s.s. minnow thrown in. I hope and trust that development teams have set their own standards better than the one we had years ago…..however even with standards I think as a DBA we need to be able to communicate and bridge the gap that it just makes sense that when you receive a data script or migration script that it is in an expected format than everyone adheres and agrees to with no gotchas.
Recently this standard below was presented; given what the business needs are the script will allow for multiple servers that the script will be allow on. This is particular handy since some people may or may not accidentally run the script on an environment it shouldn’t be ran on. This also is helpful in dealing with a CMS server.
USE [DBName]
IF @@SERVERNAME IN (‘ServerName’,’ServerName’) /*Enter Valid Server Name/Instance*/
BEGIN
BEGIN TRY
BEGIN TRANSACTION
/*Enter Code*/
COMMIT TRANSACTION
PRINT‘Successfully committed!’
End TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ‘An error has occurred. Rolled back!’
PRINT ERROR_MESSAGE()
END CATCH
END
ELSE
BEGIN
PRINT ‘PLEASE EXECUTE ON ServerName ONLY!’ /*change server name*/
END
Whatever your choice of a standard is work toward getting things streamlined and laid out so there are not variations of the same kind of step. When it is up front and in your face as a standard it is easier to follow and maintain.
Having worked as and with both developers and DBAs, I would have to say that, unless a DBA is working with an “acquired” script set or code generated by a code generator (e.g. SMO scripter), DBA scripts would tend to be even less standardized in style than developer scripts. Also, DBA code (because DDL is less parameterizable than DML and not all DBA information sources return sets and DBA code does not usually touch public consumption, e.g. it doesn’t usually appear as backing store to websites) is statistically much more likely to use dynamic SQL and cursors. So, the DBA is not necessarily is a position to “bridge the gap”.
Cheers,
Bob
Hey Bob, awesome response. Yeah the standarization is more tailored to business needs and getting developers to send over their scripts with just basics. Helps our business unit out tremendously. I can see how this can be misconstrued and your points you have made are spot on. This standarization is for simplicity sake not with a more complex type of script. “Bridging the gap” is geared toward the lack of communication that alot of Dev and DBA teams have toward each other. Truly appreciate your feedback and hope you have a gread day!!
Yates