The Heap™ – Consultancy ©®

James Anderson

8:37 AM

@JackDouglas Nice! Are all the instances of each RDBMS installed on 1 machine or are you using VMs\containers?

Philᵀᴹ

@JackDouglas I like the fact that conflict/negativity has ended up as a load of awesomeness

Jack Douglas

8:48 AM

@JamesAnderson it's one VM per backend. I'm currently trying to get my head round ASP.NET — I'm coming round to the idea of Windows VMs for SQL Server with IIS+whatever for the API (I'm not sure what 'whatever' will be though!)

so the are three postgres VMs so far for example

James Anderson

9:06 AM

@JackDouglas Cool. You might get more RDBMS instances\versions per physical box with containers but I'm not sure if the added complexity would be worth it.

Jack Douglas

exactly — I don't have that scale I think

Tom V

9:27 AM

@JackDouglas This one seems to work on rextester but not on dbfiddle

The query runs fine when tested on my 2014 instance from management studio, but I can't connect to a vnext right now to test it

Just thought I'd let you know

dezso

@TomV how's your train wreck?

James Anderson

@TomV It works on my vNext CTP 1.3

Tom V

@dezso Its being "managed"

Paul White

@JamesAnderson on *nix?

James Anderson

@TomV It works on dbfiddle if you remove the GO statements.

@PaulWhite yep

Paul White

9:39 AM

Ah well GO is the SSMS batch separator.

Some scripts will require separate batches so it's important DBfiddle matches SQLfiddle etc. in that respect.

Jack Douglas

9:55 AM

@TomV does rextester only show the result of the last query or is there a way to get the output of all the selects in the script?

@JamesAnderson thanks for checking that :)

Tom V

@JackDouglas I got them from this answer

The same happens with every query of that answer, returns as documented, fails on dbfiddle with a syntax error

Jack Douglas

21 mins ago, by James Anderson

@TomV It works on dbfiddle if you remove the GO statements.

@TomV doesn't removing the GO statements fix it for you?

DBFiddle treats each field as a separate batch, so doesn't use a batch separator

Tom V

@JackDouglas Yes it works when removing the GO statements

And those fields are batches inside the same transaction then?

Jack Douglas

no I don't think they are — but perhaps they should be?

My SQL Server knowledge is lacking

Tom V

I'm not sure if we need compatibility with rextester/sqlfiddle, but I just searched the site for rextester and tried some complex-looking queries

Jack Douglas

10:04 AM

I could strip the final 'GO' from each field behind the scenes — I do that with the final ';' on Oracle. Not sure if I'd be storing up trouble of some sort later on though

this is a learning curve for me :)

James Anderson

I think that DBFiddle should recognise the GO statement and separate the queries above and below it into separate batches in the same way SSMS does.

The current workaround would be to manually place each query in the separate query boxes that you have implemented.

Paul White

It would certainly be convenient to have GO work in the familiar way, but for me it would be OK if each input field were a separate batch, so long as that is made clear in the UI somewhere.

@JackDouglas That would be confusing. Some statements have to be in a separate batch. If someone met this requirement using GO, but you silently strip it out, the user would get a confusing error message.

Jack Douglas

10:23 AM

@PaulWhite thanks I will not do that then :)

Paul White

@JackDouglas Can I ask what the reasoning was behind the separate input boxes? Are they intended to be separate batches or something else? One result per field?

Jack Douglas

@PaulWhite yes, one result per field. There were a few edge cases on SQL Fiddle where the statement/batch separation didn't work, and I am trying to avoid that.

doing proper parsing of the SQL is something I'm keen to avoid!

@PaulWhite Is this explicit enough?

Mikael Eriksson

11:09 AM

@JackDouglas I tried doing some XML queries on dbfiddle and to make those work you need to first change some settings.

set ansi_nulls on;
set quoted_identifier on;
set concat_null_yields_null on;
set ansi_warnings on;
set ansi_padding on;

Not sure what you want for default for those but I think you should be able to change it in the connection.

Others that know more of those things please chime in here.

This works dbfiddle.uk/… and this does not dbfiddle.uk/…

Paul White

11:24 AM

@JackDouglas Yes.

ypercubeᵀᴹ

Removing GO removes the option of using GO <n> (number). Not sure how often this is used

@JackDouglas I think rextester shows results from all selects

Paul White

@JackDouglas The settings Mikael refers to above are normally set to the recommended settings automatically by modern drivers. It seems you might be using one that does not. You may be able to set this globally somewhere, but if not, the complete set is:

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

Also, at some point, people are going to be keen to see messages returned from the server (as well as results), for example the output produced by SET STATISTICS IO. Not to mention execution plans :)

Paul White

11:45 AM

One other thing: why does the Go button stay greyed out after execution? It means I have to edit the script before I can run it again. Rate limit it by all means, but forcing an edit to simply rerun a script seems wrong.

hot2use

@PaulWhite Shouldn't it be ARITHABORT OFF because of Slow in Application, fast in SSMS? (by Erland Somarskog)

Paul White

@hot2use Only if you're going to be connecting to the hidden DBFiddle databases from your application and don't want to see different performance due to mismatched cache keys :) Seriously, no. The ARITHABORT setting has no impact due to the setting of ANSI_WARNINGS so it doesn't really matter, but setting it as I said is most compatible with SSMS and the required SET options for various features.

Practically speaking, all people will care about is that a brand new DBfiddle session will allow them to use indexed views, computed column indexes, filtered indexes, XML data type methods, spatial indexes...etc.

Tom V

2:01 PM

@JackDouglas any reason you are not showing the non-results output

I mean it could be useful to have the '1 row affected' message in this one dbfiddle.uk/…

Paul White

3 hours ago, by Paul White

Also, at some point, people are going to be keen to see messages returned from the server (as well as results), for example the output produced by SET STATISTICS IO. Not to mention execution plans :)

Tom V

Brain does not cooperate today

Paul White

2:18 PM

The number of rows affected message is another good example.

Joe Obbish

2:40 PM

@ypercubeᵀᴹ @PaulWhite I posted that question that we discussed over the weekend. You should add your answers at some point. @AndriyM you were interested in this too. I think I figured it out but who can be sure?

0

Q: Why does my SELECT DISTINCT TOP N query scan the entire table?

I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two alternating values. I'll use the GetNums function to generate the data: DROP TABLE IF EXISTS X_2_DIS...

JzInqXc9Dg

anyone have any idea why log shipping might just randomly fail?

Aaron Bertrand

Usually there is some information in a log somewhere that describes what "fail" means

JzInqXc9Dg

i mean, the problem we always get is suddenly we'll have a gap in our logs

and the restore will say "log file too recent..."

i just am trying to figure out the root cause. like if it's a network hiccup or something (during the copy of a new log file over to the secondary server)

Aaron Bertrand

That sounds like bad logic in your log shipping job.

JzInqXc9Dg

just curious what other people have encountered

i mean, it happens every 2-4 months i'd say

Aaron Bertrand

2:50 PM

Or that a file copy failed / was abandoned

JzInqXc9Dg

thats what i think

need to prove it tho

Tom V

@JzInqXc9Dg By having someone confirm your guess in some chat box?

JzInqXc9Dg

precisely

Aaron Bertrand

/oy

Tom V

Did it happen recently?

JzInqXc9Dg

2:53 PM

friday

Tom V

have a look at every log you can think of

JzInqXc9Dg

we have a database that logs all our sql agent jobs, i am writing some queries

i'd like to see if the backup failed or the copy failed

Tom V

Should be in the error log on one of the sides

or the event log too

Jack Douglas

@MikaelEriksson I've set the normal defaults as per Paul's comment and now the second example works

JzInqXc9Dg

yea looking at the event log...it's weird. on the secondary server it shows "file not found" trying to restore a log file. this is just after midnight on the 18th. but it shows the log file its trying to restore was from the 15th

Tom V

3:09 PM

does the event log on primary say "backup succeeded"?

JzInqXc9Dg

yes

what i see in the log shipping status standard SQL report on the secondary

is last restored file of 2017 02 17 0700

in the event viewer on the secondary i see an error event at 12:20AM today saying the system can't find file 2017 02 17 0615

which i the previous file to what is showing as the last restored

i think we ran of out free space on the disk

and a backop of a log failed

Tom V

3:26 PM

Microsoft providing PHP sample code

They created an entire visual studio project, with an App.config etc, to create a Program.cs file which needs 31 lines of code to execute php.exe program.php

JzInqXc9Dg

haha

sounds about right

Philᵀᴹ

4:00 PM

Wow, the review queue is empty

Jack Douglas

4:14 PM

@PaulWhite I don't understand — if you haven't changed it, why do you need to re-run it?

@PaulWhite one result per field doesn't really make sense for SQL Server, I think it makes sense to have one field per batch and multiple results.

Paul White

@JackDouglas The query might be non-deterministic.

Jack Douglas

ha, good point

Paul White

@JackDouglas Or one field with GO separators :)

Jack Douglas

Yes, that works too

Paul White

Batch mode columnstore is so cool. Ten million rows scanned and aggregated in 11ms on my laptop.

@JoeObbish Thanks. The OPTIMIZE FOR solution is neater than using ROWCOUNT.

Andriy M

4:27 PM

@JoeObbish I was indeed, thanks!

ypercubeᵀᴹ

@PaulWhite if you have time to check and you feel anything is wrong or needs correction in my answer, please tell me.

I think the ROW_NUMBER could use ORDER BY unique_key in the 2nd query but not sure if that would make any difference.

Paul White

@ypercubeᵀᴹ I'll look at it next. Shame we can't have an index on VAL.

ypercubeᵀᴹ

@PaulWhite Yes. My assumption from yesterday's discussion was there was no index on that.

I suppose it might make a whole new question (or section in this one) if we add that possibility

Paul White

That's the scenario where the recursive skip-scan really flies.

Jack Douglas

@ypercubeᵀᴹ this one doesn't seem to: rextester.com/UVZE87903

ypercubeᵀᴹ

4:34 PM

@JackDouglas I think all the selects have SELECT INTO except the last one.

Jack Douglas

oh yeah!

thanks :)

Paul White

@ypercubeᵀᴹ It's not really a skip scan without an index, more like a repeated partial scan. The test data distribution is rather unfortunate for your query. For 10 rows it ran for just over 2 minutes on my laptop.

Jack Douglas

4:49 PM

@PaulWhite Now, if you visit by a link, it'll show you the cached result for the last run, but it you press 'Run' it'll force it back to the database. Does that sound sensible?

Paul White

@JackDouglas Yes.

ypercubeᵀᴹ

5:25 PM

@PaulWhite I assume that if some values are rare or if there are less than 10 distinct, it would do a table scan.

Or does it do multiple scans?

Paul White

@ypercubeᵀᴹ Multiple scans.

ypercubeᵀᴹ

well that's not good

Paul White

It would be OK if the values were evenly distributed.

ypercubeᵀᴹ

I wonder if there is a way to do something similar to the CI version, when the table is a heap

Paul White

@ypercubeᵀᴹ Hm? What do you mean?

Oh. No, not really.

There is a row locator, but you can't seek on it.

ypercubeᵀᴹ

5:59 PM

@PaulWhite You mean my second query does multiple scans too? It assumes a clustered index on (unique_key)

Ah sorry. You mean there is no way to use the row locator in heaps

Pity

Philᵀᴹ

6:40 PM

A million tables! dba.stackexchange.com/questions/164911/…

ypercubeᵀᴹ

7:43 PM

Facepalm

@ypercubeᵀᴹ I don't have that. I will try it now. — Robusto 3 mins ago

I think we could kick that to SO or close as too localized.

Jack Douglas

8:08 PM

@TomV if you paste a script DBFiddle will now split it into batches. I think that's the right default though I'll have to add the option to disable it — what do you think?

jcolebrand

@JackDouglas What if we did two things with the page?

Make the textbox a lot larger (you may have already done this) and even resizeable

And add a button for "add another textbox" which could just show the ones you already have and they can just be hidden to start

Jack Douglas

@jcolebrand they resize themselves as you type/paste

jcolebrand

to be fair I should look at it again

link me?

Jack Douglas

dbfiddle.uk

Andriy M

