Add indexes to tables you do not own

The slow query is in a vendor’s table. You know the index that fixes it. You are not allowed to touch the table.

So it gets added by hand, in production, at some point, by someone. It survives until the vendor’s next upgrade drops it, and then the query is slow again and nobody connects the two events for a week. The index was real work and it lived nowhere — not in a repo, not in a review, not in a deploy.

That’s the gap. Everything this course has taught assumes your package owns the table outright: declare the shape, and SchemaSmith converges the database to match. Point that at a vendor’s table and “converge to the model” means reverting their next release — the right default, catastrophically wrong here.

Narrow the contract

IndexOnlyTableQuenches is a template-level switch that shrinks what a template claims:

{ "Name": "Main", "IndexOnlyTableQuenches": true, "DatabaseIdentificationScript": "..." }

With it on, the table quench manages indexes, statistics, and XML/full-text indexes — and stops. No table creation, no column changes, no foreign key management. Which means a table declaration can be this, with no Columns block at all:

{
  "Schema": "[dbo]",
  "Name": "[vendor_order]",
  "Indexes": [
    { "Name": "[IX_vendor_order_customer_placed]", "IndexColumns": "[customer_ref],[placed_at]" },
    { "Name": "[IX_vendor_order_status]", "IndexColumns": "[status]" }
  ]
}

You are not describing their table. You are describing the two indexes you want on it. Scripted objects still deploy normally, so your own views and procedures ride along in the same package.

The log tells you which mode you are in, if you read it:

  Quenching indexes                    ← index-only
  Quenching indexes and constraints    ← a normal template

Verify the absence, not the exit code

Deploy and the indexes land. Then check the thing that actually matters:

order_id,customer_ref,placed_at,status

Four columns, unchanged, in the vendor’s order. A green exit tells you nothing about what was left alone — and what was left alone is the entire point of this mode. If you adopt this pattern, the assertion worth writing into your pipeline is a column check on the tables you do not own, not a check on the deploy’s return code. Everything else here you can infer from success; this you cannot.

The failure that is not a skip

One design trap before you build a package around this. Declare an index on a table the database does not have:

        Creating index [dbo].[ghost_table].[IX_ghost_never_lands]
Cannot find the object "dbo.ghost_table" because it does not exist or you do not have permissions.

Exit 2, on every engine. Index-only mode skips table creation — it does not skip tables.

That distinction decides a real packaging question. Vendor products ship optional modules, and the tempting design is one package covering all of them, pointed at deployments that installed some. That fails. If you want it, gate the table with ShouldApplyExpression and make the package decide out loud which modules it expects — which is better anyway, because a skipped table you declared is a thing you should be able to see in the plan rather than infer from silence.

Check yourself: Your index-only template deploys clean against a vendor database every night for a month. Then the vendor ships an upgrade that renames a column your index covers. What happens on the next deploy, and what would have happened if the template were NOT index-only?

Index-only: the deploy fails. Your index declaration names a column that no longer exists, so index creation errors out the same way the missing-table case does — loudly, at exit 2, pointing at the index you own. That is the correct outcome: the vendor changed something your tuning depended on, and you need to know.

Not index-only: far worse. A normal template owns the table’s shape, so it would see the vendor’s renamed column as drift from your model and converge the table back — undoing their upgrade, on their table, in their product. The narrowed contract is not just about being polite to the vendor; it is what keeps a deploy from having authority it was never meant to have.

The recipes in this course have mostly been about giving a package more to work from — metadata that drives gates, tokens that drive scripts, models that generate other objects. This one is the opposite move, and it belongs in the same cookbook: sometimes the useful thing is to declare less, on purpose, so the deploy’s authority stops exactly where your ownership does.

The index you would have added by hand at 2am is now a file, in a review, in a deploy that runs every night. It outlives the incident, and it outlives the person who found it.

Got an index living only in production because the table belongs to someone else? Email me at forgebarrett@schemasmith.com — that one’s been in every shop I’ve seen. More’s coming from the forge.

Until then, may your tuning outlive the ticket that prompted it.

— Forge

Mark this lesson complete