While playing with the new Policy-Based Management (PBM) features of SQL Server 2008 the other day, I came across a really annoying syntax implementation that is going to trip up a lot of people unless it is fixed. We all know how
SELECT DATEADD(DAY, 1, GETDATE());
Sadly, because an expression for a condition is validated using C# and not T-SQL, the syntax needs to be slightly different:
SELECT DATEADD('DAY', 1, GETDATE());
Note the single quotes around
'DAY'… this syntax, obviously, will not work in T-SQL anytime soon:
Invalid parameter 1 specified for dateadd.
So, imagine I am creating and testing a big
WHERE clause in a query window, and once I have it right, I want to copy and paste it into the expression editor for a condition, because I want to use that
WHERE clause to enforce or monitor some policy. Now I need to save two versions of it; the original T-SQL version (should I need to modify it later), and the condition-compatible version — after meticulously adding single quotes by hand (or writing my own parser that will add them for me). For example, would you really want to be going through large expressions like this, and "correcting" them?
DATEADD('DAY',1-DATEPART(WEEKDAY,GETDATE()), DATEDIFF('DAY',0,DATEADD('MINUTE', DATEDIFF('DAY',0,GETDATE()),GETDATE())))
And this, only if I know in advance that the
DATEADD syntax needs to be different (it is not the only function affected, by the way —
DATEDIFF have the same restrictions). Note that because I forgot to put single quotes around
'WEEKDAY', I will get an error message. The error message that I receive is far less than helpful, and is the same regardless of where or how often I forgot single quotes, or even if I left out one of the parentheses:
It would be great to have a helpful addendum, indicating at least the first syntax error encountered, for example
Incorrect syntax near 'WEEKDAY'"…
I espoused about much of this on Connect, of course…
…because consistency is important. Unfortunately, due to time constraints around the looming RTM date, it is unlikely this will be fixed. But one can hope.
There are some other cases in the past where some decision was made on a developer's computer somewhere, and by the time the decision came into question, it was too late to correct. Well, I have three examples that come to mind immediately, and one of them was, in fact, changed at the last minute.
This data type covers pretty much everything, doesn't it? Why not be consistent with the
CHAR types (
DATETIME(MAX))? Do you envision the need for, say,
EVENBIGGERDATETIME? Yet the name implies that they are leaving room for a higher-scale or higher-precision date/time data type (
DATETIME3 anyone?). Or maybe the name was chosen by an employee that was recruited from Oracle.
This was a very unfortunate naming blunder, since the data type is equivalent to
ROWVERSION and has nothing to do with date or time at all. But the implication given by name alone leads a lot of people to add a
TIMESTAMP column to their table, only later to seek help in the forums, asking how to display their
TIMESTAMP values as
DATETIME, or use a
WHERE clause to filter by
DATETIME. We have asked repeatedly that this alias for
ROWVERSION be marked as deprecated, and yet in
sys.types in SQL Server 2008,
TIMESTAMP appears, but
ROWVERSION does not. 🙁
DATE and TIME as CLR types
When SQL Server 2005 was in beta, the SQL team thought it would be great to use
TIME as a way to demonstrate the power of the new CLR types. Unfortunately, the new types did not play well with the other
DATETIME data types, the new SSMS GUI, or built-in functions like
DATEDIFF. Thankfully, a rather large group of us were rather vocal in our complaints: before ship date, they agreed to cut the feature until they could get it right. There are still a few issues with the types as implemented in SQL Server 2008, but trust me, we are in much, much, much better shape now.