Edward Pollack recently wrote an excellent wish list for T-SQL features he’d like to see in an upcoming version of SQL Server. Now, it’s my turn. This article covers what I’d like to see the most.

Extensibility added to SQL Server

I’ve had this wish for as long as I’ve worked with SQL Server, and I started with it in 1992! I can’t tell you how many times I’ve been in meetings with the product group, they show us a new feature, and I ask how I can build things that would enhance or complete what they’ve done. The response has almost always been “this is version 1; hopefully we’ll get to extensibility in version 2 or later”.

And, put simply, they never get there.

The problem I see with this, is that it often undermines – or worse, wastes – the product group’s own work. If they ship a feature and it’s not complete, there’s no way for us to fill in enough of what’s missing to make it useful. Then, when the feature doesn’t get enough traction in the market, it often just falls by the wayside. There are endless examples of this, but let me pick just one.

Back in SQL Server 2005, Microsoft added the ability to create our own data types as part of SQL CLR. It was great to see some extensibility appearing but, as soon as I looked into it, I realized I couldn’t use it to create what I wanted as there was no way to appropriately index the data type. I could only index properties of the data type via computed columns – not the data type itself.

This is because, with new data types, you often need a different type of index. So, I kept asking for the ability to create a custom index type, but the response was always along the lines of, “we don’t really need a new index type.”

But clearly we did need a new index type…

…and this has been proven again and again over the years. With the XML data type, Microsoft added two new index types. In SQL Server 2008, when they added their own new SQL CLR-based data types for geometry and geography, they added a new spatial index. For me, this should have been just one instance of a new custom index type.

It was no surprise, then, to see a new index type created when JSON was later added. When the vector data type was introduced, so was a new type of index for it. Since I now work with Azure SQL Database so much, and Microsoft removed their initial support for SQL CLR, we try to avoid using it in any of our SQL Server work. And we’re back to having no meaningful way to extend the language.

Meanwhile, with my other work in PostgreSQL, it’s something I really appreciate – and it’s part of the reason why the platform is considered more “developer-friendly” by many people. I’d like to see the SQL Server team think more about building an ecosystem rather than just shipping a product where they feel they need to supply everything.

Constants in SQL Server

I review large amounts of T-SQL and I so often see hard-coded values throughout the code. Performance-wise, it’s totally fine to write code like:

WHERE AdditionalStatus = 146

But as for code quality? What on earth is that ‘146’? Even if there is a comment there to explain it, it’s not needed.

Instead, we can put values like this in a variable, perhaps at the top of our code:

DECLARE @EXTENDED_STAY_STATUS int = 146;

and use it in code:

WHERE AdditionalStatus = @EXTENDED_STAY_STATUS

Unfortunately, though, it might not execute the same. You see this all the time when someone is trying to debug stored procedure code – they replace the parameters at the top with variables and then find it doesn’t run the same.

If we had true constants, the optimizer could know about them, and use them like literal values:

DECLARE @EXTENDED_STAY_STATUS CONSTANT int = 146;

I’d like to go even further and have the ability to declare constants at the database level – and potentially at the server level, too (not just session level.)

Consistency in SQL Server

Given the requirement for backward compatibility, this one is hard…but I really wish there was more consistency in how features and language elements are designed for SQL Server. We have names like DATETIMEOFFSETFROMPARTS, for example, but also have DENSE_RANK, COUNT_BIG, etc. There never seems to be any rationale for when names have spaces separating or are just run together.

The bigger issue, though, is the logical inconsistency. Even in SQL Server 2025, functions were added for EDIT_DISTANCE() and JARO_WINKLER_SIMILARITY(). What’s wrong with that, you might ask? Well, one of them – EDIT_DISTANCE – is named after what it’s measuring. And the other one – JARO_WINKLER_SIMILARITY – is named after the algorithm used!

You’d hope the naming would follow the same pattern: either named after the effect, or after the algorithm. I’d expect that across versions, but even more so within a single version of the product. EDIT_DISTANCE() should probably have been called LEVENSHTEIN_DISTANCE() for the Levenshtein (or Damerau-Levenshtein) algorithm.

I’d also like to see consistency regarding abbreviations. For example, I wish EOMONTH() was END_OF_MONTH(). Did we really need to save 3 characters in the same version as other functions with really long names were shipped? I’m told it was named after an old Excel function, but is that really how we want new language features designed?

Also, while we’re at it – why is there no START_OF_MONTH()?! And we had tinyint, smallint, int, and bigint, but then we got smalldatetime, datetime, and datetime2.

