Messing With Views

Daniel Janik shows what happens when you add a column to the middle of a table while a SELECT * view exists on that table:

Let’s say you get a request or maybe the developer adds a column in the middle of the table. What happens to the view? Was it created with SELECT *? Could the title of this post just as easily have been “Don’t SELECT * ever again!”? Sure…

This is exactly what had happened. The table was altered and the view didn’t change. The view was actually throwing a date from string conversion error.

Let’s take a peek at the after math of adding a column without rebuilding the view.

To Daniel’s two take-aways I would add a third:  don’t use the GUI to insert columns in the middle of a table.  The order of columns in a relational table is ultimately irrelevant, so add new columns at the end.  That avoids this problem altogether.

Related Posts

Migrating A Database To Managed Instances

Frank Gill shows how to migrate a database from on-premises to an Azure SQL Managed Instance: If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal.  The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in […]

Read More

Automatically Enabling SQLCMD Mode In SSMS

Greg Low shows how to have every Management Studio tab open in SQLCMD mode: Note the :CONNECT command is used to connect to another server. Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That’s […]

Read More


September 2017
« Aug Oct »