The Heap™ – Consultancy ©® - 2018-02-22 (page 1 of 2)

8:05 AM

Good morning.

Andy K

morning and evening

8:22 AM

Morning

9:00 AM

Morning

Andy K

9:15 AM

question on Postgres

is is possible to run one liners sql command through psql ? like that

psql -optiions 'CREATE SCHEMA toto;'

psql -c "drop table message;"

9:46 AM

Should be possible according to the manual.

dezso

@AndyK this is exactly how you do it

@ypercubeᵀᴹ I am wondering if they managed to do it or were fired by the manager

Andy K

did a script using powershell to parse the file and replace some characters in it

12:03 PM

morning

12:38 PM

@PaulWhite Ah, is it Friday?

@TomV Yes indeed!

Q: Inline Code Spans should not be used for emphasis, right?

12:49 PM

99

When reviewing Suggested Edits on Stack Overflow, I often come across "fixed formatting" suggestions that use inline code spans to place emphasis on certain keywords, but isn't actual code. For example: I am having a difficult time with a background task in iOS. The problem I seem to be faci...

@hot2use Have you seen this? ^

@PaulWhite Nasty me.

But then again, I inline the whole code output.

Bad?

I was thinking of it in relation to your closed meta question about formatting.

I know

It's an example of a good single-issue post that has achieved consensus.

Personally, I think that's enough to revert any non-code formatting use of backticks here, but I suppose we could ask on our local meta.

@hot2use If I understand you correctly, no, of course block-code formatting is best for whole scripts. People misusing backtick format is the issue I had in mind.

But his example is actually wrong IMHO. He didn't emphasise code in the quote, but product names (iOS) and OS features (background tasks).

@PaulWhite I agree with you on the backtick abuse.

12:58 PM

@hot2use The example is of incorrect formatting.

> This seems like an invalid use of an inline code span

Did you read the answers as well?

I haven't forgotten the meta questions. Still on my backlog. Im mulling ideas on how to title them (Style Guide: ...) and how to go about separating the individual formatting options.

Yes, even the downvoted (-31) one. :-)

Unless there are some fine points I may be missing that need clarifying, that Meta Q&A seems conclusive enough on the issue, no need for reiteration on our meta.

But to state that backticking words is an abuse, it might be better to have a Style Guide to go with, so that you can point people in the right direction and let them know what we as a community tolerate.

@AndriyM @hot2use Check this out: dba.stackexchange.com/a/198358

@hot2use I suspect a Style Guide might be too ambitious, and restrictive. People should have a certain amount of freedom (e.g. code layout within reason, actual English versus American English etc).

We can ask about specific misuses to see how people feel about it.

@PaulWhite Well, that's one of the top (ab)users of backtick formatting on DBA.SE, I'm not surprised.

1:03 PM

@PaulWhite Nice.

That being one of the reasons I brought up my question on Meta

@AndriyM The funny thing is he has stopped doing it in edits AFAICS.

@hot2use Yep I know. Thought it was a particularly egregious example :)

@PaulWhite But it sucks, because he doesn't consistently backtick MongoDB.

To me, product names are not code!

@PaulWhite I think he was called out on that by someone in a comment on one of the posts he did that to.

> When it comes to Product Names, Vendor Names, Product Features or other non-code elements I tend to cringe when finding these in revisions and will reset them if not too much fuss.

From my meta question

1:09 PM

@hot2use Your comment above read to me like you were thinking MongoDB should be code formatted.

I was about to shoot you.

@AndriyM Yes

Jesus Christ

