Grrrr... I hate bad documentation! or: "An INSERT EXEC statement cannot be nested"

I recently had to extensively use a feature in SQL I seldom used before - inserting the results of an EXEC statement directly into a table. Sounds a reasonable thing to do, right? Accidentally, I had a bunch of stored procedures calling each other, using temporary tables, and finally filling a "final" table. I finished writing the whole thing, and then - BABOOM! - it doesn't work, because "An INSERT EXEC statement cannot be nested". Huh? What?
Why? Why didn't you say so before?

Following is a quote from SQL Server Books Online for "INSERT (Transact-SQL)" :

execute_statement

Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. The SELECT statement cannot contain a CTE.

If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.

execute_statement can be used to execute stored procedures on the same server or a remote server. The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server.

If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.

Did you see any clear mention of this limitation???

SQL Table Variables - Limitations

I've come to really like table variables in SQL. They are a nice and natural development of database programming. And, when used smartly, can greatly improve performance compared to using temporary tables.

Today I've discovered that table variables cannot be truncated. I had no idea...

Anyway, I've found a cool source of more information about table variables, including some stuff that is not or not well documented in Books Online. Check here and here.

When you think of it - it makes sense. As far as I know, table variables are memory constructs whereas temporary tables are kept in tempdb. Truncating a table simply removes the reference of the table's page from the database. However, if the table is only kept in memory and in no actual database - it has no meaning...

SQL Server Execution Plan - Rantings...

Let me say it outloud: SQL Server Execution Plan SUCKS!

In essence, SSEP (I'm too lazy to keep writing the whole name) is supposed to help you analyze how the SQL Engine performed a query (more specifically what optimization decisions were taken) in order to either alter the query and/or the tables to improve performance. As such, SSEP provides you with a nice graphical interface, that shows every element of the execution along with it's relative cost. You can then see more properties for each element, which supposedly will help you better understand it.

SSEP in SQL2005 has gone through very few changes since SQL2000, and it's a pitty. With a small query, SSEP can help you out - you can easily find the most costly element, analyze it and kill the culprit. With long and complicated queries, however, it's a nightmare. The most prominent issue here is USER EXPERIENCE!!! Let me explain:

In most cases, the first thing you do when you run SSEP is to search for the most costly sub-query and in that you look for the most costly query element. When you have a long and complicated query (which sometimes is inevitable), you can find yourself "surfing" the execution plan for many long minutes, just searching for the elements of interests, never being actually certain you got them all. You just keep scrolling up/down left/right and back again, trying to get a grasp of what's going on. There is a zooming feature, but it just isn't enough! There is no easy way to navigate through the data and search for specific elements. I mean - come on, would it have been so difficult to add, for example, a list of elements with the main properties (say object name, cost and physical and logical operation), allowing you to sort it and jump from that list to the relevant element in the execution plan?

Another USER EXPERIENCE issue is that of the data that comes along with each element. Granted, SSEP is aimed at advanced users (usually DBA level), but would it hurt someone to provide some more explanative descriptions?

Lastly, if you really want to improve the USER EXPERIENCE, you should guide the user (without forcing her hand) to find the real problem as fast as possible. For instance, you could use colors for the elements, such that costly operations would stand out (color table scan in red, index seek in green). Note that these colors should also stand out in the list mentioned above. You could even propose some solutions ad-hoc. For instance, if you see a very costly Bookmark (used to link between indexes and actual data in the tables), you can propose to add an index that will include all the fields required, effectively removing the need for a bookmark (because the engine will read the data directly from the index). There are tons of things you could do to make the user's life easier, and help her finish her job faster, so why not?!?