Single Point of Administration
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cherring/singlepointofadministration.asp
------------------------------
The Users are always right - when I'm not wrong! -
Example 1
Work performed: Rather than select * from a table, a developer takes the approach to only select the columns required in some SQL code, which is more efficient in essence. The same table is selected from fifteen times in various stored procedures, none of which are documented.
Change required: One of column names has been changed and a new column added.
SPA Solution: A view should have been used to select from the table. 1 change required in 1 view only.
I agree that this technic can save time if you want to save the time to retype a complexe join on 5-6 tables. But if the programmer takes the time to list the columns in the sp's select (as he should), he'll still have to rename the columns (unless a as OldColName is used in the view), and for the new column you'll still have to see which procs actually need it. No time saved there.
Example 2
Work performed: A developer writes multiple stored procedures using xp_sendmail to send mails to five named users.
Change required: One of the users leave and is replaced by someone new.
SPA Solution: Rather than mailing users by name, an email group should have been used. 1 change required in 1 mail group.
Additionally, one separate stored procedure should have been written to use the xp_sendmail in case the group name changes. 1 change required in 1 sp only.
Just to go further... I'd build a single table with all the info required to build the e-mail and have a single proc call the xp_sendmail using that table... and a single sp populate that table
.Example 3
Work performed: An application requires a specially formatted date that a manager prefers. Every page in a web application displays this date in the top right corner. A developer writes the same SQL statement in all the pages.
Change required: A new manager decides he does not like the date and would prefer the format returned from SQL getdate() function.
SPA Solution: Create a UDF in SQL selecting getdate(). If the date format changes in the future, 1 change required in 1 function.
Actually you can't use a GetDate() in a function. You'd have to use a view to select getdate(), then select from that view and reformart correctly in the function and then use in the app.
Example 4
Work performed: A DBA has created multiple tables, all storing the 8 digit product code. For accuracy he/she has chosen to use a char(8) datatype as the manager has confirmed that the code size will never change.
Change required: The Company has merged with another. Their product codes differ and are up to 12 digits long.
SPA Solution: Create a user defined datatype called product. 1 change require to 1 UDDT.
Once the UDDT is bound to a column it CANNOT be modified. You have to unbind the UDDT to each column, drop the UDDT, recreate it and rebind. Now this is where I'd preffer go in the master table (assuming you set the relations correctly) and do one change there and let the wizard propagate the changes to all underlying tables (if it doesn't recreate them all). If the wizard does its usual drop/recreate dance then I'd just find a list of the column in syscolumns a create the script on the fly with a concatenation select and execute... still takes only 2-3 minutes to do (app retesting excluded of course).
-
Thanks for the feedback, Remi.
I seemed to have made some slightly erroneous assumptions when trying to get my point across. It reinforces the fact that you need to check every piece of code that you write to make sure you are conforming to best practice.
------------------------------
The Users are always right - when I'm not wrong! -
It's a nice article and it's true that if you can do the work just once, DO IT JUST ONCE. I'm working with a system here where the same code is rewritten 10-15 times. It's next to impossible to do any major update in that program and not break anything else. This is why I'm gonna build a new one in .net
. -
I agree whole-heartedly with the principle of this article. I have found that particularly where management or organization structure is involved, it is worthwhile writing UDFs or SPs unless you want to rewrite the code every time the organisation has a reshuffle. No matter how much of the organization structure and function you manage to encapsulate in the data, revolution makes the world go round, and someone in authority will decide that they want to change the way things work.
I automatically find myself creating a new UDF in anticipation as soon as I see a requirement with the words 'approval process' appear on my desk.
David
If it ain't broke, don't fix it...
-
"Do it once" approach is great... as long as it's not overdone. I've inherited a system where all stored procedures and functions are written in this fashion.
On one occasion I had to go though 6 nested SPs to get to the actual t-sql that performed business logic. On other occasion I was trying to troubleshoot performance issues. Again, the code used a function which was calling another function which in turn was calling another function etc.. When looking at the execution plan I noticed that there were joins to same table multiple times from different functions. I got rid of nesting functions and the performance improved 10 times.
-
Dito here... I had a single report using 24 objects!!!!!!!!. now it uses only 4 (4 different reports that make a big one).
-
Good ideas presented in the article, just one thing I'd like to point out. In Example 3, you say to use GetDate() in a UDF. That's not possible since GetDate() is non-deterministic. Only deterministic functions can be used in a UDF. The best practice would be to use CURRENT_TIMESTAMP instead of GetDate(). CURRENT_TIMESTAMP is ANSI standard, so it's more portable and you can plan on it being around for a long while. There are no guarantees with GetDate().
-
overall point that the article makes is well taken - oftentimes, the unfortunate reality, however, is that the vision that comes with careful planning and laying the right foundation is not a strong suit of the person that originally designs the database/application etc..
when it becomes your sorry task to maintain and continue developing a badly designed database - AND - you're working against deadlines - the order from above is always - "we don't have the time or money to redesign this! If it hasn't broken yet..it must be fine so just continue where the previous guy left off!
Also, you mention char(8) datatype - the manager confirmed that the code size will never change.
Change required: The Company has merged with another. Their product codes differ and are up to 12 digits long.
Where (and how) do you draw the line between a tight database design where you have all the defaults, constraints, unique indexes etc.. set - based on months of requirement gathering, submission and approval from client - to the abysmal morning that you're told - "yes, we know we said that 7 months ago...but we need to change it now" - do we just design defensively all the time anticipating any/every change and going bananas with the myriad possibilities ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Some of these issues can be addressed using TSQLMacro, a project I created a while ago:
http://www.datamanipulation.net/tsqlmacro
One specific group of problems I thought of is centralization of commonly-used bits of code. For instance, in one project I worked on, every one of the 350+ stored procedures in the system had four or five nearly identical predicates in the WHERE clause for row-level security. At one point we had to change it a bit, and we had to change every single procedure. Had we had macros, we could have centralized the code. One change in one place is a good thing...
--
Adam Machanic
whoisactive -
I try to minimise code by creating new stored procedures that can be called from more than one other SPROC. So, sometimes, less code but more SPROCS are the answer.
In my current application I have created what I call twin SPROCS. These are identical except for the substitution of one table name with another in the select statement used for an insert. Should I find a way of not doing this? Can I have conditions in the code that would enable an alternative approach. Maybe I should consider using the the 'when' command or something.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply