The Heap™ – Consultancy ©®

@EvanCarroll yes, it seems he gives a lot of bounties.

That's no proof that he has drones. What are the drones doing?

programming in a lab overseas submitting questions to a Project Manager that speaks fluent English but has no idea of technology.

Giving bounties - and getting rep from the advertisement - is not unheard.

I think I've seen it happen with another account.

That's not the case, in my opinion.

I'm not even saying I have a problem with tooling SO to run a pseudo-professional outsourcing outlet. Finding free expertise is an integral part of the problem -- even in my case when I replace SQL Server with PostgreSQL.

But, when I see the preponderance of evidence point me to that being what's happening, I tend not to need anything else to draw that conclusion.

12:20 AM

@EvanCarroll That could be the case, I agree.

I've written to Shog9 in other cases like this, I don't think SE cares, it's probably just search engine fodder from their perspective.

It's somewhat annoying because that guy has been on the site for 5 years, knows nothing about computer programming, gets routinely downvoted, and uploads screenshots of IDEs rather than copy-pasting code. That's how most of those drone-leaders work

5:07 AM

Hello and good morning. Wherever you are.

Mikael Eriksson

5:29 AM

Good morning

Good morning

5:43 AM

Morning

9:49 AM

'could not serialize access due to concurrent update'. How's that for good morning?

10:04 AM

@ypercubeᵀᴹ I said morning, I've temporary deleted word 'good' of my dictionary

Marian

10:27 AM

@ypercubeᵀᴹ stop using serializable, start using read uncommitted more :)

@Marian we use repeatable read actually

Marian

still bad, my friend, still too much

It'd be good to have, but I don't think there's really much there at the moment. It's particularly tricky because multiple statements are often involved and the culprit(s) aren't always the current running ones, especially on at-commit failures. Collecting the extra info would be ruinous for performance and memory use, but it'd be a great option to be able to turn on when debugging. — Craig Ringer Apr 19 '15 at 13:43

apparently you're in for a treat, lots of debugging to find out the involved queries

@ypercubeᵀᴹ related to your deleted comment, you also can't add a second NOT NULL constraint on the same column (even if you give it a different name), so his MODIFY without naming the constraint isn't ambiguous: dbfiddle.uk/…

@JackDouglas Yes I understand. MODIFY column NULL drops the constraint, if there is one..

MODIFY…NULL isn't the only possible alteration the OP might have in mind.

eg I don't think there is a way to rename the constraint without knowing it's name dbfiddle.uk/…

10:59 AM

mysql is on sql-server. — Wajahat Qasim 19 secs ago

Confused is an understatement

Tom V

maybe sql-server is the hostname of his MySQL box

or MYSQL is the SQL Server instance

12:31 PM

maybe he meant "My SQL is on SQL Server"

Hi

I hope you are well

question

@JoeObbish subtle.

let's say I have a column in a postgres db that's sometimes have null or '' as values. I want to have it fill with a zero when it is the case

I can create a trigger to do so

but I don't like trigger very much as they can be quite brutal

however

I read about this

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 0
);

the default

I did some test

and it is only working e.g. with price set as zero when

the column is not mentioned like here

INSERT INTO products VALUES (1,'2')

but when using this INSERT INTO products VALUES (1,'2',null)

well it puts data the way I've inserted it 1;"2";null

is there a way that in the DDL , there is something that check that each time, the values in the 3rd column equals to null or '', it puts a zero instead

If you want an explicitly inserted NULL to be automatically translated into a specific value, I don't think you can implement that declaratively only.

so a trigger is my best option

mmmm-ok

thanks @AndriyM

12:42 PM

@AndyK is there a particular reason you need this?. I mean, you could make the column as to not allow NULLs

or just have a computed column

Changing the application would probably be a better option. If that's out of the question, then probably a trigger.

@AndriyM The app is an outdated version of excel that I'm afraid to change ...

when you are at my seat, you have a whole new perspective on the meaning of the word reckless

I think, we can all play the Four Yorkshiremen :)

3

deal! :))))

@AndriyM Aye, you should see the Access pile of rubble I have to deal with!

12:46 PM

a friend of mine called it in a more fun way, he said that the people who are doing this , are in a YOLO perspective

@MaxVernon We used to dream of the Access pile of rubble!

Tom V

Nov 24 '15 at 19:37, by Tom V

Nov 18 at 12:47, by Tom V

@AndriyM well, it isn't really a pile of rubble, more like a heap at the bottom of the sea.

...but it was a rubble for us :)

wooohooo I've been nominated for the "Executive Network". :-D

12:49 PM

@MaxVernon Is that a good thing?

its a stupid spam email thing. They make out like it's some "big thing™" you just need to pay $50 to get your name published in the book.

>Hello

You have been identified and pre-nominated as a leader in your field by the American Registry. America's Registry gives you the type of National recognition not found anywhere else. Some people may join memberships, societies and organizations to develop business contacts, thus gaining image and credibility for themselves and their organization.

As a powerful third party endorsement, America's Registry offers this kind of recognition for individuals on a national basis with the added benefit of instantaneous networking with the other members. Being in America's Registry is an honor

I'm not so sure about the honor bit.

Pretty sure Trump's listed.

but it gives you the type of National recognition not found anywhere else

exactly. Just the kind you don't want. The kind that says "I'm a sucker".

I thought that type was everywhere

yes, but if you're in that book, you can be sure you're in the top 1% of suckers.

12:54 PM

I see, that's different

@MaxVernon 'Being in America's Registry is an honor' - or being in America's Registry is being a donor

@dezso ahhhhhh it was just a typo. Makes so much more sense!

An honour that you can buy for $50? Seems legit.

@AndriyM you made it so the typo is no longer valid

Philᵀᴹ

Nuke: dba.stackexchange.com/questions/168389/…

1:03 PM

@Lamak Perhaps they meant both. Being in America's Registry is being a donor, which is an honour :)

Tom V

Main site is sloooow

1:30 PM

Has anyone ever seen the "table scan" operator applied to a table with a clustered index in a SQL Server query plan?

Forrest

@JoeObbish No...I didn't think that was possible (although now I expect that Paul White has a blog post on it)

3

@AndriyM "Well you know that's nothing. I had to deal with a pile of Access once that had a pile of Access put on top of it and I had to debug it with Notepad."

I don't think it's possible either. Someone has an updated comment claiming that and I wanted to check that I wasn't crazy.

@hot2use Luxury.

3

wBob

Is there a good, free tool for documenting SSIS packages?

Philᵀᴹ

1:39 PM

@wBob Your hands & a keyboard? :)

@Philᵀᴹ did you call him a tool? ;)

wBob

@Philᵀᴹ The main source of error is normally between the user and the keyboard : )

and free?

2:17 PM

@JoeObbish No. I think Mr. Fite is saying a clustered index scan is equivalent to a table scan for a heap. The question is less than clear, which has lead to general confusion.

@wBob Between the user and the keyboard?

I blame the chair.

@PaulWhite you have you chair between you and the keyboard?

@PaulWhite The answer said that "It is strange to me that it is not using and index scan". I agree with that. It is strange to see a "table scan" on a table that supposedly has a clustered index.

@Lamak No, I have a problem between my user and the keyboard. It's the chair.

2:24 PM

If Mr. Fite meant what you said he could have explained it better. In any case, no need to continue that comment chain.

@Lamak Seems fair when you sit on the chair like this:

@JoeObbish Yes, the various interpretations and unstated assumptions contributed to my choice of words, "general confusion". The question even says the OP disabled the clustered index, which would be bad news for accessing the table at all. Presumably, he meant dropped.

@AndriyM that's what I was actually picturing

Anyway, it's a poor question. Not an advanced topic, and not clearly presented.

Max's answer is good of course.

But really. Basic indexes to support a simple UPDATE query. Gordon could answer that.

wBob

@AndriyM hands, belly, whatever : )

2:32 PM

Gordon?

Gordon Linoff, New York, United States

525k 23 170 250

@JoeObbish yup

Do some people share accounts here?

18 answers on average per day every day for 5 years...

Gordon answers a lot.

only 18 answers per day?

I swear I see him answer more

2:37 PM

There are a lot of basic, repeated questions on SO.

@PaulWhite no duplicates?

@hot2use Oodles.

@PaulWhite thought so...

There's no rep for finding a duplicate, so...

They must have a very high mark on their crap-o-meter

2:39 PM

It's been recalibrated twice.

4

@hot2use he generally doesn't care to understand the user's issue and just give an answer while everyone is asking for clarification

just answering doesn't give you points unless the asker is a Layer 8 problem

Or layer 9 - the chair.

I love bashing disks

@PaulWhite where you affected by the cyclone?

@hot2use No, that's Australia

humph

2:47 PM

Yeah, but you must have got some of the bad weather.. No?

What's your take on the continent thing?

Wee drop of rain?

@hot2use Not yet. It hasn't finished with the Aussies yet. If we get anything from it, it will be about a week, and it'll just be a low pressure thing by then.

@JoeObbish It's nice we have our own :)

Townsville is 3,602 km from me.

I can remember once being in N.Z. when a cyclone hit Australia, bounced off and veered off towards Northland. Brought a bit of rain.

@hot2use It can (and does) happen, but this one is very slow moving.

2:51 PM

kewl

Depends on who you ask as far as I understand it

Of course. There are a lot of skeptics on the West Island.

Do you mean Australia by that?

West Island (disambiguation)

That's the name the W.I. locals use yes.

West Island may refer to: Australia West Island, Cocos (Keeling) Islands West Island (Lacepede Islands) West Island (South Australia), an island south of the Fleurieu Peninsula West Island Conservation Park, a protected area in South Australia West Island Encounter Bay Aquatic Reserve, a protected area in South Australia West Island, Torres Strait, Queensland Canada West Island, unofficial name for part of Montreal India West Island (Andaman and Nicobar Islands) a protected wildlife island in the Bay of Bengal among Andaman and Nicobar Islands New Zealand West Island, one of the Three Kings Islands...

> West Island, a humorous nickname for Australia used in New Zealand

@Lamak and he never replied a question on a comment, at least to me

3:24 PM

bye folks

3:34 PM

Good morning everyone!

6

3:45 PM

Morning

Morning Evan

4:52 PM

@PaulWhite i have a question for you. i'd be happy to post it on the main site if, once you hear it, if you'd prefer.

to your knowledge, is there a way to leverage any DMVs to identify potentially useful multi-column statistics? best i can imagine is to use `sys.dm_db_missing_index_columns()` any indexes that have multiple columns with "EQUALITY" uses

@swasheck Not a question I've ever been asked. That sounds reasonable.

@PaulWhite there's the statistical notion of correlation, but that sort of correlation isnt necessarily congruent with correlated in the query predicate sense.

also ... how are you these days?

Good, thanks. It's a shame multi-column statistics are so limited.

@PaulWhite in what way? meaning that the histograms arent used and just the densities?

@swasheck Yes. The lack of multi-dimensional histograms.

5:03 PM

so is thinking through correlation (in the sql server sense) a large waste of time?

@swasheck Do you have a particular problem that you want to solve? Or just want to see if you can take advantage of multi-column stats?

@JoeObbish not necessarily a problem that i'm trying to solve per se. i'm just doing some exploration on multi-column stats.

and yes, see if we could take advantage of multi-column stats ... if there is indeed any advantage

I've never tried to find candidate multi-column stats en masse. It's an interesting question. The one's I have created have all been to solve a specific issue.

Same here. And by "ones" I mean "one".

Which version of SQL Server?

@PaulWhite well then I may not be using the correct approach. i guess possible goal would be to see how much benefit we're getting out of our never-used indexes and see if we could use a multi-column statistic to help QO.

@JoeObbish 2012-2016

5:16 PM

Won't work in 2014 on new CE (unless one of the latest updates addressed that).

@PaulWhite not familiar with this.

Oh. Unless TF 9472 is on.

yeah, that's where I was going with that

new CE in 2014 ignores multi column stats in some cases

for possibly some patch level

@swasheck Cardinality Estimation for Correlated Columns in SQL Server 2016

@swasheck In one case we had a three column index here which ruined the performance of a query because of the stats on it

the index wasn't used directly in the plan

so it can go the other way unfortunately

5:22 PM

@JoeObbish it ignores multi-column stats because it already assume correlation?

you could try testing your workload without the indexes, with only the stats, and with the indexes

to see if there's any difference