Then there’s SYSDATETIME(), SYSDATETIMEOFFSET(), and SYSUTCDATETIME(). You would have guessed the new option for getting just the date would be SYSDATE() right? No! It’s CURRENT_DATE. We still don’t have SYSUTCDATE(), either.

We don’t need to confuse users like this.

Enumerations in SQL Server

Enumerations are a static ordered set of values. For example, I might have the need to store a column called NextAction, with the allowable values being:

In T-SQL today, there are two options at our disposal. First, we could define a simple table called Actions, where we have one column called Action, add those four rows, then define a foreign key from the NextAction column to the Actions.Action column. This would work.

However, if we wanted them to appear in the order above (Open/Process/Close/Delete), we’d need to have another column – perhaps an identity or sequence – in the Actions table to specify that order. Additionally, to limit the range of allowed values in the NextAction column, you could add a CHECK constraint.

There are many scenarios where I just wish we could use enumerations instead. After all, PostgreSQL supports them already. The SQL Server equivalent could look like this, perhaps:

CREATE TYPE Action
AS ENUM('Open', 'Process', 'Close', 'Delete');

CREATE TABLE dbo.Processes
(
    ...
    LastAction Action,
    NextAction Action,
    ...
);

I’d also like to use them not just for simple ordered lists but, instead, as a set of enumerated constants. For example:

CREATE TYPE RequiredAction AS ENUM CONSTANT int
(
    Open = 4279,
    Process = 2327,
    Close = 2232,
    Delete = 2423
);

This would allow me to write code like WHERE NextAction = RequiredAction.Close;, which would work really well with IntelliSense – just like it does in C# and other languages.

It could also be used to provide every constant required: WHERE AdditionalStatus = CONSTANTS.ExtendedStayStatus…

…and extended to be used for database and server constants: OR LastStatus = DATABASE_CONSTANTS.FinalStayStatus

Code libraries in SQL Server

One of the free resources I provide is SDU Tools. It’s a library of functions, views, procedures, etc, that you can add to a database. That’s OK, but I really wish you didn’t need to add them to every database. I should be able to create a library that’s added to the server and can then be used from any database. More importantly, I wish I could create functions that work, and perform at the same speed as, the ones built into T-SQL.

There are so many things we can’t do in it when we build functions. We can’t handle overloaded and optional parameter types properly, for example, and we can’t have dynamic output data types like the recently added date-related functions DATETRUNC() and DATE_BUCKET(). And again, note the inconsistency in their names.

These are concepts that already exist in other database engines. SQL Server really needs them, too.

What else I’d like to see changed (or added) to SQL Server

Here’s what else I’d like to see changed, or added, to SQL Server.

DROP DATABASE IF EXISTS

DROP DATABASE IF EXISTS is currently useless as it fails if anyone is connected to the database. The suggested option for changing the database to single user first is not reliable, as it has to be done from master. Ultimately, it needs to have a WITH ROLLBACK IMMEDIATE option added to it.

ALTER TABLE DROP COLUMN

ALTER TABLE DROP COLUMN needs an option to drop any default constraint on the column. Other engines like PostgreSQL don’t allow you to name default constraints, so don’t have this issue. In T-SQL, you need to know the name of the constraint to drop it.

Ideally, though, default constraints shouldn’t have an exposed name in the first place. Encouraging users to use a system-generated name – and then requiring them to know the name before you can drop a column – just doesn’t make sense.

Arrays

When I first started using SQL Server in 1992, I immediately missed having arrays. The argument always was that they were a non-relational concept, but there are many scenarios where they make sense.

File I/O (input/output)

In his wish list, Edward made a good point about working with other file types, particularly compressed file formats like parquet. This has always been a weakness in SQL Server, but it’s not just file input. If you want to output a CSV or parquet file, it should be trivial – and in other database engines, it is. But not in SQL Server.

On a related note, far too many commands in T-SQL (like OPENROWSET()) don’t take variables – they require literal values. As a result, we end up writing a lot of dynamic code – making our T-SQL code even messier than it should be. This needs to be fixed. The options for file input/output should work well for both local file systems (on-premises), and cloud-based storage systems (both on-premises and cloud-based SQL).

UTF-8

I’m not a fan of how UTF-8 has been implemented within SQL Server. The biggest issue, for me, is defining columns. If I say VARCHAR(10) with UTF-8, I want that to mean up 10 characters. I don’t want it to mean “somewhere between 2 and 10 characters” like it currently does (since it’s based on bytes and depends on which characters you happen to store.)

