The Heap™ – Consultancy ©®

6:31 AM

Good morni g

Crappy tappy

Keyboards on mobiles

7:23 AM

Any good Blog on how to parse XML.

I tried this one, but I'm missing a step: brentozar.com/archive/2015/01/query-extended-events-target-xml

Philᵀᴹ

8:23 AM

Morning

The queue needs some love, if anyone is active

Jack Douglas

8:47 AM

Morning. Some Heapers may like to know why we are building TopAnswers.

apologies if you feel I should have told you sooner…

George.Palacios

Morning

@JackDouglas invalid community

George.Palacios

@dezso meanie

Jack Douglas

well, that was stupid

@dezso fixed!

@JackDouglas indeed

Andriy M

9:20 AM

Morning

2 hours later…

11:11 AM

@Johnakahot2use I've given presentations on starting with XML - want me to send you slides?

11:57 AM

@Forrest Thanks. Yes, I would appreciate that.

12:34 PM

Ok - I'll dig them up

12:47 PM

Here they are

I also have a bunch of demo scripts I can email

Heh, first SQL Server presentation I ever gave. I should improve the deck some time.

1:24 PM

@Forrest Have to d/l from home. No access to Google Drive @ work.

@Forrest Thanks.

1 hour later…

Evan Carroll

2:40 PM

meta.stackexchange.com/a/338061/157251

they deleted my proposal. booo

Saw this pattern for the first time yesterday:

INSERT INTO foo (col1, col2) SELECT TOP 1 @p1, @p2 FROM foo

It wouldn't insert data anymore after the table was truncated.

3:00 PM

@JamesL Who would do a thing like that?

Production stuff?

@Johnakahot2use The largest vendor of commercial lending software in the world : )

I'm not sure how they arrived at that.

Sounds like they're living on borrowed time

There was also a similar problem caused by someone doing INSERT INTO foo (col1) SELECT MAX(id)+1 FROM foo

@PaulWhite Thanks, I'll suggest their marketing department use that

Semiotics

Semiotics (also called semiotic studies) is the study of sign process (semiosis), which is any form of activity, conduct, or any process that involves signs, including the production of meaning. A sign is anything that communicates a meaning, that is not the sign itself, to the interpreter of the sign. The meaning can be intentional such as a word uttered with a specific meaning, or unintentional, such as a symptom being a sign of a particular medical condition. Signs can communicate through any of the senses, visual, auditory, tactile, olfactory, or taste. The semiotic tradition explores the study...

3:25 PM

That would be the only purpose to even write it that way, to only insert if there were a row, otherwise, it's equivalent to:

INSERT INTO foo (col1, col2) SELECT @p1, @p2

@CadeRoux Right, they actually wanted what you typed. I think it might have started out as Oracle code with FROM dual and they didn't realize they could remove the FROM clause in the SQL Server version. But that's just a guess.

Yeah, I do miss the explicit FROM DUAL

Why? Are you paid by the character?

George.Palacios

@PaulWhite those 2 seconds add up

3:42 PM

Lol, no but I confuse myself sometimes coming back to read things when there is no table.

I had a similar funny construct that tripped me up yesterday so I had to add a huge comment for the future

FROM [AscendCV].dbo.[Study] src_Study
INNER JOIN [AscendCV].dbo.[StudyStatus] src_StudyStatus
    ON src_StudyStatus.ID = 2 -- ALL Studies have a start - there is no join to study's current state

It's just a cross-join to generate a start for all studies, and to be able to pull the Label column for starts from the Status table.

@CadeRoux is this better than a cross join with the appropriate WHERE clause?

It's equivalent, I can't imagine the performance is different. It was probably copy pasted from another section in a different part of the UNION

It would be better written as a CROSS JOIN because I almost never need CROSS JOIN, so it wouldn't blend into the other INNER JOINs.

Andriy M

3:57 PM

I agree it's a little misleading. Although it would be an entirely different story if it was a left join and you needed to guarantee at least one row. Can't think of a practical example where that would be needed but still

Most explicit would be just putting the StudyStatus.Label in a scalar value subquery

But subqueries are always such a smell. I'd like packages with constants in future SQL Server. That 2 bugs me as well.

4:14 PM

I have to cross post - the answer was wrong! — Data Science 1 hour ago

guess who gave that incorrect answer

A: Create database level constants (enumerations) without using CLR?

Aug 9 '17 at 5:31, by Paul White

@JoeObbish SQL Server: Variables, Parameters or Literals? Or… Constants?

also

You can create an enumeration type in SQL Server using a XML Schema. For example Colors. create xml schema collection ColorsEnum as ' <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Color"> <xs:simpleType> <xs:restriction base="xs:string"> ...

Philᵀᴹ

"You have no more close votes today; come back in 7 hours.". FFS

@EvanCarroll what was it?

@PaulWhite I should consider the single row view for the future, it's easy enough

yes it's a nice workaround

4:29 PM

If the overheads are that low

they are

Evan Carroll

@Philᵀᴹ the Bye Felecia hat, which you get if if you disagree with the CoC to the extent that you get suspended, or lose your diamond/moderator privileges; and/or, you otherwise out yourself as a transphobe.

Philᵀᴹ

5:23 PM

Oh, they didn't get your humour then?

1 hour later…

6:44 PM

@Marian Alea iacta est

7:01 PM

Also our only Socrates member is going to get a ton of extra rep

7:33 PM

@Forrest You had me laughing with the first slide. 11/10, would attend presentation again

7:49 PM

@Forrest when you say 'shredding in sql server don't do it' you suggest pre parsing in powershell or python or something like that?

Yeah...a pretty nebulous elsewhere since SQL Server really isn't the best at XML

Lamak

where are the puns in that presentation?

Spoken, unfortunately

Lamak

is that true, @billinkc?

@Forrest also not sqlclr?

7:57 PM

afaik, SQLCLR works great for XML shredding

Honest question. I was fortunate enough to avoid complex xml in sql and had the option to manage it in the app side one way or another

We implemented CLR parsing for our xml processing as we had large documents (service broker message) and the native methods forced use to walk the tree multiple times. We implemented a one pass message using C#

8:17 PM

sounds like Bill knows what he's talking about

(as opposed to me, who has done very little with XML besides shredding query plans)

@Forrest That might be the first time that statement has ever been made

You found my claim off-kilter