The Heap™ – Consultancy ©®

Joe Obbish

00:10

We used it for OLAP type queries. I remember there being a lot of error handling code...

Joe Obbish

01:31

@ErikE @PaulWhite Submitted the connect item: connect.microsoft.com/SQLServer/feedback/details/3133394/…

ErikE

01:51

@JoeObbish Now it has 2 votes instead of 1!

Joe Obbish

04:03

Thanks, now MS is sure to fix it!

Tom V

06:38

@MichaelGreen or calling dtexec from SQL

Paul White

06:49

@JoeObbish Can you edit it to add some manual line breaks to the description?

Connect sucks like that.

McNets

07:29

Morning

ypercubeᵀᴹ

08:12

What's wrong with your query? — McNets 17 mins ago

@McNets what's not wrong with it ;)

McNets

10:32

@ypercubeᵀᴹ yes, some designs are really nice, dba.stackexchange.com/questions/172580/…

Philᵀᴹ

11:59

@JackDouglas what happened to dbfiddle to make it go down?

Jack Douglas

@Philᵀᴹ our host crashed — we found a bug in bcache (which didn't cause the crash) and we are pretty sure there is another one (which did). Of course our standby host also uses bcache so failover failed.

evilpiepirate.org/git/bcachefs.git/commit/…

I could have got dbfiddle up quicker than I did

Paul White

12:15

@JackDouglas I don't know much about Linux. Is that a core bug or a third-party driver?

Jack Douglas

@PaulWhite it's a kernel driver, so 'core'

Paul White

Ah I think I should have said kernel instead of core. Linux newb.

You won't find bugs like that in Windows 10. </troll>

ypercubeᵀᴹ

Linux is the kernel, technically

Paul White

Is it indeed. Is there one Linux or many flavours? I suppose I could ask Wikipedia...

ypercubeᵀᴹ

@PaulWhite If you refer to the kernel, only one. If you refer to the OS, many.

Jack Douglas

12:18

distros usually stick closely to a single kernel release and just backport bug fixes — RHEL is the exception

they back-port all sorts of stuff

Paul White

Thanks both. I also read the Wikipedia entry.

Jack Douglas

@PaulWhite you are right, it is useful being able to see the source code ;)

Paul White

@JackDouglas Yep can't see the bugs you can't see. Perfect!

Jack Douglas

I was just going to jeagl that but you are too smart :)

ypercubeᵀᴹ

Heh ;) I was reading about live kernel update today. Do Windows have any such capability (or plan to add)?
(meaning: update/apply patches without reboot)

And I'm not trolling, really curious.

Paul White

12:33

@ypercubeᵀᴹ Not my expert area, but my understanding is that does happen where possible yes.

Tom V

@ypercubeᵀᴹ Don't know of any plans but currently not when the files are in use

ypercubeᵀᴹ

thnx

Tom V

File locking can be useful rm -rf vs del *

McNets

Is it called Linux or GNU/Linux?

ypercubeᵀᴹ

@TomV nice

@McNets depends who you ask

Paul White

12:48

GNU/Linux naming controversy

The GNU/Linux naming controversy is a dispute among members of the free and open-source software community over whether to refer to computer operating systems that use a combination of GNU software and the Linux kernel as "GNU/Linux" or "Linux". GNU/Linux is a term promoted by the Free Software Foundation (FSF) and its founder Richard Stallman. They argue that GNU was a longstanding project begun in 1984 to develop a free operating system, and that when the Linux kernel was independently created in 1991, it merely provided a substantial missing piece. Several distributions employ the FSF-endorsed...

McNets

@PaulWhite this is it

If you were/are developers, and you should re-write all production/quality control software, will you consider to use Postgres instead of MS-Sql just to avoid licences?

Development tool is Visual Studio

Paul White

You could use either Postgres, Microsoft SQL Server Developer Edition, or anything else. It rather depends.

I don't think same-stack ideas are as important these days, but certainly some people prefer it.

McNets

@PaulWhite in production.

I have 2 SQL Servers(2005,2012), but I need more licences.

Paul White

Sp buy them? Or rewrite your software to work with something free.

McNets

If we don't acquire SAP/Dynamics, we must rewrite all production software just to made it FDA compliant.

Paul White

13:01

Fun times ahead then.

McNets

Yes, Diazepan days.

ypercubeᵀᴹ

This should probably be a comment. — ypercubeᵀᴹ 8 secs ago

McNets

Useful answer, replace gaps... dba.stackexchange.com/a/172612/110455

Joe Obbish

13:56

@PaulWhite Fixed!

it is puzzling how connect can be as slow as it is

Lamak

@JoeObbish is it really puzzling?

Joe Obbish

yes, I continue to be puzzled

Max Vernon

it runs on MySQL.

Paul White

@JoeObbish Thanks. I do wish they'd fix that. And yes, Connect can be slow, though last night it was fast for some reason. Didn't last.

Is everything really slow today, or am I more impatient than normal?

Tweeted by grrl_geek on May 3, 2017 at 2:03 PM

A well-timed tweet.

I thought about replying to it, but irony.

bluefeet

A bug, squish it

2

Q: Why there is a behavior difference of SQL count(*) vs SQL count(numeric)

I know that count(*) - will returns the total count of all rows including nulls. count(colName) - will returns the total count of all rows in which colName is not null. Today one of my college got an issue with count() in SQL. He was trying to get the count of rows from a view after applying so...

Joe Obbish

14:10

this is a bit of a funny line: "YYYYMMDD is preferred by SQL Server but I consider YYYY-MM-DD to be acceptable"

Also not immediately obvious that's a bug?

Paul White

Answer buried in comments. Poor question, glad it's on SO.

Lamak

@JoeObbish YYYY-MM-DD is unambiguous only for DATE, not DATETIME

for datetime it's only YYYYMMDD

(IIRC)

Joe Obbish

@Lamak Well that's exactly the problem, right?

the answer doesn't cover any of that

Unless there's a hidden setting to run SQL Server in "gordon linoff compatibility mode" that's bad advice

3

Paul White

How unusual.

Max Vernon

@JoeObbish lol

Paul White

14:14

The answer's always going to be the same. The two queries generate different execution plans (because one only needs to test for the existence of rows and one need to find non-null EMargin values). In turn, in one of the plans, a predicate is being pushed "deeper" than in the other, and so a comparison between a datetime and a string value is occurring earlier than some "guard" predicate that prevents inappropriate comparisons from occurring (when the string doesn't contain something date-like) — Damien_The_Unbeliever 31 mins ago

Answers-in-comments-as-a-service.

There really must be something fundamental that causes people to think dates/times are stored as strings. I cannot for the life of me think what that is.

Joe Obbish

@PaulWhite They have single quotes around them! Just like strings

Paul White

That must be it. Doesn't Access use # wrapping?

Joe Obbish

I have successfully avoided using or learning anything about Acess so far in my career.

Perhaps I should avert my eyes for a while

Paul White

I rather like the :: operator in PostgreSQL but I'm not sure it helps much.

People determined to ignore types will always find a way.

Lamak

@JoeObbish you know how it's spelled, liar

Joe Obbish

14:19

You can't prove anything!

2

Lamak

lol

Joe Obbish

@PaulWhite Regarding this one: dba.stackexchange.com/questions/168815/…

My thinking was that because he asked for an explanation instead of a workaround that my comment wasn't a proper answer

If that's incorrect I can turn it into an answer

Paul White

@JoeObbish Well a workaround is arguably an answer. It was certainly useful to the OP and it deserves some (rep) recognition. It won't get that as a comment.

Once the immediate problem has been solved, I'm a big fan of organizing Q & A pages so they offer maximum benefit to future readers. Temporary Post-It notes have a habit of falling off eventually.

When people don't respond to an invitation to convert a comment to an answer, I sometimes move it to a CW answer.

Joe Obbish

@PaulWhite I see. In that case I'll plan to turn it into an answer at some point in the near future.

Paul White

@JoeObbish Don't feel obligated to spend time on it. Copy/paste and a quick tidy would do.

Joe Obbish

14:31

I want my answer to offer maximum benefit to future readers ;)

It would be a few minutes I think, no worries

Paul White

