The Heap™ – Consultancy ©® - 2017-08-09 (page 1 of 2)

Joe Obbish

12:54 AM

> 2017

> not keeping your pages close together

sounds like you need to update your "should I rebuild my index?" flowchart

Are your pages too far apart? Yes -> rebuild

sp_BlitzErik

@JoeObbish as soon as I have guidance on the unit of measure and acceptable distances I'll get right on it.

Is that on disk or in memory?

We may never know!

Joe Obbish

right unit of measurement is furlongs

sp_BlitzErik

1:12 AM

Eddie?

Joe Obbish

JEAGL

Paul White

2:35 AM

38

A: Raising the 25 chars limit for tags

Thunderforge makes a compelling argument that the tag length limit should be longer than 25 characters. Tim and animuson are quite right that shorter tags are generally better; regardless, supporting arbitrarily long tag names is out of the question for the foreseeable future - there are too man...

Maximum tag length increased to 35

Paul White

3:28 AM

...

Should this question really be closed?

-1

Q: Set Default Isolation Level to RSCI

Has anyone ever set RCSI as their default isolation level across an organisation without testing every app? I understand the implications, just gauging risk / reward. We’ve had many performance issues that would be solved by using RCSI, and I haven’t heard of or seen any issues with the 10-20 s...

Joe Obbish

@PaulWhite Not sure

I don't feel too strongly about it

I don't like "has anyone ever done this before?" questions

wow, 4 upvotes and 5 downvotes

controversial question I guess

he also said that he understands the technical implications

and wants to have a "discussion" about it with other people

doesn't fit the format imo

Michael Green

4:05 AM

You know that phrase "be careful what you wish for because you just might get it?"

I work for a startup in Electricity retail. A while back we closed a partnership with a large organisation. Today is the "soft" launch. However, the press releases are in the major newspapers, and the client will be interviewed in the evening news. Also, the Prime Minister has chosen today to summons the CEOs of electricity companies for a dressing-down over prices. Our user base is about to go from less than one thousand to two point five million in about three and a half minutes.

Wish me luck.

Joe Obbish

4:28 AM

good luck!

did the power go out yet?

Paul White

4:46 AM

@JoeObbish To be fair, the word "discussion" only appears in a comment on the accepted answer, not in the question itself.

Good Subjective, Bad Subjective

Robert Cartaino on September 29, 2010

Stack Exchange is about questions with objective, factual answers. We’ve been crystal clear about this for as long as I can remember, even back to the earliest, pre-beta days of Stack Overflow. It’s right there in the standard Stack Exchange FAQ:

What kind of questions should I not ask here?

Avoid asking questions that are subjective, argumentative, or require extended discussion. This is not a discussion board, this is a place for questions that can be answered!

Thus, questions that are not answerable — discussions, debates, opinions — should be closed as subjective. It seems simple enough: Fact good; opinion and discussion bad. But why? …

I think, on average, we are a little too quick to close questions around here, and a little too slow to reopen them.

Joe Obbish

@PaulWhite I think that it's not good when we get five close votes and no comments, when a comment would be helpful

But I still think that it's a terrible and lazy question

he just wants someone to agree with him so he doesn't have to do testing

Paul White

5:02 AM

@JoeObbish Ah so you do feel strongly about it after all :)

But sure, that's a fair comment

Joe Obbish

@PaulWhite No, I've just been grumpy lately

You've done this moderation stuff like 100X more than me

and as I said if it was opened I wouldn't really mind

the community seems split on if it's a good question

Paul White

Though having said it's a "fair comment" it also seems like a reason not to answer it yourself, rather than a reason to prevent anyone from answering.

If I was clear on it myself, I'd just reopen it and be prepared to justify that on meta.

I might also just be looking for a distraction from Pitch Perfect (film my wife is watching)

Joe Obbish

@sp_BlitzErik COME AT ME, BRO:

0

A: Efficiently storing query-affecting settings in a database

It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance. Why shouldn't an 18 row table affect performance? Take a look at the estimated query plan for the original query that takes 4 seconds. The query optimizer is saying that the...

@PaulWhite I don't view it as a good format for the site and it seems designed to attract low quality answers

With that said, John did a good job with his answer

Paul White

