It’s Friday night, so I’m waiting for new CTP Releases
As soon as I got the email, I started reading the release notes. Some interesting stuff, of course.
Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
‘New’ string functions like CONCAT_WS, TRANSLATE, and TRIM.
STRING_AGG WITHIN GROUP (ORDER BY)s.
Cool.
Poking around
I started looking for new objects in sys.objects, comparing it to what’s in 2016.
I don’t know how this didn’t make the release notes. But there it is. My first Connect baby with Microsoft. I’m a proud papa.
What and how
So, what does it look like, and how do you use it?
It’s a pretty simple CROSS APPLY, like many other system functions.
|
1 2 3 4 |
SELECT OBJECT_NAME(dh.object_id), dh.stats_id, step_number, range_high_key, equal_rows, distinct_range_rows, average_range_rows FROM sys.stats s CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) dh WHERE s.object_id = 3 AND s.stats_id = 1 |
What you get out looks like a histogram. Because, well, it’s a histogram, but in rows.
THIS IS
AWESOME!
It’s not exactly perfect
For instance, if you just let it loose without filters, you get a severe error. The same thing happens if you try to filter on one of the columns in the function, rather than a column in sys.stats, like this.
But I don’t even care. It’s brand new, and it’s so much better than trying to tabularize DBCC SHOW_STATISTICS output.
Thanks, whomever worked on this.
And thanks for reading!
5 Comments. Leave new
The addition of STRAGG I mean STRING_AGG is all nice and well and good and such, but are we all just gonna keep acting like it’s nearly 2017 and SQL Server still doesn’t have native regex support?
Well, no, but if they did implement it, it would probably just be another one of those new functions that just references the .NET class anyway. In which case you can create your own CLR function that calls the .NET class, which may or may not cause stack dumps if you use it on a large enough field.
Which is fun.
It’s all a lot of fun.
congrats on the connect baby! spewing red ink without a filter…chip off the ole block, eh? =)
for my part, i would kill for a t-sql analog of the .net string.format() function. for the times you need to, such a thing would make generating dynamic sql from a template string safe, readable and fun! all the plus-y plus-y and tick-y tick-y required by concatenogenedynamic SQL makes my head hurt.
sp_executesql kinda does that, unless you need to pop in database/table/schema names.
Which you probably do.
TEE HEE
Shiny!