Sql-server – Create view with column name aliases or hard change column names on a big database with a very low amount of changes

database-designsql serverview

Background:
I have to work with an big enterprise geodatabase on an SQL Server Express 2012 server with around 170 tables where many of these tables have 10+ columns. Some column names are repetitive, e.g. FEAT_ID, GEOM, what means that very many tables have equally named column names.
The application program my company accesses these big databases with doesn't support column name length > 31. Only two of all 170 tables own one column with a column name which does not meet the requirements (so overall two column names). I have to shorten these two column names. I guess this could be done by introducing aliases. So it's a very minor change to a big database.
Below is the first problematic table. The problematic column name in this table is FT_ADDRESS_AREA_BOUNDARY_ELEMENT as its length is 32

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [foo].[baron](
[FEAT_ID] [uniqueidentifier] NOT NULL,
[FT_ROAD_ELEMENT] [bit] NOT NULL,
[FT_FERRY_ELEMENT] [bit] NOT NULL,
[FT_ADDRESS_AREA_BOUNDARY_ELEMENT] [bit] NOT NULL,
[FT_RAILWAY_ELEMENT] [bit] NOT NULL,
[COUNTRY_LEFT] [nvarchar](3) NULL,
[COUNTRY_RIGHT] [nvarchar](3) NULL,
[CENTIMETERS] [numeric](9, 0) NOT NULL,
[POSITIONAL_ACCURACY] [numeric](1, 0) NULL,
[ADA_COMPLIANT] [bit] NOT NULL,
[GEOM] [geometry] NOT NULL,
CONSTRAINT [PK_baron] PRIMARY KEY CLUSTERED 
(
 [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Further below is the second problematic table with the too long column name MAIN_OF_DETAILED_POSTAL_POINT_ID: 

USE [foo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [foo].[baroness](
    [FEAT_ID] [uniqueidentifier] NOT NULL,
    [FEAT_TYPE] [numeric](5, 0) NOT NULL,
    [NAME] [nvarchar](254) NULL,
    [LANG_CODE] [nvarchar](3) NULL,
    [COUNTRY] [nvarchar](3) NULL,
    [POSTAL_CODE] [nvarchar](254) NULL,
    [NOTATION] [numeric](2, 0) NULL,
    [ISO_SCRIPT] [nvarchar](4) NULL,
    [MAIN_OF_DETAILED_POSTAL_POINT_ID] [uniqueidentifier] NULL,
    [POSTAL_DELIVERY_TYPE] [nvarchar](1) NULL,
    [POSTAL_POINT_TYPE] [numeric](1, 0) NULL,
    [NC_MAIN] [nvarchar](254) NULL,
    [NC_MAIN_OFFSET] [numeric](1, 0) NULL,
    [NC_SUB] [nvarchar](254) NULL,
    [NC_SUB_OFFSET] [numeric](3, 0) NULL,
    [GEOM] [geometry] NULL,
 CONSTRAINT [PK_baroness] PRIMARY KEY CLUSTERED 
(
    [FEAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [foo].[baroness]  WITH CHECK ADD  CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point] FOREIGN  KEY([MAIN_OF_DETAILED_POSTAL_POINT_ID])
REFERENCES [foo].[baroness] ([FEAT_ID])
GO

ALTER TABLE [foo].[baroness] CHECK CONSTRAINT [FK_Postal_Point_MAIN_OF_DETAILED_POSTAL_POINT_ID_2_Postal_Point]
GO

Questions:
Is it worth creating a view (or multiple?) or should I just "hard change" these two columns and track it appropriately? I have no experience with views at all. When I tried to create an appropriate view with Microsoft SQL Server Management Studio, I received an error message that column names should be unique. Do I have to create aliases for every repetitive column name in the whole database when creating a view? Should I create a view with only the two changed tables and one with the rest of the database and merge them? I could also imagine that the performance would suffer if I create a view where I have to select the whole database at once.

Best Answer

Here would be two recommendation based on working in environments with lots of databases being used by lots of different apps.

Option 1: Create a view with a similar name but reflecting the changes you want made. In your case, it's just one field renamed. You may have to create triggers on the view so the apps that perform DML can use it just like they use the original table.

Publicize the view name and encourage the app developers to change their code to use the view instead of the table. You want the only change needed to be the name of the table and any changes propagated through the view (the new name of the field). They can make the changes more or less "at their leisure" as the original table still works as before. You will, of course, give them a deadline to get this finished.

When all app changes have been made (and verified), you can really consider yourself finished. I encourage a system of exposing only views to apps instead of entity tables. This allows for much more freedom to make necessary changes to the tables and change views so everything looks the same to apps.

But if you want to, rename the fields in the tables and, after extensive testing of course, notify app developers to change the "table" names back to original.

Test and finished. Delete the views or keep them around if you prefer.

Option 2: An alternative would be to rename the tables and create the views with the original table names. Then all apps would need to be changed at once to use the new field name(s). Thoroughly test after bringing the system back online.

Next, change the tables as needed and test. Then reverse the process -- rename (or drop) the views and rename the altered tables back to the original names. Test and (hopefully) finished.

Related Question