The Heap™ – Consultancy ©®

Joe Obbish

2:14 AM

@srutzky I suppose my definition would be "the minimum unit that I can select with the mouse cursor"

anyway thanks, that's interesting stuff

Joe Obbish

2:34 AM

anyone know how row distribution for parallel CCI scans works when the CCI is partitioned?

Joe Obbish

4:25 AM

nevermind, think I figured it out

hot2use

6:02 AM

Ublaahatkut (Inuit)

Andriy M

6:49 AM

Morning

McNets

Morning

dezso

7:41 AM

morning

Paul White

morning

dezso

1

Q: What's the difference between view and the select query under it?

when I executed a join query, I got an error: emporary file size exceeds temp_file_limit (65536kB) but I got results finally selecting from a view instead which is exactly the same as join query. it's weird. There should be no difference between view and the query under it, right? The fol...

I voted as too localized

^^ 'exactly the same' - well, it's not

Andy K

8:04 AM

morning

ypercubeᵀᴹ

@dezso Yeah. The question might be worth, if he was asking about the error message (Temporary file size exceeds temp_file_limit (65536kB))

I hadn't seen that ever.

Paul White

Well you have now

Colin 't Hart

9:11 AM

Who upvoted that question?

dezso

@Colin'tHart maybe the answerer

Colin 't Hart

He was being too kind :-) He answered the subject of the question, yet the question contains a different question... or several.

Andriy M

He answered it before the OP added the definition of the view (thus revealing the different joins in question)

McNets

10:19 AM

Is ISNULL() function available on SQL-Server 2000?

Tom V

@McNets yes, i think so

McNets

@TomV thanks

hot2use

10:36 AM

sqlbol.com

McNets

@hot2use Thank you

hot2use

Downloaded the BOL for SQL Server 2000 (PDF; 20'500 pages) and had a look.

Yes, ISNULL(check_express, replacement_value) was there at the time.

Ukip me smiling

McNets

11:06 AM

@hot2use Thank you !

dezso

11:31 AM

So you copied the query from another answer, omitting all the formatting. Now what? — dezso 10 secs ago

Colin 't Hart

11:47 AM

Man, a lot of crap in the review queue today.

McNets

12:24 PM

@dezso I can't find your comment...

dezso

@McNets the post was deleted

McNets

@dezso ok

This one: dba.stackexchange.com/questions/176858/… is an update of this one: dba.stackexchange.com/questions/176726/…

I've suggested to the OP to set up a new question instead of edit current one.

Andy K

1:38 PM

not sure if you can help

0

Q: triggers or else between 2 postgres databases hosted on a different datacenter

I'm still new to Postgres so my question. However if you feel it is not the correct place, I'll ask it somewhere else. I'm trying to create a trigger inside of a postgres rds db hosted in Frankfurt that we will call Frankfurt. That trigger will poll tables in a distant postgres rds db hosted in ...

postgresql trigger amazon-rds

McNets

2:29 PM

Should this edit be accepted?

dba.stackexchange.com/posts/176884/revisions

He commented as: Improve grammer

2

Andriy M

Grammar was probably improved (slightly). I also agree with his resetting the formatting, seems more readable this way. I don't know why he removed "Basically", but that's not a big issue. What's more serious is that he introduced two spelling errors. My biggest issue is his changing a "?" to a "???". Why???????

ypercubeᵀᴹ

Rolled back.

Andy K

I'm starting to hate RDS

Joe Obbish

3:09 PM

@sp_BlitzErik This is a great line: "If you stick a goofy subquery in, the optimizer will regard it with contempt and suspicion, and give it a full pat down."

sp_BlitzErik

3:26 PM

@JoeObbish thanks! that was the lowest cost of entry sentence i could find to oversimplify things.

srutzky

3:38 PM

@TomV This is certainly not the first time that I have been considered "unreasonable" ;-) (yes, I know that's not what you were saying). Still, it is an interesting and elusive problem and I enjoy figuring these things out :-).

Lamak

@srutzky I think that was exactly what he was saying ;)

srutzky

3:54 PM

@Lamak 😼 or ;-p

sp_BlitzErik

@srutzky HELLO

srutzky

@sp_BlitzErik 🙈 🙉 🙊 (if those don't show up, they are the See-No-Evil, Hear-No-Evil, Speak-No-Evil monkeys) ;-)

Also, if I forgot to say this, thank you for the kind words you said above :-)

sp_BlitzErik

@srutzky hahaha, i see them! am i a monkey, or evil?

srutzky

@sp_BlitzErik None of the above...I just like posting emojis, and it is kinda my way of saying, "I didn't do it!" ;-)

sp_BlitzErik

oh, you don't have to thank me. i am a humble sponge of your knowledge :)

brb, office hours

srutzky

4:04 PM

@sp_BlitzErik Dog gone it, now I need to thank you again for those kind words (wasn't kidding when I said I don't know how to take a compliment). But seriously, thank you very much. And yes, I do need to thank you (and the others) as nothing needed to be said, but yet you took the time (even if just 1 second) to compliment and that act should be acknowledged.

ypercubeᵀᴹ

@dezso you are in Berlin, right?

srutzky

@swasheck I forgot to say thank you for that, it is quite the compliment :-)

Lamak

@sp_BlitzErik I mean, I think it was pretty clear that you are an evil monkey

2

swasheck

4:41 PM

@srutzky well, i'm glad to hear that.

dezso

@ypercubeᵀᴹ yes, why?

ypercubeᵀᴹ

@dezso I saw an ad by zalando for a database engineer

dezso

5:30 PM

@ypercubeᵀᴹ Postgres and/or Cassandra?

ypercubeᵀᴹ

@dezso yes, exactly

dezso

@ypercubeᵀᴹ are you interested? ;)

ypercubeᵀᴹ

@dezso I was thinking about it.

dezso

5:45 PM

@ypercubeᵀᴹ just tell me when you decide to join

hot2use

any chance this is spammy: dba.stackexchange.com/questions/176918/…

I put up a flag in the review queue. Seemed a bit fishy.

Andriy M

6:03 PM

Someone's probably just learning the ropes. The question needs cleanup and clarification but otherwise I can see nothing untoward about it.

Tom V

@AndriyM I agree, I don't see any spam, just an unclear question

I'm unsure about that edit by John though

Hi there, welcome to the site. It's a bit hard to tell what's actually happening without more information. Could you share the input, the actual powershell script and the truncated output? — Tom V 9 secs ago

Sometowngeek

7:08 PM

Can anyone help with a cross reference issue?

Forrest

cross reference issue?

Max Vernon

depends what you mean by "cross reference" I suppose

Hi @MaxVernon

howdy!

Heated

7:11 PM

My friend is struggling on finding an answer to his issue. He's trying to create a cross reference in the db via C#. Should I post the question here and see if you could help out? Or should I bring him here?

Howdy :)

Forrest

Has he already posted the question on SO?

Sometowngeek

Yeah.

The short summary from his question is he is trying to display a reference from Case A to Case B and he wants Case B to be able to recognize there is a reference from Case A.

ypercubeᵀᴹ

Can you give us the link to the question?

Sometowngeek

Sure

1

Q: How to create a cross reference check?

TLDR: I am trying to display a reference from Case A to Case B and I want Case B to be able to recognize there is a reference from Case A. In my application, I have a section where I connect Cases to each other in order to see their relationships (ex. Brother, Sister and etc.) This section is ca...

c# asp.net-mvc entity-framework

Is anyone familiar with this issue?

Forrest

"This issue" is probably a known problem type, but Case A, Case B, PS-100000, and PS-100002 have no intuitive value to me, so understanding the problem would take a lot of effort.

Sometowngeek

7:25 PM

My buddy just joined this room. Feel free to ask him :)

Kevin Maxwell

@Sometowngeek Thanks

Sometowngeek

Np

@KevinMaxwell Ping Forrest to see what he wants to know and maybe he can help improve your question or find an answer for you.

Kevin Maxwell

Ok

@Forrest Hi, I'm the creator of that post. Is there any question I can answer?

Forrest

A long back and forth about a C# question is probably inappropriate for this chat room. I'll open a new room for the conversation.

Sometowngeek

@Forrest If you want, you could join the C# room?

