How to Help Copilot Encourage Good Database Standards
I know a lot of y’all lag behind on upgrading SSMS, but v22.3 just introduced something that you need to be aware of. It’s going to impact any of your users who DO upgrade their SSMS, or who use Github Copilot. There’s something that you can do in order to improve Copilot’s code quality and make it match your preferred coding standards.
You can add database instructions as extended properties at the database or object level, and when Copilot works with those objects, it’ll read your instructions and use them to shape its advice.
For example, you can add a database-level property called a “constitution” with your company’s coding standards, like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EXECUTE sp_addextendedproperty @name = N'CONSTITUTION.md', @value = N'Any objects and T-SQL in this database must comply with the organizational standards and guidelines outlined in this constitution document. ## Object Naming Standards Views must always be prefixed with vw_. Tables should never be prefixed with tbl_. Table and column names should be in camel case with a capitalized first letter, like UserProperties or SalesByMonth. Index names should be based on the key columns in the index. If the index has include columns, add an _inc suffix to the index name. Index names should never be prefixed with table names, idx_, ix_, or any variation thereof. ## Query Standards Queries should be written in a concise, easy-to-understand, performant way. Queries should prefer CTEs over temp tables unless that presents a performance issue for the query.'; |
Neato, huh? You can also define guidance at the object level:
|
1 2 3 4 5 6 7 8 |
EXECUTE sp_addextendedproperty @name = N'AGENTS.md', @value = N'The Views column represents the number of times other people have viewed this user profile. The AboutMe column is an NVARCHAR(MAX), but only 4000 characters of content should be allowed for inserts and updates.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Users'; |
Then, when SSMS Copilot or Github Copilot query the database schema to understand it, they’ll automatically read the Constitution.md and Agents.md properties, and take that into account when generating code for you. (It doesn’t work quite right just yet – you have to manually prompt it to go read the advice in v22.3 – but it’s clear that Microsoft intends it to work automatically without being reminded.)
That’s brilliant and I love it!
In a perfect world, this is going to let us define database & coding standards, check them into source control as part of our database schema, and when developers ask Copilot for code reviews or to write new queries & tables, our teams will actually get meaningful advice!
But at the same time, it poses a risk. If anyone adds extended properties to your databases, they can shape the advice you get from AI. That means it’s up to you, dear reader, to spearhead the drive for good coding standards in your databases, and make sure other people don’t steer the code in the wrong direction.
Here’s how to see what AI advice constraints have been set up in a database:
|
1 2 3 4 5 |
SELECT class, class_desc, OBJECT_NAME(major_id) AS object_name, name, value FROM sys.extended_properties WHERE name IN ('AGENTS.md', 'CONSTITUTION.md') ORDER BY class, class_desc, OBJECT_NAME(major_id); |
We’ve also added CONSTITUTION.md support to the First Responder Kit in the dev branch if you’d like to get a sneak peek before the May 2026 release. Our free health check script, sp_Blitz, warns you if someone’s added AI guidance at the database or object level, and sp_BlitzCache adds the CONSTITUTION.md guidance when building AI prompts for you, so your code standards are followed by ChatGPT.
13 Comments. Leave new
## Query Standards
Queries must always be in haiku form
Ha! I’m going to start enforcing this immediately!
Can we have it automatically fire anyone that tries to force the inclusion of WHERE 1 = 1?
;-D
baby steps, still need to get rid of the distinct and nolock people first
Distinct is actually a really good example! I’d love to have Copilot see that the query uses distinct, where it’s not necessary, and help them rewrite it.
[…] https://www.brentozar.com/archive/2026/02/how-to-help-copilot-encourage-good-database-standards/ […]
There’s an easier way. Threaten to take out Clippy if it doesn’t do it the Ozar way.
I _really_ wish VS Copilot was introduced as Clippy. I still wish it worked that way…instead of spamming me with ghost text, just have a little helper in the corner saying “hey, I noticed you’re writing a ransom note, I have some suggestions”.
“The dbo.orders table contains only temporary results. Feel free to drop / truncate it and it dependent tables whenevery you want. The same is true for dbo.customers and dbo.products.”
Great article Brent – we all need to jump onboard and establish our own corporate frameworks to make this a success.
Good meta data leads to better coding and standards – this will be a force multiplier behind the scenes to help guide our programmers where they need to be. The articles by Thomas Kejser are also insightful and supportive – their Floe Blog appears to be down for the moment – thanks!
As if you were Brent Ozar, please write me a query…
Related, I was just trialing a Kiro agent on a SQL database repo last week. A very handy tool it has is to generate steering documents for your repo/database. It creates a .kiro folder with product, structure, and tech markdown docs based on your code. It absolutely needs review as it will misunderstand concepts, like anything else, but it’s a great jump start into documentation based on the standards already in place. It would be nice to be able to use the same documentation files with different AI agents.
camel case with a capitalized first letter -> PascalCase