Yeah I get that. We do have tools at our disposal to handle low quality answers though, once they actually happen.

Joe Obbish

if anyone votes to reopen it or just reopens it I won't complain

Paul White

5:14 AM

@JoeObbish You say that now, but you're awfully contrary ;)

Joe Obbish

well I voted to close it, so I will have a justification for doing so

it would be poor form if I had no clue why I voted that way

Paul White

I'm so funny I make myself laugh sometimes

Joe Obbish

me too

but I missed the joke this time

Paul White

Aug 4 at 14:23, by Joe Obbish

so contrary!

Aug 4 at 14:27, by Joe Obbish

so.... contrary...

Joe Obbish

that's not funny!

see what I did there?

Do you have the peer pressure badge?

Paul White

5:18 AM

@JoeObbish No, I've never been downvoted.

Joe Obbish

I can fix that

but that's good, maybe I can get a badge that you don't have

Paul White

Actually I might have had one. I forget.

Joe Obbish

in that last answer I kind of recommend a scalar udf to be used in a query

Paul White

@JoeObbish Oh the come at me bro one. Let me see...

Joe Obbish

I don't know what you're talking about!

Paul White

5:23 AM

Hm?

Joe Obbish

blogs.msdn.microsoft.com/sql_server_team/…

I can't believe how excited I am about that

Paul White

@JoeObbish SQL Server: Variables, Parameters or Literals? Or… Constants?

Joe Obbish

oh yeah, should have made them columns

I don't do this kind of thing often

testing with stats io? really? that's just silly

Paul White

@JoeObbish If you work that option into your answer I'll likely upvote it.

I could upvote it now, because there's lots of good stuff in there but hey

Related: I would love it if SQL Server had better support for constants/static data

Including enumerations

Joe Obbish

5:39 AM

@PaulWhite Already did, although maybe not enough detail?

bed time for me. feel free to edit as always :)

sp_BlitzErik

I'd write the scalar function to fetch values into variables and write the query as dynamic SQL to avoid local variable issues.

I've had decent success with that in the past when there are configurable settings for users attached to a project

McNets

6:56 AM

morning

Paul White

morning

@JoeObbish I made an edit, but you didn't provide a repro for your mock data so I couldn't add plans etc.

Paul White

7:28 AM

This is another interesting option:

3

A: How to create database level constants (enumerations) without using CLR?

Since you are apparently using Sql 2016, I'd like to throw out another 'possible' option - SESSION_CONTEXT. Sharing State in SQL Server 2016 with SESSION_CONTEXT has some very good information about this new functionality in Sql 2016. I'm summarizing some key points: If you’ve ever wanted t...

Tom V

Morning

gbn

Morning

@JoeObbish I deleted that answer. I tested locally and my solution was worse, as per my comment. But I got busy and forgot to update...

Mikael Eriksson

@MikaelEriksson I'm interested :) Please add an answer. — Paul White ♦ 1 min ago

I kind of remember thinking about it. Will see if I figure it out .... again :) @PaulWhite

Paul White

Thanks :)

I really like the SESSION_CONTEXT idea though. That had not occurred to me before.

transparent-data-encryption has 27 characters woo hoo

McNets

7:55 AM

0

Q: Add new column TotalQty

can someone help to add a calculation at each level to multiple all the quantities and it must assigned to the new column named TotalQty in the below query: ALTER PROCEDURE [dbo].[SP] ( @PartNumber nvarchar(255) ) AS DECLARE @ibID UNIQUEIDENTIFIER IF OBJECT_ID('tempdb..#TmpResults...

sql-server sql sql-server-2008

I think there is the same question but from other OP

0

A: Add New column sRevision

Maybe this works if I have correctly counted the nested joins. insert into #TmpResults Select IBM.FirtPartentMaterialUsedGUID, IBM.FirstParentPartInstanceID, IBM2.IBParentMaterialUsedGUID, IBM.nQtyPerUnit, IBM.sRevision, TopMUGUID = case when IBM.TopMUGUID IS Not NULL th...

Not exactly the same.

Mikael Eriksson

8:20 AM

@PaulWhite Answer is in. More focus on the enumeration than where to put constants.

Tom V

@MikaelEriksson creative :)

Mikael Eriksson

