The Heap™ – Consultancy ©®

6:57 AM

@MaxVernon Could be done.

7:13 AM

Morning

@PaulWhite Ahh, makes sense, thank you

Morning

@PeterVandivier are you there? 😉

@PaulWhite its a bit weird these new questions from users with reputation 1,

@McNets Nothing weird I could see. I did check though.

Peter Vandivier

7:30 AM

Q: Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

7:48 AM

This is interesting:

1

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] respectively). However, whenever there is no event/season going on, I'm not sure whether I should create a...

Particularly that the question has this sentence:

> I have [found several] reasonable arguments for either case (to use or not to use a null), but the problem is that none of what I've read so far is framed in use-case that would apply for my specific dilemma.

and this exchange in the comment section:

NULL is the "natural" way in SQL to store the absence of information. I personally avoid "magic values" or "magic rows" at all costs — a_horse_with_no_name Nov 6 at 6:58

@a_horse_with_no_name Thanks, that settles it. I'm going to use nulls. Besides, what I didn't add in my question to make it simpler is that, there was a intermediate table between the ones I mentioned that would have had to "inherit" the blank category row. It seems much simpler now. — David Nov 7 at 4:02

I mean, horse's comment still didn't provide the OP with an argument specific to the OP situation but it somehow managed to convince him?

Anyway, I'm so far struggling to find how this question is not primarily opinion-based, to be honest

And an answer is not very likely to emerge either, it seems

8:05 AM

@PaulWhite Ok

Morning

Morning

8:27 AM

Hey how can I add links in comments without posting the full address?

[ <text> ] ( <link> )

This is just a test

@hot2use Thanks a lot

Without spaces

dba.stackexchange.com/questions/223618/… I'm struggling hahaha

Ah nevermind I got it - had to remove another space

Thanks :)

[This is just a test](chat.stackexchange.com/transcript/message/47798531#47798531)

Philᵀᴹ

8:39 AM

Did we do this madness yet? aws.amazon.com/blogs/aws/…

What's next? Being able to spin up a VM on a satellite and book time slots on
various types of sensors when the satellite is at a specific location?

Peter Vandivier

8:54 AM

@Philᵀᴹ I'm... genuinely usure if that's trolling or actually something they'll take your money for...

Next up: AWS Merlin - on demand, enterprise-scale MAAS (magic as a service). Support cloud-scale conjuring and arcana needs of the modern global corporation. (coming soon in wandless and incantation-free *nix distibutions)

9:15 AM

@Philᵀᴹ Why not?

You can do that alread, without the VM part

9:27 AM

spire.com

10:00 AM

Can anyone decipher what this guy wants ? I'm none the wiser.

Nevermind @ypercubeᵀᴹ has got it

@George.Palacios ha yeah. It's weird when you suggest something and they reply "no I don't want that" without trying it.

It's almost like I'm talking to a user again hahaha

:47799700

@ypercubeᵀᴹ Oh he's literally just looking for an ORDER BY clause

10:42 AM

@ypercubeᵀᴹ Based on the posted output example, your solution is the answer.

"Answer" being the key word :)

@AndriyM Well, post it then ;) I really got work to do now and I should remove myself from dba.se for a few hours

My suggestion is either correct or the column is of CHAR type and it needs some conversion.

@ypercubeᵀᴹ Ohhh, the first output does suggest that, very nice

Although the query in their question doesn't have an ORDER BY at all

Too late :)

12:02 PM

What's the community stance on homework posts?

@George.Palacios ambivalent

@hot2use Well that clears it up :P

Q: Is DBA a place for homework questions?

15

Is asking homework questions allowed or should it be allowed/tolerated in dba.stackexchange.com? Is there a guideline for this in stackexchange?

discussion allowed-questions

12

Q: Is the homework tag deprecated on DBA.SE?

As the homework tag is officially deprecated on SO, the question arises whether we should follow suit here, on DBA.SE. So, should we?

discussion tagging

Right so it's less about the fact it's homework, more to do with how much effort the OP has put into asking the question

So similar to most other questions in that regard

