SELECT * in Production Code: The Hidden Costs

A quick look at a table becomes an application query, and the star stays. SELECT * then turns every new column into an unplanned change to that query's contract. Explicit column lists make reads, results, and schema changes easier to control.

Build a Narrow Query Beside a Wide Row

Use a disposable database for permanent examples and one session for temporary tables. The first setup creates sample rows with a deliberately wide payload. Those rows are demonstration inputs, not reported measurements. An index on CustomerID includes AmountCents, allowing the narrow query to find and return its requested values without reading the payload.

CREATE TABLE #Orders
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    AmountCents int NOT NULL,
    Payload varchar(2000) NOT NULL
);
INSERT #Orders(OrderID,CustomerID,AmountCents,Payload)
SELECT value,value%100,value*10,REPLICATE('x',1500)
FROM GENERATE_SERIES(1,10000,1);
CREATE INDEX IX_Orders_CustomerID
ON #Orders(CustomerID) INCLUDE(AmountCents);

GENERATE_SERIES needs SQL Server 2022 or later with database compatibility level 160 or higher. I start by asking which columns the caller actually consumes. An application that displays two values has no reason to fetch a large message body simply because the table can provide one.

Compare Reads for SELECT * and a Column List

Turn on the actual execution plan and run both queries with IO statistics. The narrow projection fits the nonclustered index. The star projection requests Payload too. SQL Server can choose key lookups or a wider scan based on estimates and cost. Inspect the actual choice instead of promising that every star query produces one particular operator.

SET STATISTICS IO, TIME ON;
SELECT OrderID,AmountCents
FROM #Orders WHERE CustomerID=7;
SELECT *
FROM #Orders WHERE CustomerID=7;
SET STATISTICS IO, TIME OFF;

Compare logical reads, returned columns, estimated rows, and actual rows. Save the plans and messages from your own server. These scripts supply no invented read counts or timings. If the wider query scans, inspect why. Lookup cost rises with qualifying rows. A covering narrow query avoids needing a payload access regardless of whether the optimizer prefers that index in this sample.

Make the Lookup Requirement Visible

The next comparison forces the same nonclustered index as a teaching exercise. It exposes why the payload needs another access path for the wider result. Keep the hint out of production unless you have a separate, measured reason for it. A plan hint used to explain mechanics is not a general tuning recommendation.

SELECT OrderID,AmountCents
FROM #Orders WITH(INDEX(IX_Orders_CustomerID))
WHERE CustomerID=7;
SELECT OrderID,AmountCents,Payload
FROM #Orders WITH(INDEX(IX_Orders_CustomerID))
WHERE CustomerID=7;

I check the requested columns before adding another included column to an index. Covering an accidental SELECT * can create a large index that duplicates most of the table. That adds write, storage, and maintenance costs. First remove unneeded output. Then design the index around the query the application really needs. Carrying the entire cabinet is still heavy after adding better wheels.

Watch a SELECT * View Keep Its Old Metadata

A non schema bound view using star stores metadata about the column list at creation. Adding a table column does not update that stored column list. This permanent sample demonstrates the issue. CREATE VIEW starts its own batch, and GO separates each following operation in an SSMS query window.

CREATE TABLE dbo.StarSource
    (SourceID int NOT NULL PRIMARY KEY,Title nvarchar(60) NOT NULL);
INSERT dbo.StarSource(SourceID,Title) VALUES(1,N'Sample');
GO
CREATE VIEW dbo.StarView
AS
SELECT * FROM dbo.StarSource;
GO
ALTER TABLE dbo.StarSource ADD Note nvarchar(100) NULL;
SELECT * FROM dbo.StarView;
EXEC sys.sp_refreshview N'dbo.StarView';
SELECT * FROM dbo.StarView;

