The Heap™ – Consultancy ©®

Paul White

02:19

@AndriyM Stack Overflow has a lower threshold

@McNets What's up?

Peter Vandivier

06:36

@PaulWhite maybe it's nothing? "@PaulWhite are you there?" is something I just yell into the void occasionally as well ¯\_(ツ)_/¯

also, morning :)

hot2use

06:57

@MaxVernon Could be done.

hot2use

07:13

Morning

Andriy M

@PaulWhite Ahh, makes sense, thank you

Morning

Paul White

@PeterVandivier are you there? 😉

McNets

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

Paul White

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

Peter Vandivier

07:30

Andriy M

07:48

This is interesting:

1

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

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

McNets

08:05

@PaulWhite Ok

George.Palacios

Morning

McNets

Morning

George.Palacios

08:27

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

hot2use

[ <text> ] ( <link> )

This is just a test

George.Palacios

@hot2use Thanks a lot

hot2use

Without spaces

George.Palacios

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

Ah nevermind I got it - had to remove another space

Thanks :)

hot2use

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

Philᵀᴹ

08:39

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

08:54

@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)

Tom V

09:15

@Philᵀᴹ Why not?

You can do that alread, without the VM part

Tom V

09:27

spire.com

George.Palacios

10:00

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

Nevermind @ypercubeᵀᴹ has got it

ypercubeᵀᴹ

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

George.Palacios

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

Andriy M

10:42

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

"Answer" being the key word :)

ypercubeᵀᴹ

@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.

Andriy M

@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 :)

George.Palacios

12:02

What's the community stance on homework posts?

hot2use

@George.Palacios ambivalent

George.Palacios

@hot2use Well that clears it up :P

hot2use

15

Q: Is DBA a place for homework questions?

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

George.Palacios

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

hot2use

Exactly. As is the case for any question.

No Effort = No Answer

George.Palacios

12:06

@hot2use That makes sense - thanks :)

hot2use

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.

George.Palacios

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

Tom V

12:34

@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

hot2use

12:44

@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.

George.Palacios

13:03

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

Paul White

@TomV Is your keyboard broken then?

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

hot2use

@PaulWhite That was very subtlety put

Tom V

@PaulWhite I don't have a sock puppet

Paul White

@hot2use I can be a smartass 🙂

@TomV Neither did I before I created one.

Tom V

@PaulWhite Or an ass

Paul White

13:10

That too!

hot2use

I really had to laugh out loud now.

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

Paul White

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.

Tom V

@PaulWhite Muppet

George.Palacios

Hahahaha brilliant

Paul White

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

Aaron Bertrand ♦, Rhode Island

149k 18 281 480

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

Max Vernon

14:24

@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.

Paul White

14:40

@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.

George.Palacios

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

Paul White

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.

Max Vernon

@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.

Paul White

@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.

Max Vernon

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

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

Paul White

14:49

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

Max Vernon

@PaulWhite I'd second that.

Paul White

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

Max Vernon

@PaulWhite lol. You got the DDGFU!

George.Palacios

@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

Paul White

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.

Max Vernon

14:52

@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

Paul White

yep yep

Max Vernon

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

Tom V

15:04

@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

Max Vernon

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

Paul White

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

I tend to use that, then the back button.

Tom V

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

hot2use

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

George.Palacios

@hot2use 0.0006%*

Tom V

15:11

@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

hot2use

@TomV 5

George.Palacios

@hot2use hahahaha

Tom V

@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"

Paul White

@TomV Cause it to be reopened again? Huh?

Tom V

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

hot2use

15:17

42 Views in 2 years and no answer.

Tom V

@PaulWhite but in the comments you said you reopened it

hot2use

I did?

Hmmmm

Tom V

@hot2use Nah Paul

Paul White

> 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

Tom V

@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

Paul White

15:19

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

Tom V

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

Paul White

Agreed.

Max Vernon

Comment Converter MV

1 1

I added the MV to the end to differentiate

not sure if that is helpful or not

Paul White

My copyright lawyer will be in touch

Max Vernon

lol

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

hot2use

15:22

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

Paul White

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

Max Vernon

@PaulWhite seriously? what for?

Paul White

@MaxVernon take a wild guess

George.Palacios

@hot2use Oh damn. I stand mistaken

Max Vernon

@PaulWhite really I have no clue.

Paul White

15:23

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

Max Vernon

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

hot2use

