Connect digest : 2009-06-12

AaronBertrand — Fri, 12 Jun 2009 14:30:00 GMT

Again I am a day early, as tomorrow I will be speaking at the CTDOTNET CodeCamp in Hartford. As a reminder, I am not begging for votes here, just raising visibility for new issues you may not have seen yet, or older issues that have entered my peripheral vision again for some reason.

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

Better behaving hints

Lakusha had a great suggestion to allow certain table/query hints to have an option to return warnings instead of errors. The prime use case presented is the case where you drop an index on a view, then suddenly any query using WITH (NOEXPAND) against that view stops working. The suggestion is that these queries could continue working and just ignore the table hint. And this could apply to index hints as well, making them less of a no-no (not that that is necessarily a good thing, but YMMV).

#293508 : Some query hints Errors should have a Warning Only option
Current rating: 4.2 (5 votes)

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

Streaming results

Fellow MVP Adam Machanic's suggestion for better streaming of results within T-SQL would give us the flexibility of cursors without the performance hit, and would prevent us from having to use more complex solutions (e.g. CLR) when scaling to large results.

#456349 : Provide a streaming result interface via T-SQL
Current rating: 4.2 (4 votes)

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

Deeper info about hotfix / CU level

Let's face it, relying on someone's blog to determine what build of SQL Server you have (and in turn which fixes you are protected by) is not reliable enough for most people. While Microsoft has made great strides in keeping the KB up to date with full disclosure on hotfix builds, cumulative updates and service packs, there would be great value in having something within the product that could give you the details straight up, instead of having to search through KB article after KB article trying to find relevant information. This is exactly what Robert Davis has suggested. I disagree with his urging to change @@VERSION, but I think the general approach that a new server variable or SERVERPROPERTY() could be introduced would be an easy way to provide this additional and valuable information without introducing backward compatibility issues.

#464322 : Augment ProductLevel property of the ServerProperty function to include CU #
Current rating: 4.3 (5 votes)

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

Declaring variable lengths

I think it is fairly well-known that this syntax is just lazy, never mind potentially troublesome:

DECLARE @x VARCHAR;

Part of the problem is that in different scenarios this length will default to 1 or 30, and can often lead to data loss due to silent truncation. Fellow MVP Erland Sommarskog is calling for deprecation of the lazy syntax, and I wholeheartedly agree.

#244395 : Deprecate (n)varchar with out length specifcation
Current rating: 4.0 (6 votes)

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

Open Table is gone, but it is still biting us

They closed the following item as Fixed, but I disagree, and hence re-opened it. The so-called "fix" was to return an error message; while arguably this is better than updating too many rows, it still leaves the table designer totally useless if your table is made up of certain data types.

#289541: SSMS : Open Table w/binary key updates too many rows
Current rating: 4.7 (13 votes)

Right now if you choose "Edit Top n Rows" and then the data has changed in the meantime, choosing "Edit Top n Rows" again does not re-pull the data (or check that the schema is still the same), it just sets focus to the existing window you had opened previously (and depending on how attentive you are, this might fool you into believing that the data was refreshed). I think that it should automatically refresh the data *and* the schema to prevent potentially catastrophic updates.

#464596 : SSMS : Edit Top n Rows needs to refresh data and schema
Current rating: N/A (1 vote)

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

Last week's results:

#462042 : Incorrect "Duplicate key" error with unique filtered index
Previous rating: 4.7 (4 votes)
Current rating: 4.8 (6 votes) +2

#462046 : Cannot rename a default constraint for a table in a schema which is not dbo
Previous rating: 4.4 (4 votes)
Current rating: 4.5 (6 votes) +2

#462053 : The filter expression of a filtered index is lost when a table is modified by the Table Designer
Previous rating: 4.7 (4 votes)
Current rating: 4.8 (6 votes) +2

#457024 : Update statistics, top 100 percent and Sort warnings
Previous rating: N/A (0 votes)
Current rating: 4.8 (9 votes) +9

#458076 : Make %%lockres%% a documented feature
Previous rating: 4.7 (10 votes)
Current rating: 4.7 (14 votes) +4

#458080 : Lock Resource Hash Value not visible in Deadlock Graph Graphical View
Previous rating: 4.8 (8 votes)
Current rating: 4.8 (10 votes) +2

#458084 : Improve content in BOL for Deadlock Diagnosis
Previous rating: 4.4 (6 votes)
Current rating: 4.5 (7 votes) +1

#458091 : Change Lock Resource Hashing Algorithm to Reduce Likelihood of Collisions
Previous rating: 4.6 (11 votes)
Current rating: 4.7 (13 votes) +1

Search results matching tags 'cumulative updates', 'T-SQL', and 'hotfixes'

Connect digest : 2009-06-12