Another reason why we should not write SELECT *
There are so many warnings from experienced SQL developers about not to write SELECT * in your query.
I recently got an email from our employee incentive payout team about a SQL view that behaved strangely. They’ve noticed that a date time field is showing employee login ids.
The SQL view is simply as this:
CREATE VIEW V_myView
AS
SELECT * FROM T_myTable
It turned out that the base table T_myTable got some modifications. New columns were added, and existing columns got switched around.
This caused the SQL view to behave weird.
To solve the problem, we can simply ALTER the view:
ALTER VIEW V_myView
AS
SELECT * FROM T_myTable
Or, a better solution is to get rid of the SELECT *, and spell out the column names explicitly. Future modification of the base table will not cause any problems in the SQL view.
ALTER VIEW V_myView
AS
SELECT col1, col2, …. FROM T_myTable