@TomV Well ... If your only tool is XML then every problem looks like a ... hmmm, then you need to be creative.

Paul White

8:41 AM

@MikaelEriksson Thanks that's quite amazing

2

A: Create database level constants (enumerations) without using CLR?

You can create an enumeration type in SQL Server using a XML Schema. For example Colors. create xml schema collection ColorsEnum as ' <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Color"> <xs:simpleType> <xs:restriction base="xs:string"> ...

Mikael Eriksson

No worries.

Michael Green

Today's DB usage stats:

A special prize if you can guess when our partner sent the marketing email.

Paul White

9:02 AM

Don't know, but it looks like the db is giving you the finger anyway

7

McNets

haha

sp_BlitzErik

12:11 PM

@MikaelEriksson that is really cool. Never would have occurred to me.

ChrisF

1:14 PM

I have a weird problem, but don't think I've got enough information to ask a question about it.

I have a decimal value : 45.123333333333... (calculated from 135.37 / 3). It's stored in the database in a MONEY column. In one database it's stored as 45.1233 (the correct rounding), but in another it's stored as 45.1234 (the incorrect rounding). What do I need to be looking for in differences between the database that might explain this?

The column is mapped to the C# field by some autogenerated code - which could be the cause of the difference, but as far as I can tell it doesn't do any processing on the numbers.

The collations of both databases are SQL_Latin1_General_CP1_CI_AS

Is this something that could be caused by the database or is it going to be in the code somewhere?

bluefeet

1:38 PM

Hi @ChrisF :)

ChrisF

Hello

Tom V

@ChrisF Is it the same code that does the inserts? I.e. you have 2 instances of your app connecting to different databases or could it be a different line of code that does the insert?

Lamak

@ChrisF 135.37 / 3

are those values from columns?, if so, what datatype?

sp_BlitzErik

can client locale settings also affect that?

ChrisF

@TomV It's theoretically the same code ;) The are two different builds of the app connecting to two different databases, but all the relevant code appears to be identical between the two builds. The table definitions also appear to be the same.

I'm saying "appear" a lot because they should be the same, but I haven't checked everything.

@sp_BlitzErik I had that thought, but the calculated value is independent of locale.

@Lamak The code is C# and the value is decimal, the database is MSSQL and the column is money

Lamak

1:45 PM

@ChrisF decimal with what precision?

ChrisF

@Lamak It's a C# decimal. I'm thinking that the problem is in the conversion but I can't see anything obvious.

Paul White

Crikey I thought I was in the wrong room for a moment

sp_BlitzErik

who's this bruce saying crikey?

are we in straya now?

Paul White

g'day mate slip another shrimp on the barbie

So much blue I thought this was the Teachers' Lounge

bluefeet

we're trying to mess with you

Paul White

1:53 PM

@ChrisF If the calculation is being performed in .NET I would check the exact builds of SQL Server. There have been some oddities translating the Framework's idea of a decimal to SQL Server's.

@bluefeet It's working

ChrisF

@PaulWhite Ah.

sp_BlitzErik

those are pretty old though, no?

i remember a very old one in like sql server 2005 (maybe floats/decimals?)

ChrisF

Well the database that's correct is on SQL Server 11.0.6248

Paul White

@sp_BlitzErik No much more recent. But yes, it is speculation.

sp_BlitzErik

well it's settled then

we should all just use excel

formats column

ChrisF

1:55 PM

But the database that's incorrect is also on SQL Server 11.0.6248

Is it worth asking this as a question? Or am I missing something obvious?

bluefeet

@sp_BlitzErik it really is the best

Paul White

Question would be a bit vague "guess the colour I'm thinking of"

sp_BlitzErik

on which site? this sounds like it'd be a punting match between dba and so

Paul White

@ChrisF Latest is 11.00.6607

ChrisF

@PaulWhite That's what I was afraid of.

Lamak

1:57 PM

@ChrisF maybe the issue is with the calculation in C#. Is it consistent?

Paul White

@ChrisF OTOH if you can provide lots of detail, and perhaps a minimal repro? it could be quite good

sp_BlitzErik

@bluefeet vlookups are the new joins

Lamak

I mean, does it keep inserting the same value in both databases?

ChrisF

@Lamak Yes. That's consistent.

Paul White

