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.

Connect #293333 : SQL 2008: Add sysdate()

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?

Connect #258733 : Add a built-in table of numbers

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.

Connect #265266 : Add server-side compilation ability to SQL CLR

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.

Connect #690017 : CASE / COALESCE won't always evaluate in textual order

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.)

 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

4 Responses

  1. cinahcaM madA says:

    Did you happen to see an item about online index rebuilds for partitions? This is an equally major issue as compared to stats, for customers that have partitioned tables for non-sliding window use cases.

  2. AaronBertrand says:

    Louis, I think there is still hope. I had seen your Connect item but left it out precisely because it was closed that way. If they are still actively considering Erland's item, then yours should probably be closed as a duplicate, not as "won't fix."

  3. drsql says:

    I hadn't seen Erland's request. Mine was closed as won't fix:
    http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/318049/add-date-function-that-returns-the-current-date-without-time
    Of course the first person to respond thought it was good 🙂
    "Thanks for the great suggestion and feedback.
    Given the time and resource constrain for Katmai, we most likely won't be able to add the system built-in for returning date only value per your request. However, we'll seriously consider it in the next major release."
    Still hope? It seems like this would be reasonably easy enough, right? I know optimizer stuff is actually rocket science, but could it be that different than sysdatetime()