Can select * usage ever be justified?

Whistle while you work: Knowing when to break the rules

Scenarios suited for Select *

Quick looks and one-offs

Ad-hoc exploration or data migration enables SELECT * to shine. Use it to understand unfamiliar territory:

-- When you need to know what mysteries new_table holds
SELECT * FROM new_table;


Rocking CTEs and EXISTS

Use SELECT * in Common Table Expressions (CTEs) and EXISTS clauses. Guess what? It doesn't affect performance:

-- CTE for keeping things DRY
WITH CTE AS (SELECT * FROM employees)
SELECT * FROM CTE;

-- EXISTS clause for checking if you have employees 
IF EXISTS (SELECT * FROM employees)
  PRINT 'Congrats, you are not a one-man-show!';


Caution: No-go zones for Select *

Views and production code

Never ever use SELECT * in views or production code:

-- Avoid this shady path
CREATE VIEW employeeView AS
SELECT * FROM users;

-- Walk this enlightened path instead
CREATE VIEW employeeView AS
SELECT user_id, user_name, user_birthday FROM users;


On shifting ground

Employing SELECT * can cause unexpected breakages with schema alterations:

-- New fields in town
ALTER TABLE users ADD user_preferences NVARCHAR(100);

-- Might cause heartburn in production
SELECT * INTO #TempTable FROM users;


Adopting best practices to dodge 'Select *' pitfalls

Awareness of optimizer's idiosyncrasies, reliable audit triggers, and updated view metadata are your defence against 'SELECT *' missteps. Be mindful while coding for clarity and efficient execution.

The lay of the land: Understanding your environment

Knowing your code context

Appreciate the difference between development and production environments:

• In development, SELECT * gives a quick preview.
• In production, precision drives clarity and control.

Handling schema changes

Updates to the schema are bound to happen. When they do:

• Refresh view metadata to keep SELECT * in check.
• Stick to named parameters and explicit column lists to be safe.

When every microsecond counts

SELECT * can clog up the optimizer, slowing it down:

• Limited column sets make for speedier decisions.
• Select only what you need to avoid pesky data transfer and IO overhead.