The SQL Advisor: Seven Answers, All Measured

Welcome to the SQL Advisor, where the answer is never “it depends” unless I tell you what it depends on. Seven letters this time. Every answer carries a number I took myself.

The rules of this column are simple. I do not quote received wisdom back at you. I build the thing, measure it on SQL Server 2025, and tell you what happened. Where the number embarrasses a piece of advice I have given in the past, I say so.

1. Table variables or temp tables

My team lead has a rule. Never put more than a hundred rows in a table variable, always use a temp table.

They learned it at a conference talk. Is the rule right? We are on SQL Server 2025.

A hundred rows was never a real boundary. What changed is the optimizer, in 2019.

I put 20,000 rows in each and joined them to a 400,000 row table. Same procedure, same data, only the container changed. Then I ran it at two compatibility levels.

At compatibility level 140, the table variable version read 60,035 pages. At level 170, the same code read 2,029 pages, which is exactly what the temp table version read.

Thirty times the work, from a database setting, with no change to a single line of SQL.

The old advice existed for a reason. The optimizer used to assume a table variable held one row, however many it really held.

SQL Server 2019 added deferred compilation for table variables, at compatibility level 150 and above. The plan is compiled once the variable has been filled, so the real row count is available.

This is not a promotion to temp table. You still get no column statistics. The cached plan can also be reused later for a very different number of rows.

I am quoting reads rather than milliseconds on purpose. My timings were single runs and they disagreed with each other, which is what single runs do.

So check your compatibility level before you repeat the rule.

Plenty of databases still sit at 140, because somebody upgraded the server and never touched the database. There, your lead is right, and the setting is the real problem.

2. What each extra index really costs on insert

How much do indexes slow down writes? Everybody says “some”. I would like a number so I can push back in a meeting.

Here is a number. One table, 100,000 rows in one transaction, and one load for each of three index configurations.

With no extra indexes: 414 milliseconds. With three: 2,111 milliseconds. With six: 3,159 milliseconds.

The log grew more sharply than the clock did. Measured as the increase in used log space, that is 16.8 bytes per row with no extra indexes. With three it is 583.8, and with six it is 932.0.

Used log space is not the same as bytes written, because space can be reused as you go. Treat it as the shape of the cost, not an invoice.

So on this load, six indexes ran about eight times slower. They moved the log needle about fifty five times as far.

Take those numbers to your meeting as results from one load, not as a price list. And take the other half too.

An index that turns a nightly scan into a seek can pay for itself many times over. The cost is real and measurable, and so is the benefit. Nobody should be arguing this from feeling.

3. Should I shrink the database after a big delete

We archived four years of history and deleted about ninety percent of a table. The file is still enormous. Can I shrink it?

You can. Let me show you what you buy.

I built it twice, and it behaved differently each time, which turns out to be the real answer.

The second run is the cleaner one. A 200 megabyte file holding 93 megabytes of a densely packed table. I deleted nine rows in every ten.

Nothing moved. The file was still 200 megabytes and 93 were still in use. Deleted pages stay allocated to the table.

TRUNCATEONLY did nothing either, because the free space was not at the end of the file.

Then a real shrink. The file went from 200 megabytes to 93, so about 107 megabytes came back. Fragmentation stayed at 0.01 percent.

My first attempt went differently. On another table, carrying a second index, the same operation took fragmentation from 0.01 percent to 99.99.

That is the honest state of this advice. A shrink moves pages towards the front of the file. Whether that scrambles your index order depends on your table, not on a rule.

One more figure worth having. When I rebuilt afterwards, the file grew from 93 megabytes to 157, because a rebuild needs room to build in.

So do not bolt a rebuild onto every shrink out of habit. Measure the fragmentation afterwards and rebuild if it actually moved.

If you genuinely need the disk space back, take it. Expect the file to regrow if you then rebuild.

If you do not need the space, leave it alone. Empty pages inside your file are not a problem. They are room for tomorrow.

4. Is SELECT star really that bad

I have been told never to write SELECT star. But my application genuinely needs every column. Am I supposed to type all forty of them to satisfy a rule?

No, and the rule is aimed at the wrong thing.

I ran the same filter three ways against 100,000 matching rows. Asking for the two columns my index carries cost 491 logical reads. Asking for all six columns cost 9,339, whether I named them or used the asterisk.

