LIKE, SELECT *, and Column Bloat

Three habits that individually look harmless and together account for a large share of report slowness.

Leading-wildcard LIKE

WHERE ProductName LIKE '%widget%'

An index on ProductName is sorted alphabetically. "Starts with W" is a range in that ordering; "contains W somewhere" is not. There is no B-tree navigation possible, so this is always a scan of every row, regardless of indexing.

LIKE 'widget%' — anchored at the start — is SARGable and seeks normally.

If the requirement genuinely is "contains," you have three options:

Accept the scan if the table is small or the search is rare. A 50,000-row lookup table scanned occasionally is not worth engineering around.

Full-text search. Purpose-built for this: an inverted index of words, with CONTAINS and FREETEXT predicates.

-- Requires a full-text catalog and index on the table (a one-time setup)
SELECT ProductId, ProductName
FROM   dbo.Product                                  -- placeholder table
WHERE  CONTAINS(ProductName, '"widget"');

Note this searches whole words, so it is not an exact substitute for %widget% — CONTAINS will not match "superwidgets" the way the wildcard does. Confirm the semantics match the requirement before swapping.

Reverse the string. For "ends with" specifically, a persisted computed column of REVERSE(Column) with an index makes LIKE '%abc' into REVERSE(Column) LIKE 'cba%', which seeks.

In reports, leading-wildcard search boxes are often on optional filters that nobody uses, which makes this part of the catch-all query problem in Lesson 6 rather than a search problem.

SELECT *

Four separate costs, in rough order of impact:

It prevents covering. A query selecting three columns can be covered by a modest index. A query selecting all thirty can only be covered by duplicating the table, so the plan does a seek plus a Key Lookup per row — the pattern from Module 2 that dominates report runtimes.

It moves data nobody reads. Thirty columns including a couple of nvarchar(max) description fields, times 200,000 rows, is a great deal of network transfer and client memory for a report displaying six columns.

It breaks silently on schema change. Someone adds a column; the report's grid gains a column, or an ordinal-based consumer shifts and misreads.

It inflates the memory grant. Grants are sized partly from estimated row width. Wide rows mean larger grants, which means more RESOURCE_SEMAPHORE waiting for everything else on the server.

The correction is unglamorous: list the columns the report actually renders. On a Telerik report, compare the dataset's SELECT list against the columns bound in the designer. The gap is frequently large, and closing it is often the cheapest meaningful win available — no index, no deployment risk, just fewer columns.

Measuring the cost

SET STATISTICS IO, TIME ON;

-- Wide: forces lookups, moves everything
SELECT * FROM sys.all_columns;

-- Narrow: only what is needed
SELECT object_id, name FROM sys.all_columns;

SET STATISTICS IO, TIME OFF;

Against your own report's table, run the report's SELECT list versus the trimmed one and compare logical reads and elapsed time. Also check avg_rows and row width via the query stats DMV:

SELECT TOP (20)
       qs.execution_count,
       qs.total_rows / NULLIF(qs.execution_count,0) AS avg_rows_returned,
       qs.max_grant_kb / 1024                       AS max_grant_mb,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms,
       SUBSTRING(t.text, 1, 300) AS query_snippet
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qs.total_rows / NULLIF(qs.execution_count,0) > 10000
ORDER BY avg_rows_returned DESC;

Any report query in that output — averaging over ten thousand rows returned per execution — is a candidate for Module 6's argument about aggregating server-side.

SELECT DISTINCT as a symptom

SELECT DISTINCT in a report query is usually not a requirement; it is a repair for a join that produces duplicates. It forces a sort or hash aggregate over the whole result set, needing a memory grant that can spill.

When you see it, find the join that fanned out. Often the answer is that a one-to-many join was added to reach one column, and an EXISTS or a pre-aggregated subquery expresses the intent without the duplication:

-- DISTINCT repairing a fan-out
SELECT DISTINCT c.CustomerId, c.CustomerName
FROM dbo.Customer AS c
JOIN dbo.Orders   AS o ON o.CustomerId = c.CustomerId
WHERE o.OrderDate >= @From;

-- Same result, no duplicates to remove, and the index on Orders is used efficiently
SELECT c.CustomerId, c.CustomerName
FROM dbo.Customer AS c
WHERE EXISTS (SELECT 1 FROM dbo.Orders AS o
               WHERE o.CustomerId = c.CustomerId AND o.OrderDate >= @From);

EXISTS stops at the first matching row per customer. The join-plus-DISTINCT version reads every matching order and then discards duplicates.

The short version

Leading-wildcard LIKE cannot seek, because the index is sorted by the start of the string — if "contains" is a real requirement, that is full-text search, not an index. And SELECT * in a report is usually the reason a covering index is impossible, so the plan does a key lookup per row. Trimming the select list to the columns the report actually renders is often the cheapest real win, with no schema change at all.