JEAGL: For the unenlightened: What is DMCA?

Paul White

@MaxVernon copied article

Max Vernon

Digital Millenium Copyright Act

Tom V

@MaxVernon mv ./comment ./answer

Paul White

15:25

@TomV that seems clever

Max Vernon

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

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

Paul White

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

Max Vernon

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

Tom V

@MaxVernon Sure go ahead I don't mind

Max Vernon

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

I'll try.

Tom V

15:28

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

Max Vernon

> 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

Tom V

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

Max Vernon

@TomV cooool.

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

Too many raw items to process.

lol

Tom V

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

:D

hot2use

I read taht wonrg at frist

McNets

15:33

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

hot2use

people with big issues

big sodius

Max Vernon

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

McNets

big sidious

Tom V

@MaxVernon Also if you remove the dots?

Max Vernon

yah, tried that

George.Palacios

15:36

Wasn't big solidus in metal gear

Max Vernon

ohhh, this worked, but looks somewhat like crap:

mv 〳comment 〳answer

that slash is the VERTICAL KANA REPEAT MARK UPPER HALF

sounds painful

Tom V

@MaxVernon This isn't comomunity server after all

hot2use

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

McNets

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

hot2use

Stupid T-SQL Tricks (Brentozar.com)

Tom V

15:38

-2

A: How Does SQL Server Choose Indexes to Use

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.

George.Palacios

Dufus Magufus. Hmmm

hot2use

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

Tom V

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

Max Vernon

@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!

hot2use

I'm gonna name mine CC Top

3

McNets

15:48

@hot2use or ZZTop

hot2use

@McNets You get the groove

Max Vernon

@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

jadarnel27

Needs more PowerShell.

George.Palacios

Get-Comment | Set-Answer

jadarnel27

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

George.Palacios

16:00

If I can just find a replacement for the pipe character

hot2use

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

George.Palacios

@hot2use Much more subtle than mine but I like it

Max Vernon

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

McNets

@MaxVernon Kindle edition is expensive than paper!!

Jack Douglas

16:17

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

Max Vernon

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

Jack Douglas

something like that

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

Paul White

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

Jack Douglas

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/…

Paul White

@JackDouglas Yeah that seems familiar

Jack Douglas

16:22

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

Max Vernon

@JackDouglas stoush, andiluvian? wow. big words.

Paul White

@JackDouglas right

especially if you're not using a real database

2

Max Vernon

zing!

McNets

16:37

For a moment I thought Evan was here...

Cade Roux

16:50

@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...

Paul White

@McNets only in spirit

Max Vernon

@CadeRoux wow. some people.

Cade Roux

17:17

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

hot2use

18:15

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

McNets

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

hot2use

...and a unicorn dies.

McNets

18:38

And Evan is summoned, Hi @EvanCarroll

hot2use

@McNets looks like it

Colin 't Hart

18:53

Weird:

1

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

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

Evan Carroll

@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

jadarnel27

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

Evan Carroll

Put Xubuntu on it and tell him you fixed it.

(serious)

Or Lubuntu

ypercubeᵀᴹ

or Fubuntu

jadarnel27

Far out.

Evan Carroll

19:11

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!

ypercubeᵀᴹ

19:38

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?

Tom V

@jadarnel27 uninstall the antivirus. Defender is probably running already

McNets

20:07

@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.

MDCCL

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

Tom V

@MDCCL approved

MDCCL

@TomV Thanks

Cade Roux

@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.

jadarnel27

20:26

@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?

Cade Roux

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).

jadarnel27

20:47

@TomV Man, three different AVs running.

ypercubeᵀᴹ

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)

McNets

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

ypercubeᵀᴹ

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.

McNets

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

ypercubeᵀᴹ

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

McNets

20:54

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

Evan Carroll

21:39

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

MDCCL

@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.

ypercubeᵀᴹ

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

McNets

@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) )

billinkc

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

ypercubeᵀᴹ

21:54

@billinkc What is that magic in the partition key?

(convert to varchar, then to smallint?)

MDCCL

@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.

billinkc

@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

ypercubeᵀᴹ

22:20

@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

MDCCL

22:33

@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.

billinkc

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

Taryn

hi @billinkc long time

billinkc

Hello feet formerly of blue

3

MDCCL

22:52

@McNets Evan's answer brings up an interesting interpretation of the scenario.

Transcript for

The Heap™ – Consultancy ©®