The Heap™ – Consultancy ©® - 2015-02-02 (page 1 of 2)

« first day (1490 days earlier) ← previous day next day → last day (3372 days later) »

1:58 AM

What is it with DBAs and bad backs? Our production DBA's got the only stand-up desk in the organisation because of his and I'm in monthly physio.
Is it the heavy burden of responsibility we have to bear?

6 hours later…

8:12 AM

@Phil I like the state of your roads in this kind of winter. Where I live, the municipal authority never seems to be ready to clean the roads till the snow either melts or turns into ice ("Oh but it snowed so suddenly last night, we didn't expect!").

8:42 AM

Is it allowed here to reference to a Question of mine regarding sql ? ( I already got an answer but i believe there is a better way of doing it and that's what i'm after)

@RoyiNamir go ahead]

here I don't like the re-searching solution , also his inner join doesnt work , as you can see in my comment. but he just doesn't answer. so I thought there might be an intelligent 'er way to solve this issue. ( just for knowledge ...)

@RoyiNamir I think you need something like the last query in that answer, with COUNT(*) OVER (...).

but not dense rank.

Try with ROW_NUMBER() )VER(PARTITION BY name, age ORDER BY id)

I loved the join answer but it is valid only for 2 versions....( as I commented)

and WHERE cnt > 1 in the end.

8:58 AM

nope , i.imgur.com/KJuq6Ci.png

drop the rn=1

:-) working

Thank you

That's really weird becuase if someone had asked me to solve it , i would go straight to RowNumber

and now I see that the solution is without RowNumber

RowNumber seems like the Classic solution.

Which makes me wonder ( and a bit sad)

your requirements are a bit different.

?

Try learning rank() and dense_rank()

9:04 AM

I already know them

and the aggregates (count(), ...) with over.

They are often very usefyl.

Yes , Im just saying that Rownumber shows versioning for some grouped data , and here is also the case....

Not really, I think. You wanted rows that had duplicates. Versioning seemed secondary.

You got a point :-)

Think of your rows as soldiers, one behind the other, in a very long parade.
Row_Number() will give each soldier a number. If every soldier knows its number, he still doesn't how many soldiers are in his group.
Count() has that info.

9:08 AM

;-) You're right

I was so into " versioning" where I should be really at "duplicates"

But if you think about it , versioning comes AFTER duplicates ( which will cause rowNumber>1)

If you want to delete the duolicates, row_nu ber is enough.
Check Rn, if it's equal to 1, keep it. If it's >=2, throw it away.

But you want to show all rows - that have (other rows) as duplicates.

aside question : does count(age) over (partition by name,age) cnt is being calculated while scanning ? or at the end of scanning?

That is not possible with row_number.

@RoyiNamir I guess the details depends on ypour indexes. Check the execution plans.

(then add index on (name, age) amnd check again)

ConcernedOfTunbridgeWells

9:29 AM

Morning all

10:08 AM

Yo

10:31 AM

morning

3 hours later…

1:15 PM

hi!

1:55 PM

ohayou!

aloha !

2:39 PM

yesterday, by datagod

Because you obviously have no idea who I am.

Google does

bit.ly/1zw1KnF

Mercy, bit.ly is obnoxious now. Click here for this, click here for that all the while obscuring the shortener

Hi

@billinkc I think his choice of an online moniker pretty much says it all.

Yes, Google does know who I am. I give advice to large firms and government departments on which software tools they should buy.

I don't know why you guys are being so rude.

If you check the chat log for this channel, my job is to make lame jokes at every possible turn

Well in that case....I'll let it slide. I like lame jokes as much as the next guy.

The google there is an image search

Even typing "the google" makes me feel 90 years old

2:47 PM

Whatever it takes to make you feel younger

@datagod hey, welcome. We are not rude.

Thank you ypercube. I used to hang out here a few years ago, got really busy

But I'll just write off that whole incident as a weekend thing. I am willing to forget and move on.

3:08 PM

So, you are from Canada?

Yes sir.

The remark was removed. It is not important. Time to move on.

2

That's all, nothing more.

If you sat with me while I was trying to implement it, you would have seen how many drinks I needed to stop from shooting the server.

Obsessed with details. Synonyms: pedantic, anal-retentive, DBA.

3

And you can ask these folks how often I bitch about interactions. I just need to be a little more careful.

3:11 PM

Well, I am super glad we resolved this. I hate having bad feelings between people.

By "careful" I mean "explicit about what I'm actually frustrated with"

@AaronBertrand oh, come on, don't start doing that.

where would be the fun in that?

ConcernedOfTunbridgeWells

