The Heap™ – Consultancy ©® - 2016-03-04 (page 2 of 2)

id balance previous_id previous_balance   change
1   100        NULL     0   + 100
2   110       1          100     +  10
3    80        2          110     -  30

I guess having lots of concurrent threads that will try to insert into the table will result in race conditions

Paul White

8:08 PM

@dezso What a n00b!

dezso

@PaulWhite I have a degree in nitpicking

@ypercubeᵀᴹ is it important for the inserts?

Paul White

@dezso Perhaps they call it 'team chase' in us-en-GB-UK.

ypercubeᵀᴹ

@dezso Yes, required that the balances match.

dezso

@PaulWhite that's a strange place, indeed

Paul White

Sounds like a true statement :)

dezso

8:11 PM

@ypercubeᵀᴹ aren't there timestamps recorded?

ypercubeᵀᴹ

@dezso They could be.

It's still early design

mmarie

Hey, DBAs. Do you all agree with this? facility9.com/2016/02/friends-dont-let-friends-use-datetime If so... What am I supposed to use instead of datetime for audit columns in a DW? Like I need to keep track of when this row was last inserted/updated, the effective dates on a type 2 slowly changing dimension, etc.

dezso

3 hours ago, by Max Vernon

@dezso tru thing

@ypercubeᵀᴹ relying on the order of a (presumably) surrogate key is possibly not better than not caring at all :D

ypercubeᵀᴹ

@dezso thnx for your time. I got to go home now. So see you all later.

I'll probbaly update with a proper question at the site and more details on the requirements

dezso

have a nice weekend

and I'm going to check what my wife is doing

Zane

8:16 PM

@mmarie essentially the idea is that most applications don't really need that level of precision.

dezso

sounds a bit noisy

mmarie

@Zane Yeah, i get that

Lamak

@mmarie nah, I don't think that generalizations like that fit for all cases

@Zane but the blog post recommends datetime2 as another choice

Paul White

@mmarie Are your effective dates dates or dates & times?

mmarie

dates and times

Paul White

8:17 PM

Almost no one uses datetimeoffset AFAIK

Zane

@mmarie I tend to think this is a bit overblown and still use datetime on times tamp columns.

Lamak

@PaulWhite that's what I was gonna say too

Paul White

datetime is indeed still very common

mmarie

@PaulWhite common and a good practice are two different things

Paul White

If you want to be hip, use datetime2 with an explicit precision, maybe zero.

mmarie

8:18 PM

but point taken

Paul White

@mmarie Yes but I'm only qualified in this area to comment on the former.

Have you asked @billinkc or @TomV?

Lamak

@PaulWhite good thinking, so you can eliminate it as the worst choice

Paul White

@Lamak Yep, once you've eliminated everything Bill in KFC says, whatever remains must be the right answer.

3

Lamak

exactly

mmarie

@PaulWhite I asked @billinkc he said he still uses datetime and "MS has advocated that since they released datetime2. I've just never had any Fs to give about it"

Paul White

8:21 PM

@mmarie Actually, I can dig that.

