September 14, 2009 at 3:30 pm
Hello,
I am a newbie who started working as a SQL Server developer and I spend my whole day creating stored procedures. I wanted to get some inputs/advices from the senior DBA's to know some best practices while creating stored procedures.
Ex: I got an advice from a senior DBA that stored procedures in 2008 shouldn't be named with sp_xxx as system procedures too are named with sp_xxx this would create a duplicity problem.
Some one also gave me an advice that using comments for each of the parameters helps in the future.
Similarly if you can share anything that could guide me and other newbies would help!
I appreciate you taking your time.
September 15, 2009 at 9:17 am
You might start with:
I would also suggest developing a naming standard for all objects so that you can clearly tell what they are and what they do.
CEWII
September 16, 2009 at 10:45 am
I second Elliot's approach.
A naming convention is something every single shop has to have.
In regards to comments and documentation in general... no ammount of documentation is too much. Don't be shy and write everything you know about the matter.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2009 at 11:50 am
All good ideas and the link Elliot gave has some good information in it. I particullarly like Pauls suggestion on documentation.
I will caution folks that statements like "Use only ANSI code" are not appropriate for all envirionments. There are 2 basic environments to be supported by SQL Server... GUI code and batch code. My recommendation is that GUI code should be as ANSI compliant (and therefor, almost portable) as possible. Batch code is different... batch code may be processing millions of rows and you should use whatever techniques SQL Server has in it to process the data as accurately and quickly as possible. That sometimes means very little ANSI compliance if you really want the code to fly for some of the more complex things you can do in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2009 at 12:15 pm
I'd like to make an additional comment on documentation, inline documentation is a must. When you look at code there should be comments there explaining sections. If you have a section that is particularly.. exotic, the more comments the better, the goal being that someone later (not you) could look at the code and have a clear understanding of what and why. If you do something that seems odd, then you should absolutely comment on it and explain the rationale. I'll give an example.
I had a process that used table variables, to squeeze some additional performance out of it I tried temp tables. I was lucky enough to have already added timing metrics into the sproc for each major and some minor sections so I could see how each ran. What I found was very interesting. The first section ran substantially better with table variables, but the later sections floundered. The second section ran substantially better with temp tables, but horribly in the early sections. There was a CLEAR point that the performance took a dive. So my decision, good or bad, was to use BOTH. At the point performance would falter I copied all the data from the table variable into a similarly structured temp table, and away we went. Even with the cost of the data copy, the process still ran eight times faster with the hybrid approach. Now I went through this long paragraph to illustrate a point, I was able to emperically show that this method was a good one and had clear performance gains, but someone coming later and not knowing that would look at that and wonder what I had been smoking when I wrote the code. So what did I do, I added a comment that spelled out what and why and the udnerlying rationale, so if in the future they saw it they could understand my choice and not just change it because I didn't know what I was doing..
CEWII
September 16, 2009 at 3:09 pm
Nice!
May I?
Long, long time ago I worked as Assembly programmer, we were converting Assembly code from IBM/370 to ICL/10
Problem was, ICL assembly language lacked BR statement, a branch statement where you branch to an address you previously load into a registry... lets say BR R14 would act like a "go to the-address-in-system-registry-14". Got it?
So... what to do in such a conundrum?
The idea - and it worked like a charm - was to translate those BR statements as B statements - which a an inconditional branch to a given location but, how in the world would ICL's B statement know such an address when the address would depend of a previous condition?
Here is what we did. We made our program to alter its own code on-the-fly, in the place where IBM/370 code used to load the desired address in Registry 14 we "moved" the address on top of our own code altering the Branch Inconditional statement. Got it?
Needless to say... we only got green light after showing our manager not only that it worked but also the short novel we have added as comment in the source code.
Ahhh... loved that old-n-good times 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2009 at 3:48 pm
The more exotic the more documentation you should have..
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply