The Heap™ – Consultancy ©®

2:04 AM

0

Q: In what ways is Microsoft SQL Server less standards compliant than PostgreSQL?

In what ways is SQL Server less-standards compliant than PostgreSQL. Such that it, Breaks the SQL Standard. PostgreSQL does not. I can think of a couple major ones, PostgreSQL does not yet implement IDENTITY COLUMNS but currently Microsoft has an unrelated feature by the same name, potentia...

jcolebrand

3:45 AM

@PaulWhite @AaronBertrand @JNK @JackDouglas @room what do you think about this?

0

Q: Do we prefer database comparison questions or are they offtopic?

This is not the same as: Should we allow, even encourage, "feature comparison style" questions? Do we want to support questions such as: MySQL vs MariaDB Postgres vs MySQL Oracle vs MSSQL (I'm not going to list the entire potential cross-set) Poking around, I've come across this list of...

discussion faq allowed-questions site-definition comparison

Can I get a set of weigh-ins please?

Aaron Bertrand

4:19 AM

I don't like it - what is the point of the question? How can there be a right / comprehensive answer? I could maybe see a "list the ways database x violates the standard" but not "how is database x less compliant than database y"...

I didn't weigh in on-site because I didn't want the appearance of other interactions with Evan (and they're always rough - I think we're similarly abrasive) to have any influence.

Never mind that one would have to have access to the standards documents and enough time to exhaustively weigh every single feature against the spec. Sounds like a thesis not a q & a.

Paul White

6:08 AM

@jcolebrand So much meta.SE on this. I don't have time to read or respond right now but:

31

A: List questions: Community Wiki?

There's nothing inherently wrong with your "I need a comprehensive list" question; It's just that we specifically forgo asking these types of questions because they are not a good fit for this type of Q&A site. Stack Exchange is well-suited to asking very specific questions that represent real ...

18

A: What is the definition of a list question?

I just want to point out we need a clear unambiguous definition of when a question can be called a "list question" or alternatively why we shouldn't use it as a definition. It's not a definition, a condemnation, or even really a classification. It's... Shorthand. For straw-polls, GTKYs, di...

I'm closing this question as off-topic because "you should only ask practical, answerable questions based on actual problems that you face. Chatty, open-ended questions diminish the usefulness of our site and push other questions off the front page." - see What types of questions should I avoid asking? — Paul White ♦ 27 secs ago

Mikael Eriksson

@PaulWhite I found the some documentation on rewind/rebind for TVF operator here msdn.microsoft.com/en-us/library/ms191158.aspx Also works for the XML flavors of TFV operators but not for the TFV used by the internal STRING_SPLIT. The properties are there but it never rewinds for some reason.

Paul White

@MikaelEriksson TFV? :) Interesting about STRING_SPLIT.

Mikael Eriksson

@PaulWhite I blame that it is still 07:20 where I'm at :)

You are probably well into christmas eve by now.

Philᵀᴹ

7:23 AM

Isn't it New Year down there? :)

Philᵀᴹ

8:34 AM

Lack of native regexp support in SQl Server 2008r2 is a pain in the bum

Paul White

9:21 AM

@MikaelEriksson Not quite. 22:21 on the 23rd.

hot2use

Good morning

Paul White

9:40 AM

"morning"

@JackDouglas For certain values of "a bit out of date" which approximate well to "hopelessly outdated" :)

Jack Douglas

10:19 AM

@jcolebrand I've weighed in by closing a couple on that list, thanks for the effort on the meta question!

Jack Douglas

10:43 AM

@PaulWhite OK you are right :)

James Lupolt

Are there any trace flags that show more information about why a parallel plan could not be chosen?

Paul White

11:05 AM

@JamesLupolt No. You have the showplan reasons, and that's it.

0

A: Do we prefer database comparison questions or are they offtopic?

These types of questions are harmful to the site and should be quickly closed and probably deleted. I know that SO has a no-shopping-list questions policy, and this feels like a shopping-list. It's not just SO, this has been established network wide for a long time. Just two meta.SE example...

Tom V

@MDCCL well deserved

Philᵀᴹ

11:35 AM

Very quiet today. Sheffield was like a ghost town this morning

Paul White

11:59 AM

My home town was absolutely heaving.

I still have some shopping to do, but based on what my wife said about the mall traffic today I passed. Yay shopping on Christmas Eve.

hot2use

12:58 PM

I still have some shopping to do. Hopefully everbody else will be at home.

Haha

Paul White

Hope is cheap :)

hot2use

And if anbody here upvoted on my answer, thanks. It earned me the snapchat hat.

3

A: Recommendations for more than one tempdb file

We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search. Microsoft has a knowledge base article: Recommendations to redu...

And I reached 1k this year. :-)

aiming for 2k now

Philᵀᴹ

1:26 PM

I take it all back, Meadowhall is hell on Earth. Though I do have some shiny new Bose QC35 headphones to remove the noise :) Yay for buying presents for yourself!

James Lupolt

2:12 PM

@PaulWhite Thanks

Paul White

@JamesLupolt What's the scenario exactly? Is it worth getting into?

James Lupolt

@PaulWhite A simple table scan that won't go parallel due to CouldNotGenerateValidParallelPlan, even with TF 8649.

Maxdop is > 0, cost for the plan and operator are above cost threshold for parallelism.

Resource Governor has the default config.

Max dop is 0, I mean. On a machine with 32 CPUs.

Nothing weird in the table definition, like a computed column with scalar function calls in the definition.

Paul White

That was my first guess.

James Lupolt

I'll look into it more, but I'm probably missing something obvious.

Paul White

2:39 PM

@JamesLupolt If you get stuck and can share some details, let me know.

There are lots of edge cases and I tend to forget them unless prompted by seeing something (e.g. in a plan shape) that reminds me of it.

TypoCubeᵀᴹ

3:48 PM

Anyone knows a Ben Larson, from New Jersey?

SQL Server on Linux, aka project Helsinki: Story behind the idea https://blogs.msdn.microsoft.com/slavao/2016/12/20/sql-server-on-linux-aka-project-helsinki-story-behind-the-idea/

Tweeted by jlupoltsql on December 23, 2016 at 11:49 AM

jcolebrand

4:15 PM

Morning all

@JackDouglas of course. I'm also not sure that I've found all of them, but I wanted to go ahead and get community consensus before I started acting alone. Now that I've found them, encouraged a discussion, and someone else closed/deleted them, I'm okay with the outcome. Didn't want to be a lone ranger.

@PaulWhite I could have seen an argument made for they are product comparison for a highly niche market, but otherwise I already knew about that, so I wanted to offer the site the option to discuss.

bluefeet

4:31 PM

1 hour for 200k rows

9

Q: SQL query takes more than an hour to execute for 200k rows

I have two tables each with around 200,000 rows. I have run the query below and it still hasn't completed after running for more than an hour. What could be the explanation for this? SELECT dbo.[new].[colom1], dbo.[new].[colom2], dbo.[new].[colom3], dbo.[new].[colom4], dbo...

sql sql-server performance ssms

Paul White

5:21 PM

On phone

TypoCubeᵀᴹ

5:47 PM

another secret hat

My avatar looks like a CanCan dancer

Evan Carroll

HALLO EVERYONE.

My question got closed. =(

Shhh we can't ask about SQL Sever being less SQL-compliant than free PostgreSQL. That's a hush-hush subject.

Paul White

@TypoCubeᵀᴹ Certainly setting a trend!

TypoCubeᵀᴹ

6:02 PM

Secret Hat Hint #2: This time for 2 hats 6 and 8,243,721. Bert & Ernie have always been companions. As companions, they could work together with each other. — bluefeet ♦ 21 hours ago

Paul White

6:15 PM

@EvanCarroll Ha. I knew you'd say that but it's not the case at all. You can be as mean as you like about SQL Server as far as I am concerned - I find your stance amusing for the most part - but that was a terrible question and got what it deserved.

Evan Carroll

why do you feel that way?

I mean, let's say I wanted to pick between two databases with a specific criteria -- why should that question be off topic? We have the expertise on the network to answer it. It's a very specific question about adherence to spec on two versions.

Paul White

@EvanCarroll I explained my reasoning on meta.

I'm happy to chat further about it, just don't want to repeat myself unnecessarily.

No doubt one of the founding principles of PG was standards compliance, and SQL Server will fall well, well short of it, somewhat for historical reasons. I have no issue with that at all. I'm pleased for Postgres.

TypoCubeᵀᴹ

@EvanCarroll Yet, your question focuses unnecessarily on one side ("why product X is less compliant that Y").

Why didn't you phrase it in reverse?

Paul White

It's just a terrible, terrible question for Q & A format.

Like next-level HNQ.

Evan Carroll

I'd be fine with a question phrased in reverse. It's a totally valid question. I just don't have the funds for Microsoft SQL Azure Data Warehouse Edition to begin with, so it seems as if I'm wasting someone's time by pretending I'm interested in that.

Paul White

6:24 PM

@EvanCarroll Valid, interesting ... it has many fine qualities I'm sure. Just not here.

Evan Carroll

I would like to know in what ways PostgreSQL still leads the pack. Because, last I checked, it was pretty massive. pg-versus-ms.com

TypoCubeᵀᴹ

I was thinking 2 days ago of posting a somewhat related question, based on another I answered recently: dba.stackexchange.com/questions/158617/…

Paul White

@EvanCarroll Perhaps someone will do a detailed analysis on it at some point. I hope so. Just not here.

TypoCubeᵀᴹ

Note the comments: I just mentioned - in comments - some differences between MySQL and Postgres and I was warned that I might offend others.

Those are some valid points why to stay away from MySQL, i suggest you to lower your tone or we'll get based by fans. BTW can you edit my question, @Evan Carrol edited it wrongly. — justesting Dec 19 at 22:38

Evan Carroll

@PaulWhite what about this question?

erg

-4

Q: When did Microsoft SQL Server implement Identity Columns?

I noticed that the SQL 92 spec adds to the list of <reserved word>s IDENTITY. As a matter, of trivia I'm wondering when Microsoft SQL Server implemented it. On Wikipedia, Microsoft SQL Server dates back to April 24, 1989. But, I doubt it had IDENTITY that long. Which came first, the reservation...

sql-server sybase identity reserved-word

how is that totally non-subjective question with 0-room for discussion not on topic?

Paul White

6:28 PM

@TypoCubeᵀᴹ Yep.

jcolebrand

@EvanCarroll Because we don't rehost wikipedia content

That's the purpose of wikipedia

If you think that something like that, which your question is encyclopedic in nature, needs to be defined somewhere, update the encyclopedia please

TypoCubeᵀᴹ

So, I was thinking of posting a question - and answer - about how to migrate UPDATE statements from MySQL to Postgres and vice versa and what pitfalls one might have. But I haven't decided yet. To me it sounds like a specific question that might be helpful to others - but I'm not sure if it will look offending to others. And it is kind of similar to the SQL Server - Postgres question that was closed - but not so general. Focuses on a tiny part of different, only UPDATE statements.

Still I expect to write a few pages. . I can't imagine how many differences I could write for the whole SQL language between 2 products

jcolebrand

We don't mind if you're trying to figure out why your version of SQL Server doesn't support that value, but the odds that someone is running a version that old is basically zero, which means no market to support it, which means we don't need it here.

We are professionals and we try to cover professional problems, not professional curiosity, that's what chat is for

Evan Carroll

that's what this really comes down, "looking offending" to others. Mainly sensitive Microsoft users losing market space.

@TypoCubeᵀᴹ ask it, I'll upvote!

PostgreSQL users aren't afraid of competition from toy databases.

TypoCubeᵀᴹ

@EvanCarroll I may do. I have to finish an edit I have promised for another first ;)

Paul White

6:31 PM

@EvanCarroll Saw that earlier. It seems on topic to me, albeit a bit of a trivia question that people tend to disapprove of these days. The "community" took a view with votes that you could ask about on meta. I have to say I did not enjoy the comment discussion that led to the question being asked, but that's another issue.

Evan Carroll

DESTROYER OF FUN.

Paul White

(not me this time)

@TypoCubeᵀᴹ It would have to be carefully written indeed.

jcolebrand

monkeys and guns man

gotta keep an eye out for those guys

Paul White

@EvanCarroll Not at all. As I said before. It's the harmful effect on the site I object to.

@TypoCubeᵀᴹ Yes get on with it man!

Evan Carroll

Where do you even qualify why you think it's harmful.

@PaulWhite ^

Endless partial answers
Many off-topic comments, side-discussions, and arguments
Answers (and voting) based on personal opinion, interpretation, or bias
Answers that will date rapidly (e.g. with the next service pack)
Link-only answers ("I have the perfect answer on my awesome blog!")
One Q & A per product/edition/release combination would be needed
Likely to be just a rant in disguise - why does product x suck so much?

Paul White

6:37 PM

Was that a rhetorical question?

jcolebrand

@PaulWhite seemed it to me ...

Evan Carroll

Look at this question that @PaulWhite "protected"... dba.stackexchange.com/questions/20335/…

15 answers.

Tremendously popular.

No comparison.

subject to everyone of those arguments.

still useful.

Actually like The Top Question.

So in what ways do you see asking about spec adherence worse than that question?

jcolebrand

I don't see that as being the same. That's asking if the version of the database will support the workload they want to support. Seems pretty definitive to me.

Evan Carroll

More definitive than asking in which ways an implementation fails a written and approved ANSI specification?

Paul White

@EvanCarroll It's not necessarily worse. That is an old question from a different time in the site's development. We'd kill it with fire from orbit these days. The existence of another terrible question is no justification for asking a new one.

2

Evan Carroll

6:40 PM

But, it's not terrible man, nor is it harmless. Those are just libels.

Paul White

@EvanCarroll Because it would cause harm now, not in the past.

Evan Carroll

It's the third most upvoted question on the site. I'm confused at why you want to kill it. And, what makes it harmful.

jcolebrand

For starters, it's too localized. Nobody else is doing the spectrographic analysis he is.

For seconds, it's too varied, leaving too much room for personal opinion

For thirds, it's too easy for someone who doesn't know how to do what needs to be done to shoot themselves in the foot

Evan Carroll

too localized, by whose definition yours or the 238 people that read it and upvoted it?

jcolebrand

@EvanCarroll I literally gave my reasoning.

Paul White

6:43 PM

@EvanCarroll Oh sorry I only looked quickly (100 tabs open already) thought it was a different question from memory. I upvoted that one actually. It does have 9 deleted answers of very low quality from 1-rep users, which is why it was protected.

jcolebrand

If you're going to argue with me when I list a fact, then please respect the fact that I gave a fact and don't ask me what the fact is.

@PaulWhite good because I thought that was a good question too, but I can see reasons to close it nowadays :p

Paul White

Yes we (the community) would likely close it these days.

Evan Carroll

Calling the third most upvoted question too localized doesn't make much sense in my world. That's not how I read that.

jcolebrand

Just because it got a lot of attention when the site was young doesn't mean it would get the same attention today

Paul White

Popular has never equated to good necessarily. See also: HNQ.

Evan Carroll

6:45 PM

Only one way to tell. Delete it and ask it again!

Paul White

@EvanCarroll I can't parse this message at all. Can you clarify?

Evan Carroll

Yes. I can. I'm unsure what you're arguing too at this point. You keep calling these questions "terrible" and "harmful". There is no criteria for that. They're not. The third most upvoted question is more of all of those things you've raised as problems, it's far more subjective not relating to an industry spec, and it's insanely popular. We must be using different metrics for "terrible" and "horrible", but yours must be totally divorced from the community.

jcolebrand

Here's the deal Evan

I've been a member of the SE community for as long as you have, if not longer. I know what constitutes crap when I see it. I was one of the very first moderators on this site before we had our first elections

I helped define the site

We let some questions through at first so we could build a rep, set a baseline, and now we have evolved.

When we first started, there wasn't a Paul White, or an Aaron Bertrand, or whomever, to answer some of these questions. We grew organically.

NOW, however, we would like to impose different metrics on the site

Evan Carroll

Totally fair, I'm just asking for you to define those metrics.

jcolebrand

In that case, the thing that makes it too localized for me is that almost nobody else will have his use case, he would need to consult with a professional dba locally to really optimize for what he's trying to do (granted, in this modern age, you could do it via RDP or whatever, but that's not important)

Yes he has a fun puzzle, but it's unlikely to help future users of the site

It doesn't really give a lot of benefit to explain how to set up his database and etc until he gets a little more knowledge, and nobody else is likely to benefit from that specific case

There are edge cases that can be drawn from what he describes and then we can make it into a general post, or rather, better to have a series of specific posts that address his needs

One for the schema ideal, one for the perf tuning characteristics, one for the index management

That would be better than one global "will this work" question

Paul White

6:55 PM

@EvanCarroll Let me be clear at this point. I said your question (compare standards) was terrible. It has net -5 down votes and was closed by five people. That's quite bad. It's also terrible for the site, in my opinion as stated on meta. I think questions of that type are harmful again for reasons stated on meta. (side note: I already said I misread the MySQL question for another.)

Evan Carroll

So then you're talking about who the question benefits? But, I'm telling you 238 people voted it up, and got something out of it. That's how we're supposed to read into upvotes. And, moreover, my question was about spec adherence? How can you possible argue that it wouldn't benefit PostgreSQL users to know in what ways MSSQL violates the spec?

Anyway, it doesn't matter much. At the end of the day Microsoft is totally averse to these kinds of questions, and their borg will protect their interest. Oracle is the same way (even preventing you from publicizing benchmarks of their database). Same old stuff. You don't want to ruffle feathers. I'm just erked we're not honest about it; we don't want to "look offending." Nothing new.

jcolebrand

@EvanCarroll And I can see the vote history, and it didn't get 238 upvotes in December 2016

@EvanCarroll who the site benefits has always been the primary concern of volunteer moderators

Paul White

@EvanCarroll To be clear: you just called me Microsoft borg yes?

jcolebrand

Not one of us is employed by Microsoft or Oracle in their SQL Server divisions (as far as I know, anyone care to correct me?)

Your question, by the way, was about trolling users of one database system.

Paul White

Not me. Nor anyone else as far as I am aware.

jcolebrand

6:57 PM

I don't have a gargantuan knowledge of the internals of both database engines enough to be able to say "yes, see line XXXX in file YYYY for the reason why that breaks compliance on that engine, but see line AAAA on BBBB for why it works on the other"

I would need that information to adequately answer your questions

I could guess based on specific query results

But you should honestly consult both companies PR departments to ask about those things

I bet someone there even has a whitepaper for reading

Evan Carroll

@jcolebrand I also don't have that knowledge, but this seems to be the place to ask... I do have knowledge of PostgreSQL violations of the spec.. That's because postgresql actually publishes this in each section under CAVEATS.. Microsoft doesn't. Again, against their interests.

So I wanted to ask, and this seems like a good place to do that.

jcolebrand

@EvanCarroll So why make it about DB one vs DB two?

Paul White

@EvanCarroll Chat would be an OK place to ask it, yes. Main, no.

jcolebrand

Why not just say "Where can I find a canonical list from Microsoft about their caveats of SQLXXX spec?"

You could then demonstrate that Postgresql has a similar list that would be a counterpart to the one you're asking about

Paul White

Because that would be a shopping list question.

jcolebrand