There's already a room for C# chat.

ypercubeᵀᴹ

7:31 PM

@Sometowngeek I think a separate room is better.

Sometowngeek

Ok

ypercubeᵀᴹ

The general room is usually for casual conversation. Questions can be discussed of course but if they get lengthy, it's better to have a separate room.

Sometowngeek

Didn't know that. Thanks for pointing that out :)

ypercubeᵀᴹ

A benefit is that - even if you can't find the solution now - you can add a link to the room under your posted q at SO, and future readers can see the the discussion, which may help them answer

sp_BlitzErik

@JoeObbish dbfiddle.uk/…

what witchcraft is this?

Tom V

7:50 PM

@Sometowngeek Your buddy still hasn't talked in this room?

srutzky

@JoeObbish Well, with that definition, I can say that I have been able to create a 4-byte "character" in VARCHAR. NVARCHAR is harder to determine given that combining characters just pile on and I am not sure if there is a specific limit. For example, I just created a 606 byte single "character" that includes 300 diacritics ;-)

Tom V

Oh it's kevin

srutzky

A small version of it is (though not sure if it will show up correctly here): u̐̐̐̐ͨ Well, it does look like it works, so let me try something really odd:

sp_BlitzErik

@srutzky what's u smoking?

Forrest

@srutzky That's impressive. I'm going to see if I can break some of my webforms with that...

srutzky

7:53 PM

Here is the 606 byte "character": u̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐‌̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐‌̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐‌̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐̐ͨ

Tom V

@srutzky You broke the heap

sp_BlitzErik

Forrest

Just in time for the bug bounty program!

Tom V

Miss simpson do you fine something funny about the word tromboner?

srutzky

@Forrest bug bounty?

Forrest

7:56 PM

132

Q: Setting Up a HackerOne Security Bug Bounty Program

We at Stack Overflow are interested in setting up a security bug bounty program to begin rewarding users monetarily who report serious security vulnerabilities to us, and we want to know what the community thinks. This program will be run through HackerOne where we are currently testing features ...

discussion featured security

This would be a...diacritical vulnerability

4

srutzky

And yes, combining characters are difficult not only from a UI perspective, but also from a string parsing perspective.

@Forrest Is it really a "security" issue, though?

Sometowngeek

@TomV He's talking in the other room.

srutzky

Maybe the UI team would think so ;-)

sp_BlitzErik

diacritic injection

srutzky

@sp_BlitzErik ;-) nice.

Tom V

7:58 PM

Reminds me of some jokes over at thedailyWTF.com years ago

Joe Obbish

@sp_BlitzErik demo doesn't work

which version of sql server required?

Sometowngeek

@TomV if you're talking about the other room, I just noticed he's not talking there.

I'm trying to get his attention to get him back in the room.

sp_BlitzErik

@JoeObbish vnext or 2016sp1cu2 (whatever lowest 2016 supports the hint)

none of my demos ever work on dbfiddle :'(

srutzky

Here is something I have been toying with (alternate ways of writing my name, in particular, can it be done vertically?):

_

SLͦNͦͫͦ

sp_BlitzErik

that sentence might be two words too long

Forrest

8:05 PM

@srutzky How do you construct those?

Sometowngeek

My buddy is now chatting in that other room.

srutzky

@TomV and @sp_BlitzErik and @JoeObbish and @Forrest : try the following on dbfiddle (and you can try for yourself to copy and paste that single "character" and paste it into SSMS: dbfiddle.uk/…

@Forrest There are a few ways, but I did this one using the following: SELECT N'u' + NCHAR(0x0310) + REPLICATE(NCHAR(0x0310), 300) + NCHAR(0x0368); Also note that I just picked 300 as a random amount, but it could be 3000. Although I just changed it to 3000, reran, copied it, and pasted, at which point SSMS crashed ;-)

sp_BlitzErik

@srutzky that rules

srutzky

Yup, the 3000 diacritic "character" can be copied, but not pasted, at least not into SSMS ;-) Though I can paste it into Notepad++ (yet another reason that is the best editor)

Forrest

That is the nerdiest, coolest thing I've seen today.

Awesome!

srutzky

8:19 PM

