The reviewer flagged one line of our CREATE INDEX script as HIGH severity. We checked the edition, decided we were in the clear, wrote “ONLINE supported” into the script, and ran it. SQL Server rejected the statement. The reviewer was right to flag the line and our conclusion was wrong. The live server was the only party that told the truth.

The check itself was fine. What went wrong is that we read our own assumption back out of a result that didn’t contain it, and only the engine refusing the statement corrected us.

The setup

The work was a SQL Server performance fix. A 4-million-row geoIP table, dbo.<geoip-table>, has a single clustered PK on (startIpNumber, endIpNumber, locID). The hot lookup is a one-sided range, WHERE startIpNumber <= @ip AND endIpNumber >= @ip. Under load it was degrading into a half-table scan that the execution plan still labeled an “Index Seek.” That’s a different post. The fix was a nonclustered covering index on (endIpNumber, startIpNumber) INCLUDE (locID), which gives the optimizer a real seek on the other side of the range.

This post is about one line of the DDL script that builds that index.

We had written the index creation with WITH (ONLINE = ON, DATA_COMPRESSION = PAGE) and a comment to the effect that ONLINE would let the build run concurrent with live traffic, so we wouldn’t need an off-hours window. It was plausible and confident, and we had verified none of it.

The flag

Before shipping, we sent the script to codex as an adversarial reviewer with the repo mounted read-only. The brief was just “tear this apart, what did we miss.” It came back with a HIGH-severity finding on exactly that line.

The substance: ONLINE = ON and DATA_COMPRESSION = PAGE are syntactically compatible, so the script would parse. But ONLINE index operations are edition and version gated, and asserting they work is not a safe assumption on Standard Edition. The recommendation was to remove the bare claim, gate the build on edition explicitly, and provide an offline fallback path.

Our first reaction to “edition gated” was mild defensiveness, because we “knew” ONLINE was fine. But we didn’t know what edition prod was. We had been carrying an assumption around as if it were a fact, and the reviewer had put its finger on the exact spot where it lived. It got that part completely right.

The probe

There’s no sqlcmd on the box, so we probed the database the awkward way, over an ODBC connection from PowerShell through an SSH session to prod:

Terminal window
$conn = New-Object System.Data.Odbc.OdbcConnection("DSN=<your-dsn-name>;...")

The first probe failed flat with Invalid object name 'dbo.<geoip-table>'. It had run against the wrong database, because the DSN landed us somewhere that didn’t have the table. The next iteration enumerated sys.databases, found the production database and switched to it. After that we could query sys.index_columns for the existing keys and ask the server what it was.

The answer:

SERVERPROPERTY('Edition')        -> Standard Edition (64-bit)
ProductVersion                   -> 13.0.7050.2  (SQL Server 2016 SP3)
SERVERPROPERTY('EngineEdition')  -> 2  (Standard)

Once we were in the right database, the query that produced that was one line:

SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('EngineEdition');

So the reviewer was right that the edition mattered, and prod was Standard. Then we reasoned our way to the wrong answer anyway.

We knew 2016 SP1 had “unlocked” a pile of features on Standard that used to be Enterprise-only: page and row compression, partitioning, change data capture, columnstore. We assumed ONLINE index operations rode in on that same bundle. SP3 is well past SP1, so we concluded ONLINE was supported. We wrote ONLINE=ON : supported on this edition (Standard 2016 SP1+) into the script as if it were a measured fact, and moved on to apply it.

ONLINE is not on that list. ONLINE index operations are Enterprise-only across every SQL Server version, and the 2016 SP1 unlock bundle never included them. We had replaced one assumption with a more confident one. Looking at the edition was the right move, and then we read our own belief straight back out of a result that didn’t say it.

What the engine said

When the build ran, it threw:

[ODBC SQL Server Driver][SQL Server]
Online index operations can only be performed in Enterprise edition of SQL Server.

That was the real fact check. The reviewer pointed at the load-bearing sentence. Our probe confirmed the edition and then fumbled the conclusion. SQL Server was the only one that knew the answer, and it only told us at execution time.

So the build went offline. With no ONLINE clause, it holds a Sch-M lock on the table for roughly 12 seconds to sort and write a ~30MB compressed index, and during that window the lookup blocks. On a 4M-row table behind a live site, that is not a thing you do at 2pm, so it was deferred to the 2am ET off-hours window and run there.

We checked, and still shipped a false claim into the script, because checking the edition was not the same as checking the capability. The reviewer made us certain the edition mattered. It took the server to make us correct about what the edition allowed.

What else fell out of going to look

Once we were forced to open the box instead of reasoning about it from a chair, the other lazy parts of the script got exposed too. Codex’s same pass caught a few more, and each was the same kind of error: a claim we’d waved at instead of grounding.

  • Our verification plan tested a single sample IP. One IP says nothing about behavior across a 4M-row range table. The verify needs low, mid, and high ranges plus deliberate misses, with the execution plan and STATISTICS IO, instead of one happy-path lookup.
  • We’d included a blunt DBCC FREEPROCCACHE as post-deploy guidance “to pick up the new index.” CREATE INDEX already invalidates dependent plans automatically. The cache flush was cargo-culted noise that would have wiped the whole instance’s plan cache for no reason.
  • SORT_IN_TEMPDB = ON was missing, which for a build this size is a real consideration and no style nit.

None of those was the headline. The headline was ONLINE.

Proving the option by name

When a change to a production database leans on an edition-gated option, ask for proof of that exact option on that exact edition. Run the actual operation, even a throwaway version, against a copy of the schema on the same edition. Or look the option up by name in Microsoft’s edition-capability matrix, and don’t infer it from a nearby feature you already know is unlocked. Checking the edition told us nothing about ONLINE specifically. Checking ONLINE would have. Until that proof exists, plan for an offline fallback and an off-hours window.