The Heap™ – Consultancy ©®

Evan Carroll

2:03 AM

Is a Primary Key supposed to specify the ordering what it is indexing, technically?

Joe Obbish

3:07 AM

@sp_BlitzErik stop making life difficult for Pedro

sp_BlitzErik

@JoeObbish lol that was like six comments. wait for tomorrow.

hot2use

7:36 AM

good morning

ypercubeᵀᴹ

7:57 AM

@EvanCarroll in theory, Primay Key is a constraint. Whether it is enforced by indexes or not or whatever type of indexes is probably not part of the SQL standard but left to implementation to decide.

Andy K

8:13 AM

morning and evening

McNets

9:52 AM

morning

Tom V

10:04 AM

arstechnica.com/science/2018/01/…

dezso

10:40 AM

heh, just got a social engineering attempt via phone

live DB access, that's what they wanted from me

gbn

Purely for science

McNets

11:07 AM

Why using a NONCLUSTERED INDEX there is a Key Lookup for the PRIMARY KEY?

Paul White

A key lookup always uses the clustered index. In your case, that's also the primary key.

The Output List of the Key Lookup shows the columns being looked up and returned (not in the nonclustered index)

Any Predicate (not Seek Predicate) on the Key Lookup shows any column(s) used for filtering, where the filtered column(s) is/are not in the nonclustered index.

McNets

@PaulWhite Thank you.

Paul White

Or more simply: a lookup accesses data from the base object, which is always a clustered index or a heap (inc columnstore).

20 hours ago, by Joe Obbish

@sp_BlitzErik if you teach a man to fish he'll never post a screenshot of code again

McNets

11:23 AM

@PaulWhite then sometimes could be interesting to build nonclustered index with all the returned fields.

brentozar.com/pastetheplan/?id=ryUY5fI4f

Paul White

Yes that's a covering index.

McNets

Thanks again

sp_BlitzErik

12:37 PM

@PaulWhite DELETE THIS

Paul White

@sp_BlitzErik hm?

sp_BlitzErik

someone will find this someday and tell me that paul white said it was okay to create a nonclustered index that covers an entire table on some ridiculous 200 column denormalized pile of crap

Paul White

k

sp_BlitzErik

i was mostly kidding

no one would ever find that

Paul White

why risk it :)

sp_BlitzErik

12:43 PM

and if they did i'd pay you one billion vape bucks to show up and tell them they're dumb

Simon Righarts

Um would it help if I said I've seen covering indexes on a 100-column wide table in a previous life? :D (They actually helped, too, which is the depressing bit)

Paul White

Yeah can be optimal in certain read-only workloads, but it's for expert use I guess

Almost everything has a use somewhere

Except <insert name here> of course.

sp_BlitzErik

aw hek, I Must Of Lost The Particular feature you're talking about to the memory hole.

Paul White

For comedic effect, use the name of a person rather than a feature

sp_BlitzErik

i don't have dewitt to do that

Paul White

12:58 PM

very good

sp_BlitzErik

so how's your weekend going?

Master Database

1:18 PM

0

Q: Can we tag a user to get his attention?

Is there any way to tag a user to get his attention in some question? In my case, this question is not getting much attention, so I think people like RolandoMySQLDBA will be able to guide me. Will tagging in comment get users' attention?

Sean Gallardy - Microsoft

1:31 PM

Let it be known, yesterday a woman told me I was right.

sp_BlitzErik

handed?

Sean Gallardy - Microsoft

right as in correct.

sp_BlitzErik

she's playing the long game

Sean Gallardy - Microsoft

@sp_BlitzErik LMFAO

I rarely get on linkedin, but I just went there and I'm quite disturbed by what I found...

Tom V

@SeanGallardy-Microsoft Was she sober?

Sean Gallardy - Microsoft

1:37 PM

On the right hand side it says "People also viewed" and it's a bunch of people with therapy degrees....

sp_BlitzErik

perhaps you were disturbed long before finding that

Sean Gallardy - Microsoft

@TomV That's a great question, it's a 50/50 shot with her... most likely so, though, her texts were on point but she was on a plane..

@sp_BlitzErik I'm asking.. WTF are people viewing me and then a bunch of people with therapy degrees. What search could that be? "Hey, show me some technical people and then some therapists."?

sp_BlitzErik

maybe there's a therapist named sean gallardy

who specializes in disorders related to micro things

and you just kinda bubble up

Sean Gallardy - Microsoft

Doubtful, my last name is made up

sp_BlitzErik

ellis island special?

Sean Gallardy - Microsoft

1:44 PM

yep

sp_BlitzErik

my wife's family had the same thing happen. proper scottish name got turned into something that you'd see on a tub of hummus.

Sean Gallardy - Microsoft

It's amazing how terribly bad they were

sp_BlitzErik

not that there's anything wrong with hummus.

you're probably allergic though.

Sean Gallardy - Microsoft

haha, I am not sure...

to both

I used to eat it

sp_BlitzErik

deadly chick pea allergy

Sean Gallardy - Microsoft

1:45 PM

when I could eat things

it's a bean, and generally I'm allergic to legumes

might not be deadly though

some things I can get away with eating if I want to just throw up and have a rash for a few days

sp_BlitzErik

are you sure you're not just on heroin?

Sean Gallardy - Microsoft

If I were, I'd hope it felt better than this...

sp_BlitzErik

heh

Andy K

Worthy to be shared

If you have imposter syndrome you already belong and fit in far more than you know.

Tweeted by Nick_Craver on January 12, 2018 at 1:43 PM

dezso

2:02 PM

So it's a problem of transferring/processing/rendering data, not the query itself. — dezso 12 secs ago

Simon Righarts

So today's fun finding is that ENCRYPTBYKEY has different results depending on whether you pass it in a varchar or nvarchar. Which should be obvious in retrospect buuut ... yeah, I want that hour of my life back

Andy K

ConcernedOfTunbridgeWells

2:18 PM

@SimonRigharts Haven't seen you here for a while. How's it going?

Sean Gallardy - Microsoft

@SimonRigharts Nope we own that hour now, not gonna give it back. NO TAKES BACKSIES!

ConcernedOfTunbridgeWells

In unrelated news, I like szechuan pepper, but my internals not so much.

Simon Righarts

2:40 PM

@ConcernedOfTunbridgeWells Not too shabby - I'm in Sweden now, working for Scania (fulltime/permanent) as a database platform expert. Very vague title, practical translation is "dangerously close to architecture, but I still have to know how to distinguish my ass from my elbow". Are you still selling your soul^W^W^Wcontracting?

Evan Carroll

4:06 PM

Not referring to the SQL standard in that context. "A primary index is an ordered file whose records are of fixed length with two
fields, and it acts like an access structure to efficiently search for and access the
data records in a data file. The first field is of the same data type as the ordering
key field—called the primary key—of the data file."

ypercubeᵀᴹ

@EvanCarroll where is that from?

Evan Carroll

4:26 PM

@ypercubeᵀᴹ amazon.com/Fundamentals-Database-Systems-Ramez-Elmasri/dp/…

Fundamentals of Database Systems (7th Edition)

@ypercubeᵀᴹ maybe I can just make it a question

Andriy M

4:41 PM

I guess SQL Server calls a primary index a clustered index. Whether in SQL Server it's a structure consisting of two fields of fixed length, I'm not sure, though.

sp_BlitzErik

if you call it a field one more time, joe celko is going to kill you in your sleep

Andriy M

Fair enough, although I wasn't speaking in relational terms :)

bluefeet

@sp_BlitzErik field, field, and more fields

sp_BlitzErik

@bluefeet it was nice knowing you.

Joe Obbish

@sp_BlitzErik there's an ignore feature in chat

sp_BlitzErik

4:45 PM

→ 1 message moved to Trash

@JoeObbish merry christmas

Evan Carroll

What's wrong with calling it a field when you're outside of the conceptual model of SQL?

What do you call them when they're constant and sit in what otherwise amounts to a serialize C structure on disk?

a "column" lol

dezso

@EvanCarroll nothing, but then come the cattle

Evan Carroll

Texas <3s cattle.

jcolebrand

5:03 PM

@EvanCarroll You take that back!

Oh wait, we both live here

:p

Evan Carroll

I can speak for everyone, I've had the most experience doing it.

5

Max Vernon

5:22 PM

@EvanCarroll your best comment evar.

Evan Carroll

5:47 PM

No one would agree with that.

Lamak

6:00 PM

I do

Evan Carroll

Everyone knows you're a contrarian.

Sean Gallardy - Microsoft

6:18 PM

@EvanCarroll Which is odd that you don't live in Chicago...

Evan Carroll

6:30 PM

to much snow, not enough flip flops

Joe Obbish

6:44 PM

@SeanGallardy-Microsoft Any hints you can share about the BPREPARTITION spinlock?

Sean Gallardy - Microsoft

@JoeObbish Off the top of my head, no. I'd have to look at the code...

Joe Obbish

you guys don't have a secret list of spinlocks?

Sean Gallardy - Microsoft

@PaulWhite I got a weird blue circle thing over my picture in chat, what's that mean? It went away fairly quick so I couldn't investigate. Pardon my noobness...

@JoeObbish They are all secret, ask @EvanCarroll He pretends like he isn't part of the sect, but that's just to throw people off the trail...

ypercubeᵀᴹ

@SeanGallardy-Microsoft some message(s) - in some chat room, not necessarily of dba.se - was flagged. The fla usually go away quickly, as the yare visible across the whole network, to all chat users that have high enough rep.

Sean Gallardy - Microsoft

I'd have to investigate, though, I normally don't work too much in the QO space

Evan Carroll

6:48 PM

WILL TELL SQL SERVER SECRETS FOR "WINDOWS 10" STICKER REMOVAL SERVICES.

Sean Gallardy - Microsoft

@ypercubeᵀᴹ Did I secretly become a mod?

Thank you for the info, it was really weird and I didn't know if I did something or not. Much appreciated, sir :)

Still learning this platform

Evan Carroll

hahahha @ypercubeᵀᴹ got "sir"-d be the Microsoft suit. =)

