Connect Digest : 2009-08-22

AaronBertrand — Sat, 22 Aug 2009 03:18:00 GMT

I rounded up a few interesting items this week.

Does "1/10/1900" mean January 10th, or October 1st?

This item demonstrates that Microsoft isn't all that concerned about publishing sample code that uses ambiguous or troublesome formats for date literals. Initially the Books Online topics mentioned in the item used "1/10/1900" as a date literal, and of course that would have a different meaning in a default US English locale compared with a British locale. The alleged 'fix' was to change the string to "January 1, 1900." Which will simply fail if the user has, for example, SET LANGUAGE FRENCH. It seems funny that they could make a change for the better within a few days, but then making another change (also for the better) cannot be done due to costs. Whereas if they had just changed "1/10/1900" to "19000101" in the first place, the problem would have been solved in one step. What a weird organization they have over there; I personally think that the people writing their documentation samples should be assigned random locales and SET LANGUAGE settings so that they aren't writing code in a nice little US English vacuum. I can imagine a scenario where you have a bank of servers to deploy to, and you have to test on one of them, but you never know which one you're going to get... when your code blows up because the German server with SET LANGUAGE DUTCH doesn't know what "February" means, you have to go back to the drawing board. And I think this would be beneficial for everyone.

#482186 : DOC : Add/Subtract topics should warn about DATE / TIME types

Why can't we have an easier way to get first day of month, of year, etc.?

We currently use clumsy methods to get the first day of the week, month or year (given a DATETIME/SMALLDATETIME value), or to strip the time. While the DATE data type in SQL Server 2008 will relieve some of this pain, it won't help in all cases. Meanwhile, other RDBMS have much more convenient ways to do this (e.g. Oracle's TRUNC()). I am hopeful that someday they will add a function like this to SQL Server; I call it "DATEROUND."

#483913 : Add a DATEROUND function similar to Oracle's TRUNC() for date handling

Why do we allow identifiers with trailing spaces?

While this may not be common practice (notice the trailing spaces in the table and column names)...

CREATE TABLE dbo.[foo ]
(
    [bar ] INT
);

...there are end users complaining about the fact that this works (see Connect #483389). I had a slightly different take than user "aitcha1" in that I don't understand why SQL Server allows trailing spaces in identifier names in the first place. It does ignore trailing spaces when preventing duplicates (for example, you can't have a column named [bar] and a column named [bar ]), but in spite of what the documentation says, it certainly keeps the space intact when storing the metadata in the catalog views. So I filed two suggestions: one to fix the documentation to be more explicit about how "SQL Server stores the name without the trailing spaces," and one to actually tighten up identifier rules.

#483553 : DOC : Delimited Identifiers topic lies about trailing spaces

#483527 : Tighten up identifier rules

Why does SSMS not like VARCHAR(MAX) parameters?

There was an avid discussion on the newsgroups this week about a long-acknowledged bug in SSMS. If you have a stored procedure that has a VARCHAR(MAX) parameter, and you open Object Explorer, right-click the procedure and choose Script As > Execute > New Query Window, the variable declaration for that parameter is simply VARCHAR, not VARCHAR(MAX). Obviously this isn't a huge show-stopper, and an easy workaround is to use NVARCHAR(MAX) parameters (if this is acceptable), but I also believe that this can't be a very difficult bug to fix. I tried to use Profiler to peek at what SSMS calls when you run this script, but they are only retrieving metadata and not revealing how they are actually building the output that you ultimately execute. My guess is that there is just a bug when handling VARCHAR() types where max_length = -1. Anyway the bug has been sitting around for almost three years and could possibly use a little traffic.

#241782 : create script for sp execute in parameter varchar(max) wrong

Who coded that infinite loop?

In several Windows collations (e.g. Latin1_General_CI_AS), when performing a REPLACE() of CHAR(0), the SQL engine can enter an infinite loop and consume multiple CPUs. A problem with CHAR(0) was first reported by Erland back in 2006, but the infinite loop was a much more recent revelation (it was added in a comment this week, and may appear in an independent Connect item in the future).

#125502 : Replace of char(0) does not work in DB with Windows collation

Can we have an option to turn off deferred name resolution?

Deferred name resolution is confusing, and has even created a market for 3rd party products that track dependencies better than SQL Server can do on its own (e.g. Red-Gate's SQL Dependency Tracker). We know that if we create a stored procedure that calls another stored procedure that doesn't yet exist, the parser still allows us to create the procedure, giving us the benefit of the doubt that we will create the other procedure later. So in this case, we get a warning message:

CREATE PROCEDURE dbo.test1
AS
BEGIN
   EXEC dbo.proc_does_not_exist;
END
GO

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends 
on the missing table 'dbo.proc_does_not_exist'. The stored procedure will still be 
created; however, it cannot be successfully executed until the table exists.

The warning message incorrectly says "table" when it should probably say "object." In SQL Server 2008, the error message is actually:

The module 'test1' depends on the missing object 'dbo.proc_does_not_exist'. The module 
will still be created; however, it cannot run successfully until the object exists.

Sadly, we don't get a warning or an error if we reference a table that doesn't yet exist:

CREATE PROCEDURE dbo.test2
AS
BEGIN
   SELECT * FROM dbo.table_does_not_exist;
END
GO

Command(s) completed successfully.

This was complained about in Connect #246014, which was closed as fixed, however the parser is still silent about the problem in SQL Server 2008 (where we are told it has been addressed). I guess the "fixed" part is just that the error message when the referenced object is NOT a table no longer incorrectly says "table." :-(

Deferred name resolution falls completely on its face if we reference a column that DOES NOT (yet) exist, in a table that DOES exist. Where is our benefit of the doubt now? This stored procedure won't compile, even if we intend to go create the column immediately:

CREATE TABLE dbo.foo
(
    bar INT
);
GO

CREATE PROCEDURE dbo.test3
AS
BEGIN
   SELECT column_does_not_exist FROM dbo.foo;
END
GO

Msg 207, Level 16, State 1, Procedure test3, Line 3
Invalid column name 'column_does_not_exist'.

As alluded to, dependency tracking gets better in SQL Server 2008, as *some* unresolved dependencies are stored in sys.dm_sql_referenced_entities. I wrote a lengthy blog post about this last year:

Keeping sysdepends up to date in SQL Server 2008

But I would still like to make it impossible to create anything (even a synonym) that references an object that doesn't yet exist. Erland goes into much more detail about this in his paper on SET STRICT_CHECKS ON. You can't vote on that, but you could vote on these:

#127152 : Ability to disable or workaround deferred name resolution

#287100 : Turn off deferred name resolution for CREATE SYNONYM
#260762 : Add optional checks for more robust development

Search results matching tags 'SSMS', 'deferred name resolution', 'documentation', and 'SQL Server 2008'

Connect Digest : 2009-08-22