Connect Digest : 2009-07-18
I missed last week because I was having fun up in Canada… mostly without any kind of computer access at all. It was a nice break, but now I'm back in the thick of things again. So this week, I am going to try to beef it up a bit to compensate for last week's missing entry.
===================================
DELETE vs. DROP
I have always found the mix of these two terms a little perplexing, but over time I see more and more people asking questions like, "can I issue DELETE against a stored procedure?" and, "I ran DELETE dbo.table, but dbo.table still exists, why?" Part of the confusion comes from the fact that if you right-click a table in Object Explorer, there is a context menu item that says "Delete" but this is actually a DROP which drops the table, not a DELETE that deletes the data in the table. I find this confusing, but Microsoft's response is that changing this to DROP would make this more confusing. I disagree with this and I hope you do as well. I would hope that they could add a DROP menu option, and if they want to support a DELETE also then they could add an option like "Edit Top N rows" – which should of course give you the opportunity to edit the WHERE and ORDER BY clauses before running. And of course there should never be a "Delete" menu item for a stored procedure; DROP is more appropriate. The tool shouldn't be used as encouragement for people to not bother learning the difference between DROP and DELETE.
#473286 : SSMS : change "Delete" to "DROP" on context menu for objects
===================================
YYYY-DD-MM is for the birds
Fellow MVP Steve Kass has been a huge proponent of deprecating the YYYY-DD-MM format for DATETIME values. He didn't get his wish fulfilled for SQL Server 2008, so now there are all kinds of confusing behaviors with the new DATE/DATETIME2 types (which behave correctly) and the legacy DATETIME/SMALLDATETIME types (which do not). So at the very least, the behavior should be better documented.
#290971 : Deprecate the date literal interpretation 'YYYY-DD-MM'
===================================
Seeing too many databases
I have been complaining for a long time that when you connect to a shared SQL Server with hundreds of databases, and you only have access to a few ( or in a lot of cases one), you still see all of the databases you don't have access to, and have to wait for Object Explorer to enumerate them all. They fixed a few bugs early on which actually caused errors when certain properties were checked against the databases you didn't have access to, which created havoc for at least one ISP I know of when their users started upgrading to the 2008 version of SSMS. And I searched around for quite a while, certain that this was a duplicate, but I couldn't find an existing Connect item that treated the true core of the problem. Thankfully one was created the other day:
===================================
Easily determining path information
Fellow MVP Louis Davidson ("Dr. SQL") makes a good point, that we have very ugly hacks in place for programmatically determining the configuration for the locations of key file types such as data, log, error, etc. He is asking for something relatively simple: a new DMV (or additional columns in an existing DMV) that exposes this information. This is one of the few pieces of data that is easier to get through the SSMS UI than through code.
#474826 : Access to path information about SQL Server
===================================
Statistics enhancements
"Mpls Mike" filed two items that I can agree with. They are quite similar in that they are both asking for more information in the DMV sys.stats and the STATS_DATE() function. One is that there should be columns indicating the date/time when the stats were created and when they were last updated; the other asks for columns that indicate when the stats have become stale or otherwise invalid.
===================================
Can't perform index maintenance on CDC-enabled tables
This is a big one that was unfortunately missed by a lot of us throughout the SQL Server 2008 beta. If you have Change Data Capture enabled on a table, any ALTER statement fails, including simple index maintenance scripts. You can use DBCC DBREINDEX instead, but this is not a perfect answer either, since this command has been deprecated in favor of ALTER INDEX … REBUILD.
#474589 : Alter index on CDC enabled tables
===================================
SQL Server Agent and job history purging
Ranga Narasimhan pointed out that, depending on your retention settings and number of jobs, you can easily lose all of the history for a job. I agree that this can be troublesome on busy servers where you often find out about job failures long after they've failed. The item is marked as Closed (Won't Fix) but if you read the comments you'll see that this is a mistake that they haven't yet corrected.
===================================
Limited typing capabilities in "Edit Top N Rows"
Last week when I was trying to reproduce an end user issue, I came across an annoying behavior in the Open Table Edit Top N Rows dialog, where if you type SHIFT+SPACE (e.g. type "FOO BAR" while holding the Shift key) while editing the cell data, you get dumped out of the cell (however you can keep typing) and only "FOO" appears. This can be pretty annoying and counter-intuitive; while my "workaround" is to revert to what I always do (write DML statements like a normal person), this is still something that should be fixed if they want people to continue trusting the UIs within SSMS.
#473303 : SSMS : "Edit Top n Rows" mishandles a space character
===================================
So, I hope those are enough items to keep you busy for a while. 🙂
It is not only related to log file size growth alone, but also to do with the auto sync feature. If tlogs are backed up as part of mirroring configuration, after the secondary comes alive, it would be nice if mirroring sync up using the tlogs. But now mirroring is broken and we have to configure mirroring again.
I'm not so convinced about this one. No matter where you are keeping these logs, if you don't plan for down time (and hence expanded disk space to keep the logs), you're going to hit this problem wherever SQL Server puts the logs. More fault tolerance would be great but there are bigger areas where customers are shooting themselves in the foot… using bigger drives is a very easy solution to this type of problem.
Aaron, thsnks for adding my item…Here is one more to the list..
Auto sync in mirroring:
The only major problem with mirroring (asynchronous mode) is that the transaction log continues to grow if the secondary server is not available due to network issues or several other reasons…..This may lead to the filling up of the disk where the log file exists.
http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=337691