@Lamak Ha ha the reverse of "always blame the database"!

Lamak

1:57 PM

@PaulWhite for once at least

ChrisF

@Lamak It appears to be repeatable.

@PaulWhite The problem is that the SQL<>C# conversion is done in generated code and I it's rather a large file to search and examine. I think that that's my next step - find the actual code that does the conversion and see if there's a difference there.

sp_BlitzErik

ah, it's like consulting. "no it's the code!" "no it's the database!" "no it's the hardware!"

Paul White

With a mcve it might be interesting for SO or DBA.se

Lamak

@ChrisF interesting. I think I would look into any difference in the code, or the server

Paul White

Of course you'll get abuse from some for using money at all :)

Lamak

2:00 PM

@sp_BlitzErik I mean, if it's consulting, then it clearly is "It Depends ©®"

Paul White

@sp_BlitzErik Yep exactly

ChrisF

@PaulWhite Probably. At least we're not using a float :)

Paul White

Ooo I love a good float

sp_BlitzErik

i use money because it's the easiest way to produce large, readable numbers

Lamak

@ChrisF I think I prefer them over money

sp_BlitzErik

2:00 PM

such commas~

Paul White

@sp_BlitzErik You use money because smallmoney cannot hold your paychecks.

sp_BlitzErik

@PaulWhite one of us, one of us

Paul White

I would genuinely like to understand more modern references without them being explained at great length.

Sadly I do not seem to be progressing much.

Lamak

@PaulWhite didn't know it either

30

Q: Where does the "One of us! One of us!" chant originate?

I've seen the chant "One of us! One of us!" parodied in both South Park and Big Bang Theory. Where does it come from? Is "Gibble Gobble Gibble Gobble" (added by South Park) part of the original chant?

reference the-big-bang-theory origin south-park

sp_BlitzErik

i'll have all of you up to date soon enough

Paul White

2:04 PM

Oh that 1932 film! Of course!

Jesus.

sp_BlitzErik

totally OT, but i found a couple cool docs on youtube about the origins of english synthy music.

Paul White

@Lamak Nice find.

sp_BlitzErik

i always liked fad gadget, didn't realize frank was as much of a pioneer as others give him credit for

Paul White

@ChrisF Would you be so kind as to let me know if you do post a question on SO or here? I'm kinda intrigued by the details. I read a bit of C# even if I don't write it very well. Also if you get an answer! Only if you remember.

Since when does the Achievements drop down show current UTC time?

That's cool.

sp_BlitzErik

it's been there for as long as i've been point-whoring to make epic

bluefeet

2:08 PM

@PaulWhite I think always

Paul White

Well there's a thing. How have I never noticed that before.

Lamak

@sp_BlitzErik I don't think SO is that old

bluefeet

That's the clock we base everything off so we show it

Paul White

Must add "attention to detail" to my CV.

sp_BlitzErik

@PaulWhite too many white zin spritzers

bluefeet

2:09 PM

@PaulWhite or Dev Story

ChrisF

@PaulWhite Will do. I've got half a dozen other things on the go at the moment so it might be a couple of days before I get round to it.

Paul White

@bluefeet hahahahahahahahahaha

@ChrisF Sweet as thanks no worries if you forget

bluefeet

@PaulWhite we do have a timeline

McNets

@Philᵀᴹ you there?

Lamak

@PaulWhite I like the dev story

Paul White

2:10 PM

@Lamak Suck up

sp_BlitzErik

@Lamak so you're saying i've been trying to get a badge on SO before SO was a website? shouldn't that get me the hipster badge?

Paul White

@bluefeet On the dev story?

Also why isn't it DBA story?

Not that I'm one of those either. Hm.

Lamak

@sp_BlitzErik well, looking at that selfie, you must have a lot of hipster badges

Paul White

Epic hipster badges

bluefeet

@PaulWhite yeah there are two views dev story and traditional.

Lamak

2:13 PM

@sp_BlitzErik you should also have a "plant sitting in a chair" badge

Paul White

Someone should draw this incorporating chat.stackexchange.com/transcript/179?m=39299413#39299413

sp_BlitzErik

@Lamak that plant is comfy af

Philᵀᴹ

@McNets yes

Lamak

@sp_BlitzErik you are very considerate with that plant

are you vegan?