Is SQL Server the default or did it just remember my last choice (which I myself don't remember)?

Jack Douglas

8:20 PM

@AndriyM I've made it the default for now

@jcolebrand also dbfiddle.uk/…

Andriy M

I see

Jack Douglas

MySQL should really be the default because it will get the most traffic

jcolebrand

@JackDouglas needs a way to remove inputs as well

you have the plus, but there should be a minus

Jack Douglas

@jcolebrand leave them blank and press 'run'

jcolebrand

shhhh

don't be logical

Jack Douglas

8:21 PM

but yes, perhaps a 'delete' button might be better

@jcolebrand thanks for playing with it

Andriy M

This works:

SELECT 1
GO
SELECT 'a'
GO
SELECT CURRENT_TIMESTAMP

jcolebrand

@JackDouglas I'm thinking a couple tweaks

You keep sucking the width back down programmatically, and that's not fun

Andriy M

@JackDouglas Are you parsing the entire syntax or just looking for standalone GOs?

jcolebrand

style="width: 70%; height: 60px; left-margin: 30px;"

That's the initial thought I have on the text boxes

Damnit, now I wanna go write a front-end for a SQL processor, and you've got this under control

But you do need a designer to look at it and make it WEB3.0 :p

Jack Douglas

@AndriyM looking for '^GO$'

Philᵀᴹ

8:24 PM

Bootstrap 4

Easy peasy

Andriy M

I thought as much.

jcolebrand

@JackDouglas Should include leading/trailing whitespace no? Some folks do [GO ]

not because they mean to but because they only learned to type one way

If you trim each line then that's fine, just an observation.

Jack Douglas

@jcolebrand fixed

Andriy M

Ha, I've only just realised. That example I posted above doesn't work exactly as expected. It shows GO on the first two selects as the column name (which is syntactically correct).

Jack Douglas

@AndriyM parsing SQL is scary and I'm trying to avoid it. the paste thing is a hack, so if it goes wrong the user can clean it up

@AndriyM well spotted, I missed that

well the placeholder says "don't use GO" so I feel justified :)

Andriy M

8:29 PM

I successfully missed that :)

Jack Douglas

Which versions of SQL Server are people still using?

jcolebrand

style="width: 70%; height: 550px; position:relative; left: 30px;"

I think the top box looks so much better than the bottom box. Thoughts?

And it's not 100% of the screen width, I just didn't crop my entire screen

A little trailing height below the last line is nice, makes you realize that there's more room to type, and you can easily grow down from that. The left inset just makes it feel like there's a margin to the page (look at printed books)

The right space is just psychological

Jack Douglas

8:42 PM

@jcolebrand I've added that on the home page but not on the rest: dbfiddle.uk/?rdbms=sqlserver_next

Jack Douglas

8:53 PM

@jcolebrand I think you are right about the margin too, I've added a bit on the left

jcolebrand

Yeah. That already looks better as it were

It's some of the small things

And don't forget, we figured out print like over the course of 800 years or so, plus all the handwritten manuscripts. There were good reasons for that (like dirty fingers) but it's also psychological at this point

Space around text is good

Jack Douglas

I don't know why, but if you separate the INSERT statements from the CREATE TABLE statements things look more healthy: dbfiddle.uk/… — Jack Douglas ♦ 9 secs ago

Tom V

9:42 PM

@JackDouglas I think going with the supported ones is more than enough

Some people are still using 2000 but you don't want to go there :)

ypercubeᵀᴹ

@TomV But it would real fun if we could use a 6.5 version! Or even earlier

Tom V

@ypercubeᵀᴹ did we have PHP back then, or a driver?

I can't remember

Jack Douglas

@TomV I think I might use ASP anyway

Tom V

@JackDouglas That's a neat trick

ypercubeᵀᴹ

@TomV No idea. Not a SQL Server guy back then.

Tom V

9:45 PM

@JackDouglas SQL 2K would be "classic" ASP :)

@JackDouglas not sure the batch parsing trick is less confusing if you don't know what's happening though

Jack Douglas

Well I still code in VB6, so I'l lprobably find that easier :)

Tom V

but I shouldn't complain, at least we have a working effort again

Jack Douglas

all feedback is useful, it's not taken as complaining :)

my plan is to start with 2016 and work backwards

Evan Carroll

I worked in a VB6/Classic ASP job for a while

Jack Douglas

I might stop at 2012 — I think that is the earliest version that runs on Server Core?

Evan Carroll

9:48 PM

may god have mercy on your soul

Tom V

@JackDouglas good idea

Jack Douglas

@EvanCarroll it's still alive and kicking in it's VBA incarnation

Evan Carroll

I moved the whole shop over to perl.

Tom V

I think the versions down to 2012 is more than enough, then you get into "out of mainstream support" territory and I'm not sure that's worth the effort

@EvanCarroll Your successor will curse you to death for implementing perl and leaving

Evan Carroll

no way, Perl is far better than VB6/Classic ASP

Jack Douglas

9:51 PM

Perl terrifies me

ypercubeᵀᴹ

@JackDouglas but @Phil terrifies Perl ;)

Evan Carroll

Not sure why... Never understood the anti-perl thing with MS users. With Unix guys, it just is. Strawberry perl runs great on Windows too.

ypercubeᵀᴹ

I don't like perl either

Tom V

@EvanCarroll I'm hardly a windows user, I'm typing from fedora and have worked at a *nix shop

Jack Douglas

@EvanCarroll I'm not anti-perl and I'm not a Windows user

