Avoid using a wildcard (*) when building mssql views

Using a wildcard while building a View can be very convenient if you have to return all the columns from a table:

CREATE VIEW View_Fruit
AS
SELECT f.*
FROM [dbo].[MyFruitsDb].[Fruit] as f
WHERE DeletedAt IS NULL

Seems legit and easy, however there is one thing to keep in mind, just because you are using a wildcard does not mean that your View will be automatically updated when your table schema is changed. In other words, if you will add one additional column on [dbo].[Fruit] table, you will have to regenerate the view, by simple replacing CREATE by ALTER in the T-SQL script demonstrated above in order to include this new column in the view.

Depends on your development cycle, you may prefer replacing the wildcard by a list of the columns you want to return. This way when you will compare your development DB to staging or production DB schemas you will see that you forgot to add a new column, otherwise schema comparison will results a 0 changes. Tricky.

As a rule of thumb I would avoid using wildcards, as life shows it may take time to discover that a wildcard is used in a View and that it has to be regenerated in order to solve an unexpected bug.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s