Don't use SELECT * in views

We all know we should use schema_binding and always list all column names in SELECT statements and view definitions. We know we should ... but nobody does it ;-)

Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.

Tip of the day ... don't use SELECT * FROM in a view.... ;-)


/* Created by free online sql formatter */
/*
--Drop objects if they exists
DROP TABLE t1
DROP VIEW v1
*/


CREATE TABLE t1 (
x INT,
y VARCHAR(5))


INSERT INTO t1
(x,
y)
VALUES (1,
'abc')

GO


CREATE VIEW v1
AS
SELECT *
FROM t1
GO

--This shows columns a and b
SELECT *
FROM v1

--Add a third column to our table
ALTER TABLE t1
ADD z DATETIME
GO

UPDATE t1
SET z = GETDATE()

--Third column is in table
SELECT *
FROM t1

--Third column is NOT in view
SELECT *
FROM v1

--Drop column x from table
ALTER TABLE t1
DROP COLUMN x

--Here's the freaky part
--Column headers are x and y but data shown is from columns y and z
SELECT *
FROM v1