Exactly. As is the case for any question.

No Effort = No Answer

12:06 PM

@hot2use That makes sense - thanks :)

If OP hasn't tried anything or doesn't supply a good enough description, then you are free to pop it in the VtC queue.

It's this one that prompted the question by the way

12:34 PM

@PaulWhite This question Already has 4 close votes again and the user didn't respond to your comment (even if he was seen just 3 hours ago) so I think it's safe to use CommentConverter by now

12:44 PM

@George.Palacios IMO: It has a "touch" of homework, but doesn't "feel" like homework. However, it is pretty broad and may also pass as a shopping-list question. The question is probably missing the tag normalization and might benefit from it.

1:03 PM

@hot2use Yeah once I read those other posts you linked my feelings were the same roughly.

@TomV Is your keyboard broken then?

Snark aside: Ok, I'll take care of it. But seriously, anyone can convert comments to an answer.

@PaulWhite That was very subtlety put

@PaulWhite I don't have a sock puppet

@hot2use I can be a smartass 🙂

@TomV Neither did I before I created one.

@PaulWhite Or an ass

1:10 PM

That too!

I really had to laugh out loud now.

Thanks guys. You always make my day, even when you don't.

Oh! Ha! I had forgotten I'd offered to write the answer already. Sorry @TomV!

@miracle173 I reopened this so you can add a self-answer. There is some lasting value in this Q & A and it would be better to have that value in an answer rather than the question comments. If you don't want to self-answer for any reason, let me know and I'll do it for you. — Paul White ♦ Nov 22 at 3:02

That's what I get for trying to multitask.

@PaulWhite Muppet

Hahahaha brilliant

sql-server sql-server-2008-r2 sql-server-2008 sql-server-2012 t-sql sql-server-2005 sql-server-2014

@TomV Wrong mod. I think you are after Aaron.

Aaron Bertrand ♦, Rhode Island

149k 18 281 480

2:24 PM

@PaulWhite perhaps I'm mistaken, but what is the lasting value in the question? Unless I'm missing something, that only applies to sqlfiddle, no? Or, are we tech support for sqlfiddle now? (I'm not trying to be sarcastic.)

@PaulWhite your "Comment Converter" sock is misleading users into thinking that's the way to do it. Don't get me wrong, I love the idea, and perhaps everyone should just have a comment-converter-account. Of course, I suppose people could just make the answer community-wiki. And you probably don't want your name attached to every comment converted into an answer.

Maybe a nice option would be to allow "anonymous" posts with just a simple checkmark, the same way there is a checkmark for "community wiki"

I'd ask StackExchange to implement something like that, but it would probably be conscripted to the "6 to 8 weeks" pile.

and, while I'm ranting, why the heck doesn't the Close-Vote-Review-queue allow you to vote on questions. And by vote, I mean downvote.

2:40 PM

@MaxVernon That's exactly how I used to do it - a CW answer with my name on it. I created the sock simply to avoid that. Not every answer I converted was something I wanted my name on directly. The idea of converting comments to answers and optionally CW-ing is as old as the hills, and certainly predates my participation on the network.

@MaxVernon I don't know about that. We allow Q & A on other tools database people use regularly.

I can run my SQL code on fiddle so it's an rdbms :P

Also relevant:

I created Comment Converter as way to show people how much better answers are in answers than comments. It is not intended to be used as a service (it is time consuming). Why make work for others? Answering in comments does new users a disservice by illustrating how not to do things! — Paul White ♦ Jul 19 at 14:03

@MaxVernon Likely to be declined since you can already do that simply by logging out or using a different browser.

@PaulWhite ahhhh, private tab. nice.

trotts off to make a converter

@PaulWhite yah I guess. I'm just a bit "get offf my lawn" today.

@MaxVernon See meta.stackexchange.com/q/211641

@MaxVernon That works too, but you can do a lot on SE in general without being logged in at all.

@PaulWhite yah, I've really never tried that. As in, never at all.

@PaulWhite reading that now; thanks for searching for it :-)

2:49 PM