7:01 PM

Then it's not A vs B

Evan Carroll

Because this question was rooted in a dispute with Aaron. And, I don't have access to Microsoft SQL 2016, or MSDN, or their tech network to find the limitations of their database, but past experience with MS SQL leave me to believe that they're ample examples that are likely to persist.

jcolebrand

Wait, you were arguing with one of the canonical reference people on the internet about his area of specialization when it isn't your area of specialization?

Evan Carroll

And, btw, I would use MS SQL over MySQL. So on a personally level, MS SQL has not achieved MAX HATE. At least I never lost data with it.

jcolebrand

I gotta go do work work, so I'll be back in a bit

Evan Carroll

I think I was right about IDENTITY but that question was closed too, because it was encyclopedic.

RESERVED in the spec in 92, added in MS SQL as an unrelated feature in 93. Not spec compliant in 2016 because of legacy use from 93'.

Story continues...

Paul White

7:04 PM

@EvanCarroll SQL Server 2016 Developer Edition (all the features of Enterprise) is a free download.

As is SQL Server v.Next preview if you want to try it on Linux.

TypoCubeᵀᴹ

@EvanCarroll the MSDN pages are on the Internet, without any registration needed. You don't have access to it?

Evan Carroll

@PaulWhite SQL Server v.Next runs on linux?

Paul White

@EvanCarroll SQL Server has a lot more back-compat responsibilities that PG :)

@EvanCarroll Yes indeedy.

TypoCubeᵀᴹ

@EvanCarroll sudo apt-get install sql-server ;)

Paul White

microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux

Evan Carroll

7:07 PM

And, i'm willing to accept the more back-compat thing. It's just the end user has to value that. And, in 2016 I don't personally value what geriatric baby boomers were forging on 8086s.

Jack Douglas

@EvanCarroll it's a horrible question and hardly anyone on this site voted for it.

Paul White

@EvanCarroll Modern MS is much better about breaking with the past, under compatibility levels and whatnot. Only a few years ago, they wee terrified of breaking existing apps because some customers are very large and very valuable and very stroppy when stuff breaks.

@JackDouglas Oh was it mostly SO carry-over votes?

Evan Carroll

Yes, well, last I checked they still had to turn on MVCC because apparently, concurrency control wasn't backward compatible with random breakage.

Paul White

(current SQL Server v.Next Linux install options ^^^)

Evan Carroll

Anyway, I'm going to look at installing v.Next today, thanks for the heads up on that.

Paul White

7:10 PM

@EvanCarroll Wut?

At least SQL Server "MVCC" cleans up after itself ;)

TypoCubeᵀᴹ

@PaulWhite cleans up?

Paul White

Oh I see you mean being able to enable and disable row-versioning isolation levels on a per database basis. Yeah, nasty :)

Jack Douglas

@PaulWhite yes — I remember upvoting this because it correctly identifies the question as being remiss dba.stackexchange.com/a/20348/1396

Paul White

@TypoCubeᵀᴹ No VACUUM FULL. When versions aren't needed any more they are garbage collected.

@JackDouglas Interesting! Thanks. So good having you and @jcolebrand around.

TypoCubeᵀᴹ

@PaulWhite Ah, ok. Yes, Vacuum background processes have their flaws and disadvantages. Still.

Jack Douglas

7:13 PM

it's nice to be here :)

fwiw I really like pg's implementation over Oracles

Paul White

Doesn't Oracle read rollback segments for MVCC?

Jack Douglas

but which one is better kind of depends on what you are trying to do

@PaulWhite yes

Paul White

@JackDouglas As always.

Jack Douglas

aka 'undo' these days

Paul White

It's been a loooong time since I touched Oracle. My terminology will be ancient.

Jack Douglas

7:15 PM

I didn't realise you'd ever touched Oracle!

Paul White

I can see an Oracle 8 DBA handbook from here.

Evan Carroll

VACUUM FULL is awesome. =)

Jack Douglas

ha :)

Paul White

@EvanCarroll But manual?

Evan Carroll

I always picture Rosie the Robot Maid when I run it.

I never run VACUUM FULL manually unless I'm importing a ton or rows, or rewriting a table.

Jack Douglas

7:16 PM

VACUUM FULL is almost never necessary

just like index rebuilds are almost never necessary

normal vacuum is just fine

Paul White

@JackDouglas There was that question yesterday that @Evan answered I think where the guy's table blew up or something?

Evan Carroll

he probably deserved it.

Paul White

I find the concept of writing new rows to a table on update bizarre.

Evan Carroll

PostgreSQL is morally sentient

Paul White

But I guess it's what you're used to.

Also I get the impression that PG is more built for comfort than speed.

Oh come on people that's some of my best trolling right there.

Jack Douglas

7:19 PM

@PaulWhite Evan's answer is a bit misleading

Evan Carroll

If you get too comfortable, hardware test your productions machines by taking them apart power-last.

Jack Douglas

@PaulWhite it's very fast at commit and rollback ;)

Evan Carroll

VACUUM FULL to save the day.

Paul White

@JackDouglas Well that's the main thing then. Faster than SQL Server 2016 using Storage Class Memory?

How come everyone has this fancy new secret hat except me?

Jack Douglas

@PaulWhite er, yes, it's basically instant.

Evan Carroll

7:23 PM

I got #1 for the last week. FUUU YEA.

+1 PostgreSQL, -1 SQL Server.

Paul White

@EvanCarroll I gave away more in bounties last week than you will ever earn in your lifetime here.

Evan Carroll

Probably.

Paul White

(disclaimer: some facts quoted may not actually be facts and may be totally made up)

But congrats anyway. It's nice to see you contributing positively on main.

Evan Carroll

Nah, likely true.

Paul White

@JackDouglas Oh so cheating is involved then. I see.

Jack Douglas

7:26 PM

@PaulWhite no cheating, just table bloat

Evan Carroll

@PaulWhite but I'll have you know, you're only winning by two hats.

Paul White

@JackDouglas Yep. Cheating.

Jack Douglas

I'm not saying the transactions are fast, just the commit

Right, but my impression is that the MVCC should not be creating tuples for all the tuples it's modified over the course of the transaction. That is to say, when the first INSERT runs Postgres creates a single tuple, and it adds a single new tuple for each UPDATE. Since the UPDATES are run for each row 500 times, and there are 10000 INSERTs, this amounts to 500*10000 rows = 5M tuples at the time the transaction commits. Now this is just an estimate, but regardless 5M * say 50 bytes to track each tuple ~= 250MB, which is MUCH less than 30GB. Where's it all coming from? — Nikhil N 24 hours ago

Paul White

@EvanCarroll I peaked yesterday. I refuse to sell my soul to Dev Story or Docs.

Jack Douglas

this comment by Nikhil is interesting —I think he is right but postgres just lacks that optimisation

Paul White

7:27 PM

@JackDouglas Yes I see. I think we discussed this before. Epic cheating.

Jack Douglas

:)

Paul White

@JackDouglas So out of interest, where is that 250MB vs 30GB coming from and which optimization is missing?

In small words, spoken slowly.

@EvanCarroll Also I have had no time to answer questions today due to a sudden uptick in moderation time demands.

I suppose I could go for the self-answer hat.

Evan Carroll

I GOT IT.

Paul White

But then it is Christmas Eve and I have wrapped no presents.

@EvanCarroll The self-answer hat? Yes I think I saw your self-QA earlier and thought (aha! hat!)

4 hours ago, by TypoCubeᵀᴹ

SQL Server on Linux, aka project Helsinki: Story behind the idea https://blogs.msdn.microsoft.com/slavao/2016/12/20/sql-server-on-linux-aka-project-helsinki-story-behind-the-idea/

Tweeted by jlupoltsql on December 23, 2016 at 11:49 AM

Evan Carroll

7:43 PM

> So I decided to pitch this approach to the SQL Server team and I got an offer to the take the job. I started the new job on early in 2015 and within less than a month we had SQL Server booting up and able to run queries on Linux. Sometimes it would crash or hang, but it was there and running

A perfect port, with all of the drawbacks of the native version. That's amazing.

Give that guy a Nobel.

Paul White

@EvanCarroll You might also like: blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/…

TypoCubeᵀᴹ

@JackDouglas You are probably right, I'm not certain of various implementation details. But I think there's more involved in that question that that. Their code is causing the issue mainly I believe. Check my comment. I'll do some testing and may answer.

Paul White

Dec 20 at 13:41, by Paul White

> In addition, the code base is huge. There are tens of millions of lines of code in SQL Server.

Whereas Postgres has 502. All jQuery.

TypoCubeᵀᴹ

@PaulWhite Too much to be believable.

Paul White

→ 1 message moved to Trash

jcolebrand

7:50 PM

@PaulWhite I believe the codebase for SQL may be larger than the codebase for Windows, if I remember correctly ...

Paul White

@jcolebrand That sounds right.

jcolebrand

I bet I have a contact somewhere that can confirm that ...

So a fella that I know that used to work on the SQL team says that he thinks the Windows codebase is larger

Evan Carroll

That would be an interesting piece of a trivia that a lot of people would probably be entertained with. Just don't ask a self-answered question here. Remember that's encyclopedic. bwahhaha

jcolebrand

I wouldn't ask it on the main site

Paul White

Absolutely. We hate fun. And popularity. But not hats.

2

jcolebrand

7:54 PM

in chat, however, it's fine

Evan Carroll

Hats are pretty great.

I really want a sword though, this game isn't complete without PvP.

Maybe even a shank.

jcolebrand

Pretty sure this game is valid even without that :p

Evan Carroll

Death is out of place in this game. It fails an Isolation test. It tends to always effect others.

I want an IRC gateway into SE chat.

jcolebrand

@EvanCarroll several people have implemented such a gateway

Nose about on stackapps

Paul White

20

Q: Will the API support the chat system?

Having read through come of the questions on here it's my understanding that v 1.0 of the API is for read-only access to real-time data, pretty much what's available in the data dump. Write support is planned for version 2.0. My question is, will version 2.0, or some future version after that, ha...

feature-request status-deferred write-access chat

We did that yesterday. Phil asked about it.

TypoCubeᵀᴹ

8:06 PM

Anyone with vtc rep at SO:

I'm voting to close this question as off-topic because it has been cross-posted at DBA.SE. — TypoCubeᵀᴹ 2 mins ago

jcolebrand

@TypoCubeᵀᴹ added

Paul White

Ug.

wBob

0

A: Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited

Polybase is powerful but not really that sophisticated so will not be able to deal with this weird format. You have three options as I see it: Correct the file format at source. Instead of having weird mix of fixed-width and delimited file format, use a standard file format, such as .csv. Th...

"The point of using standard data interchange formats ..." is [answers on a postcard please].

@TypoCubeᵀᴹ will it be er Lipo-cube after Christmas? Ha. Ha ... do I need to explain? : )

Paul White

@wBob So glad you answered that. Even deleted your own comment. Kudos.

wBob

@PaulWhite Yeah sorry, I thought that would annoy you, but I was "AFK", in Germany in fact : )

@jcolebrand No cursors allowed in Azure SQL Data Warehouse, but yes I know what you are saying. I started down that path with the data as supplied by the OP, but I think it's dodgy. Have requested a realistic sample ...

Paul White

8:18 PM

I think azure-sql-data-warehouse is one of our longest tags.

jcolebrand

@wBob Right, I would prefer to just awk/sed it before processing up, as those regexes would be easier to implement, I think

Paul White

24 characters, 25 maximum IIRC

wBob

@PaulWhite I'm quite keen on creating an acronym for it, I often use "ADW" but it doesn't seem to stick. Basically MS don't use one, so I should put it in their suggestions box.

jcolebrand

@wBob Keyboard presses are free

wBob

@jcolebrand 30GB tho', I would beg for that to be corrected at source : )

jcolebrand

8:21 PM

@wBob autocomplete is free

Keep the verbosity

@wBob I know! What the crap! Oh well, such is life

Paul White

@wBob Longer tags are better. It's a shame about TDE.

wBob

@jcolebrand I say "beg", I mean "WTF? Do you really expect me to import this s#it? Any chance you could, you know, just use a standard format? Don't waste my time." [snark-to-max-as-it's-Christmas].

jcolebrand

@wBob hahahahaha

My snark is high today

So I'm trying to keep it under control

wBob

@jcolebrand Indeed, Merry Christmas : )

jcolebrand

@wBob And to you and yours!

Paul White

8:26 PM

> I started the new job on early in 2015 and within less than a month we had SQL Server booting up and able to run queries on Linux.

Less. Than. A. Month.

@TypoCubeᵀᴹ He deleted the dba copy facepalm

Oh and the SO one.

Probably for the best.

Paul White

9:48 PM

We overtook SO.pt in hats

jcolebrand

YAY!

TypoCubeᵀᴹ

10:24 PM

@EvanCarroll I have to say one thing abut your edits (ok, not all, some of them): they suck

dba.stackexchange.com/posts/114672/revisions

Philᵀᴹ

@jcolebrand "the SQL Server RDBMS and other services that ship with it in the SQL Server product suite account for more than 40 million lines of C++ code"

That's a hatload of code

TypoCubeᵀᴹ

Unrelated to the previous. Voted to close this. If anyone familiar with SSIS thinks it's clear, I can retract my vote

0

Q: ssrs expression to display first 3 parameter values if "ALL" is selected in Parameter List

I have a parameter to display a report, IF the Parameter is selected as "Select All"(All values in parameter list are selected) But I need the report to display values only for the first 3 parameter values selected. Is there any filter expression available for this,,Pls do the necessary help. Th...

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

Evan Carroll

@TypoCubeᵀᴹ none of that stuff is relevant.

@TypoCubeᵀᴹ we were talking about too localized. He made a very general problem specific to a script that it had nothing to do with.

TypoCubeᵀᴹ

@EvanCarroll It may seem that way retrospective. But changing an ancient question doesn't help anyone.

It has been answered. So why edit it?

Evan Carroll

Well, that's a good question, I feel differently about that. 100% of the people that find that -- from my estimation (would you disagree). will search for ERROR 1166 (42000) at line 1: Incorrect column name (because that's the only relevant thing). I want to make it more useful to them.

TypoCubeᵀᴹ

10:34 PM

And add the "resevred-keyword" tag? No, No. We don't add a tag because it was a solution. If the OP knew the reserved keyword was the issue, he would have solved it without asking.

Evan Carroll