How is this useful to a developer? It’s worth noting that again, PostgreSQL does this sensibly. At least give us the option to say VARCHAR(10 CHARACTERS) or similar, given we probably can’t fix the current implementation.

STRING_SPLIT

The SQL Server team have made several attempts at STRING_SPLIT, but it’s still not where I want it to be. For example, the delimiter says it needs to be a single character. Sometimes I want to use an empty delimiter as I want to return all the characters in the string one-by-one. Other times, I want to handle multi-character delimiters like double-pipes ( ‘||’ ), as that’s what’s in the incoming data.

Shortcut operators

It was great to see IS DISTINCT FROM and IS NOT DISTINCT FROM added to SQL Server, but I really wish they had shortcut operators like WHERE SomeValue == @ProvidedValue and == meant "IS NOT DISTINCT FROM".

The negative form of this is the most commonly used, and we did get << and >> for LEFT_SHIFT and RIGHT_SHIFT, which of course are welcome but probably won’t be used as often.

Natural joins

If we’re just joining tables using declared foreign keys, we should be able to just ask for a natural join that follows the keys. We shouldn’t need to spell it all out in ON clauses. Bonus points if we could omit interim tables like we can in DAX.

Upcoming possibilities (other SQL Server suggestions)

For some excellent coverage of suggested SQL changes from the PostgreSQL BMA meeting, which took place June 2026 in Stockholm, Sweden, I’d suggest reading this article.

From that article, I think the following are worth considering:

QUALIFY
We use WHERE after FROM, and HAVING after GROUP BY. QUALIFY applies a filter at the window function level.

INSERT BY NAME
This suggestion allows matching up column lists in INSERT statements with returned SELECT values based on the names of the columns rather than position.

SELECT LIST EXCLUDE
There’s a common requirement to select all the columns in a table except for one or two. An EXCLUDE clause can do just that. But the suggestion here is to go further, allowing REPLACE and RENAME clauses as well. Interesting.

JOIN TO ONE
This suggestion is to add a TO ONE clause to a JOIN. The best use case for me would be where I have a LEFT OUTER JOIN and instead of matching zero or more rows, I want to only match zero or one row. If more than one is matched, then an error is thrown. Also interesting.

Summary

Back in 2014, I remember asking a product group member what was new in T-SQL, and they responded by asking me why I’d even want any enhancements. He thought that “T-SQL is done”, as though it was complete.

I’m so glad that, now, the SQL Server team isn’t thinking that way. They’ve started putting in real effort in this area, and it’s good to see. There’s a lot to do.

The post What’s missing in T-SQL? My wish list of features that developers actually need in SQL Server appeared first on Simple Talk.

Learn T-SQL With Erik: Aligning Queries and Indexes Part 3

Chapters

• 00:00:00 – Introduction
• 00:02:15 – Index Alignment and Design
• 00:04:33 – Query Tuning Magic
• 00:06:54 – Parameter Sniffing Issue
• 00:09:40 – Execution Plan Variations
• 00:11:55 – Relational Columns and Aliases
• 00:14:28 – Optimizing Queries
• 00:17:14 – Memory Grants and Indexes

Full Transcript

Erik Darling here with Darling Data, and we’re going to continue our endeavors to learn T-SQL, and in our T-SQL learning endeavors, we are going to continue looking at how we can better align our queries and our indexes. This video is actually one of my favorite demos. It’s a fun one, and it’s a good sort of mental exercise when you’re tuning your own queries and perhaps wondering why SQL Server sometimes uses your indexes and sometimes it doesn’t. So, down in the video description, you will see all sorts of helpful links. Of course, because I am a helpful human being, arguably one of the most helpful human beings to have ever walked the face of the planet.

And humble to boot. And down in the links, you will find a way to buy the full course content. All of the material that you’re seeing around the index align and design stuff is part of my Learn T-SQL with Erik course. There’s a link down in the video description where you can save a hundred bucks off the course if you buy it from there.

You can also do other things that would make me happy, like you could hire me for consulting. I can bring that up. I can bring the same query tuning magic, magic energy to your SQL Servers.

You can choose to support this YouTube channel if you feel like the content is helping you in some way that is worth money. A few is four smackaroos a month. It’s USD.

That’s American for dollars. You can do that. And you can, of course, ask me office hours questions. And if you happen to just like what I’m doing, like what I get up to over here, please do like, subscribe.