@George.Palacios dbfiddle.uk is better - created by our own Jack Douglas. Consider making a donation if you find it useful. I do.

@PaulWhite I'd second that.

@MaxVernon For hat season I'm considering changing my name to metaDuckDuckGo

@PaulWhite lol. You got the DDGFU!

@PaulWhite You've just saved me several massive headaches with that - bookmarked, remembered, put in notepad. fiddle doesn't work for me assumedly because of some corporate filters / firewall

@PaulWhite But that one seems to

56

Q: A new fiddle for dba.se

SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two. I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few day...

discussion

@MaxVernon I initially misread the FU there.

2:52 PM

@PaulWhite I was wondering if you'd think the wrong thing. it's supposed to be google-fu of course :-)

but doesn't make sense since it's not google.

its DDG

yep yep

I love this comment:

I didn't buy my car. It was given to me by a company I work for. The boat is also owned by the same company. The same company also builds road networks in the area where I live. There's an area across the river I frequently want to visit, which I always do during my boat trips. I suggest to my company that a new bridge is built. My company tells me that I don't need to go there while driving their car (which I'm told not to rush with), and if I do want to go there, I can use the foot bridge 100 meters downstream. Since I like that area and the bridge is far, I'm tempted to stop using my car. — John Dvorak Dec 3 '14 at 5:01

3:04 PM

@MaxVernon I have the same gripe with the low quality posts queue, it's flagged as low quality but I can't downvote it, I have to open a new tab to downvote it which doesn't bother me, but I have to vote "looks ok" in the review queue

@TomV yah, it's dumb. The argument that they want to keep it "simple" belies usability.

There's a link to the question on the review page.

I tend to use that, then the back button.

@PaulWhite Oh I never realized you can post an answer as guest

@TomV I still think the question is off-topic, even thought it might benefit 0.0007% of the community.

@hot2use 0.0006%*

3:11 PM

@hot2use How many people use monetDB?

But I agree, I wanted to VtC initially and then saw the comments (now vanished) in which the OP asked why database client tools are on topic but SQLFiddle is considered off topic, then there was a whole discussion and the question was reopened because the answer was posted in the comments and Paul wanted to give the op the chance to self-answer

I don't really care if it ends up closed again, it's unlikely someone else will add a competing answer but I didn't cast the last vote because that would cause it to be reopened again to be closed again

It's a question, not a cat, it shouldn't resurrect twice

@TomV 5

@hot2use hahahaha

@hot2use But why close the monetDB question?

I had a look at their documentation, I think it's a valid question and an answer could be along the lines of "you can't use LDAP, but there is this trick that allows you to manage users externally or you should use this trick to create security groups"

@TomV Cause it to be reopened again? Huh?

@PaulWhite When I mentioned it here by pinging you I didn't vote to close as it would have been the final vote

3:17 PM

42 Views in 2 years and no answer.

@PaulWhite but in the comments you said you reopened it

I did?

Hmmmm

@hot2use Nah Paul

> but I didn't cast the last vote because that would cause it to be reopened again to be closed again

That's the bit I'm failing to parse correctly

@PaulWhite So had I closed it again by casting the last vote and the OP or you wanted to add an answer anyway it would have had to be reopened a second time for you or the OP to post an answer

and I assumed it would end up in the close queue at some point or another

3:19 PM

Oh right. Well I don't think I'd care that much lol

Anyway, we've wasted enough energy on it by now

Agreed.

Comment Converter MV

1 1

I added the MV to the end to differentiate

not sure if that is helpful or not

My copyright lawyer will be in touch

lol

I managed to get a completely blank avatar that I'm pretty proud of.

3:22 PM

@George.Palacios data.stackexchange.com/dba/query/935661/…

I've already had to issue one DMCA takedown notice this week.

@PaulWhite seriously? what for?

@MaxVernon take a wild guess

@hot2use Oh damn. I stand mistaken

@PaulWhite really I have no clue.

3:23 PM

@MaxVernon Well there's really only one possible purpose: to take down infringing copyright material

@PaulWhite ohhh, no. I mean't what infringing material did you need to get taken down.

JEAGL: For the unenlightened: What is DMCA?

@MaxVernon copied article

Digital Millenium Copyright Act

@MaxVernon mv ./comment ./answer

3:25 PM

@TomV that seems clever

@PaulWhite ahhh. And I figured it would have been a video of you surfing.

@PaulWhite much agreed. too clever for me, in fact. :-)

looks like a shell command to rename/move a comment to an answer

@TomV I like it... mind if I use it?

@MaxVernon Sure go ahead I don't mind

although I probably can't - can't use periods and slashes in names, I think.

I'll try.

3:28 PM

@MaxVernon Maybe solomon has a neat trick for a character looking like a slash without being one

> Oops! There was a problem updating your profile:

Display Name can only contain letters, digits, spaces, apostrophes or hyphens and must start with a letter or digit

@TomV ooooh good idea

@MaxVernon unicode.org/cldr/utility/confusables.jsp?a=%2F&r=None

@TomV cooool.

> Total raw values: 72,921,008,607,068,160,000,000

Too many raw items to process.

lol

From the looks of it you could use a ⧸ which is a big solidus and not a slash

:D

I read taht wonrg at frist

3:33 PM

@TomV 'big solidus', who choose character's name?

people with big issues

big sodius

it seems none of the "slash-like" characters work. :-(

big sidious

@MaxVernon Also if you remove the dots?

yah, tried that

3:36 PM

Wasn't big solidus in metal gear

ohhh, this worked, but looks somewhat like crap:

mv 〳comment 〳answer

that slash is the VERTICAL KANA REPEAT MARK UPPER HALF

sounds painful

@MaxVernon This isn't comomunity server after all

Brent uses a symbol that looked like a dot but wasn't in one of his articles.

@MaxVernon you can try with some others confuse : chars unicode.org/cldr/utility/character.jsp?a=003A

Stupid T-SQL Tricks (Brentozar.com)

A: How Does SQL Server Choose Indexes to Use

3:38 PM

-2

first off you are making a mistake by using views with indexes...use the tables themselves and utilize their indexes...views themselves do not use the underlying indexes to the tables. No sense in looking in 2 places for what indexes to use.

Dufus Magufus. Hmmm

Gist 4 Stupid T-SQL Tricks (Brent's Gist)

I can just see max frantically trying several combinations in order to cheat the validation, he'll either be back in 5 minutes to proudly show the result or back in 20 to say it can't be done

@TomV presumably the latter since there appears to be not enough space in a user-name for the required characters to make it look good. I'll keep trying though!

I'm gonna name mine CC Top

3

3:48 PM

@hot2use or ZZTop

@McNets You get the groove

@hot2use that's a great one

mv ٠〳comment ٠〳answer

ahhh, I dunno.

it'll work, I suppose. Thanks for the help, @TomV , @hot2use and @McNets

Needs more PowerShell.

Get-Comment | Set-Answer

Haha I was thinking of sticking with the Move-Item approach, but that's a good one.

4:00 PM

If I can just find a replacement for the pipe character

Get-ChildItem -Path Comment | Write-Output -Verbose

@hot2use Much more subtle than mine but I like it

Reading "IBM and the Holocaust" by Edwin Black. What an incredible book. Thanks @CadeRoux for the suggestion.

The CEO of Amazon AWS says they're dropping Oracle databases by end of 2019.

https://www.cnbc.com/2018/11/28/amazon-will-be-off-all-oracle-databases-by-end-of-2019-says-aws-chief.html $AMZN Amazon will be off all Oracle databases by end of 2019, says AWS chief

Tweeted by OphirGottlieb on November 28, 2018 at 4:11 PM

@MaxVernon Kindle edition is expensive than paper!!

4:17 PM

@MaxVernon I don't think they really had a choice

@JackDouglas couldn't afford the licensing? ;-)

something like that

itnews.com.au/news/amazon-and-oracle-in-database-drama-515447

I vaguely remember reading something that said their transition hadn't been entirely smooth

AWS is kind of vulnerable to vendors tweaking their licenses to double prices on AWS v their own hosting platform. Wonder if MS will follow suit at some point…

@PaulWhite crn.com.au/news/…

@JackDouglas Yeah that seems familiar

4:22 PM

it shouldn't be surprising that massive cross rdbms migrations sometimes cause performance problems though…

@JackDouglas stoush, andiluvian? wow. big words.

@JackDouglas right

especially if you're not using a real database

2

zing!

4:37 PM

For a moment I thought Evan was here...

4:50 PM

@MaxVernon Maybe I will continue on from the Kindle sample. I'm kind of vulnerable to losing all faith in humanity when reading stuff like that. Like I pulled up to the park the other day to go for a walk and there was a story on NPR about a woman surgeon who had done all kinds of great things, and then just as I got ready to turn off the ignition, they got to the part where she was shot and killed in front of her hospital by an abusive ex-boyfriend. If only I had driven a little faster...

@McNets only in spirit

@CadeRoux wow. some people.

5:17 PM

@MaxVernon npr.org/2018/11/24/670466930/…

6:15 PM

@PaulWhite but weren't they transitioning to a real database?

Every time someone pronounces "real database" a little fairy disappears.

...and a unicorn dies.

6:38 PM

And Evan is summoned, Hi @EvanCarroll

Q: How are these null values stored in a NOT NULL column?

@McNets looks like it

Colin 't Hart

6:53 PM

Weird:

1

We are replicating tables from SAP ECC 6.0 on HANA into an Oracle 10g warehouse, using SAP SLT. Since starting this, we have noticed the NOT NULL column definitions from HANA are retained in the Oracle copies of the tables, but HANA stores many values as empty strings. Oracle stores empty (varcha...

oracle-10g sap-hana

@CadeRoux Depressing, women should kill their abusive boyfriends and we should all find them not-guilty.

Oracle is dropping pretty fast.

I wonder how getting dropped from AWS will affect them

My neighbor helped me fix a problem with my car, and then he asked if I would take a look at his laptop to see why it's running so slowly.

It's a Dell Latitude D620. Windows Vista, 2GB of RAM.

o_0

Put Xubuntu on it and tell him you fixed it.

(serious)

Or Lubuntu

or Fubuntu

Far out.

7:11 PM

I spent the past week totally changing my stack

Now I'm using i3 (tiling wm)/Kitty/Ranger

It's really damn cool to be able to render pictures in the terminal.

Also discovered Surfing Keys for Chrome

which is awesome for StackExchange

now I can edit text boxes in web page with visual mode!

reformating shit code in questions has never been easier!

7:38 PM

But you still have these ID's in one table >>> same disk space, plus additional space for the new key. And keep in mind you'll need 2 reads or 2 writes to select/insert that table. You would save space if you had more than one table — McNets 1 hour ago

@McNets I think the idea is sound and it will save space. Why do you think it isn't?

@jadarnel27 uninstall the antivirus. Defender is probably running already

8:07 PM

@ypercubeᵀᴹ because data is still there and then you add a int/bigint twice. I think it would help if that ID would exists as FK in many tables and you were able to change it by a surrogate key (i dont know if SK is correct in this context) But this field exists only once, and you'll add some extra work maintaining 2 tables. Maybe I'm not understanding correctly.

I suggested this wiki excerpt edit, in case somebody wants to review it.

@MDCCL approved

@TomV Thanks

@jadarnel27 Lol, people think that's a fair trade!

I make my gf fix all the family computer problems.

She also now knows how to crimp CAT5 cable.

8:26 PM

@CadeRoux well, he did save me a bit of money 😀

Although my answer to his computer problem is mostly going to be that it's old, underpowered, and slow by it's very nature haha.

I think it's maybe 10 years old?

We stopped by my aunt's in Houston on our way to Austin to troubleshoot several issues - took 2 hours instead of 1 and ended up in traffic...: They needed AOL installed on a new laptop (yes), They had WiFi issues (due to a mirror in one spot and distance and walls in another - they'll need a repeater).

8:47 PM

@TomV Man, three different AVs running.

Lest assume that this (long ID) is repeated 10 times per average.
We have 56 bytes * 10 = 560.
If we split to 2 tables, we shave (56-4) * 10= 520 bytes and add 56+4+32 = 92 bytes.

That looks like a gain of 424 bytes. For 10 rows!

(32 is an estimate for the fluff per row)

@ypercubeᵀᴹ But OP says: I want to keep the IDs, as far as I understand he is not trying to split them.

With 1.5G rows, the gain is about 63GB

@McNets I understood he wanted to keep them in a separate table

> My idea is to use one table that only contains the ID strings and the second table that contains all the other information and a foreign key on the first table.

Yes, you gain in one table but not in disk space.

I don't follow. Do you disagree with my analysis above?

8:54 PM

No, no

I suppose I'm misunderstanding the question.

All it depends on how many repetitions of each ID

If there is only one table, I think OP should 'replace' current ID by a new ones

Without generate a new table, or at least, until new IDs have been generated

9:39 PM

@ypercubeᵀᴹ I answered it dba.stackexchange.com/a/223700/2639

@McNets I guess that's one situation where it would be nice to have database management systems that provide the option of preventing redundant physical storage of foreign key values, as mentioned in this interesting SO answer.

@MDCCL I'm not really sure what that answer suggests.

@MDCCL yes, but still not clear to me where these ID's comes from, how a user selects one specific car.

Imagine you want to get all points from 'user1' 'car1', how do you compose an ID like 4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d?

It must be stored somewhere else

The first entry is a random ID that marks one rental. (I guess something like hash(user, car, RNG) )

Anyone on 12.0.5600.1 aka Microsoft SQL Server 2014 (SP2-CU14) want to see if you can repro my index rebuild issue? https://gist.github.com/billinkc/49753a891722884b5b274afb449a0966

Tweeted by billinkc on November 28, 2018 at 9:48 PM

9:54 PM

@billinkc What is that magic in the partition key?

(convert to varchar, then to smallint?)

@ypercubeᵀᴹ As per my interpretation, the physical mechanisms in current SQL DBMSs entail storing the values of PK-FK pairs at least in (a) the physical structures supporting the row with the PK value and also in (b) the physical structures supporting the row with the FK value —though sometimes the physical redundancy goes beyond this example—.

Ideally, one should have the option of storing the value only once, let's say in (1) the physical structure supporting the row with the PK value, and simply (2) setting up the physical structure supporting the row with the FK value so that it only makes a physical —as opposed to logical— reference to (1), if convenient, e.g. to save disk space.

@ypercubeᵀᴹ The record key is generated from the mainframe and it assembles this unique key based on datetime + location + random factor and I believe that digit is the random bit.

Had I been here when we developed this partition strategy, we'd have just done integer division and modulus to get a "clean" value versus this stuff

Plus as Paul's article talks about (citation needed), the engine will ignore the persisted column and happily recalculate it every flipping time

At any rate, there's magic in having 100 rows in the table to cause this blow up

99 and the index rebuild works. 100 and boom

The partition key can be redefined as [PartitionKey] AS CAST((RECORD_KEY % 1000000) / 100000 AS smallint) PERSISTED NOT NULL and it'll still fail on the rebuild

But having a persisted computed column as part of the partitioning key is required. Reran it with providing explicit values for the column and it works great

10:20 PM

@MDCCL I see. That looks like .. pointers, .. unrelational.

But if it is provided by the DBMS and is not visible to users, and provide performance benefits, it might be worth the trouble

10:33 PM

@ypercubeᵀᴹ Yes, but all this handled at the physical layer, not touching the logical (relational) one. So e.g. when there's an UPDATE on a PK value, the physical layer only modifies the structure (e.g. an INDEX) supporting the row with the PK value, and the reference in the physical structure supporting the row with the FK value does not have to be touched, since it is only a physical reference (e.g. a pointer, as you mention), so that would be an example of a performance benefit, for instance.

Also applicable to Microsoft SQL Server 2016 (SP2-CU3-OD) (KB4466671) - 13.0.5221.0

Taryn

hi @billinkc long time

Hello feet formerly of blue

3