Awesome outcome on that SO question. Four new noise comments on Gordon's answer by the OP and a commentary added to the question.

Yeah, nah.

closes tab

Back to sensible land, this might be of interest if you've ever wondered:

0

A: Scalar Operator in Seek Predicate

What is the purpose of scalar operator in this seek predicate? Internally, the query processor acts on a tree representation, which at a basic level contains a combination of relational and scalar operations. When SQL Server builds showplan output, each operator in the tree (that is enabled...

Joe Obbish

But what can I do if I want to select a TOP 1 that's not a scalar operator?

looks like "not look at the XML" might be the only answer

Paul White

Looking at XML is rarely a good thing.

Joe Obbish

oh wait, that's easy

Paul White

@Joe Thanks for the bounty by the way. I will try to spend it wisely.

Joe Obbish

14:40

<StmtSimple StatementCompId="1" StatementId="1" StatementText="SELECT 1" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />

Paul White

Yeah hah.

ypercubeᵀᴹ

@PaulWhite they are strings - of bits ;)

Joe Obbish

@PaulWhite Thanks for the answer!

I think in this case the "trickle down" theory of economics actually applies

Paul White

@ypercubeᵀᴹ Indeed. And SUBSTRING would agree with you!

SELECT SUBSTRING(0xDEADBEEF, 3, 2);

======
0xBEEF

ypercubeᵀᴹ

@PaulWhite and sometimes they do it themselves. They just need a little nagging.

Paul White

14:50

@ypercubeᵀᴹ Only the good people :)

ypercubeᵀᴹ

0xG00D

dezso

@ypercubeᵀᴹ ALL_SORTS_OF_ERRORS

Joe Obbish

15:32

stream aggregate is a great physical operator imo

Paul White

It speaks very highly of you too. But only in row mode.

Joe Obbish

By that do you mean that you wouldn't expect it to show up in batch mode? Or that when it does it's usually a bad sign?

It's just one of those small pleasures

I thought of the physical implementation that I wanted

and was trying to think of a way to make SQL Server do that

but I looked at the plan more carefully and it was already doing it!

bluefeet

16:30

So much code

0

Q: Formatting results to be used in a graph in Report Builder

I am trying to graphically show the previous 12 months of MTBF by Month in Report Builder. I had the results in the sql query showing the the way I wanted. Example: December-16 January-17 February-17 March-17 Current 39.24 39.55 40.28 42.77 63.21 When I try to put it ...

sql sql-server-2012 pivot union reportbuilder

Joe Obbish

17:22

"how-to-add-hours-to-work-day-in-sql issue"

I try to use SQL to reduce the hours in the work day...

Tom V

19:01

@JoeObbish I refunded your bounty ;)

Joe Obbish

@TomV Thanks! Appreciated.

Tom V

@JoeObbish Nah I liked the Q and your answer too

And Paul's too, but he already got his share of unicorn points on that one

Joe Obbish

I put the bounty on Paul's because at the time his answer had less votes than mine which didn't seem right

Also wanted to try out the system

It is nice to get concrete evidence that answers are helpful or interesting to people, so thank you

Tom V

@JoeObbish I understand, you wanted the badge

/jk

Joe Obbish

yes have to get them all

I've been wanting the score -3 one but you people keep unhelpfully upvoting my posts

3

Unrelated, but cardinality estimate for ORDER BY COL OFFSET 0 ROWS FETCH FIRST 500000 ROWS ONLY; is 8 M in Oracle? ...

Joe Obbish

20:49

@PaulWhite I posted a beautiful, inspiring answer just for you!

McNets

21:51

When you mention some user on one answer, do you use the nickname or the real name, if possible?

Joe Obbish

22:52

@McNets Not sure what you mean with that question. Can you ask it a different way?

McNets

@JoeObbish The answer on this question, I mentioned A. Bertrand and BlitzEric.

But I used @

This question: dba.stackexchange.com/a/172677/110455

Joe Obbish

23:15

@McNets @ is used to notify a user. I don't think that it works in a question or answer, not sure

I would link to their profiles or just omit the @

McNets

23:39

@JoeObbish I don't try to ping them, only thanks their help.

Transcript for

The Heap™ – Consultancy ©®