September 18, 2009 | SQL Server

Connect Digest : 2009-09-18

It sounds funny, but I'm running out of things to say in this intro.  I wonder if I should introduce the list the way David Letterman is introduced differently on every episode of his late night TV show?  Anyway, one of these items was suggested to me, and one I filed myself, but the rest I just found on my own by browsing new items each day.  If you have a Connect item you think deserves more exposure than it's getting, please let me know!


Mark our own objects as deprecated

MVP Steve Jones filed an issue recently where he suggests allowing us to mark our own user objects as deprecated.  This would be a much easier way than SQL Audit to determine which objects we *think* are no longer being used, actually are still being used.

#490420 : Allow User objects to be deprecated


Waiting for a deadlock to happen

This issue asks for trace to have the ability to automatically enable the deadlock graph event in the default trace once a deadlock has happened.  I think this makes a lot of sense, as it will allow people to have information without going back to re-configure a trace or add trace flags and then site and wait for another deadlock to occur.


Determining Licensing

When I initially saw this Connect item, I assumed the user must have installed SQL Server from the MSDN media.  This was the case with SQL Server 2000, but in SQL Server 2005 and above, the registry is no longer used to indicate licensing type (see this blog post), so several SERVERPROPERTY() properties are now useless.  So, there seems to be no easy way to determine how SQL Server was installed, and even if you do know, setting the properties in the registry (as suggested in the above blog post) does not seem to work for a lot of users.  Also, this change in behavior does not seem to be documented in Books Online.

#295301 : SERVERPROPERTY ( 'licensetype') returns disabled on any SQL Server 2005 Install


More reliability issues in the DMVs 

Earlier this week, Adam Machanic filed this issue, where he discovered that under MARS the request_id in sys.dm_os_tasks can be inaccurate.  This makes it difficult to correlate activity when troubleshooting an issue or observing performance.  And this is another case (just like with transaction count and database id) where we can go back and rely on the deprecated and to-be-removed sys.sysprocesses view, as the data there is correct.

#490178 : request_id in sys.dm_os_tasks wrong when using MARS


SELECT INTO … this time with feeling

Jamie Thomson suggests that SELECT INTO be enhanced to create the relevant indexes on the destination table.  While this works when you are simply doing SELECT * INTO newtable FROM oldtable;, what about when you are using a view, multiple tables, TVF, external source, etc?  What if you want to specify compression, filegroup or partition scheme at create time?  I agree with David Portas' suggestion that we add standard DDL like CREATE TABLE AS where full object definition is supported *and* it can "clone" the data from another table.

#490142 : New version of SELECT INTO that supports indexes


Function keys should learn how to toggle

F8 used to show and hide the Object Explorer; now it will show it if it is not already visible, but it will not hide it if you press it again.  A similar issue occurs with F4.  As I describe in this item, I often hit F4 by mistake instead of F5, and I'd like an easy way to reverse that action (by clicking F4 again) instead of having to take my hand off the keyboard, grab my mouse, find the little x on the properties panel, and close it manually.

#489927 : SSMS : F4 shows Properties panel, but can't hide it


SSMS parser issue

This is an interesting bug where you suddenly need to be careful about what you place after a single-line comment.  Apparently the sequence –(* will trip the parser up and cause a Msg 102, "Incorrect syntax near" error message.

#489130 : usage of –(* in SSMS returns error