@ypercube Hadn't you noticed from the politeness?

I thoroughly enjoy powershell. I wrote a distributed report writer that would poll hundreds of servers (buried in movie kiosks) and collect data for central analysis. But I could not get the same process to talk to a few old SQL 2000 boxes.

@datagod Yeah I don't want to rehash that whole thing, but the OP never said they were using 15-year old versions of SQL or Windows.

If SQL was old enough they couldn't have used Agent, either. :-)

3:14 PM

@ConcernedOfTunbridgeWells I restricted myself commenting earlier on that. Fearing another misunderstanding.

ConcernedOfTunbridgeWells

@ypercube I just assume nobody takes me seriously anyway.

The sad reality of smaller markets is that older versions of software will be used until the machines they are on actually die.

@ConcernedOfTunbridgeWells and you are not even from Canada ;)

ConcernedOfTunbridgeWells

@ypercube No, although my mother did live there for several years when she was a child.

Was your mom running away from the draft?

3:16 PM

:19850536 I said out on the comments on the question, though, I still stand by 100%, would gladly share them with my boss and co-workers. I don't think anything I said out there was rude, offensive, or non-constructive. The moderator who declined your flags agreed.

Nifty. Up arrow to the rescue.

ConcernedOfTunbridgeWells

@datagod Technically running away from the Japanese invasion of Burma (as it was known at the time).

oops

@datagod Right but don't assume that "someone must be in that scenario, therefore newer and better solutions aren't possible."

@AaronBertrand that ending was unbelievable

3:17 PM

I get it, if the OP said we are running SQL Server 6.5 and all of our servers are Windows 2000, then I wouldn't have recommended PowerShell.

I actually only said sometimes the wrong tool is the only tool.

@datagod up-arrow lets you edit messages

thanks. up arrows are cool.

ConcernedOfTunbridgeWells

r/nocontext

there are some good features in this chat client

3:19 PM

Anyway, like I said, I apologize for over reacting.

and being too sensitive.

But I will NOT apologize for my hat.

user image

Down-arrows are cool too (I actually find the up-arrow approach tedious and more often gets in the way than being on purpose)

@datagod which one?

user image

That is my little data chef.

aha :)

When I was writing "Cooking with SQL"

3:21 PM

you look more serious in the other one

I like hats.

WHY SO SERIOUS

ConcernedOfTunbridgeWells

@datagod Can I suggest:

user image

nice

@ConcernedOfTunbridgeWells Where did your picture with the hat go?

3:23 PM

Juice drips on keyboards = sticky keys

@bluefeet You should have seen this house. We terrified multiple toddlers.

ConcernedOfTunbridgeWells

@ypercube Probably still visible on some of my subscriptions

@AaronBertrand not surprised. One of my dogs was barking like crazy at me.

user image

Not at all.

ConcernedOfTunbridgeWells

3:24 PM

I have a silly hat too.

Edward De Bono would be proud

@ConcernedOfTunbridgeWells to remove the attention from the ~~skirts~~ lava-lavas?

I have a mohawk

ConcernedOfTunbridgeWells

@ypercube No. I think LavaLavas need more attention.

Have a good day, I have to get back to work. Those SQL 6.0 boxes won't upgrade themselves.

oh yuck

3:29 PM

/kidding

What's the oldest sql-server version that has been mentioned in a dba.se question?

@ypercube dunno, but the newest one I worked with was 6.5

17

Q: Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns. The database development started in the days of SQL Server 6.0. One of the rules followed from the beginning was, Avoid creating a clu...

sql-server sql-server-2005 clustered-index

2000 was out back then, causing some confusion among the devs

Mentions 6.0

3:34 PM

hm, apparently it was too far in the past - in which version did CREATE FUNCTION appear?

@datagod talked about 4.2 in this answer:

6

A: Does a DBA need to know how to program in a system language besides SQL?

I would say not typically, but it never hurts. SQL Server is big enough that I don't think one person can master the entire product. I have been a DBA since SQL Server 4.2, and consider myself an expert in most areas, but I admit I wouldn't be able to write an MDX query without google at my fin...

But not really anything specific

And another answer here:

1

A: Which database engines will allow me to GRANT/REVOKE on a specific column?

As @igor said SQL Server has that capability. I think back to Sybase times i.e. version 4.2. I'm sure later (perhaps SQL-Server 7.0) I heard recommendation not to use that feature, but to use views or stored procedures instead,

@AaronBertrand ok. I was more keen on finding anyone actually using old versions and posting a question

@ypercube yeah I don't think there are many

@ypercube people still using a version that old are probably just limping along on it because it just works

@dezso I think 2000 actually