@swasheck No, 2014 just didn't know how to combine multi-column with exponential backoff.

They're still knocking the edges off the new CE.

@PaulWhite ah. that's helpful, thanks. i dont think i realized that, in all honesty.

i knew it was all new and shiny in 2014 but didnt realize this specific issue

@swasheck Read the link I posted when you get chance. It's quite informative.

@PaulWhite so far it's great. thanks

5:24 PM

I still don't fully grasp what containment is

I just know I've been burned by it

@JoeObbish Dima has a good explanation here

@JoeObbish even though @PaulWhite tried to explain it to me last year, i still dont know what it looks like to merge histograms. my brain's too dumb

@swasheck I have a mental model of how it works but it's almost certainly wrong

I think some people have the formula figured out. Fortunately never needed to go that far

@swasheck That was histogram alignment, IIRC.

And yeah, no one needs to do that by hand, except for curiosity.

@PaulWhite yes. that was it. and it was only for curiosity

5:33 PM

Thanks, will read carefully later

I had a test case where almost any filter on a table would result on a join CE estimate of 1

column and filter value mostly didn't matter

Join selectivity is hard.

Wilson

@MDCCL and @CadeRoux: You might also be interested in my question: Versioning: Is this technology used in DBMS other than spatial?

I don't doubt it. Still hard not to get a little frustrated with behavior like that.

Indeed.

The thing is, we only get to see the small proportion of queries with problems.

The vast majority just run fine without any special attention.

In most situations, anyway.

'exactly. the ones that run great necessarily do not come do our attention

5:42 PM

I'm constantly amazed that SQL Server does as well as it does with our workload

don't get me wrong

And you probably have a vaguely sensible schema. Think of all those people that don't!

and the repro couldn't be reproduced from nothing

no comment

@JoeObbish we have cognos and that ORM is just one of the worst

@JoeObbish Ha ha ha

You look at a lot of different systems in consulting work, right?

5:44 PM

@JoeObbish Not personally, but I know people who do.

I'm not much of a generalist, despite having the badge here :)

The thought of getting in, fixing something, and getting out sounds nice sometimes. Every less than optimal thing we do here is ultimately our fault which can be discouraging.

@PaulWhite So what you are saying is you may be a generalist here but you are not much of a generalist generally, is that correct?

@AndriyM Generally speaking :)

@JoeObbish I'm told the frustrations are similar-ish on the other side of the fence. Compromises due to lack of time/money, clients that won't take the advice they've paid to get, not seeing solutions implemented...etc.

@JoeObbish i live that daily

@PaulWhite Hmm. In that case, maybe get out and stay out? :)

5:50 PM

I think I've been lucky in that most of the systems I have encountered have been 3NF with generally good indexing. And I've worked with developers and DB people that communicate and get on together.

But I get your point.

Win Lotto or invent the next Snapchat.

it's why i checked out of the whole "stats/index/optimization to make things better" game because no matter what we did, it still wasnt good enough to make up for a crappy logical model with crappier decisions being made by crappier still developers

My app idea from years ago was a single click pizza ordering app. As far as I know it has been done already, so too late for that.

@swasheck Can't fix that with multi-col stats, unfortunately.

5:52 PM

More RAM!

@PaulWhite no. the mutli-column stats are for internal DBA stuff.

@JoeObbish my idea is to go be a deep woods forest ranger

That won't make you rich

Maybe you could find gold

that all depends on your definition of rich

4

Forrest

6:21 PM

Finding and fixing misbehaving queries is really fun for me, I just don't get to do it often. I assume that's something a DBA in a large environment would be doing more of?

it is fun when you have a receptive audience. when you get "shrug ... we dont have time to write this query properly. you need to fix the server." it can be disheartening.

@swasheck so you know our latest WTF?

@dezso huh?

well, they at least promised there would be a fix in a few weeks

how generous of them

6:24 PM

@swasheck it was no huh, but a request to pump a group of EC2 instances up a bit (10x)

where, apparently, CPU and RAM were the smallest of all issues

i thought ec2 was supposed to enable more a more agile infrastructure. i thought we were supposed to be able to do away with those long waits

@JoeObbish 0-click then!

@swasheck aye, extending them happens very agile-ly, but the queries do not get agiler, at least not to the same proportions

