Block SELECT * Queries with a One‑Line Computed Column Hack

Everyone knows that using SELECT * is a bad practice, but it still happens. The drawbacks include returning every column (even newly added ones, which can cause issues with large types like VARCHAR(MAX)) and preventing the use of covering non‑clustered indexes because the extra data is duplicated.

To stop SELECT * without relying on code reviews, you can add a computed column that deliberately raises a divide‑by‑zero error. This forces any SELECT * statement to fail while allowing explicit column lists to work.

-- Create a simple table with a computed column that generates a divide by zero exception.
CREATE TABLE Foo (
    Col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Col2 CHAR(100) NOT NULL,
    Col3 CHAR(100) NOT NULL,
    DevelopersPain AS (1 / 0)
);
GO

-- Insert some test data
INSERT INTO Foo VALUES ('a', 'a'), ('b', 'b'), ('c', 'c');
GO

When you run SELECT * FROM Foo, the query fails with a divide‑by‑zero error:

-- A SELECT * statement doesn't work anymore, ouch...
SELECT * FROM Foo;
GO

However, selecting columns by name bypasses the computed column and works as expected:

-- This SQL statement works
SELECT Col1, Col2, Col3 FROM Foo;
GO

The method is simple but requires a schema change. Remember to apply it when designing new tables to avoid the pitfalls of SELECT *.