Connect digest : 2009-08-01

I had a fairly busy week, so I did not get to spend a lot of time rounding up old, nearly-forgotten Connect items.  But I do believe I came up with at least one or two that you will want to vote or comment on…


Getting default parameters from catalog views

Currently the catalog view sys.parameters has a column called "has_default_value" which, logically, you would expect to be 1 (true) for stored procedure parameters that have a default value, and 0 (false) for parameters that do not.  Sadly, this column is only accurate when the parent object is a CLR object.  There is nothing in the catalog views, INFORMATION_SCHEMA views, or metadata functions that will tell you whether or not a parameter has a default value, never mind what the default value is.  You are stuck with parsing the text of the object yourself, as I once tried to demonstrate but never truly finished.  Since we know that Management Studio can show default / no default in Object Explorer, we can guess that they must be parsing the object text as well, but this parsing is buried somewhere in the SSMS code.  Why can't this parsing be done by the code that actually saves the stored procedure?  Then the catalog views could be populated correctly and accurately whenever a procedure is created or altered – ideally with both the flag that indicates whether there is a default, and the actual default value itself.  While you can always get this information by visually inspecting the code of the object, it would be great to be able to script these out as well.  Nobody wants to build a data dictionary or interface document by hand and, as you can imagine, building code that automatically parses the object text would not be very fun either.


Better visual cues for high-cost query plan nodes

User "Ewan1" filed this suggestion, requesting that the high-percentage items in a graphical execution plan be highlighted in some way.  He asked for a shortcut key that would cycle through the most expensive operators; my addendum was that a visual cue might be enough.  This would make easier work of investigating plans that force you to scroll (because they are wider/longer than your screen at normal size) or zoom in/out constantly. 

#477390 : Rank cost of graphical execution plan components in SSMS


Horrible experience for users with Large Fonts

Windows offers a "Large Fonts" setting that is useful for the visually impaired as well as those using laptops to give presentations and demos to large audiences.  However many dialogs in SQL Server were clearly not tested with settings other than the default, or were tested but the issues were not deemed important enough.  I complained vehemently about a particular screen in the SQL Server 2008 hotfix installers, and unfortunately I am still the only person who has voted for it.

So please, help motivate them to test all UIs using Windows in Large Fonts mode.  The following item was closed as "Won't Fix" without any explanation at all; I re-opened it last week.  Surely for a case like the above, it would be trivial to change the location of the divider and, instead of wasting all that gray space, make more room for the panel that has information that we might actually want to see before blindly clicking Next.


Allow "new query" to behave more like a browser link

Since I often end up with a very busy SSMS window, sometimes I would rather start fresh with a new instance of SSMS.  While I can currently click on the start menu shortcut and authenticate again, sometimes I feel like I should be able to right-click a database or instance in Object Explorer, and have the option to connect to that database or instance in a brand new SSMS session.  It would launch a new instance of SSMS, automatically connect with the same credentials (or prompt if I haven't saved the password), and open with a new query window.  They don't seem opposed to the idea so far, but they need to know if more people would like this kind of feature.


Copying large resultsets from the grid

Fellow MVP Steve Kass brought attention to the fact that if you have more than 100,000 rows in a grid result in SSMS, if you try to copy, you are never able to paste the results anywhere.  This is just an arbitrary number that is an attempt at preventing resource drainage; however, it is not a very effective or practical one.  You could have 100,000 single-digit values in one column, and not be allowed to copy, whereas if you have 99,999 rows of many wide NVARCHAR columns, the copy will not be prevented (of course it is not guaranteed to be fast, especially on older, less powerful machines).  The worst part of it is, other than the fact that the Paste menu item is simply not available, there is no indication given to the user that his/her copy operation is invalid, and nothing about this issue is documented in Books Online.


Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

1 Response