SQLBlog / Aaron

November CTP : more impressions of SSMS changes

A little over a month ago, I posted some thoughts on a few of the new features in SSMS that I found (not) useful (see /blogs/aaron_bertrand/archive/2007/11/18/november-ctp-initial-impressions-of-ssms-changes.aspx).

In addition to learning that the server prefix information was designed for multi-server queries, and that by RTM we will be able to suppress this information for "local" queries, I realize now that I forgot a couple of changes that are actually very important!

Gone from the table context menu are the options "Open Table" and "Edit."  The former opened the entire table, and also allowed for modification of data — this was a very dangerous thing to do, especially for a large table and/or busy system (I complained about it in Connect #264592).  The latter basically duplicated the functionality of Script Table As > Create To > New Window.

Now they have separated the "Open Table" feature into two separate options:

1. Select Top <n> Rows – this returns a normal query window, with a SELECT query including a TOP clause (but no ORDER BY).  Personally, I think there should be a commented-out ORDER BY with instructions to add at least one column or expression, to help prevent the further spreading of the popular myth that ORDER BY is not necessary to get data back in a predictable order.  Alternatively, they could include an ORDER BY that is generated based on the primary key or, if there is no PK, then the column in sys.columns with MIN(column_id).
2. Edit Top <n> Rows – this returns the grid you are used to, where you can edit existing rows, or add new ones.  Like in the SELECT above, it is not clear WHICH <n> rows you get, and this case is slightly different in that it is more cumbersome to dictate an ORDER BY clause (you need to click on the SQL icon on the toolbar).  In the current CTP, this functionality is still prone to the bug I brought up in Connect #289541, where the WHERE clause is not constructed correctly on UPDATEs for tables with complex primary keys, leading to corruption of data.

 
While the SELECT option is a welcome read-only replacement for Open Table, I am still going to continue to recommend using proper DML statements to modify data, rather than trying to edit them in a grid using the EDIT option.

Anyway, I just wanted to bring up those new context menu items, because I realized they were a shameful omission from my first review.  Hopefully I will have more to share with you soon.