Connect Digest : 2011-09-22
Where is SYSDATE()?
I've talked about this one before, but due to a recent conversation about building a datetime value from separate date/time columns or variables, it may be worth bringing up again. When SQL Server 2008 introduced several new date/time types, they also added new built-in functions, like SYSDATETIME() and SYSDATETIMEOFFSET(). But they forgot about the same functions they forgot about back when GETDATE() and GETUTCDATE() were introduced: functions that return only date or only time. So we're still stuck with using implicit conversion to assign CURRENT_TIMESTAMP or SYSDATETIME() to a DATE or TIME variable, only to throw away the irrelevant part.
Where is my built-in sequence?
In Denali we've seen the addition of SEQUENCE, an easy and more efficient way to centralize IDENTITY-like identifier values. But often we want to generate these numbers without incrementing a counter or running a bunch of inserts. To generate numbers on the fly in cases where I haven't been able to create my own numbers table, I often use master..spt_values.number, convoluted CTEs, or ROW_NUMBER() OVER () a cross join against arbitrary system tables. Why can't there be an in-memory table of numbers that we could reference, without all this extra scaffolding?
Why is CLR still so hard?
For ages I've wished it were easier to implement CLR solutions. RegEx is still not in SQL Server, and CLR has proven much more efficient than traditional XML, numbers table and looping methods in dealing with string splitting operations as well. And probably dozens of use cases that I haven't had the necessity to research myself. But it is often hard to help folks implement this on their own, never mind deal with strict DBA/management environments where CLR is still off-limits. Wouldn't it be great if it were far easier to implement C# code directly within a stored procedure or function, without all the overhead of deploying a DLL, creating an assembly, etc.
Will CASE always evaluate in left-to-right order?
The documentation for CASE states that the WHEN expressions are evaluated in textual order. Most people infer from this that any subsequent expression will not be evaluated, but there have been several cases where this has not been true. While I didn't find this particular manifestation of the bug (that was Itzik), and while I do hope they correct this scenario, I'm much more interested in clarity in the documentation so that people know whether or not they can rely on this behavior to always be true.
Why can't I have partition-level stats?
Partitioning is great, but if you have disproportionate partition sizes, or a lot of partitions, statistics can become a real challenge. Consider a table with 100 million rows, but only 1 million rows in the currently active partition. If you want to update the statistics on the active partition, your probable approach today is to update statistics on the whole table. (The same type of issue exists with filtered indexes – auto statistics updates kicks in based on the row count in the table, not the subset in the index.)