@JoeObbish In my limited experience, that's pretty much the only venue that can support such technical levels. There are some nuggets from MSFT people to be found on e.g. channel 9/SQLBits etc. as well.

It does test one's search skills.

@JoeObbish @AaronBertrand Might know of some others.

Beyond that, I guess you're into hiring someone directly for specific work.

Or becoming that person and retiring at 30 on an island made of gold.

@JoeObbish it's either a bug in vNext or in Jack's dbfiddle implementation/drivers.

That's pretty whack

Hm I should install vNext somewhere.

Seems like numbers, when converted into strings for display, go through a transition.

1 is ASCII 31 in hex.

But it's definitely a number - try superficial modification 1 + ROW_NUMBER

17 becomes char1-char7 becomes 3137

@PaulWhite Here is what I have: pastebin.com/WGqx7vrc

@JoeObbish Thanks. I'll get to it shortly.

looks like I lost a comment

first one ran in 17 seconds on my machine

it just... feels wrong

thanks for the advice

need to run to dinner soon

@JoeObbish In what sense?

In the slow case, the Top N Sort has Top Rows = 20000000. In the fast case, 1.

Top N Sort with Top Rows <= 100 can use the optimized (replacement) algorithm.

I hope I make that clear in the article.

Adding an ORDER BY (SELECT NULL) to the inner TOP might help you make sense of it.

There's more about Top N Sort in sqlperformance.com/2015/04/sql-plan/…

You can also fix that spill with TF 7470, but it's still less efficient.

It's possible to analyze simple demos too much though :)

@ypercubeᵀᴹ So we're blaming @JackDouglas?

@PaulWhite No, not really. I was just wondering if it's a vNext bug or something with the dbfiddle-vnext interface

I would hope it's the interface.

@ypercubeᵀᴹ it seems there is more than one bug dbfiddle.uk/…

dbfiddle.uk/…

Something wrong with bigint? @JackDouglas ^^^

dbfiddle.uk/…

Seems so. decimal/numeric is fine as well.

@McNets The timestamp on a comment is a link you can paste here.

dbfiddle.uk/…

It doesn't matter. I''ve changed my answer. Thanks.

And he is Back: dba.stackexchange.com/posts/165420/revisions

→ 9 messages moved to Trash

@EvanCarroll Instead of thinking the normal set-based way, think about each row one at a time. Consider the following data (Times will be shown as integers):

Time  Status
----  -------
13    Running
16    Walking
19    Walking
27    Running
31    Running
35    Walking
38    Running

Time  Status
----  -------
13    Running -- 1st row - 1 Running status before and including = 0
16    Walking -- 2nd row - 1 Walking status before and including = 1
19    Walking -- 3rd row - 2 Walking statuses before and including = 1
27    Running -- 4th row - 2 Running statuses before and including = 2
31    Running -- 5th row - 3 Running statuses before and including = 2
35    Walking -- 6th row - 3 Running statuses before and including = 3
38    Running -- 7th row - 4 Running statuses before and including = 3

You can see then that the groups are unique per Status. There can be overlaps between statuses (where two rows have the same result that aren't part of the same group) but they are unique when you also group by Status.

Some thought shows how this can work with non-unique times (as long as they share the same status) by using DENSE_RANK() instead of ROW_NUMBER().

The DENSE_RANK() basically enables it to completely ignore the duplicates, pretending they aren't there.

The "before and including part" is easy to understand when you think about what PARTITION BY on a ROW_NUMBER() does... exactly what we need: number the rows before and including this one that have the same Status, in order by date.

@PaulWhite In my defense I did say that I hadn't had a chance to carefully read your article yet and the answer might be there. I understand that the plans are different and that's why performance is worse in one case. However, I don't understand why the query optimizer picked what seems at first glance to be such a bad plan. It's been a very long day so I will look at it further later. Thanks for the links

@JoeObbish One could make a case that the sort is pushed further down the plan than is optimal (hence my ORDER BY (SELECT NULL) suggestion) but as I say, this is rather overdoing the analysis of the demo code.

@ypercubeᵀᴹ thanks

I can't fix it without breaking something else :(

the linux driver tells me the type is 'unknown' for bigint — and in that case I was converting to hex, to fix the issue with GUIDs select service_broker_guid from sys.databases;

(which also show type as 'unknown')

so bigint is fixed, but GUID is broken again

@JackDouglas Really broken. SELECT NEWID() (change to vNext manually) returns:

Internal Server Error

22P02ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1:

@PaulWhite works for me

@TomV On vNext only.

ah ok, I clicked your link

@TomV Yes it has to be 2016 otherwise all you get is the error (no fiddle screen). Clarified above.

@JackDouglas So before, bigint was broken but GUID was showing fine?

That might be better than getting fatal errors. We can always cast to varchar to display bigint, until the drivers get fixed.

@ypercubeᵀᴹ Or cast to int if the ROW_NUMBER() aren't really that large

@ypercubeᵀᴹ I've fudged a compromise

if the type is reported as 'unknown', it will convert to hex if there are any non-printing characters

dbfiddle.uk/…

It might sometimes show as text when it should be hex, but it shouldn't fail at least

bigint will always work

@JackDouglas But not in this case? (link earlier posted by ypercube)

I don't understand it really, this one kind of works. Although it appears that the bigint is treated as a string, at least the correct value is shown.

Why it works differently with ROW_NUMBER is beyond me.

@PaulWhite Now that I look at it again I don't think we're disagreeing on anything. When I said "TOP 1 sort" I meant seeing that in the query text. It looks like you meant a TOP N sort with TOP = 1 as an operator in the query plan.

If I remember I think the demo was supposed to demonstrate that a larger sort is worse than linear compared to a smaller sort. But I found it confusing because I read it as a TOP 1 sort. I don't think that it has any practical value, but I plan to look at it more later to identify the gap in my understanding.

@AndriyM the result was cached — pressing 'run' fixed it.

@JackDouglas Ha, why didn't I think of that? Thanks.

Conway's life: Clock

@AndriyM it's getting really hacky, but now bigint is right justified. All of this will just work with vNext on Windows, but I guess having the Linux variant might be handy.

see comments

@AndriyM :_

@AndriyM you missed the <> in the query

Or rather you didn't but the pre/code ate them ;)

@ypercubeᵀᴹ Yes, I usually check for those but this time I forgot, thanks.

It's weird isn't it. I'm sure it says somewhere in the markdown guide that indenting four spaces is equivalent to pre/code - but it's really not.

I blame XML.

@JackDouglas Some other type display issues with SQL Server 2014/2016: dbfiddle.uk/…

Expect a binary representation instead of System.Byte[] and the commented out ones should not cause a server error.

HALLO EVERYONE.

HALLO EVAN!!!1!!1

HAI!!!!

@EvanCarroll Did you give up on that gaps/islands Q & A?

Hola

Hi

Hola senior

@ypercubeᵀᴹ isn't there a ñ on your keyboard?? ;-)

@McNets Nope

McNets junior is my son, today he celebrates his 18

In fact, I speak catalan, ñ (esp) = ny (cat) = gn (french)

@McNets Is Catalan considered a different language or a Spanish dialect?

@ypercubeᵀᴹ Ñope

It a different language

@ypercubeᵀᴹ Both derived from Latin.

Unregistered accounts are a pain.

@McNets thnx, I'm reading wikipedia about it

@PaulWhite Nope, I've rewritten at least 20 times.

@EvanCarroll Oh OK. I was expecting to see a deleted question.

finally back home

I didn't think sql saturday would be so tiring

@JoeObbish Is it less tiring on other weekdays? ;)

