Debugging T-SQL in SQL Server v.Next (Denali) : You can make a difference!
This afternoon, Adam Machanic (blog | twitter) reminded me how the current T-SQL debugger in Management Studio is ineffective. Sure, there are some enhancements coming in Denali (I mentioned them briefly yesterday), but there are some real key elements missing. One of the most glaring omissions is the ability to see the contents of #temp tables and @table variables during a debugging session. For stored procedures that can take a long time to run, it is quite cumbersome to edit the procedure to dump the contents of the #temp table as a resultset, which can obviously interfere with the operation of the procedure (especially if applications currently rely on its interface). As Adam eloquently stated, "All I want right now is a debugger that helps with real challenges. Scalar variables are not enough."
Hear, hear. This is one of the reasons I have yet to step back from my curmudgeony debugging methods: PRINT, RAISERROR, and (since 2005) TRY/CATCH. I don't come from an OOP background, but I do understand why many people value a more formal method of debugging.
So, how can you help? Well, I know some of you do not believe in Connect, but for those that do – this issue of not being able to see into #temp tables and @table variables is covered by no fewer than four Connect items. I am asking that you go and vote for all four of them, because Connect doesn't have a good way for us peons to close duplicates and focus on a single item. (I'd love to pick just one and tell you to vote there, but with my luck, Microsoft will choose a different one to keep alive, if they ever get around to consolidating.)
Connect #623353 : View contents of table variables and temp tables in SSMS debugger
Connect #454870 : No way to see contents of table valued variable in debugger
Connect #363054 : temp table and table variable visibility through debug
So please, if you value debugging and you want to see better improvements than just being able to watch scalar variables, vote on these items. And if someone tells you they're going to create a new Connect item in a similar vein, please tell them to search first. Having multiple items covering the same issue just spreads out the votes and makes it less likely that the issue will be tackled. Thanks!
Thanks Aaron for posting this. I just voted for all of these Connect items. This is something that really hamstrings our developer.
Yes,this is one of the essential requirement,
Programmers frequently asks this Questions to SQL Developers is there any way to debug for Temp Table/Table Variable Values.
Ashton: Temp tables and table variables are stored in the same ways. Table variables are not stored "in memory"; that's a SQL Server urban legend. Either can be viewed with DBCC PAGE, but it would be incredibly slow, would require sysadmin privileges, and wouldn't allow for ad hoc queries against the data. So if that's how the Apex product works it sounds like it has some serious deficiencies. I am hoping that Microsoft will create proper hooks so that this can be done the right way.
The ApexSQL debugger has allowed temp table content viewing for quite some time now, and has quite a few other features such as conditional breakpoints and call stack viewing, execution profiling (line hit counts, durations), etc. Very nice SSMS add-in. I consider this yet another area where Microsoft could let the third parties have a market and focus on other things that only Microsoft can do.
http://www.apexsql.com/sql_tools_debug.aspx
ApexSQL Debug will actually display the contents of a temp table during debugging (or at least it did originally when I wrote it 🙂
Displaying table variables is not possible without having access to the memory space where the data resides (accessing a temp table is fairly easy using DBCC Page to view the actual contents, table variables aren't store the same way, of if someone can tell me how to access table var data via DBCC Page then we can get somewhere…)
Thank you, these are all valid points, we do need a better support in coping with daily endeavors, voted for each and twitted asking to support.