And tell every single one of your friends, whether they use SQL Server or not. Perhaps they will find other reasons to come to this channel and watch me. Maybe they’ll just be casually entertained by the shape of my head.

You never can tell. If you are in the market for free SQL Server performance monitoring, my free monitoring tool, totally open source, no sign up, no weird telemetry. Just all the stuff that you would care about monitoring if you want to figure out why SQL Server performance maybe is good, bad, ugly, somewhere in between.

Got a cool knock style dashboard. If you want to just make like just do a little sanity check, make sure all your servers are up and running and all that good stuff. And if you’re a fan of today’s robot companions, I don’t know, maybe before the prices go up, you can also use those things to do some built in MCP stuff.

And you can do some MCP server analysis of your performance data. And it’s isolated to just your performance data. It’s not going out and running weird queries on your SQL Server to look at stuff.

So, coming up in, wow, that is like, it’s like, I mean like 10 days away. I will be at Data Saturday Croatia, June 12th and 13th. And I have an advanced T-SQL pre-con there.

You can, you know, jump in there. Learn about that. Learn about T-SQL for me live and in person. And if you show up to that, you get free access to the Learn T-SQL material that we’re covering today in this video.

I will also be at PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So, you know, not quite on my birthday, but pretty close there. And, but for now, it is June and we are Juning about going crazy from the heat.

I mean, I’m not going crazy from the heat. It was like 60 degrees in New York today or something. I don’t know, whatever.

Anyway, let’s go learn something about SQL over here. Ah, that’s Management Studio. Now we got it. So, like I said, this is one of my favorite demos. I’ve got a few different things that I build off of this one-store procedure.

But right now, we have got this computed column that I’ve added to the post table. And we’ve got this index that I’ve added, which I know there’s a little red squiggle here, but I promise you this index has been created and the active period column is there.

So, anyway, I don’t know why that was highlighted. We’ve got this store procedure called top lookup. And this store procedure is doing something.

I mean, okay, so like, look, we’re doing select star here. I’ve talked in other videos about how select star is just a convenient shortcut. For a lot of this stuff.

You could list out all the columns if you wanted, and it would still be the same thing. Or you could even just put a few columns that aren’t in your index in here. And you would run into the same potential plan switcheroo.

Because no matter how many columns your nonclustered index is missing, SQL Server has to do lookups to find those columns from the clustered index. And it costs one column the same way that it costs 150.

1,024 columns. It doesn’t matter how many columns are outside of your index. The key lookup costs the same.

Regardless of the number of columns or the data types of those columns. So, I’ve got a hint on this query. And that hint is to set it to compat level 140. It’s not because, I’ll explain why.

The parameter sensitive plan optimization that came around in compat level 150 just makes this demo really confusing. And I’ll talk about why in a moment.

But for now, just understand that this procedure takes two parameters. One of them is post type ID and the other one is gap. The gap parameter works off the computed column.

This date diff year, creation date, last activity date. That works off the computed column and index that I created up here. So, let’s turn on actual execution plans.

Let’s feel fully actualized here. And the reason why I have the compat level set is because it sort of makes this demo it just muddies it up a bunch.

What happens is the parameter sensitive plan optimization kicks in. And you get one plan for the most common value. One plan for the least common value.

And then all of the post type IDs in the middle share the same plan. And it’s just really a bad situation. And it just makes it harder to explain the point of what I’m doing here.

So, to just sort of visualize that breakout if I run this query and we get these Oh, I scrolled the wrong way. We get these results back. Go away SQL prompt.

Come on. Be a pal here. We look in here. So, just to sort of explain a little bit. You get the three plan variants. Post type ID 2 at 11 million rows gets plan variant 3.

Post type ID 8 at 2 rows gets plan variant 1. And all of the post type IDs in the middle ranging from 4 rows to 6 million rows get the second plan variant.

This is not a good situation. And looking at these numbers you may start to understand why that particular feature muddies this demo up quite a bit. So, we’re not going to do that.

Now, the first thing we’re going to look at is the 9-year gap. So, the 9-year gap is very uncommon. Gap ID 9, post type ID 1. And we run this.

This returns very quickly. We get this plan here. It is a parallel nested loops plan with a key lookup to fetch all of the columns that we care about from the post table after we seek both to the post type ID that we care about and the gap years parameter that we care about.

The key lookup down here, there’s no predicate on that. If I move my big head out of the way, there’s no predicate on that up here. It is just outputting columns, right? But it’s outputting all the columns in the post table, which maybe isn’t a lot of fun.

