Replace SELECT * with Column Names in SQL Server: SQL in Sixty Seconds #017

Replace SELECT * with the column names your application needs, and SSMS can help you build the list.

SQL in Sixty Seconds episode #017 demonstrates two shortcuts in the 2012 interface. The habit behind them still matters. I tested the reasons on SQL Server 2025.

Two Shortcuts from the Original Video

The video drags the Columns folder from Object Explorer into the query editor, which pastes the column names. It also scripts a SELECT statement from the table’s shortcut menu. In current SSMS you right-click the table, choose Script Table as, then SELECT To and New Query Editor Window.

A generated list of every column is only a starting point. Keeping all of them still retrieves all of them. The useful step is to delete the columns the caller does not need.

Fewer Columns Can Mean Fewer Reads

I built a table of 200,000 products and an index on CategoryId that includes ProductName. Then I asked for the same 40 or so rows two ways.

CREATE TABLE dbo.Product (ProductId int IDENTITY(1,1) PRIMARY KEY, ProductName nvarchar(40) NOT NULL,
                          CategoryId int NOT NULL, ListPrice decimal(8,2) NOT NULL, Notes char(200) NOT NULL DEFAULT 'x');
INSERT INTO dbo.Product (ProductName, CategoryId, ListPrice)
SELECT TOP (200000) CONCAT(N'Item ', ROW_NUMBER() OVER (ORDER BY (SELECT 1))),
       1 + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 50, 9.99
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_Product_Category ON dbo.Product (CategoryId) INCLUDE (ProductName);

SET STATISTICS IO ON;
SELECT ProductId, ProductName FROM dbo.Product WHERE CategoryId = 7 AND ProductId < 2000;
SELECT * FROM dbo.Product WHERE CategoryId = 7 AND ProductId < 2000;
SELECT 'star' AS Form WHERE EXISTS (SELECT * FROM dbo.Product WHERE CategoryId = 7);
SELECT 'one' AS Form WHERE EXISTS (SELECT 1 FROM dbo.Product WHERE CategoryId = 7);
SET STATISTICS IO OFF;

The Messages tab shows the cost. The named columns came entirely from the index and read 4 pages. SELECT * needed columns that the index does not hold, and it read 64. That does not promise a seek or a faster plan in every query. Compare actual plans when speed is the reason. Row counts and index design matter as much as the column list. The two EXISTS forms each read 3 pages. The asterisk inside EXISTS returns no columns, so it costs nothing extra.

Build the List with T-SQL

SSMS is not the only way to get the names. The catalog view sys.columns lists them, and STRING_AGG joins them into one line you can paste. This version leaves out the wide Notes column. QUOTENAME adds brackets, so a name with a space still works.

SELECT STRING_AGG(QUOTENAME(c.name), N', ') WITHIN GROUP (ORDER BY c.column_id) AS ColumnList
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.Product') AND c.name <> N'Notes';

It returned [ProductId], [ProductName], [CategoryId], [ListPrice]. Paste that after SELECT and trim it further. The list follows the column order of the table at that moment.

A View Keeps Its Old Column List

A view written with SELECT * stores its column list when it is created. A column added later does not appear until the view is refreshed.

CREATE TABLE dbo.Gadget (GadgetId int PRIMARY KEY, GadgetName nvarchar(30) NOT NULL);
INSERT INTO dbo.Gadget VALUES (1, N'Lamp');
GO
CREATE VIEW dbo.GadgetAll AS SELECT * FROM dbo.Gadget;
GO
ALTER TABLE dbo.Gadget ADD Colour nvarchar(20) NOT NULL DEFAULT N'Green';
GO
SELECT * FROM dbo.GadgetAll;
EXEC sys.sp_refreshview N'dbo.GadgetAll';
SELECT * FROM dbo.GadgetAll;

The first query returned two columns. After sp_refreshview, the view returned all three. Review and refresh views whenever you change the tables beneath them. A view created WITH SCHEMABINDING refuses the asterisk outright, with Msg 1054, so it always lists its columns.

INSERT with SELECT * Breaks Later

An INSERT ... SELECT * works while the two tables match. It fails the day one of them changes. My archive table kept two columns, and the source table now had three.

CREATE TABLE dbo.GadgetArchive (GadgetId int PRIMARY KEY, GadgetName nvarchar(30) NOT NULL);
GO
INSERT INTO dbo.GadgetArchive SELECT * FROM dbo.Gadget;
GO
INSERT INTO dbo.GadgetArchive (GadgetId, GadgetName) SELECT GadgetId, GadgetName FROM dbo.Gadget;

The first insert failed with Msg 213, because the column counts differ. The second, with named columns on both sides, inserted the row. Names also protect you when someone reorders columns.

A Join Repeats Column Names

Two tables can share a column name. SELECT * over a join returns both, and that breaks the moment you wrap the query in a derived table.

CREATE TABLE dbo.Maker (MakerId int PRIMARY KEY, GadgetName nvarchar(30) NOT NULL);
INSERT INTO dbo.Maker VALUES (1, N'Acme');
GO
SELECT * FROM (SELECT * FROM dbo.Gadget AS g JOIN dbo.Maker AS m ON m.MakerId = g.GadgetId) AS Joined;
GO
SELECT g.GadgetId, g.GadgetName, m.GadgetName AS MakerName
FROM dbo.Gadget AS g JOIN dbo.Maker AS m ON m.MakerId = g.GadgetId;

The first query failed with Msg 8156, because GadgetName was specified more than once. Naming the columns and aliasing the second one fixed it.

My Habit

I still use SELECT * for a quick look when I need every column. For an application result, I prefer an explicit list, because it states the contract. In a code review, I check every SELECT * that feeds an application, and I ask what the caller reads. COUNT(*) counts rows and is a different use of the asterisk.

Related Reading

• Puzzle: SELECT * vs SELECT COUNT(*)
• Solution to the Puzzle: SELECT * vs SELECT COUNT(*)
• SELECT vs SET Performance Comparison

An explicit column list is not extra typing, it is a clear description of the result.

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