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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
## 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
I was stoked when I first read about the ability to use the Constitution.md extended property with SSMS and Github Copilot. Spent way too much time building it in an effort to get developers to use ‘better’ ‘architecture and query’ practices when using Copilot. Then asked myself “How did I forget there was a 7500 byte limit on extended properties!!!” Argh!!! Mine was 247 lines ( over 21K characters) of best coding practices , with the YAML frontmatter all segmented into into discrete Scopes – broken down into SQL Object types, Best Query practices, Best coding Security practices, and even Data Type recommendations for specifically named columns!
** I REALLY WISH THAT ALL THE POSTS ON THE INTERNET RAVING ABOUT THE CONSTITUTION.md EXTENDED PROPERTY WOULD HAVE SAID:: **
” Whoa buddy, slow down! 7500 byte limit here, space is going to be tight, you will only get a fraction of best practices defined. Do want a little of best architecture practices listed here -or- a little of best coding practices listed here? Because you as heck won’t be doing it all here! “
Well at least you can put an instruction file for each SQL Object type at the Repo level, although there are best practices/limits there too. If your not already using it you can craft the applyTo syntax in the YAML frontmatter to be applicable to all schemas and don’t have to add them per schema (if all your schemas will have the same rules for similar type objects).
Yep, that’s good advice anytime you’re working with AI – or with humans. More text isn’t necessarily better. You’ll find things drift out of the AI’s active context (and the human’s active attention span) even if they claim they support the full length.
Putting the instructions into an Extended Property is ‘interesting’.
Even though it’s long I’m hoping that using the ‘Scope:’ in the YAML frontmatter will assist in limiting context drift.
CoPilot responded with examples like the syntax below.
Note – rules below are only for demo purposes and do not reflect what I am using.
I haven’t tested this yet and only going by responses CoPilot gave me.
…any comments on the internal frontmatter structure?
DECLARE @ConstitutionText NVARCHAR(MAX);
SET @ConstitutionText = N’—
AppliesTo:
– Scope: “Tables”
Instructions:
– “Enforce Pascal Case for table names.”
– “Every table must have a clustered primary key.”
– “Avoid the use of Nullable columns.”
Scope: “Stored Procedures”
Instructions:
“Explicitly name all columns in SELECT statements.”
“Always include SET NOCOUNT ON at the beginning of the procedure body”
Scope: “Views”
Instructions:
“Always define views using the WITH SCHEMABINDING.”
“Avoid nesting views within other views.”
—‘
doh! lost some of the formatting such as the critical tabs in the YAML and the – in front of the rules, and the looking like a single underline at the top and bottom. IYKYK