But this takes 28 milliseconds to run and it gets 500 rows and everyone’s pretty happy. If we run this for a gap of 0 years, most posts do not have 9 years of time between when they were first posted and when they were last edited.

A gap of 0 years is much, much more common. And this query runs for about 4 1⁄2 seconds here. Excuse me.

Very dry. 4 1⁄2 seconds. And a lot of the time is spent in this sort that spills because the memory grant that we assign to the initial execution of this procedure maintains for this execution.

So that takes some time. Now, if we recompile this thing and we run this in reverse and we ask for a gap of 0 years first, this does a bit better, right?

About twice as fast plus another 500 milliseconds faster for a gap of 0 years. The plan changes quite substantially, though.

I mean, we still have a plan for a parallel nested loops join plan. But notice that we fully scan the clustered index over here.

And then we have this strange filter operator over here. And the filter operator is on both the post type ID and the gap parameter. Part of the reason for this is because the computed column that I added to the post table was not persisted.

SQL Server expands the definition of the persisted computed column. And if I were to change that to a persisted computed column, we could avoid this part.

But we would still get the same basic plan shape. Now, I think probably the biggest downside of this late filter operator, and if I’ve said it once, I’ve said it a million times, always be suspicious of a filter operator in a query plan, is that we have to fully scan all 17 million rows from the post table, drag them across the couple compute scalars, and then filter stuff out over here.

Okay? What really sucks is that reusing that plan for the gap of nine years, in other words, the very uncommon one, this used to take 28 milliseconds, and now this thing takes almost a second.

Right? And it’s obviously the same plan gets reused. Whenever I’m talking about parameter sensitivity problems, a lot of people get this big idea in their head. It’s like, well, why not just use the big plan for everything?

The big plan for small amounts of data is often somewhat, I mean, not surprising to me, but surprising, not surprising to other people who see this stuff, not a good sort of trade-off there.

It’s not a good fit. So we have, you know, essentially two execution plans. Neither one is a particularly good fit for the amount of data that we’re dealing with. So the mental exercise that I like to put people through when we’re doing this is to mentally in your head separate informational columns from relational columns.

Right? And what we’re going to do in the query below is we’re going to write a self join between the post table and itself. Right?

One alias will be responsible for the relational activities in our query, the join, the where clause, the order by, stuff like that. And the other alias will just be responsible for providing the select list.

Right? And if you can start mentally separating the duties of your queries and the columns in your queries between purely informational stuff that’s only in the select list and columns that are used for relational activities in your queries, you can do a lot of cool query tuning stuff.

This is just one of them. Right? So let me create this and then we will talk through the code just a little bit up here.

So I have the post table joined to itself. ID is the clustered primary key in the post table so we can get away with this. It is a unique column so doing this is very, very easy.

And from P1, right, that’s this one, this is all of the relational stuff. Right? P1 is there.

P1 is there. P1 is the where clause. P1 is further down here in the where clause and in the order by clause down here. The only place we reference P2 is up here in the select list.

Right? This is our star. Right? So if we run this query now, both 9 and 0 will be fast doing this. Right?

The 1 for 9 got even faster. The 1 for 0, instead of taking 4.5 seconds, takes 1.2 seconds. Right? So we’ve kind of come to a little bit better of a situation here.

Now, we still have this sort over here and this sort still spills. Right? So, you know, it does slow this query down a bit but we’re not at 4.5 seconds of sort of crappy spilling.

We’re at one point, actually it’s about 900 milliseconds of crappy spilling there. So this is a lot more tolerable. If we reverse things, just like we did before and we do 0 first and then 9, the plan actually gets a little bit better.

So in this case, the first query, not only does the sort spill a bit less because we get the memory grant for the larger amount of data that comes out of the POST table, but we didn’t finish in about 200 milliseconds.

Now we also get this parallel nested loops plan. And the same thing goes for this gap down here. So this is a pretty reasonable rewrite to get better performance out of both queries.

You might start playing some other tricks with this query if you really were getting, if you really wanted to like optimize, optimize this, you could even say like option use hint optimize for gap equals 0.

So you keep getting that plan for the 0 value and the gap parameter. And talk through some of the important differences between the original and the rewrite.

I’m going to put both of them into the same store procedure. And then I’m going to run them for the gap of nine years. And the thing that I just want to talk about here a little bit is the plan shape.

