Query Pilot Column Projections

Column projections are temporary Query Pilot shorthand for selecting useful columns from very wide tables. They are replaced with ordinary quoted column names before SQL is checked or sent to SQL Server.

They are most useful when a table has dozens or hundreds of columns and ordinary SELECT * returns too much data, but typing the desired column list would interrupt an investigation. Typical uses include removing large payload columns, ignoring trailing audit columns, finding a family of similarly named metrics, and comparing a manageable slice of a denormalized or vendor-owned table.

Syntax

Exclude the final columns in catalog order:

SELECT * EXCEPT (LAST 3)
FROM dbo.Servers;

Exclude every column with an exact SQL declaration type:

SELECT * EXCEPT (TYPE varchar(max))
FROM dbo.Servers;

This is useful when a wide operational table contains XML, JSON, document, or diagnostic payloads that are expensive and difficult to read in a result grid:

SELECT * EXCEPT (TYPE nvarchar(max), TYPE varbinary(max))
FROM dbo.EventArchive;

Keep column names matching a SQL-style pattern. % matches any number of characters and _ matches one character:

SELECT * MATCH (Server%)
FROM dbo.Servers;

Use multiple patterns to pull related column families from a wide monitoring table:

SELECT * MATCH (Cpu%, Memory%, SampleTime)
FROM dbo.ServerTelemetry;

Qualify the star when a statement has joins:

SELECT s.* MATCH (Server%),
       l.* EXCEPT (TYPE varchar(max), LAST 2)
FROM dbo.Servers AS s
JOIN dbo.ServerLog AS l ON l.ServerID = s.ServerID;

Multiple EXCEPT selectors are applied from left to right. Output columns always retain their original catalog order.

For a table whose final audit columns are always CreatedBy, CreatedDate, and RowVersion, this keeps the business columns without requiring the author to know every business-column name:

SELECT * EXCEPT (LAST 3)
FROM dbo.CustomerSnapshot;

Resolution

With Resolve column projections as typed enabled, completing the outer closing parenthesis starts resolution after a short typing delay. Query Pilot fetches each distinct object's names, ordinals, and declaration types once, loads independent objects concurrently, and replaces only completed projection expressions. The replacement is one undoable editor change.

Projection resolution requires one selected database. When the query tab targets multiple servers, the first selected server supplies metadata and Query Pilot warns that another server may have a different schema. Disable automatic resolution in Query Pilot Options to keep the shorthand until Expand * is clicked.

If no columns match, the original text remains and the Query Pilot status reports the problem. Projection text is never sent to SQL Server.

Related: Query Pilot and Query Pilot Objects.

Documentation revision: 404D147631F2C679