Sean Gallardy - Microsoft

Yeah, every once and a while I show respect.

Especially after someone helps me...

ypercubeᵀᴹ

@SeanGallardy-Microsoft your chat profile shows that the total rep (across all SE sites) is above 10K. So, yes, you are a mod, regarding chat flags ;)

Joe Obbish

@SeanGallardy-Microsoft What makes you think that it's a QO problem? Or am I being dumb?

Andriy M

6:59 PM

What is QO (if you pardon my being dumb)?

ypercubeᵀᴹ

16

A: Why can I see spam/offensive chat flags?

Privileges on chat.stackexchange.com are based on the sum of your reputation across all Stack Exchange sites, not your reputation on a specific site. In your chat profile you can see that this adds up to 10489 reputation1, allowing you to see chat flags. 1 This is slightly less than the sum of ...

Evan Carroll

@AndriyM Quality Obliterater, that's where a product goes after acquisition before it gets to QA, (Quick Adieux) and goes to market.

I'm not sure what's worse stupid "Web 2.0" tech names, or acronyms.

*abbreviations technically.

McNets

7:28 PM

@ypercubeᵀᴹ Usually Russian messages that I don't understand...

Evan Carroll

I wonder what the least-bullshit most demanding online curriculum is with the most name recognition

I know Berkeley, and MIT have their own free stuff but they're not graduating online students afaik

Joe Obbish

7:50 PM

@AndriyM query optimizer I believe

Andriy M

@JoeObbish Oh, yes, now I remember, thanks!

I know that QA stands for "Quality Assurance". I've looked it up enough times for the meaning to finally get stuck in my head. Wasn't sure if QO was related or something different.

Evan Carroll

8:05 PM

I honestly didn't know that.

A "query optimizer" is a pretty vague term anyway. Historically it just meant anything MySQL couldn't do.

Evan Carroll

8:50 PM

Oh wait. Can't do an INT. Have to store Canadian as well. — SysChaos 10 mins ago

@SysChaos at this point, I'm confused at what would be a desirable answer? You've defined the problem as not geographical so I can't suggest storing WGS84 cords and GIS to solve this problem, and you've withheld business information to suggest alternatives. Essentially, you want to use totally ad-hoc arbitrary postal codes from different nations created over 50 years ago that have no geographical relevance for your task to divide up an unstated workload and you're asking how to best use arbitrary data to do that? — Evan Carroll 1 min ago

sp_BlitzErik

@JoeObbish how was blog traffic this week?

Evan Carroll

He rolled back my title change lol: dba.stackexchange.com/posts/195273/revisions

sp_BlitzErik

stop trying to solve a geography problem, MAN

Evan Carroll

9:08 PM

lol

IT IS NOT A GEOGRAPHY PROBLEM. IT'S A BUSINESS PROBLEM WITH DIVIDING A WORKLOAD. I AM NOT ASKING ABOUT THAT BECAUSE I LIKE BICYCLES.

MySQL users take the (?:cake|biscuit) when it comes to absurdity.

sp_BlitzErik

9:25 PM

dag nabbit, plan guides ignore max grant percent hints

Joe Obbish

10:19 PM

@sp_BlitzErik I think that's just for plan shape

something like if it won't work in a USE PLAN hint then it won't work in a plan guide

bluefeet

@sp_BlitzErik you should change your name to Elmer Fudd, dag nabbit!!

sp_BlitzErik

@JoeObbish maxdop hints work, though, along with parameterization hints

@bluefeet i may start hunting penguins :P

bluefeet

@sp_BlitzErik considering I'm not a penguin feel free

sp_BlitzErik

@bluefeet everyone on stack exchange is a penguin until proven otherwise

bluefeet

heh

Evan Carroll

11:00 PM

When I run into the same problem on SQL Server and find my same question on Google dba.stackexchange.com/q/187090/2639 =)

I should get to upvote my own questions

THEY HELP ME

dezso

11:39 PM

-1

Q: How do I ensure a relationship is commutative and unique?

In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance, CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b) ) ); INSERT INTO foo VALUES (1,2); -- works INSERT INTO foo VALUES (2,1); -- explodes I know ...

sql-server index unique-key computed-column

Evan Carroll

That's twice sqlcmd screwed me.

dezso

Well, check your partner's phone

Evan Carroll

> When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. [...] This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

So basically.. "sqlcmd is a "DB-Library application", and what Microsoft means to say is the default gets default-changed by the default in their own client. That's pretty much inane gobbledygook.

dezso

@EvanCarroll I didn't read anything but the title

Evan Carroll

Yea, it was a minor syntax error caused by their product. I was just seeing if this was possible, first ran into an error with GREATEST and LEAST not existing. Googled it and found my very own question about that thing from months ago. Then I ran into the SQL cmd error and I got the same answer, so I wanted to see what QUOTED IDENTIFIERS did and how it would look if they were off, and I found my same question the last time I got bite by this error dba.stackexchange.com/q/193451/2639

I should just ask a question why sqlcmd.exe sucks and has a crappy default and then everyone can close my other questions as dupes of it.

heh

Transcript for

The Heap™ – Consultancy ©®