2

Sometimes, when developing in a hurry, devs make poor choices on column names.

Changing them can be a challenge, having to stage code, and time migration perfectly.

I'm told that Oracle has a way to give a secondary name to a column. Does SQL Server have anything like this? The best I can think of is to add a calculated column to duplicate the column, but this will lead to follow on indexing and performance challenges.

Any good patterns or a magic bullet for SQL Server 2008 or 2012?

(Someone mentioned a view, which would definitely work, but wondering if there's a built-in option.)

I guess I'm looking for a Synonym for Columns. http://msdn.microsoft.com/en-us/library/ms177544.aspx

  • 2
    The most obvious option is to rename the columns in a view, and query that view in new code. – JNK Feb 26 '14 at 19:58
  • This is a good use for views. SQL statements including UPDATE and INSERT can operate on a view as easily as a table, so you never actually have to reference tables in the DML. It's not a bad idea to use views throughout an app for naming consistency. They can also be used to avoid naming collisions, for instance to add a prefixed alias to a foreign key that has the same name as the primary key it references. – dartonw Feb 26 '14 at 22:13
2

As @JNK mentioned, you can use a view. There are some steps to take, of course. The view should map only to columns in a single table.

I assume that you do not want to deploy completely updated code that uses the new name. So, you can create a view with a different (but similar) name and any new code could use the view, and the new name. Existing code would still be going directly to the table.

This would get you moving, but one day you will probably want everthing to use the new column name. Eventually, you will have to deploy new code. Which would still mean a number of name changes if you don't want to have the view layer over the table.

| improve this answer | |
1

You could also create a view with the column names you want (and the original column names), then change the name of the table, and change the name of the view to what the table was called, and point the view at the new table's name. So let's say before:

CREATE TABLE dbo.Stuff(ID INT, BadName VARCHAR(32));

After:

CREATE TABLE dbo.RealStuff(ID INT, GoodName VARCHAR(32));

INSERT dbo.RealStuff SELECT ID, BadName FROM dbo.Stuff;

DROP TABLE dbo.Stuff;
GO

CREATE VIEW dbo.Stuff
AS
  SELECT ID, GoodName, BadName = GoodName FROM dbo.RealStuff;

Then the app isn't any wiser, though you may need to set up instead of triggers for certain operations, jiggle some constraints, etc. within the database. And then change it all back when you can finally make the code right.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.