The asterisk was not the expensive part. The columns were.

Nineteen times the reads. I went back and captured both plans, because I wanted the mechanism rather than a guess.

The two column version was an Index Seek. The all columns version was a Clustered Index Scan. It did not look each row up individually. It abandoned the index and read the table.

Then something more interesting. I put the asterisk inside a subquery and used none of its columns. That came back at 491 reads, because the optimizer threw away the columns nobody wanted.

So the honest rule is not “never write star”. It is “know which columns your index carries, and do not ask for more than you will use”.

If you truly need all forty, the asterisk costs you nothing extra today. It costs you the day somebody adds a wide column. Every query in your application then starts dragging it around.

5. Does ORDER BY in a view do anything

A developer here puts TOP 100 PERCENT and ORDER BY inside views so the data comes out sorted. It seems to work. Is it safe?

It is not safe, and you can prove it to them in one minute.

I built exactly that view, ordering by amount descending. I selected the first five rows from it. They came back as 30.50, 50.50, 70.50, 90.50 and 110.50. That is ascending, and it is the order of the clustered index.

Then I looked at the plan. There is no sort operator in it at all. The ORDER BY was discarded at compile time. A view is a table expression, and a table expression has no order.

It seems to work because the clustered index usually hands rows back in a helpful sequence. That is a coincidence your application is now depending on. Put the ORDER BY in the query that needs it, every time.

6. Is a heap ever acceptable

I inherited a table with no clustered index and I have been told this is a firing offence. It is a staging table that gets loaded and read once. Do I have to fix it?

For load once and read once, a heap is fine. Better than fine, sometimes. The trouble starts when rows change size.

I loaded 50,000 short rows into a heap, and the same 50,000 into a clustered table for comparison. Both sat in 230 pages.

Then I widened every row from 20 bytes to 1,900.

The heap went to 12,615 pages and picked up 49,541 forwarded records. The clustered table went to 24,082 pages and cannot have forwarded records at all.

Notice that the heap is the smaller of the two. Most of that page growth is simply the wider data. The heap packed it better, at 95 percent full against 49.

The cost shows up when you read it. A full scan of the heap took 62,156 logical reads. The same scan of the clustered table took 24,170.

Two and a half times the reads, on half the pages. That gap is the forwarding.

A forwarded record is a note where the row used to be. Every scan follows those notes around the file.

So keep your staging heap, and load it, read it, truncate it.

If something does start widening rows in place, measure the forwarded records and the reads first. A clustered index is one option, not an automatic sentence.

7. Does the order of my WHERE predicates matter

A senior colleague rearranges WHERE clauses to put the most selective condition first. They say it helps. I cannot find it in the documentation.

You cannot find it because it is not there.

I ran the same three conditions in two different orders. Both produced the plan hash 0x7DBB75C42747C908, and both did three logical reads.

A matching hash says the plans are the same shape, not that every detail is identical. For this question it is enough.

SQL is declarative. You describe the result and the optimizer decides the order of operations, which is the entire point of the thing.

Writing the most selective predicate first does not force SQL Server to evaluate it first. Nothing in the language lets you demand an evaluation order.

Your colleague is not doing damage, so this is not worth a fight. Order your predicates however reads best to whoever maintains the query.

What all seven have in common

Only one of these seven is really about an old version. The table variable rule describes optimizer behaviour that changed in 2019.

The rest are still true, and have simply lost their conditions. Indexes do cost you on write. Shrinking can fragment an index. Extra columns do cost reads.

What went missing is the “when”. A rule arrives with its conditions attached, gets repeated without them, and hardens into a flat prohibition.

That is how our field accumulates rules. Somebody measures, writes it down, and the conditions fall off in transit. Nobody goes back and checks, because checking takes an afternoon and repeating takes a second.

You have a test instance. Most of the numbers above took me under ten minutes each to produce.

Sometimes a team is carrying rules nobody can date. That is the sort of thing I get called in to sort out. You can see how I work with teams on my consulting page.

I write about confident answers and where they come from in my book AI: Nobody’s in There. But we’re still in here. All thirty essays are free to read online, and the paperback is on Amazon.

Old advice is not wisdom, it is a measurement that lost the conditions it came with.

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