@PaulWhite Sorry :-(

A: SQL Server MCSA Certification

0

As Per MSDN BOL Here before attempting the (Exam 70-764 & Exam 70-765) you have to possess foundational IT skills . If you feel that you do not possess these skills, consider pursuing one or more Microsoft Technology Associate (MTA) certification. But for Completion of MCSA: SQL 2016 Database A...

At least the formatting doesn't one-box!

hahaha

Hands up. Who would like to format the question? And who would like to format the answer?

okay

Curiously, I can't find anything of the kind in e.g. this answer of his (exactly one year ago). I don't know why but it looks to me as if he's intentionally abusing the formatting in his own posts after being reprimanded for doing so in his edits to others.

@PaulWhite Want to pop that comment into his other answer (MongoDB) too?

1:15 PM

Of course he could've started doing that in his posts first, then thought it looked "better" before going on to edit other people's posts likewise

I clicked edit on his answer and he links with <A href="">

It just gives me burning eyes when I look at it for too long.

yikes

@PaulWhite Ah you can't because Q is closed.

ah ok you finished your edit before me @PaulWhite

1:20 PM

@TomV Ask me if I had fun.

@TomV Hey, that's markupism!

Markdown links, sanity has been restored

@PaulWhite Did you enjoy it?

@TomV ~~NO!~~

I was enjoying it, felt a bit like a puzzle

I don't mind html links if that's what people choose to use, but the result has to look reasonable. When I'm fixing stuff I will use markdown because it's more familiar to me.

@PaulWhite♦, Thanks for information. — Md Haidar Ali Khan 1 min ago

Hopefully that will be that.

The one grey area for me is column names.

Some people backtick them, some don't.

And I'm OK with that :)

1:27 PM

If only they stopped using grey background for inline code...

But that's another issue with no consensus

I cleaned up the MongoDB answer.

@AndriyM I lost that argument spectacularly as I recall :)

@hot2use Ta

@PaulWhite I remember Jack argued against it.

A: Should we change the formatting of backtick markdown elements?

24

No - it is just fine as it is.

@AndriyM He was probably right.

Usually is.

Oh, the No was accepted eventually. I stopped following at some point, didn't know that.

1:32 PM

@AndriyM I accepted it because the votes on the no answers were much higher than the yes.

I did leave it for a decent time.

@PaulWhite 4 votes isn't what I would call much

@TomV The 24 and 20 answers are both saying no. This one says yes, score 12 dba.meta.stackexchange.com/a/2541

@PaulWhite That's one way to look at it, I see the 20 votes one as "we shouldn't leave it as is but explore other options instead of removing the background"

In fairness, it shouldn't count as either yes or no then.

The point is, there were far fewer people who agreed to just remove the background completely

Exactly, the question was yes or no. Proposing a different solution would be a separate meta.

I saw it as yes:12, no: 24.

Do something else: 20.

and it was discussion not feature-request anyway.

3:06 PM

how many banana types are there?

@JoeObbish Six

that's what I got out of the meta post

that's more than I expected

@JoeObbish Oh really, I just answered the first number that came to mind, I have no idea

and to think that you're a TRUSTED user...

Which meta post?

3:13 PM

The one about bananas obviously.

Oh, I see now

Cool.

Only I can't see how Joe could get six banana types out of it.

Well Tom supplied the six tbf

nevermind

3:18 PM

@TomV This one dba.meta.stackexchange.com/q/2540

Glad to see I'm not the only one slow today :)

I think we all got patched for spectre/meltdown

what's the preferred data type for BananaType?

is TINYINT too aggressive?

recently I had to go back to caring about data types. it was a big of a rough transition

Well I would think 255 should be enough but with row compression, might as well int/bigint it.

Not that anyone's using row store these days

Banana types are hard

List of banana cultivars

The following is a list of banana cultivars and the groups into which they are classified. Almost all modern cultivated varieties (cultivars) of edible bananas and plantains are hybrids and polyploids of two wild, seeded banana species, Musa acuminata and Musa balbisiana. Cultivated bananas are almost always seedless (parthenocarpic) and hence sterile, so they are propagated vegetatively. They are classified into groups according to a genome-based system introduced by Ernest Cheesman, Norman Simmonds, and Ken Shepherd, which indicates the degree of genetic inheritance from the two wild parents...