How about those Patriots! What a great game!

@AaronBertrand it's possible then that my version was 7.0, really don't remember

3:38 PM

@MaxVernon yes it was awesome, now I hope all the haters shut up :-)

was very rudimentary from many points of view compared to any modern RDBMS

@AaronBertrand yeah, it's easy to claim someone has soft balls after the fact.

I had the audacity last night to tell Sherman, Lynch and Carroll to suck it, and a couple of Seattle fans called me out for being classless. You mean like relentlessly calling my team cheaters without proof for two straight weeks?

Or like making fun of Revis on camera when you thought you were going to win? Or like calling out Brady during the media stuff? Yeah me telling them to suck it was the pinnacle of classlessness ...

that interception at the end was a beautiful thing to witness.

Sherman ran his mouth off and now he has to eat crow.

3:41 PM

@AaronBertrand no doubt!

@MaxVernon I actually couldn't believe it. First that they threw the ball at all, and I had to rewind it because my initial viewing did not convince me that it was an actual interception.

The Seahawks still had a chance to get to us in the end zone at the start of the next play - it wouldn't have been a simple kneel-down. But then we drew them offsides and then, on the next play, more class

I was astounded too that they threw. What a risk.

it was great ending to a really thrilling game, I thought.

It was a very entertaining game, and both sides really did give it their all. In the end, though, two big mistakes really cost the Seahawks - it was their game for the taking. I have to wonder if karma played a role (Richard Sherman may know)

@MaxVernon I heard that play was not supposed to be the final one, it was a we'll see if we get this, if not, we can hammer it in on the last two downs

Meanwhile, I was in bed futzing around killing monsters and writing so know nothing of the big game

@billinkc pretty crazy time to "see if this works"!

3:49 PM

Their coach said that the Patriots had their run defense in so they thought they'd see if they could surprise them. #LessonLearned

@MaxVernon yeah, "what does this button do?"

@billinkc They still have Marshawn Lynch. The best run defenses in the league can't stop him from getting 1 yard when given 3 tries.

Don't get me wrong, I'm glad Carroll screwed it up. But he would have easily run that in no matter how tall we stood up on the goal line.

Before the interception, I thought it was a big mistake of Belichick to not call a time out with ~ 50 seconds left - he let 20 seconds or so run off. If they had scored, we would have been screwed. One extra time out but still a whole field to gain.

I didn't watch but the highlight reel was entertaining

I wonder if you could create a sporting event's "best of" by recording ambient noise, say at a bar. Every time there's a spike, you assume the previous N timeunits was valuable and add that to the product pool

We almost muted the TV and listened to the radio station's call of the game. But that would be annoying for replays, commercials, etc.

That's a given for the few times I watch the Chiefs. Local guys >> national broadcasters

4:05 PM

Oh yeah 100%, most of the national guys are - well, almost said morons

That gives too much credit to morons

anyone have bill nye's email?

Why, do you want to digitally punch him for this nonsense?

Wonder if Brady has trouble controlling the fully inflated ball. Hmmm...

Tweeted by TheScienceGuy on February 2, 2015 at 1:48 AM

Funny how he's been refuted by several scientists but he's still adamant that the Patriots cheated.

Wonder what he'll say when Goodell has to officially exonerate them.

4:21 PM

He was here a few weeks ago

@Kermit billnye.com/#contact - bnsg@billnye.com - well, that's his assistant

@AaronBertrand nah, sports are dumb. i wanna autographed signature

@AaronBertrand thanks!

OH (and not from @PaulRandal!) : "sports are dumb"

Tweeted by AaronBertrand on February 2, 2015 at 4:23 PM

lol

@AaronBertrand Because I'm lazy....do you have a link handy for the AG bug that was in the recent CUs? IF it's not handy, I can google.

4:26 PM

blogs.msdn.com/b/sqlreleaseservices/archive/2015/01/21/…

Relatively handy

Thanks, mang.

@mike_fal No problemo. Sorry my team won

I know that's probably not the outcome you wanted

@AaronBertrand I wanted both teams to lose.

@MikeFal but I bet you kind of wanted Brady to lose more than you cared about Russell

Just because of where that propels him compared to Peyton

Yes, but I wanted Carroll to lose more than Belicheck.

4:31 PM

Yeah Carroll is a DB and I'm glad this play call will be his Bill Buckner

Meh, that argument is pretty old hat. I have my opinion and sometimes it's fun to debate it. The Pats definitely have a strong system and team.

I think my case about Brady will be made when Brady retires and whomever replaces him will have similar success. :)

@AaronBertrand is that Bill Nye's actual twitter or one of those stupid imitation pages people seem to love so much?

