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 DATEADD works:
| 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:
Msg 1023, Level 15, State 1, Line 1
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(DW,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 -- DATEPART() and DATEDIFF() have the same restrictions). Note that because I forgot to put single quotes around 'DW', 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:
| Error parsing 'DATEADD('DAY',1-DATEPART(DW,GETDATE()),DATEDIFF('DAY',0,DATEADD('MINUTE',DATEDIFF('DAY',0,GETDATE()),GETDATE())))'. Make sure string constants are enclosed in single quotes and facet properties are prefixed with '@' sign. |
It would be great to have a helpful addendum, indicating at least the first syntax error encountered, for example "Incorrect syntax near 'DW'"...
I espoused about much of this on Connect, of course:
PBM : Expressions like DATEADD require inconsistent and unintuitive syntax
If you think consistency is important, please consider voting. 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.
DATETIME2
This data type covers pretty much everything, doesn't it? Why not be consistent with the INT or CHAR types (BIGDATETIME or 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?). Or maybe the name was chosen by an employee that was recruited from Oracle.
TIMESTAMP
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 perform 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 DATE and 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 DATEADD and DATEDIFF. Thankfully, a rather large group of us were so vocal in our complaints, that 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.