June 12, 2015 at 8:25 am
I've renamed lots of objects (especially columns, tables and stored procedures).
Speaking about stored procedures, it was simpler than the other two cases.
For renaming columns and tables I've followed the database refactoring patterns suggested by Scott Ambler: "Rename Column" and "Rename Table".
Rename Column
- Introduce new column, with ALTER TABLE ADD
- Copy the "old" data into the "new" column
- Introduce Sync Trigger, for copying data from the "old" column to the "new" one
I set the final date for the transition period, during which the applications/customer softwares have to change the name of the column.
Finally (the day of the "final date"), I drop the trigger and the "old" column.
Rename Table
- Create a new table with the new name
- Eventually, create the new FKs
- Copy the "old" data into the new table
- Create two triggers, in order to keep both tables synced (some applications can use the new table from the beginning)
The same happens for the transition periods: drop triggers, drop old table
This was powerful when multiple applications (also multiple customers) are connected to my dbs.
I loved the "Refactoring Databases - Evolutionary Database Design" book.
Alessandro Alpi
CTO Engage IT Services S.r.l.
DBA | Team Leader
Microsoft MVP - Data platform
[MCP] [MCITP]
June 12, 2015 at 6:28 pm
My philosophy is that once in production names are written in stone, spelling mistakes and all. As has been mentioned by others, our customers have many tools they use to extend our DBs, particularly for reporting, so any DB "out in the wild" is as it is. If there is something that seriously cannot be tolerated as named then put the new functionality into an SP and/or UDF so it doesn't break the customers additions.
June 13, 2015 at 11:56 am
Hi All
On a regular basis tables/stored procedures/views have to renamed as the functionality of that object from a business point of view is correctly understood. This improves the maintainability of the product considerably.
June 13, 2015 at 12:09 pm
Ed Elliott (6/11/2015)
Hi,Renaming is vital when maintaining a database as it means you can refactor it to improve the quality of it. The areas I mean are having consistent naming, fixing typos or just when things change.
This is the main reason I use ssdt, the refactoring support is something I haven't seen in any other ode i and means you can correct things without having to manually correct it, just refactor-->rename done.
Ed
I Agree, but how often? Is this something you do weekly when actively developing a database? Only the first few months? Later?
June 13, 2015 at 12:09 pm
h.tobisch (6/12/2015)
i DO renaming.To find all references to the old name i use script which simply scrawls through all my sources
and looks for occurrences of the old name.
How often?
June 13, 2015 at 12:15 pm
It varies from project to project but I would say I do it at least once every couple of weeks on average.
June 13, 2015 at 12:17 pm
Dan Guzman-481633 (6/12/2015)
Yes, I sometimes rename objects and columns. Our development process is Agile with Continuous Integration. Database changes checked into source control are deployed to QA every night. Refactoring type database changes, like column renames or schema changes that require data migration, are performed with custom script run during the database install.
Install or upgrade? I assume both. Is this happening often?
June 13, 2015 at 12:18 pm
tom.w.brannon (6/12/2015)
I had a situation where in older tables a column was named CNTRY_CODE due to column name length limitations at the time. When the name length restriction went away they started using Country_code. I could never remember which table used which name. I finally renamed the existing CNTRY_CODE columns and added a calculated column with the old name to equal the new one. That way anything that called on the table expecting the old name would still work. I only had a couple of issues where somebody tried to do an insert using the old column name where they had to change their code. I hope to get rid of the old references next year. It is just cleaner. I may be doing a similar thing with some tables. In that case, I expect to create some synonyms with the old names.
I'd like to think this is the typical database refactoring pattern. Add something new, slowly move, and after a year, drop the old object.
June 13, 2015 at 12:21 pm
Bill Talada (6/12/2015)
After reading all posts, I see everyone is doing what is right for their environment. Personally I would only work for a company that strives for clarity and purity in code; that means embracing lots of renaming. I like typing intuitive names instead of wasting time looking thru tree browsers to figure out abbreviated spelling or weirdly named things.Using sp_rename caused me some quirky errors early on. And using the "right-click modify table" caused me many more problems. Eventually I wrote my own "modify table" scripter that works perfect for me. So, now it takes a few seconds to recreate a table or column with a new name instead of renaming. The scripter also regenerates all templated procs on a table so very few custom objects need fixed afterwards. In other words, once you have a robust change process, any requested modifications can be handled easily.
Isn't our true value in being able to quickly solve whatever problem we are given?
How many custom objects? I assume this is procs/UDFs/views and a limited number.
What about application code that needs to access renamed objects?
June 13, 2015 at 1:09 pm
Unless I miss my guess here, from the responses it is quite apparent there is a mixture of DBAs and developers, and of both there is a mixture of seasoned and relative newbies. In a small and/or new environment and/or new application the limits are different than in other cases where applications (and databases) have been inherited or 3rd party interfaces exist into and/or out-of applications and databases. Agile often works well in development, not so much once in production when clients are not so fast or willing to install updates. There are also potential issues with ORMs where manually renaming an object can be fatal.
There is something to be said for clarity and purity of code (fresh out of the box it's "perfect" in the eyes of the creator), but the client doesn't care how pretty it looks, they care that it works and more importantly that an update doesn't break their business.
June 13, 2015 at 1:40 pm
Install or upgrade? I assume both. Is this happening often?
The QA databases as well as databases used for automated unit and performance testing are upgraded nightly with the latest check-in changes. Custom scripts for refactoring are coded such that they can be rerun. We generally don't bother with normalizing custom scripts until we're near production release. The prod installer only supports upgrading clients from the last released version.
Renaming isn't often necessary since our devs are pretty good about naming things well to begin with. The most common reasons I see are simple column name typos and misnamed constraints. We have thousands of objects so it happens.
June 13, 2015 at 9:21 pm
My experience has shown me that there is a quest for perfection on the dba/modeler side with no thought to down stream consequence. I had to let a modeler go because he keep change the name of things to what he had determined was the correct syntax or what have your. No regard for common sense or that he keep braking etls and reports. Even after explaining the consequences and asking him to at least discuss change before implementing. Hopeless.
June 13, 2015 at 10:37 pm
Earlier I said I don't rename things like tables. And that for the most part I oppose it. Well, I've been handled a new project at work, having to take over something started a few years by someone who left it unfinished. I'm afraid that didn't do a good job. There are tables in it with column names like C_R and C_R1. I have no idea at all what any of these columns are for. I would rename all of the columns to something that conveys meaning, once I learn what the meaning of the data is supposed to be.
Only problem is all of the reports run off of these columns. That's a lot of work and this project is under a short deadline. I'll probably be forced to leave it all alone.
Rod
June 14, 2015 at 9:54 am
I have sometimes wondered whether any database vendors would ever implement some kind of intrinsic naming convention. Such that certain objects were allocated unchanging keys references at creation and all textual names were actually aliases such that changing the alias would cascade down through all code or alternatively you could use the unchangeable name within code.
It could be made to work.
In effect treating objects as records which themselves have primary keys and on which can be enforced referential integrity.
It kind of appeals to me:)
June 14, 2015 at 10:01 am
As an architect, I always try to align object names with what they really are. As the systems I develop adapt to changing conditions over the years, sometimes the objects need to be refactored, for example changing a table called "students" to "clients" with a new column called "client_type", one of which is "student". To support these changes, which are infrequent, I store all my DB code in Visual Source Safe, including separate environments (DEV/TEST/PROD). I have had situations where I've had to debug PROD while making longer-term changes in DEV/TEST, so having separate source code for each environment was a blessing.
Because VSS allows you to search the stored code, I can easily find all references to the objects that need to change. I document the changes being made, usually both in an MS Word project doc (objects affected) and a SQL script (detailed changes, including actual DDL/DML statements). Some changes are implemented manually (e.g. adding/renaming columns) and others are based on a script (e.g. data updates/transforms).
Because I am usually the only one making the changes, I do not try to automate everything as that would add more time to create the scripts and test them. However, SSDT has arrived later in my career, and I read with interest the earlier post about handling refactoring more easily. Definitely something to look into, especially since using SSDT encourages source code control.
J Pratt
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply