This is part of a pair of articles on the impacts of agent driven development - you can read thoughts on the review process here.

I threw away the entire AI for Annhexation and started again. Annhexation is my 4X strategy game (which incidentally you can wishlist on Steam!).

Not refactored it. Deleted it, wrote a new spec, built the second one from scratch. If you look in the source you’ll find a package called ai-v2. The first version worked, in the sense that it played legal moves, expanded sensibly, never fell over. It was also no fun at all to play against, didn’t generate interesting decisions, and as I tinkered with it I realised that no amount of tuning was going to fix that, because the problem was in the shape of the thing I’d specified rather than in its rules and weights.

Version two is much better. But I could not have written the version two spec first because all the insights in it came from having built version one and played against it over and over. The thrown-away implementation was the instrument that produced the better specification. If you’re interested in how the final version works I’ve written about it here.

I’ve been thinking about that a lot, because there’s a model of agentic development going round at the moment that has no room in it for what I just described. At least as often presented.

The model I don’t believe in

The pitch is that you have a backlog, you point an agent at a ticket, it reads the ticket, understands the codebase, makes the change, writes the tests, opens the PR, responds to your review comments. You do that at volume and the backlog goes down.

I want to be clear that the individual steps in that work. I’ve built systems that do a chunk of it. I’ve developed parts of Annhexation in this way and built project specific tools to support it. Building Annhexation without this technology, this approach, would have taken me years. Its not a trivial project, its large, with many subsystems, and full of nuance. I want to be clear this isn’t a piece about the tools being bad, they’re really not, in fact I think they’re better than most people arguing about them realise.

Rather it’s a piece about what this model assumes. And what it assumes is that the ticket is a sufficient description of the work.

A ticket is a hypothesis

A ticket isn’t the work. It’s the residue of a decision, or series of decisions, somebody already made.

Somebody thought about the problem, formed a view about what should happen, and wrote down enough for a person to act on. All the expensive part - the deciding, the weighing, the discarding of three worse options - happened before the ticket existed. What’s left in the ticket is the conclusion with the reasoning boiled off. Do this. And here’s how you know its done.

That’s fine, as long as everyone treats it as what it is: a hypothesis about what would be good, written by someone who hadn’t yet seen it running.

But an agent doesn’t treat it as a hypothesis. Rather it treats it as a contract, and it satisfies it.

Where the line actually falls

Now I don’t actually think there’s anything new here, good product teams know this. And this isn’t a general argument against pointing agents at tickets. There’s a real dividing line, and it isn’t difficulty. It’s whether the acceptance criterion exists outside a human head.

A crash with a stack trace, a dependency that needs bumping, a calculation returning the wrong number for a known input. These are cases where reality supplies the specification. The ticket is a complete statement of the work, there’s something external for the agent to be right or wrong against, and if it’s wrong you’ll know. That whole category is a fair target for automation, and I’d defend building for it.

Design work is the other side of the line. Not because it’s harder, but because the criterion lives in a person, and often doesn’t exist yet even there. Nobody knows whether a mechanic is interesting until it exists and someone has played it. Nobody knows if a feature will resonate with customers until they can try it. All companies have delivered what they were sure were killer features that quietly died.

Everything below is about that second category.

What actually happened with eight civilisations

Annhexation has eight asymmetric civilisations, three victory paths, deterministic combat, and an AI in three layers. All of that was specified up front - I worked on what turned out to be a large set of game design documents. And I’m not making a case for working without specs: I wrote them, they were reasonable, some of them were quite detailed, and they drove the early development. Without them I’d have been working blindly with or without agents.

But what’s in the game now bears very little resemblance to any of it.

Not because the specs were careless. Rather because a civilisation that reads as distinctive on paper turns out to play identically to another one by turn 40. Because an asymmetry that looks interesting in a design document turns out to just be worse, and nobody picks it. Because the mid game sagged. Because a victory path that ought to create tension instead creates a race that’s decided by turn 15. None of that is visible before the thing exists. You find it by playing.

And I responded to those discoveries in three quite different ways, at three different scales:

On the fly. Something’s off, you’re already in the code, you change it. Half a mechanic gets adjusted before you’ve articulated what was wrong with it.

A new sub-spec. The discovery is big enough to need its own thinking, but the surrounding design holds. You write the smaller thing down and build it properly.

Bin it and rewrite. The spec was wrong at the root. That’s the AI v1 story. No amount of adjustment gets you there, so you throw the implementation away and write a better specification, which you can now write because you’ve seen the failure.

A ticket-driven agentic flow can do the first of those in a limited way. It can’t do the third at all. It can help you write the new spec, yes, but only because you’ve seen the failure.

Fidelity is the failure mode

This is the bit I’d want anyone building these systems to sit with for a minute.

The agent’s problem isn’t that it might get it wrong. It’s that it gets it right.

It will implement the hypothesis with total fidelity and it will never come back and say this civilisation is boring by turn 40, because it hasn’t played the game and nothing in the loop asks it to. A person implementing that same spec usually says something on the way. Halfway in they’ve felt the shape of it, and they push back, or they quietly build the better thing and mention it at standup.

That channel doesn’t exist. Worse, its absence is invisible, because a faithful implementation of a bad spec looks exactly like a faithful implementation of a good one. Same green tests, same clean diff, same tidy PR description. There’s no signal in the artefact that tells you the specification was wrong.

There’s a second loss that’s easier to miss. Building is how you find out. You learn things while implementing that change what should be implemented - the balance problem you only see with the actual numbers in front of you, the mechanic that turns out to interact badly with another. Delegate the implementation and that information is generated inside the agent, and it doesn’t reach you. You get the artefact without the learning.

This is waterfall

I’d been dancing round saying this, so let me just say it: ticket in, finished solution out, is waterfall.

Not “a bit like waterfall”. The same control structure. Waterfall’s failure was never the documentation - people get distracted by the documentation. It was the single forward pass with no return path. Requirements go one way, and there’s no mechanism by which building the thing is allowed to change what the thing should be.

Ticket to agent to PR to merge is a single forward pass with no return path, running considerably faster.

And here’s why nobody notices: waterfall was survivable in practice because people cheated. Most real waterfall projects had an informal back channel doing the work that the process denied. The corridor conversation, the requirement quietly renegotiated, the engineer who built the sensible thing and got forgiveness afterwards. The process on paper never worked. The humans inside it made it work by ignoring it.

An agent doesn’t cheat. It does exactly what the ticket says.

If we allow agents to run from ticket to merge then we’ve built the first faithful implementation of waterfall anyone’s managed, and faithfulness turns out to be the flaw.

What they’re actually good for

So here’s where I land, and it’s not where the negative half of this post might suggest.

The gain from these tools isn’t getting it right first time. That was never available and it isn’t now. The gain is that iteration got cheap.

Iteration always happened in any healthy product cycle. The question was only ever what it cost. And what an agent compresses is exactly the expensive middle bit - the two weeks of implementation that stood between having a hypothesis and finding out whether it was any good. Fantastic ideas would never be surfaced to a customer down to this. Growth opporuntities sidelined on the back of risk and cost.

Which changes something real. Throwing away two weeks of your own work is a decision people avoid, and they avoid it for reasons that have nothing to do with engineering. Throwing away an afternoon is easy. So you can afford more hypotheses, and you can afford to kill the bad ones earlier, and that’s a genuine improvement in how a product gets made rather than a marginal speedup.

I’m reasonably sure the AI v1 rewrite would have taken me longer to decide on if the implementation had cost more. I can easily imagine a game studio never actually doing it - the cost being too high. Gamers (and I’m one) often scratch their heads at the seemingly obvious flaws that make it to release. This is one of the reasons why. It was too expensive top iterate and fix.

But it caps out, and it caps out quickly, because evaluation didn’t get cheaper. You still have to play the game. Someone still has to use the feature and notice it’s dull. That’s human, it runs at human speed, and it doesn’t compress. Make the build step ten times faster and the loop gets ten times faster only if judgement keeps up, which it doesn’t - so what you actually get is a shorter build step and the same bottleneck sitting immediately after it, now more obviously the bottleneck than it was before. You can build tools, and I have, to accelerate the feedback once you start to understand the failure states - but you still have to understand the failure states.

There’s also a trap sitting underneath the whole “just write better tickets” response, which is the one I’d expect to this post. A specification precise enough to be unambiguous is isomorphic to the program. Push the ticket-writing far enough to make the agent reliable on design work and you have written the code in a worse language, more slowly, with no compiler.

Three things I think are true

Confidence attached, as is becoming usual.

1. The specifiable fraction of a backlog is a real ceiling on ticket-driven agents. Reasonably confident. It’s not a property of the tools, it’s a property of your product. How much of your board has an acceptance criterion that exists outside somebody’s head. My guess is well under half for most product teams and lower still for anything pre-product-market-fit, but that’s a guess though one founded on working on a lot of products.

2. The value shows up as iteration count, not as throughput. Moderately confident. If you’re measuring tickets closed you’ll conclude these tools are working brilliantly while the product gets worse. The thing to watch is how many hypotheses you tested and how many you killed.

3. Somebody has to keep playing the game. High confidence. Whatever the ratio ends up being, it’s bounded by how much contact a human still has with the running thing, because that contact is the only place the feedback comes from.

Where this landed

The Annhexation AI is on its second version and I’d say it’s had four or five significant reworkings since. I created new sub specs (naval warfare turned out to require a really different set of goals and tasks), on-the-fly changes, and a substantial rethink of the diplomacy mechanism. It’s still not finished. In truth it never will be. I’m still tuning behaviour now, months after the game was otherwise done, because I keep playing it and keep finding things.

Not one of those changes could have been a ticket written in advance. Every one of them came from sitting in front of the thing, playing lots of games (real and simulated), and noticing things. I’ve accelerated this process by evolving my analysis tools in parallel - but similarly I couldn’t spec those up front beyond a core because the process of understanding what to analyse, what to look for, is similarly iterative.

That’s the part these tools don’t touch. It’s not a gap they’re closing slowly - it’s a different activity, and it’s the one that determines whether what you built is any good. The agents make everything around it cheaper, which is genuinely useful, transformational even, and I’d not go back.

But somebody still has to play the game. Somebody still has to have taste and judgement.

SQL Server 2025 finally gives developers a native JSON data type and, with it, a purpose-built way to index JSON documents with the new CREATE JSON INDEX statement. Before this, indexing JSON meant exposing individual properties through computed columns and building standard indexes on top.

It’s a major step toward closing the gap with databases like PostgreSQL, long praised for its JSON and JSONB support. However, as a preview feature, JSON indexing comes with real constraints DBAs and developers should understand before adopting it.

This guide has everything you need to know about JSON indexing in SQL Server 2025: what it is, how it works, and current limitations.

For many years, one of the reasons given as to why developers preferred PostgreSQL to SQL Server was the level of JSON (and more recently JSONB) support. From SQL Server 2017, that started to change.

JSON support in SQL Server pre-2025: what was it like?

In SQL Server 2017, several useful JSON-related functions were added:

If you’d worked with XML in SQL Server, this was all very familiar, aside from one key difference. With XML, we had an XML data type – but with JSON, there was not a JSON data type.

Why was there no JSON data type?

One of the key reasons for this was that, for a while, the product team weren’t keen on adding new data types because of the extra work required around the edges. For example, all the work required in client libraries and related code.

Another useful addition along the way was the ISJSON function, designed to test if the value was Internet Engineering Task Force (IETF) conformant JSON. In SQL Server 2022, it could check if the JSON was a valid VALUE, ARRAY, OBJECT, or SCALAR.

There were limitations in practical use – it didn’t, for example, check for key uniqueness, and while we could check if a value was JSON, we couldn’t actually store it as JSON.

Sub-queries were a challenge with this. If a sub-query returned JSON data, what data type would it actually return without a JSON data type? When XML had the same issue early on, the TYPE directive was effectively its replacement, saying: “I really meant XML”.

Another useful addition was the JSON_PATH_EXISTS function, which eliminated potential confusion when you were checking for JSON values. If you asked to retrieve a value and the path to the value didn’t exist, you just got NULL back. OK, you did also get NULL back if the value was defined in the JSON as NULL, but this function then let us work out what was happening.

Finally, in SQL Server 2022, we could construct JSON in T-SQL code by using new functions like JSON_ARRAY and JSON_OBJECT.

JSON indexing in SQL Server 2025

SQL Server 2025 changed everything for JSON, adding – finally – a JSON data type, along with the aggregates JSON_ARRAYAGG and JSON_OBJECTAGG.

The biggest change, however, was around indexing. Prior to SQL Server 2025, indexing JSON data generally meant exposing individual JSON properties through computed columns and then creating standard SQL Server indexes on those columns.

I must admit, it worked quite well – in fact, we’d done the same with XML decades ago. But, the approach introduced in SQL Server 2025 was far more direct: the JSON index, with the new CREATE JSON INDEX statement. This new statement can index values within a JSON document without requiring a computed column for every property that you want to search.

It’s worth noting that, at the time of writing, JSON indexes are still listed as a preview feature. As an aside, I really wish there weren’t so many features in SQL Server 2025 still in preview!

An example of JSON indexing in SQL Server 2025

Consider an application that stores additional customer information in JSON:

USE tempdb;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int NOT NULL
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL,
    ContactDetails json NULL
);

The ContactDetails column might contain documents such as:

{
    "type": "Retail",
    "region": "Australia",
    "contact": {
        "preferredMethod": "Email"
    }
}

SQL Server can retrieve individual properties with functions such as JSON_VALUE:

SELECT CustomerID,
       CustomerName
FROM dbo.Customers
WHERE JSON_VALUE(ContactDetails, '$.region') = 'Australia';

If we didn’t have a way to index this, SQL Server might need to examine all of the JSON data – and for every row.

As I mentioned, one solution was to create a computed column:

ALTER TABLE dbo.Customers
ADD Region AS
    JSON_VALUE(ContactDetails, '$.region') PERSISTED;
GO

CREATE INDEX IX_dbo_Customers_RegionLookup
ON dbo.Customers(Region);
GO

Note that I’ve used the PERSISTED option as I like these to just be calculated when the value is changed.

Indexing this way is still useful, particularly when JSON is stored in varchar or nvarchar columns. SQL Server can match a query containing the same JSON_VALUE expression to the indexed computed column without requiring applications to query the computed column explicitly.

How to create a JSON index

For a column using the new native JSON data type introduced in SQL Server 2025, we can create an index directly:

CREATE JSON INDEX JX_dbo_Customers_ContactDetails
ON dbo.Customers(ContactDetails);
GO

When you do this, SQL Server recursively indexes the paths in the JSON document. Queries like the one searching for Australian customers might then take advantage of the JSON index.

This is even more powerful when applications search several properties within a document. Instead of adding multiple computed columns and indexes, one JSON index can support searches across multiple JSON paths.

How to define paths to index

To minimize the impact of this data, we can redefine the index to only index the paths that our apps actually search:

DROP INDEX JX_dbo_Customers_ContactDetails
ON dbo.Customers;
GO

CREATE JSON INDEX JX_dbo_Customers_ContactDetails
ON dbo.Customers(ContactDetails)
FOR
(
    '$.type',
    '$.region',
    '$.contact'
);
GO

Note that specifying $.contact recursively covers values below that path. As such, SQL Server doesn’t allow overlapping paths, like $.contact and $.contact.preferredMethod, in the same JSON index.

Limiting the indexed paths can make sense for large documents where only a small part of the JSON is used for searching.

Which queries can use JSON indexes?

Unlike other special indexes (such as spatial ones), JSON indexes aren’t limited to just one new JSON-specific predicate. They let you search JSON far more conveniently.

For example, a straightforward scalar comparison can use JSON_VALUE:

SELECT CustomerID,
       CustomerName
FROM dbo.Customers
WHERE JSON_VALUE
      (
          ContactDetails,
          '$.region'
      ) = 'Australia';

I mentioned that JSON_PATH_EXISTS can test whether a path is present:

SELECT CustomerID
FROM dbo.Customers
WHERE JSON_PATH_EXISTS
      (
          ContactDetails,
          '$.contact.preferredMethod'
      ) = 1;

Also added in SQL Server 2025 was JSON_CONTAINS. It searches for values, objects, or arrays within JSON documents and can take advantage of a JSON index.

A bit more on JSON arrays

If an application frequently searches values held inside JSON arrays, the index can be created with:

CREATE JSON INDEX IX_Customers_ContactDetails
ON dbo.Customers(ContactDetails)
WITH
(
    OPTIMIZE_FOR_ARRAY_SEARCH = ON
);

This option isn’t on by default, so should be enabled intentionally for workloads where array searches are important.

What are the current limitations of JSON indexing in SQL Server 2025?

Unfortunately, as with many new features, there are (currently) some restrictions and limitations to JSON indexing in SQL Server 2025.

First, a table must have a clustered primary key before a JSON index can be created. Furthermore, only one JSON index can currently be created for a particular JSON column, and changing the paths included in an index requires recreating it.

JSON indexes also don’t currently support online index creation or rebuilding, so maintenance operations can require a schema-modification lock on the underlying table.

Finally, there are still some limitations on which predicates can make effective use of the index. For example, the current implementation supports comparisons using JSON_VALUE for JSON-index optimization, but not the LIKE or IS NULL predicates.

Summary: JSON indexing in SQL Server 2025

In SQL Server 2025 JSON indexing becomes a design choice, but you need to decide if you are OK with using a preview feature. Many people have asked for this to be released in a cumulative update (CU) rather than in the next version, but it’s hard to know what will happen.

Either way, computed-column indexes haven’t suddenly become obsolete. They are still useful, particularly when JSON is stored as character data (previously the only option). These indexes can provide very targeted outcomes and let you use other standard features, like included columns.

Overall though, SQL Server 2025’s support for JSON makes it considerably more practical for both developers and DBAs alike.

The post JSON indexing in SQL Server 2025: how it works and current limitations appeared first on Simple Talk.

Learn what's new in Visual Studio Code 1.135 (Insiders)

Read the full article