I'm a day early; sorry.  But I have a lot of interesting items to share this week.

========================================

More control over TOP (n) in DML

This week I realized that the workarounds for affecting which row(s) are impacted by UPDATE/DELETE TOP (n) were cumbersome, and so I asked for an extension of TOP that allows you to specify how the TOP should be applied (very similar to how OVER() is used for window and ranking functions).  Since the current syntax does not allow ORDER BY in an UPDATE statement, the TOP (n) is arbitrary; however, we have been banging our heads against the wall attempting to drill into people that TOP is relatively useless without ORDER BY… and here is a case where ORDER BY is not even allowed.  I'd say this was ironic at best, but I'll let you decide.  P.S. I tried very hard to find an existing Connect item that covered this, because I know it has been discussed multiple times in the past, but I came up empty.

#476678 : Enhance TOP with OVER() for certain DML

========================================

VIEW DEFINITION permissions per database

In last week's digest, I complained about the security of exposing the database list to non-sysadmin users, which Object Explorer currently does by default.  (I also blogged about ways you can potentially "fix" this in a few scenarios.)  Fellow MVP Erland Sommarskog was quick to remind me that only changing the way Management Studio decides to present the database list is not necessarily the best approach; rather, it would be best to have per-database permissions on the login level that are stored *above* the database hierarchy, instead of attached at the database level (which means you have to get into the database to see if you can access the database). 

#273830 : Need VIEW DEFINITION permissions per database

========================================

MERGE and SCOPE_IDENTITY()

While I'm not sure that you should be using singleton IDENTITY functions like SCOPE_IDENTITY() and @@IDENTITY in combination with MERGE (I would much prefer using the OUTPUT clause, since you're almost always dealing with multiple rows), Fabio Lunardon reported this bug, where MERGE can make SCOPE_IDENTITY() behave more like @@IDENTITY.  Whether Microsoft considers this a bug or "by design" remains to be seen, but in the meantime, you should check your code for this scenario if you are currently using MERGE in SQL Server 2008.

#476577 : MERGE can corrupt SCOPE_IDENTITY()

========================================

Automatically maintained columns

MVP Louis Davidson points out that we often create triggers simply to update a "modified date" column every time a row is touched, and that it would be nice if there was some built-in functionality.  I agree, as I have been involved in gigs where every single table in the schema had such a trigger.  The overhead isn't atrocious, and the maintenance isn't horrible either (since you can learn to script them in bulk once you've realized the prospect of doing it manually), but it is still a pain.

#203570 : Add automatically maintained columns

========================================

Specifying Filegroup for SELECT INTO

It has long been observed that using SELECT INTO you cannot specify the destination filegroup (or partition scheme).  Of course the benefit of SELECT INTO over INSERT…SELECT is performance; since SELECT INTO is what we call "minimally logged" and, in most cases, INSERT…SELECT is not.  However, due to minimal logging changes in SQL Server 2008, you can achieve this in some INSERT…SELECT cases and in combination with a trace flag (see Sunil Agarwal's storage engine blog article for more information, and be aware of the caveats he mentioned in this post).  But ideally, we would not have to use a trace flag; and even when our goal is not performance, using the more convenient syntax of SELECT INTO to create a copy of a table's structure, it would still be beneficial to be able to dictate the destination partition or filegroup.

#254140 : SELECT INTO ON FILEGROUP

========================================

Another interesting "incorrect results" bug

Someone named "Ionel" reported this bug, where COUNT() in a LEFT JOIN with an empty subquery can report the wrong result.  The bug appears in SQL Server 2005 and SQL Server 2008.

#380304 : COUNT(column) bug when using subqueries that return 0 rows

========================================

Statistics on linked server queries

MVPs Linchi Shea and Erland Sommarskog sparked an interesting discussion about the need for access to distribution statistics when running a query against a linked server.  In short, when your linked server permissions do not allow you to access the stats for the table(s) you are hitting on the linked server, performance can really suffer (as Linchi blogged about this week).  Currently, a common workaround for people might be the temptation to set up all linked servers with sysadmin logins on the remote end; not exactly a security best practice.  Each of them filed their own Connect item: Linchi asks for better documentation around the issue, while Erland asks for a better permissions implementation (essentially, if I can SELECT, I should also be able to see stats):

#476001 : Document the need for and impact of distribution stats for a remote table in processing a distributed join

#475804 : Permissions to access statistics should for SELECT permissions for the object

========================================

Better access for missing index DMVs

Currently, you need VIEW SERVER STATE permissions to see the details in the missing index DMVs, even if you only after the data for databases you own.  MVP Adam Machanic points out that this should not require VIEW SERVER STATE permissions; instead, dbo should be sufficient (in which case the DMVs would only expose the rows for your databases).  The primary use case I can envision for lowering this requirement is, when you host your SQL Server database at a shared hosting provider, you are usually dbo for your database(s) but you would never be given VIEW SERVER STATE permissions.  Essentially this means that you can not troubleshoot index strategy in performance effectively in this case.

#475574 : DBO should have access to missing index details for the local database

========================================