So what happens in the original query where we do the lookup is we find 4,500 rows here and we do 4,500 nested loops to do the key lookup here.

Key lookup plan, key lookups and query plans are very, very tightly coupled operations. SQL Server cannot move these around, right? SQL Server has to do this stuff all in one place at the same time.

It can put a sort before the nested loops join, it just doesn’t here. And then after we find all that data, then we do our sort. And this is where we start to sort of narrow stuff down for the top, right?

Down here in the one that we rewrote in order to, what do you call it, do the self join.

We still have the same seek, but notice that the post table immediately joins to the users table here. And what’s nice is that this sort cuts down the results to just about all the ones that we need for the top very much earlier on, right?

And then after we figure out relationally what rows we care about maintaining for this query to get the top 500, we do our nested loop.

We do our nested loops for only those 500 rows back to the post table here. And again, there is about a 20 millisecond difference between these. This isn’t big night and day performance tuning stuff, but you do see a general improvement.

And what I think is nice too is you see that general 20 millisecond improvement with the serial execution plan. In other words, you don’t need to get a parallel plan here in order for this to be competitively fast, right?

This plan up here, it runs, goes parallel, gets a DOP eight query plan. And this is one of those like, oh, well, you’re using a bunch of extra resources, but your query’s not getting faster, right?

It’s kind of a weird thing. Anyway, when you’re working with queries, especially parameter sensitive ones, one of the biggest differences that you will see in those queries aside from stuff like the type of join and the size of the memory grants and all that other stuff is going to be the sort of like index usage, right?

And if you can’t get SQL Server to reliably use your narrower nonclustered indexes because you are selecting columns that are outside of them and SQL Server now has this clustered index for its key lookup choice, it might just totally be worth rewriting the query to do a self join so that you can get all of your relational work done that narrows the rows down to just the ones you care about.

And then do the self join back to get the stuff later because the key lookup, again, very tightly coupled. When you write a self join, that tight coupling is sort of removed a bit.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk some more about very similar techniques. All right, thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

The post Learn T-SQL With Erik: Aligning Queries and Indexes Part 3 appeared first on Darling Data.

Birgitta Böckeler now reports on her recent experiences trying local LLMs for coding. She compares them using two standard tasks, and tries out the most promising model for day-to-day use.

more…

I’ve been thinking a lot about the future of the firm in an AI-driven economy.

This transition is different than any previous platform shift. In the past, we used digital systems to enhance human capital. This is the first time we can create a real cognitive loop between people and digital systems. That is a mind-bender, because it changes how we even conceptualize work inside an enterprise.

What is at stake is not some digital tool or system and its use, but how organizations continue to learn, build IP, differentiate, and thrive in a world where AI models can continuously absorb the expertise of humans and organizations and commoditize it.

Co-authored by
AtilGurcan

In our previous post, we mapped where exposure can exist when organizations deploy Microsoft 365 Copilot and grouped those risks into two layers. Layer 1 focused on who can access Copilot—the identity and device conditions that determine whether a user can reach the service.  

If those identity and device conditions are weak, exposure may extend beyond a single workload across the user’s entire Microsoft 365 data surface. This post shifts from mapping risk to reducing it. 

Each of the six Layer 1 risks (R1–R6) is governed primarily by the identity and endpoints pillars of Zero Trust. The good news: customers with Microsoft 365 E5 already own the controls required to address these risks, including Microsoft Entra ID, Conditional Access, Microsoft Intune, and Microsoft Defender. The next job is configuring and scoping them deliberately before scaling deployment.

How to read this post 

Each section recaps one risk, describes the mitigation, names the Microsoft control that delivers it and indicates where to capture the supporting screenshot. These controls are additive. Conditional Access ties identity and device signals together, so several mitigations reinforce one another. 

R1—Unmanaged identity access 

Pillar: Identity 

Because Copilot operates across the full scope of a user's permissions, a compromised, shared, or orphaned account exposes far more than it would have before. Mitigation starts with disciplined identity lifecycle management so that accounts exist only when they should and belong to real users. 

What to do: 

• Automate joiner, mover, and leaver processes with Microsoft Entra Lifecycle Workflows so accounts are provisioned, re-scoped, and disabled on schedule rather than manually. 
• Run recurring access reviews on Copilot-licensed groups to identify stale or unnecessary accounts.
• Eliminate shared and generic accounts; require an individual, attributable identity for every Copilot user.
• Block or remove dormant accounts and monitor sign-in activity for privileged identities. 

The following examples show lifecycle workflows and access review configuration: 

Figure: Lifecycle Workflows—leaver workflow showing account-disable tasks

Figure: Access reviews—review scoped to the Copilot users group

R2—Weak or absent multi-factor authentication 

Pillar: Identity 

If MFA is inconsistent—or legacy authentication bypasses modern sign-in controls—an attacker may need only a stolen password to open a Copilot session that synthesizes data across services. Closing this gap means enforcing strong authentication consistently and shutting down the protocols that route around it. 

What to do: 

• Require MFA for all users with a Conditional Access policy; use Microsoft-managed policies as a baseline, then tighten. 
• Move to phishing-resistant authentication methods, such as FIDO2 security keys, Windows Hello for Business, or certificate-based authentication.
• Block legacy authentication protocols that don't support modern MFA.
• Turn on Microsoft Authenticator number matching and additional context to help resist MFA fatigue attacks. 

Figure: Conditional Access—require multifactor authentication policy

Figure: Authentication methods—phishing-resistant methods enabled

R3—Unmanaged or noncompliant devices 

Pillar: Endpoints 

Users can reach Copilot from any device where they can authenticate. Without endpoint protection, encryption, and compliance evaluation, sessions and their outputs can be stored or exfiltrated from untrusted devices. Mitigation pairs device compliance in Intune with a Conditional Access policy that enforces it. 

What to do: 

• Define Intune compliance policies that require disk encryption, minimum OS versions, endpoint protection (EDR/Defender), and no jailbreak or root status. 
• Use a Conditional Access grant control that requires devices to be marked compliant, or hybrid Microsoft Entra joined before accessing Microsoft 365 and Copilot.
• Feed Microsoft Defender for Endpoint device risk signals into compliance evaluation, so high-risk devices fall out of compliance automatically. 

Figure: Intune compliance policy—encryption, minimum OS, and Defender requirements

Figure: Conditional Access—require device to be marked as compliant

R4—License sprawl without role-based scoping 

Pillar: Identity and apps 

Broad pilot enrollment—licensing whole departments or floors—is itself a risk factor because it grants Copilot access to both well-governed and minimally governed users without an access review. Mitigation makes licensing deliberate: a reviewed group assigned through policy after each member's access is checked. 

What to do:

• Assign Copilot through group-based licensing tied to a named, security-reviewed pilot group—not by department or location. 
• Run an access review on each pilot member's permission posture and role sensitivity before granting the license.
• Phase rollout in cohorts and consider Restricted SharePoint Search during the pilot to limit the grounding surface. 

Figure: Microsoft 365 admin center—Copilot license assignment count

R5—Missing real-time risk evaluation at sign-in 

Pillar: Identity and endpoints 

Static controls grant or deny access once; they don't react to a session that turns risky. If Conditional Access doesn’t evaluate sign-in and user risk signals—impossible travel, anomalous tokens, Identity Protection alerts—a high-risk session can still reach Copilot before anyone responds. Mitigation makes access decisions risk-aware in real time. 

What to do:

• Turn on Microsoft Entra ID Protection (included in E5) to generate user risk and sign-in-risk signals. 
• Create risk-based Conditional Access policies: require MFA or a secure password change on elevated risk, and block on high risk.
• Add token protection to bind sign-in sessions to the device and reduce token replay exposure. 

Figure: Conditional Access—user risk condition set

Figure: Identity Protection—risky sign-ins dashboard

Figure: Conditional Access—require token protection session control

R6—App protection gap on mobile devices 

Pillar: Endpoints and apps 

On personal phones that aren't enrolled in MDM, organizations still need to govern the app. Without an application protection policy, users can copy Copilot output into unmanaged apps, and data on a lost device can't be wiped. Mobile Application Management (MAM) protects corporate data inside the app without managing the whole device. 

What to do:

• Deploy Intune App Protection Policies (MAM) for iOS and Android: require an app PIN, encrypt app data, and allow selective wipe of organizational data. 
• Restrict data egress: block copy/paste and 'Save As' to unmanaged locations, and block screen capture where the platform supports it (Android).
• Pair with a Conditional Access grant requiring an approved client app and an app protection policy for mobile access. 

Figure: Intune app protection policies—iOS and Android policy list

Figure: App protection policy—data protection, block copy/paste, and “Save As”

Figure: Conditional Access—require approved client app and require App Protection Policy

Layer 1 mitigations at a glance 

Each Layer 1 risk maps to a primary control and the Microsoft tool that delivers it. Conditional Access recurs because it is where identity and device signals are enforced together. 