The first query returns only SourceID and Title, even though the table now has Note. After sp_refreshview, the same view returns all three columns. Refresh changes metadata, not your intended public contract. A consumer can break when the refreshed view exposes an extra column. Prefer an explicit view projection and deliberate ALTER VIEW changes. Schema binding adds other safeguards, but it also requires explicit columns and appropriate qualified references rather than this star definition.

Reproduce an INSERT That Changes Shape

An INSERT without a target column list depends on the target's implicit writable columns. A star source depends on the source's changing shape. Combining them makes two fragile assumptions. The sample first works with matching columns, then adds a source column. Dynamic execution lets TRY CATCH report the later statement's compilation error without aborting the whole demonstration.

CREATE TABLE #CopySource(SourceID int NOT NULL,Title nvarchar(60) NOT NULL);
CREATE TABLE #CopyTarget(SourceID int NOT NULL,Title nvarchar(60) NOT NULL);
INSERT #CopySource VALUES(1,N'Sample');
INSERT #CopyTarget SELECT * FROM #CopySource;
ALTER TABLE #CopySource ADD Note nvarchar(100) NULL;
BEGIN TRY
    EXEC sys.sp_executesql N'INSERT #CopyTarget SELECT * FROM #CopySource;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
INSERT #CopyTarget(SourceID,Title)
SELECT SourceID,Title FROM #CopySource;

The CATCH block returns error 213, because the source now has more columns than the target. An added column can also shift an ordinal mapping in other designs and send compatible values into the wrong destination. Explicit lists on both sides state the mapping. Review conversions and nullable defaults too. Matching column counts alone do not establish meaning. Test inserts after schema changes, even when every individual column type still looks familiar.

Count the Bytes You Asked to Return

Wider projection also means a wider result to serialize and transfer. Large text, binary values, and columns the screen never displays still use client and server resources. DATALENGTH helps demonstrate payload size without claiming to measure full wire traffic. Protocol framing, null representation, and client processing add their own details beyond stored value bytes.

SELECT SUM(CONVERT(bigint,DATALENGTH(Payload))) AS SelectedPayloadBytes,
       COUNT_BIG(*) AS SelectedRows
FROM #Orders WHERE CustomerID=7;
SELECT TOP(10) OrderID,AmountCents
FROM #Orders ORDER BY OrderID;

Use client statistics or approved application telemetry to measure actual transfer and rendering. Compare the same row set when testing projections. A faster narrow query returning fewer rows is a different comparison. What does the caller do with each returned column? If nobody can answer for Payload, remove it from that path and measure the corrected query.

Find SELECT * in Stored Modules

Search module definitions for common SELECT star spellings. This is a candidate list, not a SQL parser. Whitespace, comments, aliases such as t.*, dynamic strings, and generated SQL need additional inspection. The query cannot inspect encrypted definitions or application code outside the database. Permissions also limit which module text is visible.

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName,
       OBJECT_NAME(m.object_id) AS ModuleName,m.definition
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%SELECT%*%'
ORDER BY SchemaName,ModuleName;

Read each finding before editing. COUNT(*) counts rows and is unrelated to returning every column. EXISTS(SELECT *) tests existence and does not need the entire row projected to a client. Neither deserves a blind search and replace. Focus on result projections, view definitions, and insert sources whose shape escapes into a dependent contract.

Make Schema Changes Deliberate

Keep explicit projections in application queries, public views, and data movement statements. Test result names and order where clients bind by ordinal. When a feature needs a new field, add it intentionally with the corresponding client change. That makes a schema release easier to understand and reduces surprises from unrelated additions.

I still use SELECT * during short investigations when seeing the whole row is the purpose. Production code needs a stated result contract. Review that contract with indexes and consumers before optimizing. The star saves a few keystrokes at authoring time, while an explicit list saves repeated interpretation every time the schema changes.

Related reading on this blog: Stale Views: Why SELECT * Breaks After ALTER TABLE and Tricks to Replace SELECT * with Column Names: SQL in Sixty Seconds #017: Video.

A column list is not typing overhead, it is a contract for the data you return.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.