@sp_BlitzErik I wonder if I can change my login name from "srutzky" to "SLͦMͦNͦ".

sp_BlitzErik

@srutzky given some of the usernames i see in the stack users tables, yes

the field is nvarchar(40)

srutzky

@sp_BlitzErik Hmmm..is NVARCHAR(40) a limit or a challenge? ;-)

sp_BlitzErik

@srutzky yes

srutzky

Cuz there are funkier things one can do with combining characters than what I have shown here

@sp_BlitzErik Word!

Joe Obbish

9:49 PM

@sp_BlitzErik I'll have to install it

Lamak

@sp_BlitzErik now, who's fault could that be?

sp_BlitzErik

@Lamak the rain

Lamak

that was my first suspect

Joe Obbish

this demo better be amazing

also, did you like your own comment?

2

sp_BlitzErik

@JoeObbish the results of the demo are spelled out in the comments

@JoeObbish no

Joe Obbish

9:57 PM

oh I see now

I changed the theme

I don't trust comments

or people who hack stats by hand

sp_BlitzErik

is untrustworthy on several fronts

Joe Obbish

yes

I fixed your demo: dbfiddle.uk/…

but I don't think that queries can go parallel on that site

I did spent a bunch of hours figuring out how parallel scans work against CCIs

maybe that's related somehow, but I doubt it

need one that doesn't hack stats for it to be meaningful

the query plans have tiny costs and don't estimate any rows being returned

but I'll throw some data into a table later

Master Database

1

Q: Should one open a new question on DBA if a similar question exists on SO which cannot be migrated?

I was about to ask about AWS migration, but found it addressed on SO. That question is more than 60 days old, and therefore it can't be migrated (I believe). Still, it's a DBA-related question, and the answer may change as Amazon adds capabilities. It would be valuable to have it - or a copy o...

discussion migration

sp_BlitzErik

the query costs on mine are high - for the rowstore queries it's >30k, for the cci queries it's 424 serial 575 parallel

the stats hack is because i seem to mess up dbfiddle when i stick a lot of rows in a table

Joe Obbish

10:18 PM

just made a query on sys.objects and sys.schemas 30X faster

sp_BlitzErik

go on

Joe Obbish

should I write a blog post on optimizing your sys.objects queries?

sp_BlitzErik

absolutely, they're probably some of the most common queries

Joe Obbish

11:33 PM

@sp_BlitzErik I made the blog look not as bad

any feedback?

also installing CU3 now

@sp_BlitzErik I often see spools associated with a serial zone in the plan

don't know the exact rules around that

I certainly can't remember seeing a parallel window function

do see the new batch mode window functions go parallel

you can get a parallel plan with the rowstore table like this:

create table #dummy_cci (b int, INDEX IX CLUSTERED COLUMNSTORE);

SELECT SUM(a) OVER ()
FROM dbo.eric_t
LEFT OUTER JOIN #dummy_cci ON 1 = 0
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

looks like Itzik got a parallel window sum: sqlmag.com/site-files/sqlmag.com/files/uploads/2016/02/…

Trying to figure out how. I don't see it in practice very often

you might need a partition by column

SELECT SUM(a) OVER (PARTITION BY a)
FROM dbo.eric_t
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

that goes parallel, though obviously results change

this goes parallel, but with an additional sort: SELECT SUM(a) OVER (PARTITION BY a % 1)
FROM dbo.eric_t
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

forgot that the cool kids don't use 9481 anymore

EBrown

11:55 PM

Serious question: if I use a VARCHAR(64) as a PK, on a table with ~1000 rows or so, and there are NO FK's to this PK, will the performance of it become a concern? Frequent reads, rare writes.

Scratch that, it's NVARCHAR(64) but the same question applies.

Michael Green

@hot2use Vote to close as DUP-licate!

sp_BlitzErik

@JoeObbish can you email me a link? Afk for the evening and don't have to forget tomorrow.

EBrown

I know I'm looking at an excess of 24.8kB of overhead in the PK index (compared to using an INT, for example), but I'm concerned if that will affect SELECT performance a lot / noticeably (when it's always a SELECT *).

Transcript for

The Heap™ – Consultancy ©®