@Zane that's actually him

Weird.

Surprised he hasn't had twitter put that blue checkmark on him though

@MikeFal well I don't submit that the Colts and Broncos put crappy teams in front of Peyton, and I don't think that's how QBs are ultimately judged anyhow.

4:36 PM

@AaronBertrand It's not just the teams. It's the system and coaching. If you want me to admit to something, it's that Belicheck is probably the best coach of our generation.

I'm not asking you to admit to anything, and I don't expect you to ever concede that Brady is a better QB than Peyton. But I think this result will actually cement that for just about everyone else outside of Indy & Denver.

Heh, fair enough. :)

@AaronBertrand there's a common misconception that broncos fans are manning fans.

It was a fun game to watch.

@AaronBertrand i'd rather have brady than manning.

4:39 PM

I'm both. But I was a Manning fan before he got to Denver.

I'm into indie quarterbacks, but you've probably never heard of them

@swasheck We can't be friends any more.

It also had the single greatest half time show I've ever seen.

@swasheck Hey I wasn't saying that all Broncos fans think Peyton is better. Just that most of the people who still do are likely fans of either of those teams. Or jealous jerks like Aikman.

@MikeFal cold. hard. facts.

4:39 PM

Anyone seen SQL Server 2008 (SP3 CU 16 or later, specifically) fail to detect a deadlock?

Yes, SQL Server 2008.... please forgive me.

@Zane beach balls! Also the only lion who will ever appear in a super bowl

@AaronBertrand ok, fair enough. it's so funny how patriots fans usually jump on that same old tired trope.

I've never laughed so hard in my life.

@AaronBertrand unless you count the bengals

user image

4:40 PM

@swasheck those are tigers. I like to make fun of Detroit

@swasheck Bengals are tigers my friend.

@swasheck I'm not jumping on anything, just a casual observation

i am le stupid

@AaronBertrand i'll exclude you, though :)

@AaronBertrand @Zane so they are. hurrrrrrr

Between Mecha Katy Perry and the dancing sharks/beach balls. It was the funniest thing I've seen in my life.

Someone said it was more like an Olympics opening ceremony than a half-time show.

4:42 PM

@Zane what about seeing her fly on the "The More You Know" star

I really wanted her to fly that thing right out of the stadium.

lenny made an appearance ... that was nice

@AaronBertrand in fairness, the media usually plays up the Manning vs. Brady thing too much and kinda feeds the frenzy

Wife and I were discussing whether her outfit was supposed to be a reference to the Hunger Games tribute costumes. With LK showing up, we thought it was confirmed

@swasheck well I think their performances this post season will start changing that

@billinkc I thought she was going for a Power Rangers villain sort of look on that one.

4:45 PM

But then I see all these posts comparing her outfit to the Will Farrel character in Blades of Glory and I had to defer

@AaronBertrand Brady vs. Some Dude Who Used To Be Good

@billinkc or Magmar from Pokemon.

@Zane @billinkc buzzfeed.com/awesomer/…

Will look at that later

I still think that corner back should have won MVP.

4:46 PM

@Zane i agree with that. though he did give up the miracle catch to set it all up

@Zane I said the same thing on twitter

but brady won it based on the career postseason body of work

@Zane He did get top play on SportsCenter. ESPN who, amazingly, suddenly had good things to say about the Pats

@swasheck I don't know that I would say he gave up that catch.

@swasheck that was a miracle catch, he broke it up, you can't put the fact that he still caught it on him.

4:48 PM

He played the ball perfectly.

@AaronBertrand they usually have good things to say

@swasheck not for the last two weeks

@Zane @AaronBertrand agreed on the catch --- but he put it on himself so i'm glad that he made the big play

he also had quite a few very good passes defensed

@AaronBertrand well that's true

@AaronBertrand I could not believe how long that irrelevant nonsense went on.

especially with a lot of other quarterbacks coming out and saying how often it happens

4:51 PM

@Zane and it's still not done. And it will go on beyond whatever Goodell comes up with (almost certainly exoneration given the most recent details).

One thing I don't miss about SQL 4.2 (and 6, 6.5) is having to create "containers" for the databases.

@AaronBertrand so pathetic. they were conspicuously silent about adderall, though.

The fumbles analogy was stupid too. Of course you can make the stats look like that was such an impossible feat. But when you admit that you used imperfect data and cooked the y-axis it looks a lot less damning. Funny how nobody is coming out calling anyone cheaters from last night's game - 125 offensive plays and not a single fumble? Surely both teams must have been cheating to have such impressive fumble statistics?

