SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single Point of Administration


Single Point of Administration

Author
Message
Jonathan Stokes
Jonathan Stokes
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 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!
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82261 Visits: 9671
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).
Jonathan Stokes
Jonathan Stokes
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 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!
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82261 Visits: 9671
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 .
David le Quesne
David le Quesne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 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...
VD
VD
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 1234

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





Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82261 Visits: 9671
Dito here... I had a single report using 24 objects!!!!!!!!. now it uses only 4 (4 different reports that make a big one).
Mike C
Mike C
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 1172
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().
sushila
sushila
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9969 Visits: 639
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 !!!**
Adam Machanic
Adam Machanic
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 714
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search