sp_BlitzErik

@Lamak no, i'm human

Lamak

2:15 PM

are you sure?

McNets

IIRC PETER should appear on January and February because DAVE has been assigned on March?

sp_BlitzErik

@Lamak given the amount of times i've eaten steaks that could still remember their name when they hit the plate, yes. i can safely say i'm not vegan.

Lamak

@sp_BlitzErik I meant human

Philᵀᴹ

@McNets yes

McNets

@Philᵀᴹ IIRC PETER should appear on January and February because DAVE has been assigned on March?

ok

sp_BlitzErik

2:19 PM

@Lamak shhhh

the less you know about that, the better

Philᵀᴹ

@sp_BlitzErik I eat so much of it, I may as well just mooooooooo 🐄 🐮

sp_BlitzErik

@Philᵀᴹ have you ever eaten so much steak you got the urge to invade a country?

Philᵀᴹ

@sp_BlitzErik Yeah, but I was in Jackson Hole (Wyoming), at the time. Was easier to just drink & snowboard it off :)

Paul White

From a certain perspective, you had already invaded a country at that point

Philᵀᴹ

English people certainly were a novelty there

Paul White

2:33 PM

Hm well this is freaky weird.

Latest SSMS shows SQL queries run by the Object Explorer in the Output window.

sp_BlitzErik

:wat:

Paul White

You'd think they'd want to keep that code quiet

sp_BlitzErik

i'm still not sure what you mean

Lamak

@PaulWhite do you have a very small screenshot of it?

sp_BlitzErik

or very long

Paul White

2:36 PM

sp_BlitzErik

i still don't know where that is

Paul White

@sp_BlitzErik Joe is that you?

@sp_BlitzErik View menu -> Output (Ctrl+Alt+O)

You can choose between Object Explorer output and "Telemetry"

Easier than running Profiler I guess, but I wonder why they did this.

Lamak

@PaulWhite curious...but not really unhelpful

sp_BlitzErik

i have never seen this before

wild

Paul White

@Lamak Sure, but given dev resources and so many other things to do with SSMS...?

Lamak

2:42 PM

yeah, true

Paul White

Anyway it is a thing now

sp_BlitzErik

anyone else ever accidentally paste address to a haggis into powerpoint?

Tom V

mmmm, haggis

sp_BlitzErik

i went to a robert burns night hosted by laphroaig a couple years ago

it turned out most of the people at our table had never had haggis before

i ended up eating a lot of other people's haggis that night

Forrest

Were they too sheepish to try it, or could they just not stomach it?

4

Paul White

2:53 PM

good grief

sp_BlitzErik

i see what you did there

Tom V

Even though a lot of people agree it's not bad if they can bring themselves to trying

Kris Gruttemeyer

3:10 PM

Any of you traveling DBAs have a recommendation on a 2-laptop (primary + backup) carry-on bag that's security friendly?

sp_BlitzErik

get tsa precheck or goes

Kris Gruttemeyer

already have pre-check thankfully

worth every penny i spent on it

sp_BlitzErik

then what do you need? they stopped asking me to take laptops out with that.

even international

Kris Gruttemeyer

a good bag that can carry 2 laptops. Personal preference?

sp_BlitzErik

ah

large laptops?

Kris Gruttemeyer

3:12 PM

both are 15"

I know with the 2 laptops it doesn't leave room for much else in a normal carry on roll bag

sp_BlitzErik

you know what, lemme ask richie

he never goes anywhere without three laptops

he has some crazy backpack

Kris Gruttemeyer

That would be awesome. i can't go anywhere without 2. Had a failure on my primary last trip and having that backup saved my bacon.

sp_BlitzErik

he's traveling for the company retreat today so i probably won't have an answer super fast

but i'll let you know

Kris Gruttemeyer

Sounds good to me, thanks again Erik!

sp_BlitzErik

np

Zane

3:33 PM

Training question that I was legitimately asked "Why are databases targets of attacks?" Correct answer "They Contain data".....

sp_BlitzErik

(╯°□°）╯
︵┻━┻)

Forrest

@Zane What was the wrong answer? Because it's fun?

sp_BlitzErik

@KrisGruttemeyer ebags.com/hproduct/ebags/tls-professional-slim-laptop-backpack/…

