This week I reported a few bugs – one with sp_helptext, one with Activity Monitor in Management Studio, and one in the new sys.dm_server_services DMV.

#683234 : sp_helptext treats non-system objects as system objects

For this one there is at least one weird case where running sp_helptext against on object prefixed with dbo ignores the dbo and tries to retrieve text for its sys counterpart (in this case, you can't run sp_helptext against a procedure you can create and execute called dbo.sp_hexadecimal; instead, you get error 15197, because the system thinks your object is a system object). I blogged about this situation over the weekend as well.

#683031 : SSMS : Activity Monitor, execution plan implies that .sqlplan file is saved

In testing the new Management Studio add-in we created for SQL Sentry Plan Explorer, it was discovered that the file handle SSMS associates with execution plans launched from within Activity Monitor is not valid – it says the file is in %USERPROFILE%\Documents, but the file is not actually there until you manually save it.

#683275 : sys.dm_server_services has two rows for SQL Server Agent in CTP3

This DMV is new in Denali and 2008 R2 SP1 (though the bug I've reported seems to only affect Denali CTP3). I had previously reported that last_startup_time is always NULL for SQL Server Agent; that bug does affect both 2008 R2 and Denali.

Then I have five others I'd like to highlight, only because they crossed my path for one reason or another. In no particular order:

#668871 : TSQL – Stop the STUFF function treating NULL as the empty string

While they've confirmed that they are not going to fix STUFF, the key here is to put in some comments suggesting why the ANSI-standard OVERLAY function would be a useful addition to T-SQL. (Or maybe create a new suggestion.)

#256665 : Extend OBJECT_DEFINITION to include more objects

I would love to be able to call a function that gives me the script for that object – be it CREATE TABLE, CREATE VIEW, CREATE SYNONYM, what have you. Currently this function only works for "script" objects, such as functions, procedures and views. If you've ever tried to manually/programmatically re-create the type of script Management Studio spits out for a table, you should have no problems voting this one up.

#640236 : The optimizer should considering materialising results of CTEs

Erland filed this one that properly suggests that a CTE *should* be able to be materialized by the optimizer only once, instead of up to (and sometimes more than) as many times as it is referenced in the rest of the query. For some insight into how much performance can be affected when the CTE is materialized only once, see this blog post from SQLCAT. For more viewpoints on the problem, see Connect #218968, Connect #377976 and Connect #483181.

#640863 : Please allow creation of temporary views

I don't think I would have very many uses for this one, but I can see the use case that is being presented. The workaround suggested by Microsoft (to create a view in tempdb, then drop it) is no more useful than creating a permanent view in the current database and then dropping it – the problem is that only one user could do this at a time.

#127219 : CREATE OR REPLACE

I've probably talked about this one before. Because of the complications with checking for existence of a stored procedure, for example, and the fact that a CREATE or ALTER PROCEDURE command must be the only statement in a batch, it is very difficult to determine the correct action without building the procedure body in dynamic SQL. On top of those, if you just blindly DROP and CREATE each time, you lose permissions, dependencies, etc. So the ability to say CREATE OR REPLACE or CREATE OR ALTER instead of performing these checks and executing one script or the other, would be welcome indeed.