@PeterVandivier Super User is a cesspool with some of the worst moderators on the network.

It doesn't surprise me that it has more users and fewer people stick around.

Speaking as one of the users that hasn't stuck around.

The site at whole is for consumer technologies. So the best amongst them only use stuff and have no understanding of what they're using. Imagine opinionated laypeople talking about things they don't understand, and banning things that they don't want to talk about and people that bring up those things. That's pretty much the whole of Super User.

morning

Morning

Moarnining

Morning

Q&A with Denny Cherry

Morning

> trying to get SQL Server to do things your way just isn't going to work

Well, unless you are very good at pulling all the right strings to get it to do just what you want.

Or, as someone else put it, "There is no magic (unless you’re Paul White)."

Morning

Morning

> Probably one of the worst problems that I've seen was thankfully one of the easiest to fix. The application was using an open source scheduler to scheduler things to happen in the application. The open source scheduler stored its data in SQL Server. The columns were different data types than the calling code, and because it was open source there was no one to call and fix it.

> We simply changed the database tables to match the data types of the code, and the developers promised to never upgrade the job scheduler.

I guess he has never heard of PRs ...

Or the only PR he has head of is Public Relations ;)

But I admit, he is good at that!

If that's the worst he's seen I envy him

Morning

@TomV Hard to believe though. I can't point to a specific example but my general impression from his answers on the main (even if they are mostly very laconic) is that his experience must be rich enough for him to have seen worse.

@AndriyM I've attended a session of him on virtualization and SAN stuff where he had much scarier stories to tell.

There you go

I suppose they would be hard to explain in a one-liner to fit that article though

Makes sense

Morning all

@TomV I've got plenty of horror stories about virtualisation and SANs.

Like the time that a brand shiny new £500,000 netapp and 64 bit consolidation server farm couldn't run an ETL job quickly enough to fit into its batch window. The server they were migrating off was the original 32 bit server they built the system on when SQL Server 2000 was current tech. P4-era xeons and U160 SCSI disks. This was 2012 and the original server was more than 10 years old.

The infrastructure people tried to blame it on the ETL job until it was pointed out that it ran just fine on the old server.

apropos of nothing...

happy Tuesday, everybody

@ConcernedOfTunbridgeWells and? and?

@dezso My PHB got the job of tuning the crusty old legacy ETL process to fit it into the batch window.

Having said this, it was a full refresh of around 1TB of data that really badly needed re-jigging as an incremental loader. Not a stunning example of back-end design.

@ConcernedOfTunbridgeWells so in the end it looked like the infrastructure people were right

@dezso They had just gotten the business to shell out the better part of £1m for a SAN and server consolidation environment. Of course it was faster than the old server. How could you possibly think otherwise?

Plus, they all got Netapp and VMWare certifications. Mustn't forget our priorities.

I think I've had half a dozen gigs now where my PC or laptop would run the ETL process faster than the production environment.

It's also fun getting told off for doing lots of IO & slowing other VMs/apps down

Not like we didn't warn them it was an OLTP app with hard IOPS requirements

Capacity planning. Yeah, we heard of that.

I've also been on more than one gig where the capacity plan I produced for the warehouse was the first one the CIO had ever seen.

@ConcernedOfTunbridgeWells sometimes when I did an IO benchmark of some new SAN or infrastructure set up I include the numbers from my laptop with a single NVMe SSD for giggles

So far no one has dared ask why it's so much faster than their expensive full SSD SAN

@TomV A lot of folks are under the mistaken impression that you buy SANs for speed. Back in the late Jurassic I was working in the same building as the EMC office and their reps would quite freely admit off the record that most of their EDW customers used direct attach storage as it was a lot faster.

There was a gig however where I compared the new 500K euro all-flash array performance to the old P4000 it replaced and the old SAN was twice as fast when it came to latency as well as throughput

The look on their faces when I presented that report were glorious

In the end the issue was with the de-dupe firmware of the new SAN, but they learned they shouldn't just assume the new shiny and expensive piece of hardware will be faster just because a sales rep drummed up some theoretical IOPS numbers

Don't trust, test

@TomV And the more complex the infrastructure, the more complex the failure modes.

A lot of folks don't give complexity the respect it deserves.

The best one was the slave DC being miles away, and the OS not acknowledging the write had been completed until it hit the writeback cache on the remote disk array. That caused a couple of perf problems - got much much worse once the cache had been saturated. Was in the days of spinning rust though. Things have moved on now

@Philᵀᴹ I'm pretty sure Microsoft mandates that behaviour on SANs in order to get them certified for use with SQL Server. At least they used to.

I've also had the stock arguments about caching with infrastructure types on more than one occasion. On a DW, many queries are table scans of large fact tables. Imagine what the locality of reference might look like on a scan of a table that's just slightly larger than the cache.

I'm talking about a slave SAN though. The IO had to hit the slave and the master. The slave was many many KMs of fibre away

In that case you get a 0% (for a first approximation) cache hit rate. Now that you can build caches in the TB range it's less of an issue.

@Philᵀᴹ I would have thought he master could have queued the updates, but I guess it didn't. I guess that might be sensible if you have a really anal replication policy.

@Philᵀᴹ Haha I had a customer like that too, they tested disaster recovery failing over to a new site, and afterwards the fail back didn't go as planned. One of the physical hosts was still routing network traffic between the application tier and the database to the remote site and back

Obviously that was blamed on the database having problems since the DR test

Good afternoon. I'd like to do a quick impromptu survey of database features that are not commonly available but that people would like to see. In answering this question, I became a bit confused (happens a lot! :-) ) and went down a rabbit hole and produced an answer that was far too complex involving

the chaining of 5 CTEs (35 lines). It transpires that the OP is on MySQL 5.6 and my answer wouldn't have worked anyway, but thanks to another poster, I was inspired to produce a 2 liner which does the job AFAICS - no feedback from OP. However, from my complex answer, I was led to ponder the possibility of using SQL in LIMIT and OFFSET clauses - I see no reason in theory why this should not be possible.

I then searched for same and came up with nothing. The one useful titbit I did come up with was Firebird which allows SQL in CHECK constraints. I tested this on dbfiddle.uk here and here but the implementation seems (ahem...) buggy. However, I was blown away with the extra power that SQL in CHECKs can give.

As mentioned, I would also like to see SQL be available in the LIMIT and OFFSET clauses. I would also like to see the possibility of referring to other tables safely (I saw mention of this for Firebird, but apparently it's risky). Database assertions are another area I would like to see available. Are there any other features that posters here feel that are missing and are readily implementable in the RDBMS?

@Vérace I don't see how the short query answers the problem. Where is the GROUP BY done?

Second thing, how are CHECK constraints related to the above issue? Or is that a different problem?

There's no GROUP BY - the OP already provided data that was GROUPed. Akina's solution missed that. If you look at my comment to my own 2nd answer, you'll see I provide a second fiddle where I thrash out that issue.

I don't follow.

Say there are many rows with count of 40. How does your query return only 1 of them?

@ypercubeᵀᴹ CHECK constraints have absolutely nothing to do with the question. I was just, as I mention, "led to ponder..." and that's where that led me. I could find nothing about SQL in LIMIT and OFFSET - the only thing I could find was SQL in CHECK constraints in Firebird.

@ypercubeᵀᴹ "Say there are many rows with count of 40. How does your query return only 1 of them?" The OP want the first N records which have a count > 40, then they want a random selection of those with a count of 40.

@Vérace I have a hard time coming up with a problem you could solve that way that couldn't be solved with a join of some sort to the subquery you would put in the limit clause (albeit probably more verbose than would be possible with your suggestion)

Or is it about elegance more than possibilities?

@TomV Elegance and simplicity surely lead to more easily developed, less buggy code?

Standard SQL has not LIMIT. It has FIRST ROWS and OFFSET but anyway. I'm not sure if it allows expressions or subqueries there. I could check later.

@Vérace Yes I agree, I just wasn't sure what you meant

@ypercubeᵀᴹ "It has FIRST ROWS and OFFSET" - OK then, allow, using the FIRST ROWS and OFFSET mechanism of your RDBMS (MySQL has LIMIT), allow SQL in there.

As I said, I can check if arbitrary expressions are allowed or not.

You can surely check in dbfiddle.uk in the mean time, what the various DBMS allow.

@TomV Also, Firebird have shown that SQL in CHECK constraints is possible. I haven't the time to set up FB here, but I'm sure those problems I've shown in the fiddles have been sorted out by now.

To be fair, I don't think the CTE and row number solution is that hard to read

Which doesn't mean I oppose your view, for example a lot of CTE or windowing solutions could be done with subqueries or temptables before them but that doesn't mean we had no use for improvements to the language off course

Postgres allows subqueries there: dbfiddle.uk/…

@TomV Yes, but you have to chain them - if you could have SQL in the clauses, it would lead (given certain cases) fewer CTEs in the chain. Anyway - we're getting away from the primary point of my question which was about features that those here think are feasible and would like to see implemented - I mentioned SQL in CHECKs, LIMITs (or FIRST ROWS) and OFFSETs. Are there any others that people are interested in. Other than using TRIGGER logic,

how do you stop a customer having more than 5 (open) invoices or going into debt for more than €1000? I think having the logic in the CHECKs is really nifty and cool!

CHECK constraints that are related to anything else than the current row (e.g. other tables), are called assertions. They are in the standard.

Not yet implemented anywhere (except maybe Firebird as you say)

Reason: complexity.

To repeat a comment from the Heap:

SQL Server allows you to implement some constraints of this kind using indexed views. Not the same thing, of course. I guess you could call it a workaround.

I don't know how complex implementing assertions would be, but it would certainly be tricky to do in a way that performs well

@ypercubeᵀᴹ "Postgres allows subqueries there" - nice. Should have known that PostgreSQL would be ahead of the posse in some ways - now, if only they could do this for CHECKs. I did search and found nothing - and it seems that none (neithe?) of the commercial big boys have done this. As for complexity - my thoughts on that subject would be that it is up to the brilliant designers and programmers of our RDBMSs

to hide this complexit y from us and allow mere mortals like myself to have powerful yet easy to use tools at our disposal.

@Vérace Actually, both SQL Server and Postgres allow using custom functions in CHECK constraints.

Yes, that one too.

But the advice i- in both DBMS - is NOT to use them, for implementing assertions.

@PaulWhite I was thinking the same, but depending on how the syntax allows for a specification I think it could be implemented in a way were "classic" constraints would be performed the old way, and off course the performance of an assert is entirely the responsibility of the developer, both in writing an assertion that performs reasonably well and only using it where the design can handle the performance hit

@TomV yes but people are people, and developers especially so

@PaulWhite If implementing this as an assertion is a hit, well so are triggers, no?

@PaulWhite That doesn't mean you should oppose the feature IMO

@PaulWhite Thank you

@TomV I don't oppose it

Yeah I didn't mean to say "you" in that way, not sure if it came across

maybe I should have used "one" insted of "you"

sounds posh

@ypercubeᵀᴹ - "advice... is NOT to use them, for implementing assertions." - same for Firebird - can't find the link, but there was something about "could lead to... inconsistencies...".

In any case, if there is a requirement like the assertion @Vérace describes (only allow x invoices over y amount for a certain customer) the performance impact on a database construct could be significant, but so would it be when you have to check it in the application (possibly more so) and it would probably be better to enforce it in the database than try to consistenly enforce it in all the apps

I don't think anyone here opposes assertions. But we realize the reasons for not being implemented yet: it is far from easy to do while keeping performance in a reasonable level.

@ypercubeᵀᴹ see above

I can see why vendors don't have them (yet?) but I agree with the point that they would be useful had they been available

@ypercubeᵀᴹ Then why is it OK to let developers run riot and use triggers left, right and centre but not let us have this functionality through SQL in our contraints and other places?

Don't get me started on triggers ;)

@Vérace I'm not certain I understand the point you're making here. Writing an 'assertion' in a trigger can be simple and not perform well, yes.

I think Verace is saying that he can't understand why they have implemented triggers which can also to all kind of performance issues.

why triggers but not assertions

Well people write the trigger code themselves

And surely it's far better to allow the presumably (very) competent RDBMS implementers provide a mechanism behind the scenes for implementing this functionality rather than letting journeyman developers (and I include myself in there) use procedural code to do it?

i.e. the implementation not the specification

@ypercubeᵀᴹ "I think Verace is saying that he can't understand why they have implemented triggers which can also to all kind of performance issues. why triggers but not assertions" - Bang on the money!

Slightly related. Postgers allowed at some point mutable functions in CHECK constraints:

create table t (id serial, a text check (a = '' or a = CURRENT_USER::text));

You can imagine what problem that would cause.

There could be a case made for providing limited types of assertions - potentially very limited - that the system could implement simply and efficiently in triggers.

But I think we're a long way from knowing how to do that with arbitrarily complex assertions

You only have to look at how limited SQL Server materialized views are

A similar problem in some ways

unrelated: I should have stayed away from that SSAS question. I just knew there would be something missing from the info given

Also: "updatable views".

@PaulWhite But any muppet can screw up the performance of a server - just CROSS JOIN the same table with a million records 5 times... I just found the ability to put those CHECKs in the Firebird fiddles (if they worked properly :-) ) very elegant and aesthetically pleasing compared to triggers - think the Mona Lisa vs. crappy local graffiti!

@ypercubeᵀᴹ yes very much so

This is on my "to read" list: i-programmer.info/bookreviews/21-database/…

@ypercubeᵀᴹ For bedtime reading to the kids? If that doesn't get them off to sleep, nothing will! :-)

@Vérace Yes simple assertions with a single table reference, a maximum of one aggregate, and a simple conditional are the sort of thing I was talking about

But they're easy to do with triggers as well

I do take the point that the system might write better trigger code than the average human

@Vérace @ypercubeᵀᴹ One explanation could be: triggers are easier to implement and more versatile than assertions. Yes, triggers are sometimes used to solve problems that shouldn't be solved using triggers, but valid uses of triggers still include much more than just assertions.