in any case, the pull request for the change got the '... supercomputer ' name from me

nice

in other news, marc_s seems to be on fire on the site

@swasheck yeah, if I cannot help it, I still can make bad jokes

6:29 PM

our queries are slow. please migrate mongo

@dezso stay sane somehow

@swasheck ha, that's a sane idea

@JackDouglas no, it is 'just a thought'

wasntme

@dezso ftfy :p

dba.stackexchange.com/posts/129761/revisions

an answer is still an answer by any other name

@JackDouglas +3 -2 ...

Tempted to revert my vote.

I have no idea if it is a good solution for MySQL

I'm sure you do though @ypercubeᵀᴹ ?

@JackDouglas I have no idea what they mean. Session trigger?

6:40 PM

logon trigger presumably?

@Philᵀᴹ might know.

@JackDouglas Seems you are right. Didn't know about that option.

I still don't know if it would work

@JackDouglas that's a counterfeit

Does a user with rep=1 add comments to his question?

@McNets Yep, but not to other questions

6:53 PM

"Please note that you can always comment on your own posts, and any part of your questions. However, commenting on other people's posts is a privilege."

7:15 PM

-1

Q: Enter hours only in SQL

Is it possible to enter the hours only for an employee? For example, I would like to either enter or import the total hours worked for a week, but I keep getting errors. I will want to calculate hours based on in and out punches in the future, but I want to start with a simple hours and minutes...

sql

7:43 PM

@McNets - I hope you don't mind - I added some to your answer there.

8:01 PM

I'm glad to get your collaboration Sir

@MaxVernon

@McNets my pleasure

Q: FIFO Inventory by location, solve with CTE?

9:10 PM

0

I've been trying to solve the following with a CTE for about 6 hours now and I'm starting to wonder if I can. Given the following table: CREATE TABLE [dbo].[INV]( [InvID] [int] IDENTITY(1,1) NOT NULL, [Product] [varchar](50) NULL, [TransactionCode] [char](2) NULL, [Qty] [decimal]...

sql tsql fifo inventory inventory-management

SO question

solid avatar

@JoeObbish ;)

9:36 PM

Hullo! Poll question: what's your policy on people making trivial edits? There's a particular person who apparently doesn't like a couple of abbreviations I use, and who insists on changing them to other forms; I find it irritating, but I don't know if I should raise it on meta.

Ha! "mysql is on sql-server"

@JonofAllTrades trivial edits that unambiguously improve a post are OK, but if it's just style that's not OK

