Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Single Point of Administration Expand / Collapse
Author
Message
Posted Tuesday, August 02, 2005 1:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 03, 2007 2:55 AM
Points: 928, Visits: 1
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!
Post #206624
Posted Tuesday, August 02, 2005 3:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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).
Post #206640
Posted Wednesday, August 03, 2005 8:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 03, 2007 2:55 AM
Points: 928, Visits: 1

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!
Post #206896
Posted Wednesday, August 03, 2005 8:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 .
Post #206901
Posted Monday, August 15, 2005 1:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

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...
Post #210346
Posted Monday, August 15, 2005 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:36 PM
Points: 265, Visits: 959

"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. 




Post #210403
Posted Monday, August 15, 2005 7:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Dito here... I had a single report using 24 objects!!!!!!!!. now it uses only 4 (4 different reports that make a big one).
Post #210405
Posted Monday, August 15, 2005 8:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
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().
Post #210468
Posted Monday, August 15, 2005 1:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
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 !!!**
Post #210584
Posted Monday, August 15, 2005 3:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 21, 2014 7:45 AM
Points: 1,138, Visits: 698
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #210611
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse