The Heap™ – Consultancy ©®

Max Vernon

03:58

@McNets sorry i missed your message - anything I can do now?

McNets

06:32

Morning

Mikael Eriksson

Good morning.

McNets

@MaxVernon no worries, like Cinderella after 00:00 I don't know how much 2 + 2 sum.

Mikael Eriksson

@PaulWhite Have you seen this?

11

Q: Concatenation Physical Operation: Does it guarantee order of execution?

In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like: select 'A' as c union all select 'B' Could return two rows in any order (although, in practice on any database I know of, 'A' will come before 'B'). In SQL Server, this turns into an executio...

sql-server

Somewhat related to UNION ALL Optimization

Paul White

08:00

@MikaelEriksson Ooo no I hadn't, thanks.

I found quite a few questions here and elsewhere but not that one. Very relevant.

Andy K

08:16

morning

Philᵀᴹ

08:30

Lols

Msg 8623, Level 16, State 1, Line 5
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I have therefore completed the SQL Server game by defeating the optimiser!

7

ypercubeᵀᴹ

09:05

@MaxVernon "Microsoft MySQL"? really?

Paul White

@ypercubeᵀᴹ Yep MySQL and PostgreSQL on Azure.

Kenshin

what is azure?

ypercubeᵀᴹ

@PaulWhite and are they called "Microsoft MySQL" and "Microsoft PostgreSQL"?

Paul White

@ypercubeᵀᴹ Azure Database for [MySQL/PostgreSQL]

Azure Database for MySQL

Azure Database for PostgreSQL

@Kenshin Microsoft's Cloud offering.

Kenshin

Why store data in a "cloud"?

ypercubeᵀᴹ

09:11

@PaulWhite that is sensible.

Kenshin

how is a "cloud" any better than having your own datawarehouse on site?

Paul White

@ypercubeᵀᴹ One wonders if Azure SQL Database will be renamed (once again) to Azure Database for SQL Server ;)

Kenshin

@PaulWhite and surely a "cloud" isn't a real thing, it would just mean access data that is stored in a remote location by the interent?

Paul White

It's just someone else's computer, yes.

But then they patch and maintain it, not you.

Kenshin

ah k

but you lose power

and you have to pay them to maintain it

so why would you go cloud if you already have your own datastorage and administrators

Paul White

09:13

Yep. Everything is a trade-off of one sort or another.

Kenshin

ah k

ypercubeᵀᴹ

@Kenshin but if you don't have those (infrastructure and dbas and sys admins)?

Paul White

@ypercubeᵀᴹ Nothing Microsoft do really surprises anyone any more.

Kenshin

@ypercubeᵀᴹ welkl at my work microsoft came in one day trying to sell us the "cloud"

@ypercubeᵀᴹ we already have our own data storage and administrators etc.

the comapny didn't purcahse

Michael Green

09:31

Last place I worked, Microsoft gave us meeeeelions of dollars of Azure as part of the enterprise agreement. Last I heard they still haven't used any of it. I left just under a year ago.

Paul White

It's not hard to see how cloud makes sense for Microsoft. Nice steady revenue stream for one thing.

Kenshin

is the cloud hackable

imagine if a big comapny went with Azure and the data was hacked

and microsoft is sued for billions

Michael Green

@PaulWhite that. And if you have to build out infrastructure and capability for your multi-billion dollar company you may as well on-sell and recoup some revenue.

Paul White

Yep.

Michael Green

Earlier I had a play with the "new" Cosmos DB which apparently grew out of an internal initiative from 2010.

@PaulWhite I believe that's the back-story to AWS too.

Paul White

09:39

Oct 25 '16 at 10:13, by Paul White

@AndriyM If you don't like the name of a cloud service, just wait 15 minutes.

Planet-scale LOL

2

ypercubeᵀᴹ

I searched those docs (azure, postgres) but no mention if they run on Linux or Windows ...

or if I have a choice

Paul White

@ypercubeᵀᴹ Yes I noticed that too.

Kenshin

is Linux good?

Philᵀᴹ

@Kenshin No, it's amazing

Kenshin

how come

I use microsoft and only ever have used microsoft

Philᵀᴹ

09:49

Do you own an Android phone?

Paul White

Tricky.

Kenshin

10:00

Nope

Apple phone

Colin 't Hart

10:20

@PaulWhite You could just about remove "for Microsoft". from that sentence. Apple is up to 13% revenue from services, and that piece of the pie is just growing. macrumors.com/2017/05/02/2q-2017-results

dezso

> To me, changing the behavior of a widely-used system call in a maintenance release seems about as friendly as locking a family of angry racoons in someone's car [about glibc]

Paul White

@Colin'tHart Yes it was a general observation, the local context was simply Microsoft

ypercubeᵀᴹ

@dezso Yeah, I read that part ;)

dezso

@Marian who was it then?

Colin 't Hart

10:39

@ypercubeᵀᴹ PDF slides of new features in Postgresql 10: momjian.us/main/writings/pgsql/features.pdf

ypercubeᵀᴹ

11:35

@Colin'tHart nice thnx!

Tom V

13:00

@Kenshin Most companies are easier to hack than Microsoft I suppose, but the scale of damage would of course be a lot bigger if it happened on Azure. Though they seem to have fairly good walls between tenants it is not unthinkable that the whole thing can be compromised

Kenshin

@TomV my company has onsite data storage, so surely it would be harder to hack than azure which is stored over the cloud?

Tom V

@Kenshin I would bet it isn't

Lamak

@Kenshin I wouldn't think so

Kenshin

@TomV you would be happy if your bank decided to store all transaction data on azure?

Tom V

I don't know where you work, but if it's a bank it may be another story

that's not the target audience

Lamak

13:03

@Kenshin that wouldn't make really much sense

Kenshin

well microsoft approached us

we didn't purchase

Tom V

the security at a lot of SMB's is swiss cheese compared to Azure or AMW

Ya if you work for a bank you probably shouldn't

Tom V

13:25

@Philᵀᴹ FATALITY!

Joe Obbish

@Philᵀᴹ Were you using any unsavory trace flags when you saw that error?

Tom V

Last week I learned SQL Server only supports up to 2100 parameters

4

No I didn't write the query, nor the code that autogenerated it

Joe Obbish

@ErikE I posted a question that you might be interested in. In my research I found a similar one on SO that you had answered

@TomV You mean you don't have the maximum capacity specifications memorized? docs.microsoft.com/en-us/sql/sql-server/…

Tom V

@JoeObbish I guess I just wasn't expecting to ever run into that one

Joe Obbish

I think I've only ever used 3 of those

little surprised that a log can only be 2 TB

8060 byte limit for a row, 16 column primary key limit (would rather not talk about it), 900 byte index limit

Tom V

13:42

And 5 instances per machine

dezso

@TomV it was a friend, right?

Tom V

2

I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instanc...

sql-server sql-server-2008 sql-server-2008-r2

Username checks out ^^

McNets

Double account.

dba.stackexchange.com/review/suggested-edits/92425

Joe Obbish

@dezso former friend

@TomV Am I reading that right? 16 GB for an OS and 50 instances of SQL Server?

it's early in the morning so I don't believe my eyes yet

Tom V

@JoeObbish Yes, the guy was really hard headed too

Philᵀᴹ

13:47

@JoeObbish Nope. Opened SSMS, wrote query, hit execute. On production

Tom V

He just wouldn't give up in the comments and admit it was a bad idea

Philᵀᴹ

I may have neglected to mention the IN clause that was HUGE :P But still...

Tom V

@TomV Not really my problem, I just work here... :) — Drunken Code Monkey Jun 29 '16 at 17:52

Joe Obbish

is unplugging the server a valid recommendation?

Tom V

Always

Joe Obbish

13:53

@Philᵀᴹ If it's not too much trouble, is it possible to share the (anonymized if necessary) query text? Just curious.

Philᵀᴹ

14:11

with spends as (
select CardCode, SUM(DocTotal) as spend
from DB1.dbo.ORDR
where DocDate>'2016-05-01'
group by CardCode
), itops as (
select CardCode, COUNT(*) as itops
from DB1.dbo.OOPR
where Status='O'
group by CardCode
), connectops as (
select U_Lnk_CardCode, COUNT(*) as connectops
from DB2.dbo.OOPR
join DB2.dbo.OCRD on OCRD.CardCode = OOPR.CardCode
where Status='O'
group by U_Lnk_CardCode
)
select ZipCode, CASE when CardType='C' then 'Customer' when CardType = 'S' then 'Supplier' when CardType='L' then 'Lead' else 'Unknown' end as customertype ,

It was the IN list that killed it. I replaced it with a temporary table

Was just being lazy for a 1-off

Still on 2008R2 on this DB. Cry

Lamak

@Philᵀᴹ you should be crying

Joe Obbish

I realized that might have come off the wrong way over text

Philᵀᴹ

Nah

We like the banter

Joe Obbish

@Philᵀᴹ It's a pain but I'd consider submitting a connect item for it

Philᵀᴹ

I've got the same DB on a new version of SQL server, for testing purposes. I'll see if it can reproduce it on there

Lamak

14:19

@JoeObbish you realize that almost all of us can see deleted comments in here?

;)

Tom V

@Philᵀᴹ Does the query fail if you remove the collate (even if it may produce the wrong result set)?

Joe Obbish

good point!

Tom V

@JoeObbish Lamak is used to more than that

Philᵀᴹ

@TomV The collate wasn't be there anyway, sorry :)

Tom V

@JoeObbish connect.microsoft.com/SQLServer/feedback/details/521943/…

Joe Obbish

14:24

good find

not sure why people downvote ideas like that

Tom V

It's only been open for almost a decade, so obviously not fixed yet

Joe Obbish

"No I want the query optimizer to throw an error"

Tom V

I downvote all connect items that don't affect me so the ones that do affect me end up higher on the priority list

7

/JK

Joe Obbish

that might be it actually...

Lamak

@TomV I am

ypercubeᵀᴹ

14:27

joining with a VALUES subquery might be another option.
Not sure though, Paul White would know for sure.

Joe Obbish

An RID lookup? On MY database?

Philᵀᴹ

I have a friend that claims to have completed Tinder. He said it was awesome, but the final level was a nightmare

Marian

15:19

@dezso Steve, he had an electrifying performance :).

and some nice remixes

dezso

15:33

@Marian oh, thanks. In the meantime, the colleague sitting next to me came up with the same three (DISTINCT 1) words and enlightened me

Tom A

15:49

Does anyone have any ideas on how I can create a trigger without creating a trigger? I need to be able to detect deletes in a SQL Server database, but a trigger on the table causes the application running on the db to crash.

McNets

@TomA use only an stored procedure to delete records

Tom A

can't. i don't have access to the source code

Cade Roux

Detect Deletes like in auditing or to be able to do some kind of real process? Change Data Capture can give you the deletes.

I would be a surprised that a basic non-invasive trigger for auditing or inserting items into a workflow queue for subsequent processes causes an application to crash.

Tom A

i was surprised myself. I created the trigger as an after delete trigger, and thought it should be ok, as I'm just using it to add an entry to an audit table. We know the software is rather weird, so I can't say it was a complete surprise, but it is annoying

meanwhile, the after insert trigger worked fine...

dezso

now I am considering the pen name Tom X

2

ypercubeᵀᴹ

16:01

@TomA I thought the after trigger caused the application to crash?

Tom A

after delete caused it to crash. after insert worked

ypercubeᵀᴹ

does the trigger take care for multiple rows inserts/deletes?

Tom A

nope, looks like I missed that. However, normally it would only insert or delete one at a time, so I understand how I didn't catch that part.

ypercubeᵀᴹ

That's a common pitfall - although I can't see how that might be the case in your scenario.

Tom A

and also, I'm not a regular dba, just a solo developer who gets dba duties from time to time

ypercubeᵀᴹ

16:07

I mean if there were multiple line deletes and the trigger would fail to do its job, it could crash an app but you wouldn't see the trigger effects

Lamak

@ypercubeᵀᴹ too common

Tom A

the other crazy thing about this is that I can detect 4 distinct styles of design in this one database :(

and there's probably more, but I haven't had the need to go through some areas of the db

Lamak

@TomA you know, the issue might just be what @ypercubeᵀᴹ said

Tom A

i know. I'll go ahead and fix that issue, and look at the Change Data Capture if it doesn't help. That feels a lot better than my idea of snapshotting the table every 15 minutes and using an outer join to compare tables

Tom V

16:27

@dezso nice

Cade Roux

If the trigger itself causes an error, the scope of the transaction which triggered it would fail as well, potentially manifesting in a crash/runtime error. So it's most likely that the trigger is doing something which just results in an error. Like inserting duplicates in a table violating a key or some other constraint. Or assuming deleted has a single row in the set.

Andriy M

@dezso There haven't been any Tom VI, Tom VII, Tom VIII or Tom IX yet, though.

4

Tom V

I'm not sure the world needs more versions of me

5

Ok who starred that

9

Matt

Likely someone that agrees with you :)

Joe Obbish

16:54

@TomV It was me! Sorry, couldn't resist

Apparently I can't unstar it

Tom V

@JoeObbish you must be new here if you think I'm actually offended

Joe Obbish

I am new here, thanks for noticing!

Lamak

@JoeObbish after a couple of minutes it's there forever and ever

dezso

@AndriyM how do you explain the existence of @TomA then?

Andriy M

@dezso Easy. "A" stands for "anomaly", for all I know :)

Tom A

17:11

A is for my last name as I was tired of getting notifications for every "Tom" on the se sites I frequent

Lamak

@AndriyM and here I was thinking that @billinkc stands for it instead

Andriy M

@Lamak So you basically considered Bill to be one of a kind. That's nice, I'm sure Bill would appreciate that.

Lamak

17:44

@AndriyM eeew

Andriy M

excellently expressed, explanation-wise?

Lamak

it's like you can read my mind

Andriy M

@Lamak Curious, that's the kind of response we use in similar situations too. People are not that different, after all, even when living many units of distance apart.

Lamak

@AndriyM I'm the same as everyone?

Tom V

We have a new series on the Flemish culture it's quite hilarious

Het soldenritueel

►

Though that one isn't specific to Flanders but gives you an idea about the tone

Sean Gallardy

18:03

@TomV LOL - it's like black friday but much much more civil.

Andriy M

@Lamak Of course not. It just that there appear to be more things in common between various people than I myself thought. Otherwise I'm sure you have an entirely unique personality.

Like everyone else ;)

Tom V

@SeanGallardy There isn't much online yet as it's a new series, but this one was good too

Homo Flandriens - File (traffic jam)

►

Sean Gallardy

@TomV I just watched it and thought - that's not too bad! A normal day getting through LA traffic was ~ 1 hour per 10 miles.

Tom V

18:18

@SeanGallardy I just like the concept of watching your culture through as if it was a wildlife show with David Attenborough

Sean Gallardy

@TomV AHAHAHAHA - for sure!

Andriy M

@TomV Similar to this one:

Jockeys in the wild - Big Train - BBC comedy

►

Paul White

19:12

@JoeObbish The sorts in your answer are CQScanInMemSortNew see
https://sqlperformance.com/2015/05/sql-plan/internals-of-the-seven-sql-server-sorts-part-2

Grr mobile copy paste

Joe Obbish

19:39

@PaulWhite Thanks, I'll have to save that one for later. Seems like you have a blog post for everything

Someone suggested that my question should have been posted on code golf

1

Q: How can I convert the first 100 million positive integers to strings?

To be upfront I'm not aware of any practical application for this question. I'm just interested in efficient ways of generating the data within SQL Server. I'll try to start with a somewhat formal definition. A string is included in the series if it only consists of capital letters from A - Z. T...

sql-server query-performance

Any second opinions? It seems nothing like any of the other questions that I've seen on there

Tom V

reminds me of a Q by I believe sp_BlitzEric, lemme search

41

Q: What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

What this isn't about: This is not a question about catch-all queries that accept user input or use variables. This is strictly about queries where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a predicate, and different ways to rewrite those ...

sql-server

Not the problem at hand obviously but the "I'll start, does anybody have other methods" part

I like them, I have no issue with those being here, but I'm only one vote in the community

Well, 2 votes, one for your question and one for your answer but hey

Joe Obbish

Is posting a self answer really relevant though? If I had waited a day to post it that wouldn't have changed whether or not it was on topic, right?

@TomV Can you vote down all other questions so mine looks better in comparison? :)

Tom V

@JoeObbish Nice one

dezso

@ypercubeᵀᴹ dba.stackexchange.com/a/173371/6219

seems to be a comment to you

Tom V

@JoeObbish Personally I don't think the answers (whoever posted them) validate or invalidate a question

Paul White

19:49

@JoeObbish No idea if it would be on topic for Code Golf. Seems happy enough here. I don't see any close votes, which is the normal sign of a poorly received question.

Tom V

I would expect Paul to show us a meta post discussing the subject though

Paul White

I suppose it's technically off topic but we shouldn't really be lawyers about that - it's the impact on the site we should care about IMO.

Joe Obbish

yes, there appears to be an infinite supply of meta posts

Paul White

Meta search is right out of the question on mobile!

Matt

Self answers are supposed to be fine. As long as the Q and A are treated individually to their own standards. Sometimes it can be advised to wait a while before answering as to not discourage others but it is not a hard rule. PPCG does not really do those types of questions.. as it is not a challenge per sei.

PPCG might allow these but I have not found a meta for it. In general they are not abundant.

Paul White

19:52

I'm not sure we'd want thousands of questions in the same vein, but high rep users have earned a bit of latitude I reckon

Joe Obbish