like sending web service requests per row to print an email on the other side of the world

@PaulWhite "the system might write better trigger code than the average human" - no "might" about it IMHO...

@PaulWhite Totally legit

@Vérace on the other hand, it does depend on the users one is exposed to on a regular basis

my personal average (from SQL people I know) would be reasonably high

@ypercubeᵀᴹ aren't backups guaranteed to be transactionally consistent already?

in Postgres I mean

@PaulWhite it is, so not sure why the restore cannot take that into account (i.e. do not check the constraints)

there would seem to be no need

of course something like dbcc checkdb would need to

but that's different

At the risk of sounding too posh, cannot one meddle with a backup in a way that changes just the data without corrupting the structure? What would prevent such a backup from restoring successfully?

checksums

but for sure backups can be corrupted, that's why we test them by restoring

and running a consistency check

the chance of a corruption passing checksum and consistency checks is pretty small

@ypercubeᵀᴹ updated

+ improved the conflicting cascade fiddle

I have updated my votes ;)

;-p

And I don't think that the FK would always be redundant but ...

The OP needs some clarification to do

3rd fiddle incoming addressing that exactly 😜

dbfiddle.uk/…

I like that you find Postgres bizarre. SQL Server can't handle multiple cascade paths, so anything better is .. bizarre ;)

@AndriyM Not sure about SQL Server, but Oracle imp/exp files can be tweaked with sed. Although they're a binary format, they are a stream with no internal pointers. However, they have tablespace and schema names and other such items embedded, so migrating across these needs the file to be tweaked.

At least as of 8.1/9 it did. Not sure if you can coerce it now.

lol, i <3 postgres

but conflicting cascade is most definitely bizarre

it's the "dad said no so i'll ask mom" of algebraic logic

@ConcernedOfTunbridgeWells Interesting. @ypercubeᵀᴹ mentioned above that PostgreSQL runs CHECK constraints during restore, so I was trying to think of a reason to justify such a decision. If you can modify a Postgres backup as easily as you could an Oracle 8 one, then I guess running CHECK constraints on restore makes sense.

Well, if one path says CASCADE DELETE and the other CASCADE RESTRICT, the delete might fail.

@AndriyM yes, the dumps can be of various type, with text files as one option.

There you are then

also, fwiw, sql server does support divergent cascade (dbfiddle.uk/…), just explicitly disallows conflicting cascade (dbfiddle.uk/…)

but if you find yourself looking up the docs for which of those works for anything other reason than curiosity, then it's time to close the laptop and go for a walk

i think i need to go for a walk :p

@PeterVandivier ah thnx! I didn't remeber that.

I need to go for walk and lucnh

@PeterVandivier You can't do this either however, even though there's no conflict.

@AndriyM whether the rules would conflict if both were to be allowed to evaluate is not the thing that defines conflict. the fact that there's two sets of rules that both have the same trigger is the conflict

what if arbitrary commands were allowed rather than simply cascade/set()/do nothing? how would the engine know what to evaluate and in what order to evaluate and what a consistent end state should be?

the fact that you're trying to set a parallel evaluation in a strictly serial container is the conflict, regardless of whether or not it "would" work or "should" work

at least that's how i think about it

@TomV I remember that story

the 3PAR we had problems with found its usage elsewhere, and by now is not with us anymore

@PeterVandivier I have a very wild guess that there may be some connection here with how MERGE doesn't allow you to delete the same row multiple times when your joining condition results in duplicate rows, even though DELETE would be OK with it. So, in a similar fashion multiple paths may result in touching (deleting) the same row many times and that could be a problem.

possibly

although iirc merge does separate insert/update/delete under the hood anyway

i thought that's part of why it ignored checks at one point

@ConcernedOfTunbridgeWells Deprecated. Ded. It's all expdp/impdp now (data pump)

I have been known to pipe exp/imp between machines without using disk

@CadeRoux Perhaps the following is relevant to your situation:

Yeah, I could probably do that, but it's quicker to just rejig the query...

    -- For some reason queue processing is not happening, we will go ahead and re-run these directly to see if we can find the errors
    IF ( [AscendDataMart].Config.GetValueString('QUEUE_PROCESSING_ENABLED') <> 'TRUE'
            OR EXISTS (SELECT * FROM [AscendDataMart].[ETL].[ChangeQueue])
            OR EXISTS (
                SELECT *
                FROM [AscendDataMart].sys.service_queues q
                INNER JOIN [AscendDataMart].sys.schemas s
                    ON s.schema_id = q.schema_id

It's already in a big section of non-dynamic SQL being run from a different database looking at the target database after install.

The first set of tests expects that the service broker is working. If it is not working or disabled, I go and pull the XML messages from the log and re-run them without the service broker to find any errors that might have gotten swallowed up in the service broker transaction rollbacks.

ok