can you post an example (or go to meta, that's always good)

@Ja That certainly sounds reasonable. In this case, his pet changes are "MS SQL" to "SQL Server" (in my humble opinion, the latter is technically more correct but

Oops! Interrupted myself. Link: dba.stackexchange.com/posts/51692/revisions

I feel this falls in the "trivial/stylistic edits" category, as described on Meta. Of course, why complain if it's trivial? I guess I find a bit condescending. Like someone changing American English to British or vice-versa.

@JonofAllTrades I agree about the British/American.

But when I make an edit to an answer or question, I usually change MS SQL to SQL Server, too.

if I notice it.

But I wouldn't normally make a edit, only for that.

I agree that it's technically more correct, but it's also ambiguous. "MS SQL" refers to a specific product; there are many "SQL server" products," though just one is copyrighted as "SQL Server."

9:47 PM

"MS SQL" may not be ambiguous but I'd rather not see it. It bugs me as it was written someone who is oblivious to the rest of the world, aside from MS documentation.

We - not so rarely - see even "I use SQL" and they mean SQL Server...

yes, that is annoying as it really is ambiguous. I don't think "SQL Server" is ambiguous though

@JonofAllTrades I think that edit is minor but helpful — if you can live with it that would be the best outcome, but of course you are always free to roll back an edit you don't like.

It shouldn't be, but considering that people can be less-than-diligent about capitalization and so forth, I'd rather be exact.

I suppose so, but Marc will just edit it again some day when he's bored. It's just irritating.

Microsoft SQL Server then — I'm not sure I'd recognize MS SQL at all

and it's much too close to My SQL

One more quick question if you don't mind - what's the 10.5k by my user name? My DBA rep is around 4 K, it's not that.

@JonofAllTrades your whole network rep is shown in chat

9:51 PM

Hmm. Though I deleted those accounts years ago? I guess the SE never forgets. Oi.

@JonofAllTrades shall we change your chat profile parent site to DBA? It's currently pointing at a non-existent SO profile

@JackDouglas: sure, I suppose.

Easy enough,.

@JonofAllTrades did you delete your SO account?

Yes.

@JonofAllTrades stackexchange.com/users/273940/jon-of-all-trades?tab=accounts

9:53 PM

I kept DBA for the résumé, but gave up on the rest.

Your accounts rep doesn't add up to 10K, only about 6-7K stackexchange.com/users/273940/jon-of-all-trades?tab=accounts

I guess some were really deleted and some were just hidden? Deleting accounts is a PITA, it has to be done one community at a time.

yes, it is

but you could probably send an email instead

@JonofAllTrades I'm glad dba.se is the one account you wanted to keep :)

@JonofAllTrades you dba.se profile has "Delete me". perhaps by mistake?

@JackDouglas: Thanks! We have a good set of people here. I guess I shouldn't be surprised if there are a few obsessive-compulsive editers, it goes with the DBA territory.

9:58 PM

we have other issues too as a community, but compared to the rest of SE, this is paradise

I have to admit I don't like "sproc" either ;)

@ypercubeᵀᴹ: Huh, I guess so. I had to copy that into each other community, it may have been merged in from my SO account or something.

phew @JonofAllTrades, that was a close one. I've changed it to "Don't delete me"!

Jeez, I guess I shouldn't admit to using indentation, then! Though come to think of it, I don't think I've seen religious wars in DBA like you can see in IT forums and such.

Thanks.

Q: Whether we should be CAP COPs

you can change it to what you like of course

31

A certain user has suggested a boatload of edits recently. The changes have been either largely or completely revolving around the upper-casing of Transact-SQL keywords. For example, a post that looked like this: select * from tablename where columnName = 'foo' Would be edited to this: SELECT...

discussion edits sql-server

@JonofAllTrades we don't want to be CAP COPs

there is stuff that is just personal style and that's each to their own

10:01 PM

Ah, I remember that one. I think I agree with the consensus: it's not a bad change, but it's certainly not a justification on its own. I'd consider Marc's changes to be similarly satisfying his preferences rather than objective improvements, but... whatev. It's 5:00 in my time zone.

ha, that's either very early or very late

my bedtime too, so g'night

Thank you, Jack, and Ypercube, for the input. And for the zillions of answers - no one could use DBA.SE without recognizing your names. Good night to you too!

thnx Jon. Your name is distinguishable, too.

10:15 PM

I told a developer that testing with 10 M rows wasn't realistic... he proceeds to test with 11 M rows...

3

10:25 PM

Damn SQL Server. I TOLD it the rollback should be IMMEDIATE. Why didn't it instantly roll back? It's been like 10 minutes!!! (wink wink nudge nudge)

ALTER DATABASE MyDb SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDb SET ONLINE;

See, I did everything right, but the dumb thing won't listen to me

Though, removing tongue from cheek, the transaction was rolled back but SSMS still thought the query was running, couldn't cope with it when I tried to close the query tab, and promptly crashed.

@ErikE just curious. Is there no other way to kill/rollback other transactions?

@ypercubeᵀᴹ You can kill them one at a time. Find the SPID and then KILL 123 for example.

@ypercubeᵀᴹ You could also SET SINGLE_USER instead of OFFLINE

Which gives you exclusive access until someone stops the service and starts it again

Well, it gives ONE user exclusive access, so if you drop your connection and someone else grabs it, then they have it now.

Or you SET MULTI_USER again

aha

In some environments with thousands of clients trying to connect all the time, killing SPIDS is an exercise in futility.

@ErikE Yeah, it's the same in "real" databases, too.

One has to REVOKE access first, then kill all pids.

10:41 PM

@ypercubeᵀᴹ I don't have a mental model that REVOKE fits into anywhere. :)

there is a CONNECT (to database) permission that can be revoked.

@ypercubeᵀᴹ Ah, then they can't reconnect? I see. You can probably do that in SQL Server but it sounds rife with potential for not GRANTing back the correct permissions afterward.