AzureFeeds

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Advanced

Question:

You are reviewing stored procedures in the master database.

You note both Stored Procedures, and Extended Stored Procedures.

What is the difference between these types of procedures?

Answer:

Stored Procedures are written in T-SQL or SQL CLR and run in the standard user memory space of a SQL Server session.

Extended Stored Procedures are added as DLLs (Dynamic Link Libraries), mostly written in C++. They execute directly in the memory space of the SQL Server process. This makes them potentially far more likely to cause serious errors in SQL Server.

The use of extended stored procedures is deprecated. They should be replaced by using SQL CLR based procedures if the functionality cannot be achieved using T-SQL.

The post SQL Interview #25: Extended Stored Procedures in SQL Server appeared first on The Bit Bucket.