Securing Copilot access isn't only about who can sign in—it's about validating the devices, conditions, and access patterns behind every session. The six controls above close Layer 1 gaps before risky access patterns scale across the organization. 

Governing Microsoft 365 Copilot risk: Next steps 

Microsoft 365 Copilot security starts before a prompt is ever entered. Identity, device, and session controls help verify that the right people are accessing Copilot from trusted devices under the right conditions. Closing Layer 1 gaps reduces the likelihood that compromised identities, risky sign-ins, or unmanaged devices can access organizational data. 

Controlling who can access Copilot is the first step. The next challenge is governing what Copilot can access once a user is authenticated. Strong access controls reduce the chances that the wrong person reaches Copilot. Layer 2 focuses on a different question: if the right person signs in, what information can they discover, summarize, and use? 

In the next post of this series, we'll shift from access controls to data controls and explore how organizations can reduce Layer 2 risk through SharePoint and OneDrive permissions management, sensitivity labels, data loss prevention (DLP), connector governance, and auditing capabilities. 

Before expanding your Copilot deployment, review the six Layer 1 controls covered in this article and identify any gaps in your identity, device, and access policies. You can also use the Zero Trust Workshop to assess your current security posture and prioritize remediation efforts. 

When you're ready, continue to Post 3 in this series: Governing Microsoft 365 Copilot data risk to examine how data governance controls help limit exposure after authentication and strengthen secure Copilot adoption. 

We’re excited to introduce a major upgrade to Copilot in Microsoft Forms - now featuring seamless integration with a Microsoft 365 Copilot chat experience.

This new experience builds on the familiar Copilot interactions users already know from Word, Excel, and PowerPoint, while adding Forms-specific intelligence to help you create, refine, and analyze forms more effectively. Copilot works alongside your form to deliver tailored suggestions and refinements, help configure settings, prepare your form for distribution, and turn responses into clear, actionable insights – all within a chat pane that you know and use across the Microsoft 365 suite.

Using the new Copilot experience in Forms

When you create or open a form or quiz in Microsoft Forms, you’ll see the Copilot icon in the lower-right corner. This Dynamic Action Button works the same way across Microsoft 365 apps - select it to access contextual assistance, such as improving your questions or analyzing results right where you’re working. When you open the chat pane, Copilot is automatically grounded to your form, and able to access specialized Forms features to help you edit, send, and analyze.

Here are some examples of how chat can improve your workflow:

• Review and suggest improvements to your form such as organizational layout, sectioning, missing questions, or settings that may conflict with your form’s purpose - such as enabling anonymous response or multiple responses per user.
• Complete bulk edits like replacing a placeholder name with an updated one throughout the form, or changing multiple questions to required or not required.
• Ask about specific drilldowns or summaries you’re seeking from your form results, to help you understand your data and unlock your next action.

What’s new

This new experience merges the benefits of the familiar Copilot chat experience with Forms specialization, building on your feedback and introducing key improvements:

• Smarter suggestions & refinements: Get targeted recommendations to improve your form’s structure, clarity, and effectiveness. Copilot can also apply refinements directly to the form, so you can save time making edits – just describe what you want, and watch Copilot make it happen.
• Deeper analysis: Copilot can now analyze your results in-depth to provide clear insights and actionable takeaways for you and your team. You can even ask follow-up questions to help parse and summarize your data and unlock your next step.
• More settings: Review and update form settings with ease, such as applying custom thank-you messages and close dates, so your form is ready to send. You can also adjust question settings in bulk, such as making questions required.
• Open-ended chat: Copilot chat gives you access to a broad world of capabilities, whether you’re seeking inspiration on survey topics or consulting on how to configure your form – the possibilities are broad with Copilot at your fingertips.
• Basic branching: Apply basic branching logic directly through the agent. (Note that some complex scenarios are not yet supported, and you should continue to review your branching logic prior to sending your forms.)

And some features that you use and love today are not changing at all – such as Draft with Copilot and on-canvas Copilot features like Rewrite, Add question, Answer explanations for quizzes, and more. These will continue to help you work with your form directly in the canvas.

Availability

The new Copilot in Forms is rolling out to worldwide users now, and will be available to users with Microsoft 365 commercial Copilot licenses. Consumer Copilot subscribers will continue to see the previous Copilot experience in Forms for now, which includes a Copilot refinement bar that supports AI-credit-based Copilot licenses. To try it for yourself, go to Forms.