Inquiry About Extended Stored Procedures

System stored procedures are written in plain T-SQL code and stored in the system database.

Extendend stored procedure are written in native C++ code and located in an external library = DLL.

Some XP are supported to be used by user, others not.

General rule: Not documented = not supported.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/general-extended-stored-procedures-transact-sql?view=sql-server-ver16

Extended stored procedures is a very old technology. As Olaf says, you write then in C++ and they are stored in master. This quite inflexible, because typically would write an extended stored procedure to support some need in an application database. Another issue is that extended stored procedures execute in the address space of SQL Server. This means that if you have a rogue pointer (as mistake easily made in a language like C++ the internal memory of SQL Server could be thrashed, leading to that the entire SQL Server process crashes.

With SQL 2005, Microsoft added support for writing stored procedure in managed code, that is .NET languages such C# and VB.Net, also known as CLR, Common Language Runtime. When this was introduced, this was seen as a safe alternative to extended stored procedure, since you can give different permission sets to assemblies. Also, they are stored in the user database, which gives more flexibility. Furthermore, beside stored procedures, you can also write functions in the CLR, which can be easier to plug into other code than stored procedures.

Thus, with the better functionality and better code security of the CLR, there was all reason for Microsoft to deprecate extended stored procedures.

I don't know what drives you to ask this question, but if you are considering writing an extended stored procedure, you should look at the CLR instead.