oh man

can't handle puns right now

sorry

have you ever been to one? was my first

I think there are some events in Europe but I don't know how widespread it is

I haven't.

imo it's too difficult to figure out which sessions will be good

the best one I went to was actually at the "beginner" level

but the guy knew what he was talking about, had a good presentation planned, and had good visual aids

it was about the translation log

had a tool that looked at VLFs and color coded them as they were filled/truncated/active

so you could really visualize what happened to the log while a workload went on on the server

there was another guy who only spent five minutes out of an hour on what his talk was supposed to be about

@JoeObbish "translation"? Did you mean transaction log?

@ypercubeᵀᴹ yes, thank you for the translation :)

the other guy also promoted the TF that will not be named

See, 1 minute in the Heap and you already peeling better. Making puns, etc.

Peeling better. Hmm.

@JoeObbish 610 with full recovery? Did you go to that?

@JoeObbish That doesn't seem very "beginner".

It's like "basic SQL".

@PaulWhite No, we actually left before the last session of the day. The thought of trying to sit through that was not appealing

I may be able to get the notes though

so we can all learn about TF 610

Ha!

@PaulWhite It was towards the end. IMO he built up to it nicely

@JoeObbish Ok.

There was another guy who had books to give away

and said he would give them away to people who asked good questions

anyone want to guess what happened?

you got a book?

Lots of good questions?

no, but thank you

He ran out of books?

also not correct

He 'forgot' the books.

Ran out of time for questions.

What Paul said is accurate but for the wrong reason

He was eaten by a giant watermelon.

well

he ran out of time for his material

giant strawberry

answered dumb questions for 75% of the session

@ypercubeᵀᴹ Not again! That keeps happening! Security!!!

For example, he said "On this one database I have about 80% of the indexes page compressed."

"is page compression a setting at the database level?"

are you even trying????

Imagine: a database level setting that specifies 80% of indexes should be compressed.

if you drop an uncompressed index would the database then uncompress an additional index?

Naturally. What a dumb question. No book for you!

SQL Saturday is a great venue for new speakers to learn what works.

Unfortunately that sometimes means the audience gets to experience what does not.

I couldn't figure out why some folks were presenting

because they seemed to be experienced

but didn't seem to try

@PaulWhite Speaking of which, sql_handle presented for the first time. Want to see the session description?

@JoeObbish Sure.

@JoeObbish Well that's a shame. For them, and another person that would have tried in their place.

Lets send that to SO

I can flag if that's appropriate.

Done.

@PaulWhite Thnx.

@PaulWhite sqlsaturday.com/600/Sessions/Details.aspx?sid=59564

He has been pushing everyone to get more involved in the community. So I suppose that requires trying out new things.

I have been giving more thought to creating a blog but it's hard to imagine who I would be helping

I've just noticed that TF 610 session had 10x in the title.

@JoeObbish I started blogging just for myself. I found it a great way to consolidate my thoughts and ensure I really understood it well enough to explain to others. These days I would probably self-Q & A more here as well.

@PaulWhite For those aspiring to be 10x-ers in ETL building.

@ypercubeᵀᴹ Wow.

@AndriyM I guess.

@PaulWhite @ypercubeᵀᴹ Kind of defeats the point.