If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. Starting with SQL Server 2005, Query Analyzer and Enterprise Manager had been clubbed together as SQL Server Management Studio (SSMS). Though this single interface has simplified working with SQL Server, one major drawback was, it does not allow you to debug a routine from there. For that purpose you needed Visual Studio (Enterprise and Professional) edition installed, on your development machine, which allowed you to debug a routine. The requirement to install Visual Studio is something that database developers and DBAs would be reluctant to do as it requires additional funds for a Visual Studio license and puts additional pressure on the physical box after installation. Thankfully Microsoft SQL Server team decided to provide this feature in SQL Server 2008 SSMS.
Refer this link to learn more details about it.