(but don't tell him)

billinkc

One of my clients uses datetimeoffset in the rewrite of their app which used datetime

Zane

@billinkc aaaaand

Paul White

@billinkc Did they have fun with it?

billinkc

I spent an afternoon showing them that while I get what the app dev team is trying to do, here's all the things that the DW side of things is doing that isn't aligned with that

swasheck

we use datetimeoffset for stamping our backups. that's it

billinkc

8:23 PM

The biggest being that we were looking at sql agent history to determine when the job ran but nobody was factoring in the offset. And when the app dev team loaded data, they attached with timezone but when they did updates, it was universally tagged to UTC so they weren't supplying it there

"but that covers daylight savings time, right" HAHAHAHAHHAHAHAHAHAHAHAHAH

swasheck

@billinkc @mmarie our developers use DATETIME ... if that's any help

billinkc

A

datetimeoffset makes sense, until I can convince the entire world to do away with timezone, but you gotta be religious in your usage. Otherwise, it's a different level of hell

Sorry, checked my notes. They were using smalldatetime

swasheck

@billinkc ah yes. i see the confusion. they're easily mixed up

billinkc

smalldatetime in the original system. But the logic issues they had remained. And DST remains a fickle POS

swasheck

Thank you, autocorrect, for not recognizing Skype and telling someone that I wanted to "sleep with you real soon"

Tweeted by BastardKeith on March 4, 2016 at 7:53 PM

Paul White

8:33 PM

Honestly, I can't think of a case where I'd rather use datetimeoffset than UTC.

Zane

@PaulWhite I've always thought that as well.

Use UTC. Make it clear that it's UTC. Then deal with the rest on the display layer.

Paul White

There's probably some edge case where it's super-useful, but I've never encountered it.

swasheck

@PaulWhite where people refuse to exist that there are timezones other than their own?

Paul White

@swasheck I'm not interested in anywhere people refuse to exist.

mmarie

@PaulWhite Hmmm. I might be. It's probably nice and quiet there.

ypercubeᵀᴹ

8:44 PM

@PaulWhite that query is a mess:

2

Q: Query optimization possibilities?

I've embarked on attempting to optimize a rather large query that has 3 nested subqueries (like Russian dolls). The query itself is generated by south from a Django project, and I freely admit I'm no expert at SQL optimization. My strategy thus far is to start with the innermost query and work my...

Paul White

@mmarie Well that's true. We could get lots of work done.

ypercubeᵀᴹ

No wonder it was produce by an ORM (or something like an ORM)

Paul White

@ypercubeᵀᴹ Yes I looked at it briefly when checking if he had cross-posted.

I saw your answer, but the transform eluded me.

ypercubeᵀᴹ

The 1st post, at SO, has a stripped version of the query. (still a big mess!)

Paul White

Missing a vital correlation too IIRC.

If I were less tired right now I'd look up distinct on and have a go at it.

But Postgres, so I can skip it and not feel bad.

My worry at this point is he'll end up asking 100 terrible questions.

Terrible from the point of view of ever being useful to anyone else.

MDCCL

8:52 PM

@PaulWhite That potential flag design looks really good, the current one is not very identifying-friendly, so to speak.

Paul White

@MDCCL Quite so. It's almost indistinguishable from Australia's (gasp!) especially when small in size e.g. on a TV screen.

MDCCL

@PaulWhite Yes, as a foreginer it's very hard to distinguish them, perhaps not that much for the natives to NZ or AU. I found this related link.

Perhaps it's not very official, and I'm sure you are aware of those points already, but I find it interesting.

Paul White

@MDCCL Not all of them, no! Very good link, pity it's on Quora :)

@MDCCL You'd be surprised. I forget the exact proportion of kiwi's that incorrectly chose Australia's flag when asked to choose between the two in a recent survey, but it was something like 20%!

billinkc

9:07 PM

I must not troll. Trolling is the ban inducer. Trolling is the thousand little jokes that only I will snicker at. I must face my trolling. I will let it pass over me and through me and where it has gone, I will make no cracks about new zealand

Paul White

@billinkc No, no go right ahead. I promise not to ban you. /s

billinkc

I gave up trolling for Lent

Paul White

Very wise.

billinkc

Yes, I'm like one of your Powerful Owl's. Very wise

Paul White

Kicked for 1 minute. :-D

bluefeet

9:15 PM

what is happening??

I guess @billinkc failed with his giving up trolling

billinkc

@bluefeet The road to Damascus is very long

I need to find a recording of the Morepork owl

Morepork

The morepork (Ninox novaeseelandiae), also called the Tasmanian spotted owl, is a small brown owl found throughout New Zealand and Tasmania. == Taxonomy == Two subspecies, the Lord Howe boobook and the Norfolk Island boobook, became extinct during the 20th century. Janette Norman and colleagues tested the cytochrome b DNA of three subspecies (as well as the powerful and rufous owls) to ascertain whether the closest relative was used in breeding with the last surviving female of the Norfolk boobook. They discovered that although the Norfolk boobook was similar in plumage to the Tasmanian boobook...

Paul White

@bluefeet Yep

swasheck

@billinkc so you've already broken your lenten vows?

billinkc

Resolution, not vow

morepork

►

I can see how that'd be interpreted as morepork or boobook

bluefeet

@PaulWhite maybe the room topic should be changed to include "mention owls and you'll be kicked" :)

Paul White

9:20 PM

@bluefeet Bill's very cunning though. He'd find a loophole.

@billinkc The funny thing is I find that a soothing sound. My wife hates it with a passion. I'm sure she'd shoot them if she could.

MDCCL

@PaulWhite Yes, one out of five people is a quite surprising proportion, one would expect a much lower percentage. I guess there's always people who is not very concerned of this kind of things.

swasheck

@PaulWhite i find it soothing as well

Paul White

@MDCCL There was a separate process on one of the evening post-news 'entertainment' programmes, where people were interviewed and told when they got it wrong. They were mortified, mostly. They are very similar, and it's easy to forget which has white stars, and which has red stars with a white border :)

room topic changed to The Heap™ - Consultancy ©®: General on- and off-site discussion for dba.stackexchange.com [mysql] [no-owls] [nosql] [oracle] [postgresql] [sql-server] [ssrs-support]

MDCCL

@PaulWhite I can imagine their faces :D

Paul White

> In the Discworld novels, the main city is called Ankh-Morpork and has moreporks on its coat-of-arms. Terry Pratchett had not heard of the bird when he came up with the name but retroactively associated the name with the bird in later books.

Huh, didn't know that.

billinkc

9:35 PM

the first rule of heap club is we do not talk about owls!

swasheck

STOP LOOKING AT ME, SWAN

billinkc

Shh, shh, be cool

Paul White

9:50 PM

Ha. I should kick myself.

swasheck