3:20 PM

I should have tagged it database-design

more like most people aren't using rowstore compression, which I've always found to be weird

Whether tinyint is too aggressive may depend on the phase of the mood.

should be on by default

I'm still a bit suspicious of row compression personally

still haven't used it in shipped code

Bananatype should allow NULL

3:22 PM

also, using the internet with 50% packet loss is fun

will probably start using it soon

for unorthodox reasons

@JoeObbish All my consultant buddies tell me it is all win with no downside

And they're Trusted Users to me

@PaulWhite Surprisingly I had some success with it in a DWH project

never trust a consultant

@TomV At least it shouldn't aggressively disallow it.

Awesome for all common data types and Unicode compression is cool

Page compression is pretty much useless IME

Important typo

3:24 PM

you're being serious (hard to tell right now)?

I am fully serious.

No doubt page compression is a great thing for some people, just not me.

@PaulWhite Why not?

@TomV Extra compression above row is minimal, much more overhead and CPU.

I like the idea of making row compression default now all editions have it.

I used page compression just the other day to mitigate an important bottleneck, but it was a very unorthodox situation

Default where the row size wouldn't exceed the limit anyway.

@JoeObbish I fell over reading that.

3:30 PM

yes, I know that it's shocking

anyway, now I feel better about wanting to use row compression in more places

@PaulWhite I disagree

It's probably true that any table that would benefit from page compression ought to be column oriented.

@TomV Ok. I did say "in my experience".

EXEC sp_estimate_data_compression_savings 'dbo', 'sometable', NULL, NULL, 'ROW' ;
Sample size with requested compression: 19032
EXEC sp_estimate_data_compression_savings 'dbo', 'sometable', NULL, NULL, 'PAGE' ;
Sample size with requested compression: 6696

@PaulWhite I'm on 2008 R2

@JoeObbish You've upgraded?

3:34 PM

Anyone here used the ApexSQL Script tool before?

WTF

2

I'm not arguing the overhead, but the space savings in my experience aren't "minimal"

Refuse to answer, no blue feet

@PaulWhite so harsh

silence

3:34 PM

@TomV I'm right there with you, but it depends on your data

Paul did say that it was limited to his experience

:(

I used Apex for a while. I don't any more.

Imagine storing BananaType as an NVARCHAR(100)

Now you benefit from page compression!

Certainly it depends on the data, and some people will only care about the compression, but if so they might need columnstore or compress.

@PaulWhite okie

and I apologize for the shocking change :P

6

3:36 PM

@PaulWhite One exception is if you actually get compression across different columns

Of course, some would be suspicious of such a data model

There's always an exception

@PaulWhite Not always

@Taryn Official policy to use real names now?

@JoeObbish That's the exception

@PaulWhite naw, just felt like a change.

Who would use their real names on the internet?

3:38 PM

Or faces

@Taryn But now your avatar makes no sense

@PaulWhite gotta keep something the same

Well you wouldn't want to shock people

Wouldn't want to be completely unrecognizable

There is that

bluefeet might come back

3:41 PM

Well we're used to Taryn now so

lol

Have you ever thought about changing the color?

at least you adapt quickly

@JoeObbish they will always be blue

@Taryn I kept my chilli

Ha. Just found a table where I found page compression was worth it. Large table, mostly append-only.

I'd probably still CS it these days.

@JoeObbish Didn't you say you found a table where CS was less compressed than RS?

3:49 PM

@PaulWhite I didn't just say it, I did it!

might blog about it tonight

Well I might read about it if you do

Stay inside @TomV: Brussels lockdown: Belgian police seal part of capital in major security operation

@PaulWhite you are in the future, you can read it now

I'll be sure to look for a single page view from NZ

but he represents the whole country

3:51 PM

@Lamak Unfortunately, Joe is in the past (like 2008 R2)

that's way in the past

It's the unorthodox past

it's still supported by MS!

not in NZ

@JoeObbish Just give it some time... I'm counting down the days...

3:57 PM

@SeanGallardy-Microsoft now hold on here

checkmate atheists

If it was a bad product, then why would MS support it?

It wasn't a bad product, it was a great product.... 8 years ago

Using that logic, why do we use machines if hand tools were fine for millennia?

I'll devil's advocate all day if I could - it's one of my favorite things.

@SeanGallardy-Microsoft laziness

the greatest motivator of all time

I can commiserate, I'm ~~lazy~~ efficient

@hot2use Thanks hadn't heard of it. I'm not near Brussels fortunately

@SeanGallardy-Microsoft you can't prove that

4:04 PM

SQL Server 7.0 was an awesome product

the release notes (?) for it were awesome

Mainstream support for 2008 R2 ended 7/8/2014. Which may be 7 August or 8 July.

3

it'll be supported for forever in our hearts

Not by me :)

2012 feels old right now.

those blog comments are great

4:09 PM

> I have a sting ‘101|1|1|2|3|4|5|6|7|8|9|10##102|1|1|2|3|4|5|6|7|8|9|10’ want to split in row column values like split with ‘##’ for rows and | for columns

Sounds painful

@SeanGallardy-Microsoft With a terrible name.

Actually, what did R2 give us over 2008?

Some BI stuff?

pfff BI, what a waste of time

Oxymoron eh

Jul 29 '13 at 14:03, by ConcernedOfTunbridgeWells

I never get tired of 'business intelligence is an oxymoron' jokes ;-}

;)

From 2013!

I love that word

4:16 PM

Noun: oxymoron (plural oxymorons or oxymora)

A figure of speech in which two words or phrases with opposing meanings are used together intentionally for effect.
(loosely) A contradiction in terms.

όχιmoron

ypercubeᵀᴹ

Paradoxically, although the word is composed of two Greek terms, it first appeared in Latin!

@PaulWhite οξύμωρον

Those crazy Romans

@ypercubeᵀᴹ So much more squiggly in proper Greek.

όχι is the only Greek word I know.

I remember "adopting" that word in 2009....back when "savethewords.org" was alive

Pinging @bluefeet for old times' sake.

ypercubeᵀᴹ

4:22 PM

@PaulWhite oxy- has a different root than όχι

although they look similar written that way

Yes the similar look was the whole depth of my point

Reply from @bluefeet: TTL expired in transit.

3

:(

It worked!

@PaulWhite wait, what?

why is @bluefeet @Taryn now?

1 hour ago, by Andriy M

Glad to see I'm not the only one slow today :)

4:25 PM

46 mins ago, by Taryn

@PaulWhite naw, just felt like a change.

Lamak the dot matrix printer

@PaulWhite you should get back to sqlkiwi

I have considered it from time to time

I can be Kamal...

@Lamak who's Kamal?

4:29 PM

@Lamak DO IT!

That would be quite a shock @Lamak

@JoeObbish Lamak is a pseudonym

@Taryn this will severely reduce the "lamark" replies I get constantly

And the Lexmark "jokes"

@JoeObbish a BI guy...

@PaulWhite oh, those were jokes?

No hence quotes

4:30 PM

@Lamak maybe you need a change

She's like a virus

blame the feet

@PaulWhite this sounds like a trick

@JoeObbish His real name is Kamal.

@Taryn maybe....I do think that "Kamal" would be quite a used username

4:32 PM

@Lamak true

@PaulWhite yup...first name "Kamal", middle name "Felipe".

you could consider "bluefeet"

I hear that one is available now

@Lamak you could use Kamal_lamaK

wow, that packet loss

Kamal is quite an unused name in Chile....and I didn't know I was named like that till I was in school....my parents always called me "Felipe" or a nickname from it

4:33 PM

@JoeObbish not really stackoverflow.com/…

imposters

they barely have any rep

(removed) would be a good user name

@PaulWhite stackoverflow.com/…

I keep expecting Sean to become SeanGallardy-Microsoft-CU1

2

@Taryn Parentheses not allowed I guess

4:50 PM

@hot2use false alarm it seens

5:17 PM

@PaulWhite or (twice removed)

5:44 PM

@PaulWhite agreed on that point.

@JoeObbish Why you gotta talk about me like I'm not here ;)

@PaulWhite I like it... maybe I will :) but weren't we on CU4 now?

@SeanGallardy-Microsoft speaking of

remember that undocumented operator?

did the developer finally get back to you?

@JoeObbish I do not. If it's the thing @sp_BlitzErik is also about, then yes she did get back to me because it isn't working as intended. I looked in TFS for that one and there is a defect for it set to be worked on.

foreign key operator

oh that one

let me check

how many undocumented operators are there???

5:51 PM

I don't know, it's undocumented

boooooo

Looks like it's been reported but the report shows the operator in the plan

so... shoulder shrugs.

Q: What is a "Partial Matching Index"?

25

I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can be seen by deleting a row from a parent table wit...

sql-server index sql-server-2016 foreign-key

that one

Yes, AFAIK it does show up in query plans but I haven't tried it recently

You're looking for this? I just wrote this...

No, I want to know what the details of the operator mean and to see a repro for it

I can make the operator appear

6:05 PM

Whatever is in the answer is what they told me, I haven't had any updates. You could send Joe an email ;) He owns that...

booooooo

If you can make the operator appear, you have a repro

No I don't!

explain

Maybe I will file a Connect Item

It's in the question. I don't know how to explain it in a better way...

Want to see Partial Matching Indexes Count <> 0

6:07 PM

ahh

I remember looking at the logic for that now

but I can't remember what it was

lol

you posted an answer

but it seems bugged

I'm going to assume that it's bugged

you should just write a patch to fix it

You don't want me writing that patch

I'd remove it all and put: return 1

Is that the usual practice?

6:22 PM

@AndriyM Negative

Very well

6:56 PM

Precon is done. Now everyone knows as little about sql as I do.

I met wbob!

4

He's a real boy.

@sp_BlitzErik did he heckle you?

Also poidh

@TomV no, I think he's the most polite Bob on the entire island

7:15 PM

@sp_BlitzErik Did they fix the audio issues?

@PaulWhite that's just how he sounds, dude

@sp_BlitzErik does that mean you're back to bother us? :P

If you’re coming to our #SQLbits pre-con tomorrow, GET THERE *EARLY* AND GET IN THE FRONT ROWS. The venue’s audio isn’t working.

Tweeted by BrentO on February 21, 2018 at 10:05 AM

@PaulWhite should say "front records"

@SeanGallardy-Microsoft Well I'll be on CU3 for a few more days to give a chance for CU4 symbols to make it out.

@Lamak Good point.

Interesting feature switch of the day: FConcurrentSerialZoneEnabled

7:44 PM

@PaulWhite 5 tweets away from 100000

Public service announcement: @SQLBob is Erik Darling’s new hero. That is all.

Tweeted by BrentO on February 22, 2018 at 11:00 AM

Is that wbob?

@PaulWhite yeah, bit of a tough morning, but it got worked out

@TomV No twitter.com/SQLBob

@Taryn for a bit, yeah - here until Sunday though

@TomV no, different Bob - my laptop charger had a meltdown (quite literally) when I plugged it in here

@sp_BlitzErik Cool. No fun shouting all day. BTW I added a section to part 2 after you got the preview (published now)

7:57 PM

@hot2use turned out to be a drunk Polish guy... As if there are any other

@PaulWhite not sure what you're talking about but I'm happy to click a link of yours any day 🤐

@sp_BlitzErik nice

Also thanks for the tourism recs!