thechive.com/2015/02/02/…

My favorite:

user image

@AaronBertrand thechive is wait for it ... blocked. anyway ... statistics are only as good as the agenda you're attempting to promote.

that's pretty funny.

i was surprised at how many katy perry songs my daughter knew, considering my wife and i can't stand her and never listen to her.

I knew the fireworks one because it was on some commercial on a network I watch for a long time (sorry I forget which one). I didn't recognize any others.

5:10 PM

i only knew Roar because a local high school won a contest using that song and the news couldnt let it go

Oh yeah, I confess, I did know that one too. But with company and two 2-year olds and a 4 month old here, I was in and out of the TV room during her performance.

hahahahahah. nice.

user image

That sir is what you missed.

ConcernedOfTunbridgeWells

Wasn't there supposed to be some football as well?

I seemed to recall there being a game but at the moment all I can remember is derpy tree's and some dancing chess pieces.

5:21 PM

@ConcernedOfTunbridgeWells yes. it almost went to penalty kicks

@Zane hey. lenny was fun to see and missy elliott brought back the high school memories

user image

he he he.

@swasheck blocked

i see you trollin'

5:37 PM

@swasheck Look at you, branching out and going to SQLSat ABQ. I'm proud of you. ;-)

@mmarie next year i may be crazy and go to sqlsat denver

@swasheck Didn't you do SqlSatABQ last year?

yeah, I guess he did.

but he always says he can't travel because family/doesn't want to, so I"m glad he is going it again.

I hadn't checked the schedule for ABQ since I can't go this year.

@swasheck Come to KC!

@mmarie Lots of good folks, a couple new speakers (like Melody Z). Should be pretty good. As long as I don't fall on my face for my precon

@MikeFal Didn't you do a precon in Denver?

No, you did a half-day session or something

not a pre-con, right?

5:42 PM

@mmarie Right. It was the lead up to the precon, but it's a lot harder assembling material for a full day than for 3 hours.

@MikeFal Yeah, I can imagine. Well, good luck! Hope it goes well. Are you skiing with people before the weekend?

I'm not trying to be overly dramatic about it. I'm excited and I think I have enough material, but it's still intimidating.

@MikeFal yes. i did. i thought she was being sarcastic so i was playing along

@mmarie No skiing, since they'll be skiing the day I'm pre-conning.

Oh, I thought they were skiing on Thursday, too

5:44 PM

@mmarie change the weekend.

@swasheck It probably is changed.

@mmarie sometime in july?

Built my PC this weekend. Super pumped. All that's left is to bump up from 8 to 32 gigs of ram. Then my work will be complete.

@Zane did you name it the millenium falcon?

probably not. we would have to be planning already and we are way not prepared for that. but it won't be the weekend right before Denver. Bill and I decided we don't like that either.

Plus, you don't want to come to KC when it is hot.

5:46 PM

I'd like a KC change so I can make it. I miss the bbq.

i dont want to go anywhere when it's hot

@swasheck Then why did you say July?

@mmarie Did you guys do precons last year?

@Zane cost?

No. we don't do pre-cons because BBQ

5:46 PM

1200

@mmarie because. the late summer/early fall is turrible timing for me

After the ram 1350 or so.

Fair enough

@ypercube pcpartpicker.com/guide/Ht8TwP/enthusiast-gaming-build

I just wrote my UNPIVOT. I'm proud of myself. I had pivoted before, but not unpivoted.

2

5:54 PM

Am I missing something here or is this guy just not getting it?

I understood that - there is however extra work in putting it in a separate table, as that will require further joins on all queries that access the table. Sure, we will be updating the queries anyway, but do we really want to paint ourselves even further into a corner by keeping useless legacy data around? — Olle 55 mins ago

@mmarie I failed an interview recently because I didn't know how to UNPIVOT

Now I'm UNPIVOTing everything!

@JamesLupolt that stinks

@mmarie This sentence tickles me.

Failed an interview because of UNPIVOT? I'm not sure I'd want to work somewhere that dings me for not knowing minutia.

3

I haven't had a need to unpivot anything until now. I dislike interviews that require you to know exact syntax. If I can look it up and figure it out in a few minutes, i don't see it as a problem.

@MikeFal + 1

5:58 PM

I ask some minutia in an interview, but I want to see how people react when they don't know or can't answer a question.

Next up, a pirouette and return to first position

@JamesLupolt good sign you wouldn't want to work there

I always think I'm interviewing them, and using "gotcha" questions on obscure syntax does not bode well for the work environment

« first day (1490 days earlier) ← previous day next day → last day (3372 days later) »

Transcript for

Feb '152

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile