Books Online says it best (http://msdn.microsoft.com/en-us/library/ms175200.aspx):
Extended stored procedures let you create your own external routines in a programming language such as C. The extended stored procedures appear to users as regular stored procedures and are executed in the same way. Parameters can be passed to extended
stored procedures, and extended stored procedures can return results and return status.
Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure
API.
Typically, these extended stored procedures are prefixed with xp_, but this is not mandatory. Many DBAs have an aversion to running code directly in SQL Server's memory space, as poorly written code can destablise the SQL Server instance.
CLR integration is the replacement for XPs.