Tom V

9:52 PM

Perl is ok for text parsing, but it's readability is terrible

Evan Carroll

Perl has the most advanced OO system of any of scripting language, and it has the most 3rd party packages.

No way.

ypercubeᵀᴹ

@EvanCarroll Yeah, right.

Jack Douglas

@EvanCarroll except PS

Tom V

I'd go with Python for 99 percent of the jobs on *nix

Evan Carroll

Python is fine too, Perl just has better OO, and Web Dev packages.

Tom V

9:53 PM

I'd pick perl over Ruby though

Evan Carroll

Ruby is hideous

Jack Douglas

yes, we can all agree on that

and MySQL has a new 'surprise' for me every day

27

Q: SQL UPDATE order of evaluation

What is the order of evaluation in the following query: UPDATE tbl SET q = q + 1, p = q; That is, will "tbl"."p" be set to q or q + 1? Is order of evaluation here governed by SQL standard? Thanks. UPDATE After considering Migs' answer, I ran some tests on all DBs I could find. While I don...

sql sql-update evaluation order-of-evaluation

@ypercubeᵀᴹ I think the combination of @Philᵀᴹ and Perl is even more terrifying than either one

ypercubeᵀᴹ

@JackDouglas True.

Could only be balanced with @ConcernedOfTunbridgeWells and Postscript

@JackDouglas Ah, you didn't know that?

MySQL UPDATE totally breaks the standard. In 2 ways: First with the order of evaluation and second because it checks unique constraints after every row update, not at the end of statement.

Evan Carroll

You ever see the list of drawbacks/bugs on SQL Server merge?

McNets

Is there a way to copy the whole script on dbfiddle?

Jack Douglas

10:03 PM

@McNets no, great question though. I'm already planning to add a button for a complete markdown dump for cut'n'paste to dba.se and and SSMS export would be sensible too

ypercubeᵀᴹ

@EvanCarroll One can avoid MERGE but not UPDATE.

McNets

@JackDouglas yes, I think it is necessary, just to avoid copy and paste every batch.

Jack Douglas

@ypercubeᵀᴹ pg is guilty of something like the second of those too

ypercubeᵀᴹ

@JackDouglas no, I don't think so.

Jack Douglas

@ypercubeᵀᴹ check the last para here: dba.stackexchange.com/a/105092/1396

ypercubeᵀᴹ

10:10 PM

@JackDouglas I will test and let you know. This looks weird.

Jack Douglas

@ypercubeᵀᴹ worse is that NOT NULL and CHECK constraints are not deferrable

ypercubeᵀᴹ

@JackDouglas yes, it's right. I didn't know that. Only if the constraints are deferrable, it works ok.

Philᵀᴹ

@ypercubeᵀᴹ perl == success

Though done a fair bit of Python recently. And R

As well as the ghetto PHP

Jack Douglas

@ypercubeᵀᴹ it was infinitely worse back on 8.4 when PK and unique constraints weren't deferrable either :)

@Philᵀᴹ what job is perl the obvious choice for?

I'd guess you'd say shell scripting replacement…

ypercubeᵀᴹ

@JackDouglas also: deferrable UNIQUE constraints cannot be used with ON CONFLICT

Jack Douglas

10:20 PM

@ypercubeᵀᴹ oh that sucks

ypercubeᵀᴹ

it makes a bit of sense that

ypercubeᵀᴹ

10:34 PM

Did you try OUTER APPLY? — Aaron Bertrand ♦ 40 secs ago

@PaulWhite what we were discussing yesterday ^^

Evan Carroll

@ypercubeᵀᴹ how much of your exp from the quarter comes from your work in the quarter, vs past work?

that is to say, how much of it is the synergy from past work?

@EvanCarroll "exp"?

Yea. EXP.

is that a word?

aqwwiki.wikidot.com/exp-class-points-rep

billinkc

10:41 PM

I see it abbreviated as xp far more frequently than exp

I believe there is a saved query on the data explorer, something like jam for my money or something with Jam in the title that attempts to identify how much is new reputation points versus residual.

If I were understanding your original question

ypercubeᵀᴹ

Sorry, I'm not into role playing games

Evan Carroll

Sure, but you play dba.stackexchange.com. you have like 54,000 exps

ypercubeᵀᴹ

11:00 PM

So you are asking how much rep comes from recent answers vs old ones?

That would be interestng to find out.

billinkc

data.stackexchange.com/stackoverflow/query/8116/…

ypercubeᵀᴹ