So we can either close it for being too localized -- and that question is too localized, or we can trim it of useless information (unless you actually think that it's useful)

TypoCubeᵀᴹ

I'd rather close it.

jcolebrand

@Philᵀᴹ Yeah but Windows is nearly 60m loc

TypoCubeᵀᴹ

But I try to be really careful when editing old questions.

Evan Carroll

I try to make them useful for those likely to find them, while not going against the mode of the question.

TypoCubeᵀᴹ

10:36 PM

@EvanCarroll So why isn't it helpful as it is?

If someone searches for "error 1166" and finds the question, they see the answer which clearly explains the issue.

Evan Carroll

Because you're having to read through a script that has nothing to do with the problem. We have no evidence the person who answer question even read it, and we have no reason to believe he should have read it.

jcolebrand

@TypoCubeᵀᴹ please review the edit on that question for me?

TypoCubeᵀᴹ

Reserved keyword. And they are helped.

jcolebrand

@TypoCubeᵀᴹ I agree. Anything older than about two years is really not good for modification anyways

Evan Carroll

Exactly, that about sums it up: Using a reserved keyword error -> Don't use a reserved keyword. That's what's important to both the people asking the question, and those likely to benefit from it later. Why burden them with useless information? I mean, as a general rule provide surplus information, and let those that know what they're doing help you trim it if it's not relevant? Doesn't that sound like a sane practice for this site?

TypoCubeᵀᴹ

10:38 PM

@jcolebrand Seems fine. Except why did you remove the sql-server tag? Isn't custom to have the dbms and the dbms-version tag as well?

jcolebrand

I wasn't sure ... I can add it back

Philᵀᴹ

Anagram of "Larval Crone". Awesome

TypoCubeᵀᴹ

@jcolebrand I don't know but I feel like it's common these days. The dbms tag so anyone "listening" to sql-server doesn't miss the question and the version just in case it's relevant. I might be wrong.

jcolebrand

It was a fair point, I felt weird about removing it. I didn't think sql added any value for sure

TypoCubeᵀᴹ

@EvanCarroll It's a valid argument.

@jcolebrand Yeah. They even had mysql in the first revision. Another user removed it ...

And it's SSRS, not SSIS. I read wrong in this tiny font size.

Or I need galasses

jcolebrand

10:46 PM

Both of you be quiet or you'll get the boot

Philᵀᴹ

No, I'm here and civil. There was no need for that

Night

jcolebrand

He was making a joke

You think I don't get the gamut of "cole trickle" and "cole slaw" and "king cole"?

If you can't be nice I'm going to have to kick you from the room.

TypoCubeᵀᴹ

→ 4 messages moved to Trash

Evan Carroll

You can trash his joke too.

jcolebrand

You've already accused us of being paid shills for Microsoft and Oracle today, you've come in her contesting our long running policies, you've been abrasive on the site with your edits, it's not called for. We want you to succeed with us, but you're making it difficult.

TypoCubeᵀᴹ

10:51 PM

@EvanCarroll the original, Phil's copy or your re-copy?

Evan Carroll

All of them.

My skin is super thin. I'm a deep empathizing person with a 12-dimensional attachment to the universe. My chakras are lit af.

TypoCubeᵀᴹ

@EvanCarroll the original is an old comment on another room, and we wouldn't know about it if you hadn't let us know. So please don't post anything that you don't want us to repeat afterwards ;)

And I don't even know what larval or crone means.

The only crone I know is swedish coin

jcolebrand

Larval means like budding. It's from larvae. Which is when a bug or crustacean is very small

Evan Carroll

the original was funny though.

jcolebrand

A crone is like a witch, the two words are basically synonymous

TypoCubeᵀᴹ

10:59 PM

@jcolebrand Ah thnx.

Today i learned about Cassiterides Islands.

in a geography book I was reading in a book store. About fictional places or wrongly put on maps.

Evan Carroll

Yea, and calling people witches is a mean and nasty thing. I was TRIGGERED.

TypoCubeᵀᴹ

They were supposedly islands that only Phoenicians knew were they were - in Roman times - and they were bringing tin from there. (Cassiterus means Tin so you could call them Tin Islands.)
Speculation is that they might be the British Isles

On another page (of the same book) there were early maps of America, where California was thought to be an island

jcolebrand

@TypoCubeᵀᴹ How interesting!

I was completely unaware

Also, I'm very annoyed by many things today, including that I can't access a page that I need to in my web app

Evan Carroll

how many times have I been kicked or banned from this chatroom? does anyone have this statistic?

TypoCubeᵀᴹ

So the summary of the question is "Please provide me with arguments against OEM" (note that I have no idea what OEM actually is, I suppose it's a monitoring tool) — TypoCubeᵀᴹ 20 mins ago

it was more of a rhetorical question.

Yes exactly . I need arguments against it. — user2923332 14 mins ago

Too bad @Phil didn't see that question.

jcolebrand

11:09 PM

I feel bad that I ran him off.

Tried to reach out to him alternately, will see if/when he responds/returns

Transcript for

The Heap™ – Consultancy ©®