I wrote a CREATE INDEX script that confidently used ONLINE=ON on a production table, and I had no idea what SQL Server edition the box was running. An AI reviewer flagged it HIGH severity. I went and checked the edition, decided I was in the clear, baked “ONLINE supported” into the script, and ran it. SQL Server rejected it on the spot. The reviewer was right to flag the line, my conclusion was wrong, and the server was the only one in the room telling the truth.

That’s the whole post. When you catch yourself writing a capability claim about something you could verify, checking is mandatory. But checking badly and reading your own assumption back out of the result is its own failure mode, and the only thing that caught it was the live server saying no.

The setup

The actual work was a SQL Server performance fix. There’s a 4-million-row geoIP table, dbo.<geoip-table>, with a single clustered PK on (startIpNumber, endIpNumber, locID). The hot lookup is a one-sided range, WHERE startIpNumber <= @ip AND endIpNumber >= @ip, and under load it was degrading into a half-table scan that the execution plan still cheerfully 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.

I 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. Plausible, confident, and completely unverified.

The flag

Before I shipped it, I sent the script to codex as an adversarial reviewer with the repo mounted read-only. The intent was just “tear this apart, what did I 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. Asserting ONLINE would 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.

My first instinct on reading “edition gated” was mild defensiveness, because I “knew” ONLINE was fine. But I didn’t actually know what edition prod was. I had been carrying an assumption around as if it were a fact, and the reviewer had just put its finger on the exact spot where the assumption lived. That part it got completely right. Not the verdict, the finger.

Going to check instead of arguing

There’s no sqlcmd on the box, so I 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>;...")

First probe failed flat with Invalid object name 'dbo.<geoip-table>', because it ran against the wrong database. The DSN landed me somewhere that didn’t have the table. So the next iteration enumerated sys.databases, found the production database, switched to it, and then I could actually 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)

The query that produced that, once I was finally in the right database, was one line:

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

So the reviewer was right that the edition mattered, and prod was indeed Standard, not Enterprise. Then I reasoned my way to the wrong answer anyway.

I knew 2016 SP1 had “unlocked” a pile of features that used to be Enterprise-only on Standard: page and row compression, partitioning, change data capture, columnstore. I assumed ONLINE index operations rode in on that same bundle. SP3 is well past SP1, so I concluded ONLINE was supported, 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.

It 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. I had replaced one assumption with a more confident one. I went and looked at the edition, which was the right move, and then read my own belief straight back out of a result that didn’t actually say it.

The thing that caught me was the server. 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. Not the reviewer, not my probe, the engine refusing the statement. The reviewer pointed at the load-bearing sentence. My probe confirmed the edition and then fumbled the conclusion. SQL Server was the only one that actually knew the answer, and it only told me at execution time.

So the build went offline. No ONLINE clause, 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 on a live site that is not a thing you do at 2pm, so it got deferred to the 2am ET off-hours window and run there.

I 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 me certain the edition mattered. It took the server to make me correct about what the edition actually allowed.

What else fell out of going to look

Once you’re forced to actually open the box instead of reasoning about it from your chair, the other lazy parts of the script get exposed too. Codex’s same pass caught a few more, and every one of them was the same species of error: a claim I’d waved at instead of grounding.

  • My verification plan tested a single sample IP. One IP proves nothing about distribution-wide behavior across a 4M-row range table. The real verify needs low, mid, and high ranges plus deliberate misses, with the execution plan and STATISTICS IO, not one happy-path lookup.
  • I’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 nuked the whole instance’s plan cache for no reason.
  • Missing SORT_IN_TEMPDB = ON, which for a build this size is a real consideration, not a style nit.

None of those were the headline. The headline was ONLINE. Before you write any edition-gated DDL option into a script as fact, don’t stop at confirming the environment. Run the actual operation, even a throwaway version of it, against a copy of the schema on the same edition, or look up that exact option by name in Microsoft’s edition-capability matrix instead of inferring it from a nearby feature you already know is unlocked. Checking the edition told me nothing about ONLINE specifically. Checking ONLINE would have.