Is it off topic because I say there's no practical use case? I would describe it as "Advanced Querying"

Paul White

dba.stackexchange.com/help/dont-ask

Joe Obbish

oh, I see

Paul White

I agree with Matt about the self answer thing

Joe Obbish

Well, technically I do have a use case for it

but it was silly so I didn't mention it

I could add it to the question if that would improve the quality of the question

Matt

19:55

Reasons and motivation can help the Q&A format if users come back to you looking for more information to understand your question.

but I don't think it is an absolute must? However I cant speak for this stack.

Paul White

@JoeObbish Sure

Tom V

Apr 3 at 19:04, by Tom V

@PaulWhite Self answers are just blog posts, not questions :)

Kidding as usual

Paul White

Yeah but we pay no attention to that guy

Tom A-Z

Joe Obbish

here's the practical use case

suppose I want to generate all possible Tom user names

Tom V is one example

but I want to go in order

Tom V

Oh There we go

Joe Obbish

19:58

Tom A (who was here previously), Tom B, etc

how an I do that in sql server?

the first 100 million user names should be sufficient

Paul White

Logins eh

Joe Obbish

@PaulWhite That's the real reason. Someone asked for a full list of MS reserved logins

I thought that I would try to generate all of them by brute force, mostly out of curiosity to see what kind of rate I could get

Paul White

XY problem!!!1!

Joe Obbish

the problem of generating all strings in order seemed interesting

yes, you caught me

but I could only create 55 logins per second so the efficiency of the string code didn't matter

my problem is that I was doing something stupid

Paul White

So the opening paragraph of the question was its own worst enemy

Tom V

20:01

@JoeObbish Yes, I can see why you would be ashamed of posting the use case

Joe Obbish

@PaulWhite Without it I thought I would get a lot of comments along the lines of "This is stupid, why are you doing this?"

I'm doing it for fun!

Tom V

And I'm not deleting that because it could be offending anybody ;)

Paul White

We hate fun as it's well known

Joe Obbish

maybe I'll go with the Tom use case

Tom V

And if you added your use case along the lines of "I want to create Tom V to Tom XYZ users" the only correct answer would be "don't"

Paul White

20:04

Or just say it's an abstraction of a real problem and leave it at that

Tom V

INB4 @Lamak makes the joke

Joe Obbish

I want to create the Tom A to Tom HJUNYV users

hmm

what if I want to generate hash collisions in sql server?

going in order like that is probably the wrong way to do it

I did write some oracle code once to find a collision in their CHECKSUM function

and after a few hours I got one

Tom V

A data migration in need of autogenerated account numbers or something like that?

Paul White

Kejser did one for SQL Server if memory serves

Tom V

A data obfuscation algorithm?

Joe Obbish

20:08

Those are pretty good, you definitely earned the "V" by your name

Tom V

Customer A to Customer XYZ

Paul White

No kejser.org/exploring-hash-functions-in-sql-server

Joe Obbish

@PaulWhite From his about page: "When I play computer games to relax, I approach them from a tuning perspective."

He seems intimidating

Paul White

I would think a native compiled in memory procedure would be fastest

Ha yeah

Joe Obbish

@PaulWhite For testing hash algorithms?

Paul White

20:15

@JoeObbish Sorry no I had gone back to your question

Joe Obbish

ah, I see

Tom V

@JoeObbish I would be interested in a data obfuscation solution myself, I created one for the BI team so they can do demo's on real data with fake customers or items but it's pretty crappy so any improvement I can implement would suit me

Joe Obbish

I haven't tried anything in-memory yet

Paul White

Which leads us to saying the job is best done outside SQL Server maybe

@TomV Something not suitable for the built-in data masking?

Tom V

@PaulWhite The idea was to use existing SSAS cube solutions but instead of using the real customers etc (with their turnover) it would show "customer one => 43234 euro" etc

Paul White

20:20

@JoeObbish You have to relearn while loops and goto but the end result can be amazing

@TomV OIC

Tom V

@PaulWhite I hadn't heard about data masking so maybe in a future requirement it might be useful

Thanks (again)

The original implementation was on SQL 2008 IIRC but still, thanks for teaching me something I didn't know about

From the brief reading so far the email orpartialfunction could have done an acceptable job in versions where the feature is available

I'm turning into typocube with that last edit

Paul White

20:39

He's contagious like that

Joe Obbish

21:52

server's at 99.8% CPU

we're getting our money's worth!

Nelson Casanova

23:36

aloha

Transcript for

The Heap™ – Consultancy ©®