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