bluefeet

@sp_BlitzErik those are supposed to really nice

sp_BlitzErik

yeah, now that i'm looking at it i'm thinking about buying one, hahaha

Zane

3:38 PM

It's not that the answer is incorrect. It's that it's hilarious. This is a training for Database Security for DBA's

sp_BlitzErik

oof, security training

bluefeet

@sp_BlitzErik I've actually been thinking about getting their backpack which is a suitcase

sp_BlitzErik

@_@

Kris Gruttemeyer

@sp_BlitzErik I like that one. Is that what Richie uses?

or the one he recommended?

sp_BlitzErik

yep! he just responded.

Kris Gruttemeyer

3:41 PM

Very nice. Just bought that doggie

Tell him thanks from me

sp_BlitzErik

will do!

@bluefeet which one were you looking at?

subquestion: is it blue?

bluefeet

@sp_BlitzErik this one ebags.com/product/ebags/…

maybe blue or graphite

sp_BlitzErik

ooh that's nifty with the compartments

bluefeet

yeah, and it has amazing reviews

Joe Obbish

@Forrest I did a SQL injection attack for fun yesterday

A developer gave a presentation to a room full of developers and said that "I'm pretty sure that I designed it so SQL injection can't happen"

I mean, I really had no choice

Kris Gruttemeyer

3:51 PM

@JoeObbish Challenge accepted....

sp_BlitzErik

hot sql injection

Paul White

So contrary

Forrest

@JoeObbish Nice! It's one of the first things I figured out how to do on our company websites.

Joe Obbish

@PaulWhite Why's that?

Sometimes SSMS had performance problems and seeing those queries really helps

there's some other way to do it but I don't remember now

sp_BlitzErik

i wish i had that when i was starting out just to know which views to get which information from

also handy for new features, i suppose

Paul White

3:56 PM

@JoeObbish Not always their finest work

sp_BlitzErik

if you're still expecting good queries from microsoft...

Joe Obbish

that makes it more important to show it!

Paul White

@sp_BlitzErik Have you used WWI much?

sp_BlitzErik

i have used it 0 times

Paul White

Same

sp_BlitzErik

3:59 PM

@PaulWhite i'd rather use bars

Joe Obbish

@PaulWhite Pink?

Paul White

@sp_BlitzErik Northgale :)

@JoeObbish Nope

@sp_BlitzErik ha!

sp_BlitzErik

brb wearing shoes and tipping a waiter

sp_BlitzErik

4:52 PM

when it's so hard to find a good stock picture of a video game power up that you settle for a picture of a guy eating a mushroom off a tree stump

Joe Obbish

just generate a query plan that when zoomed out is in the shape that you want

sp_BlitzErik

Joe Obbish

the coloring might be tough

you could use the query plan diff tool for that

sp_BlitzErik

i'm just gonna stick with my happy camper

Lamak

5:08 PM

@sp_BlitzErik that's not a power up

sp_BlitzErik

you're right, but it's the shape i'd want

Lamak

@sp_BlitzErik conformist

sp_BlitzErik

i know, what with my white picket fences and khakis and college degree

Lamak

don't forget a green mushroom "power up"

sp_BlitzErik

i don't know, most people would consider getting an extra life quite powerful

Lamak

5:13 PM

I didn't think of you as "most people" though

sp_BlitzErik

you're clearly unaware of my transhumanist interests

(this is why you keep getting fired)

(fyi)

Lamak

@sp_BlitzErik I mean, I can think of you as "most people" if you want

sp_BlitzErik

the terms of our friendship require that you put me on a pedestal above all others, so that would clearly not work

Lamak

you should have really just find a proper red mushroom power up

sp_BlitzErik

i did.

Lamak

5:22 PM

why did that guy had to ruin that pretty mushroom?

sp_BlitzErik

to hallucinate, probably

Max Vernon

5:33 PM

@PaulWhite Interestingly all that data is sent to Microsoft

Paul White

@MaxVernon Maybe, yes. Not sure of the details. Might depend on some setting.

(assuming you were talking about telemetry)

Max Vernon

yep

sp_BlitzErik

@PaulWhite are there blog posts that you get comment notifications for that give you anxiety?

Transcript for

The Heap™ – Consultancy ©®