The Heap™ – Consultancy ©® - 2017-03-31 (page 1 of 2)

« first day (2278 days earlier) ← previous day next day → last day (2573 days later) »

12:38 AM

@McNets mmmm tequila

2 hours later…

2:12 AM

@JoeObbish - after an admittedly cursory look at the plan xml, it looks like the coalesce is the cause of the problem. I know this is not particularly helpful since you've already identified that as a problematic item. It looks like that is causing the loop join between the derived table and the X_LAST_TABLE to estimate a maximum of 94025 rows for each row in the derived table. This matches your hypothesis of the join acting like a cross join.

Why removing the link table fixes the issue is unknown to me. I'm certain Paul will provide the answer since this question is exactly the kind of thing he excels at, as you know!

2:26 AM

@JoeObbish I've had problems with Coalesce against different LEFT-JOINed tables, too. These are things I've done that have helped, some, in various (perhaps not identical, but similar) situations.

1. Make each successive LEFT JOIN after the first one add a condition WHERE PreviousTable.Value IS NULL

2. Don't compose the final value of the Coalesce from "left-to-right" but from "top-to-bottom" using some form of aggregation, ala Min() or SELECT TOP 1 FROM A UNION B UNION C

3. Strategic use of CROSS APPLY seems to alleviate situations like this sometimes

And just a wild idea, an indexed view that UNIONs the three tables could do wonders potentially. (Is that possible? I am ambivalent about this and don't have time to look it up. My instinct says indexed views can't use UNION but I can't remember.)

3:04 AM

anyone ever read SQUARE

> NAME, SAL EMP DEPT, MGR ('TOY', 'ANDERSON')

vs

> SELECT NAME, SAL
FROM EMP
WHERE DEPT = 'TOY'
AND MGR = 'ANDERSON'

4:03 AM

thanks for looking at it guys

one important point is that this is as simple of an example as I could get that still had the CE issue

so the query as written doesn't really make sense

and the goal is to understand the poor estimate instead of working around it

Master Database

4:23 AM

0

Q: How 'Off-Topic' can someone go

I was just checking my answer/reply history and noticed this became Closed as 'Off Topic'. Tag reads "this could be because your code has a typo, basic error, or is not relevant to most of our audience" In the case of this question that doesn't happens. If you can understand what the user is ask...

discussion verbosity rhetoric

3 hours later…

6:56 AM

good morning

bon dia

Bon día @AndriyM

доброго ранку

@McNets :)

7:17 AM

Morning all

> Additionally, if you offer multiple bounties on the same question, the minimum spend doubles with each subsequent bounty (50 reputation on the first bounty, 100 reputation on the second, 200 on the third, and so on).

Would that mean that if I start out at 500 I can't add an additional bounty?

Or for example when starting at 250 only 1 additional bounty can be added, but when starting at 100 3 additional bounties can be added?

s/additional// ^^ calculus is off, I need coffee

7:52 AM

@TomV No. I've awarded multiple 500s on the same question. example

I guess I'll be losing some reputation tomorrow then :)

@TomV How so?

Please, Can someone explain me what is the desired result here:

0

Q: SQL query to compare time difference

I've used the code below to query and got the output shown. Now, I would like to query as describe below. How should I do it? Find code 2 of an ItemID, check if code 1 comes after code 2. If yes, compare the time difference. If time difference is less than 10 seconds, display the two compared...

sql-server-2012

@PaulWhite I would really like an answer to my last question, and it's eligible for a bounty tomorrow

@TomV Can I bounty it for you? Remind me if I forget.

8:10 AM

@PaulWhite Sure you could, but I'm not sure that's the purpose of the bounty system (i.e. begging a high rep user to bounty for you) :)

@TomV You didn't ask. I offered :)

ok, I think I get it.

> ...if your first bounty was worth 50 reputation, your second bounty on the same question will have to be for at least 100, your third for at least 200 and so on. If you've already offered a bounty for more than 250, you can still offer more bounties for 500 (the maximum amount) as long as you like (or as long as you have the rep). This doubling applies only to bounties by the same user on the same question.

From How does the bounty system work? in the SE FAQ.

@PaulWhite Thanks that's clearer

8:35 AM

0

A: How 'Off-Topic' can someone go

I think it was considered too localized because the problem was a missing join condition. The scope of the site states dba.se is for those needing expert answers to advanced database-related questions [...] Doing a Cartesian product instead of an inner join is hardly a topic that I woul...

@dezso "or one that an experienced person commits when he/she is tired enough" that is nice, +1 from me

ConcernedOfTunbridgeWells

9:01 AM

Morning all

Morning

8

Morning

9:19 AM

'

m

what I learned today: MongoDB is a database

don't forget - databases include little more than MySQL or PostgreSQL, for example MongoDB is also database and it not support foreign keys, and inside relation databases realisations could be different, so developer could decide for some reasons move this logic outside of database and use database with most universal set of commands - SELECT, UPDATE, INSERT. I not tell - it good or bad, but this is often practice. — a_vlad 2 hours ago

Why did I get 3 stars for that morning message?

3

you wanted 4? ;)

Bah.

who said 4?? I see 5

9:25 AM

I give up :)

Is this correct? dbfiddle.uk/…

According to this question on SO: stackoverflow.com/a/43134897/3270427

@ypercubeᵀᴹ DELETE is not universal?

@McNets I guess he omitted that by mistake. DELETE is in all SQL products I know.

Although both UPDATE and DELETE have different syntax in the variosu products when you move from a single table to joining multiple ones.

@PaulWhite you were possibly mistaken for someone else

9:40 AM

I guess.

Master Database

10:12 AM

0

Q: Merge pgAdmin version tags?

Tags pgadmin-3 and pgadmin-4 were created a while ago. pgadmin-1.18 also exists. Is there any need for these, or should they be made synonyms of pgadmin and merged? Related meta Q & A: PgAdmin Tags should be redone

feature-request tagging retag-request

10:26 AM

@PaulWhite I just thought it was funny you said "morning" this time, we all know it's not morning where you are, and you sometimes jokingly say "evening" when somebody says "morning" but didn't this time. That triggered me to star your message, and apparently some people followed suit. Your asking "why" just added to the fun so now you have 6 stars

--JEAGL

One of the best ever.

10:45 AM

0

A: Merge pgAdmin version tags?

Well, pgadmin-1.18 is definitely useless (it refers to a specific pgAdmin III version). At the same time, pgAdmin III and pgAdmin 4 are two very different products. If the question is tagged rightly with pgadmin, it completely makes sense to indicate which one it is about. That the users don't...

Maybe not the tag's fault but we shouldn't keep useless tags simply because they're innocent and blameless :) — Paul White ♦ 29 secs ago

But thanks for the feedback. That's what I'm after.

@PaulWhite I don't think pgadmin-4 is useless. It's only a few months the versiion has been released.

@ypercubeᵀᴹ I'll tell you what prompted this (aside from the other meta question on the subject). I noticed earlier than an existing question had it's pgadmin replaced with pgadmin-4.

don't even have usage guidance Don't they? ;) — dezso 33 secs ago

@PaulWhite by whom I wonder ;)

10:53 AM

@dezso I get notified for every answer and comment there already.

@PaulWhite have you ever heard about shameless self plugs?

@ypercubeᵀᴹ Yeah well. Anyway, I could have just added pgadmin back, so there were both, but then I thought: hey! why is this an issue at all?

@dezso Nope. Completely new thing to me ;)

@PaulWhite TYL

@dezso I still don't see any usage guidance. Should I blame caching?

I don't see anything either. For pgadmin-3 or 4.

10:56 AM

Ah. Pending approval. @ypercubeᵀᴹ @dezso

ha, got 4 unicorn points

11:19 AM

I guess I'm struggling to see the advantages of a version hierarchy there.

What's the difference with the several SQL Server version tags?

If the differences between versions are big enough, why not

I'm not familiar enough with PGAdmin, but I could imagine someone with only experience in version 4 to monitor that tag instead of the generic one

@TomV Scale.

There are only 9 people monitoring the pgadmin tags at all.

Thousands for sql-server and hundreds each for the version tags.

Fair point, but as I said I'm not familiar enough with the tool

If you have some time could you review my cardinality answer to see if it makes sense? I would appreciate feedback

11:35 AM

@TomV Link?

0

A: SQL Server cardinality hint

There is no way to inject a cardinality estimation directly into the optimizer but depending on what you want to achieve there are a few options. You could use an OPTION (FAST N) hint to introduce row goals, and possibly rewrite your query using CTE's or subqueries to inject your row goals in di...

@PaulWhite yes but the vast majority of questions with pgadmin are also tagged with postgresql. I follow postgresql, so I hadn't bothered to tag with pagdmin

11:52 AM

Ok. But I'm not sure that changes the discussion at all. What's the point of a tag no one uses?

If there's only a single pgadmin tag, you have to read the question to check the version. Ok.

If there are pgadmin, pgadmin-3, and pgadmin-4, you have to check all three, potentially.

Some questions will be tagged pgadmin, others pgadmin and pgadmin-4, others pgadmin-4.

What's been gained?

Potentially, all questions about pgadmin-4 will have the pgadmin tag as well. So if I want to follow all pgadmin questions, I follow the pgadmin tag.
If I want only the pgadmin-4 question, the pgadmin-4 tag.

And I trust my fellow dba.se users to retag appropriately the ill-tagged questions ;)

So the cost is maintaining a new version hierarchy and applying it consistently. What's the corresponding gain?

Also pgadmin + pgadmin-x = 40% of the maximum number of tags.

And I don't even feel strongly about this. I'm just opposing the "why not?" argument.

I don't know. Do we gain anything from having sql-server and sql-server-2008, etc tags?
Aren't there questions that fall through the gaps and are not tagged right?

@ypercubeᵀᴹ Yes there are. But we do spend time and effort fixing that because it is worthwhile.

Saying undesirable 'x' happens elsewhere is not an argument for doing more 'x'.

I don't feel to ostrong about it either. But it doesn't seem like it needs big time and effort to maintain the pgadmin tags.

compared to sql-server or mysql ones

12:03 PM

4 mins ago, by Paul White

So the cost is maintaining a new version hierarchy and applying it consistently. What's the corresponding gain?

If the answer is there is no gain, that's fine. I just want people to understand they're supporting something that adds nothing, but costs a little bit of someone's time.

And they're only tags. I get that.

good afternoon everybody. Weekend is nearing in leaps and bounds.

user image

@TomV I added the TOP thing because FAST hints can only be used at the top level (not in a CTE or subquery/derived table). If that's not what you meant, feel free to edit/rollback as always.

Grammar Translator by Colin Daley. Converts e.g. Transact-SQL syntax BNF to Oracle-style railroad diagrams.

user image

12:49 PM

@PaulWhite No that's what I meant, I struggled with the wording

Thanks

weird experience: I was cleaning the bottom of my mouse, and incidentally got into the Trash room

And a nice addition on the connect item, thanks again

@dezso What was the room topic?

1:04 PM

> Jack Douglas: room topic changed to Trash: DELETE trash FROM [The Heap] (no tags)

that was the last message

@TomV nw

@dezso Oh, I didn't realise that Trash room's topic also applied to the room you found at the bottom of your house.

@AndriyM when I take my wife's glasses and drink a lot, I can also mis-see mouse for house

@hot2use cool image

@MaxVernon ...one of Philippe Halsman’s photographs from his ‘Jump’ collection ... Cut and Chic Vintage

1:17 PM

so cool. thanks for the link

1:27 PM

You're welcome.

@TomV In case you wanted more feedback, it might be helpful to clarify that TOP can only be used to lower a cardinality estimate and not to increase it

@dezso Ha, how embarrassing! I only now saw my mistake.

Wow

ConcernedOfTunbridgeWells

Seen on the interwebs today ...

user image

1:46 PM

Halsman's found that by taking people staged portraits, they always had their "face" on. When he asked them to jump, they'd let their guard down and you'd see the real them

I think this is the article I heard on him npr.org/sections/pictureshow/2010/04/26/126289722/jump

2:10 PM

@ConcernedOfTunbridgeWells there must be some serious background to it

@ypercubeᵀᴹ - you around?

@ConcernedOfTunbridgeWells That sign seems like more of a way to give people ideas than to stop anything.

it is actually a fake

photoshop, I mean

but sounds legit nevertheless :D

related story: opposingviews.com/i/money/jobs-and-careers/…

@ConcernedOfTunbridgeWells thisisagoodsign.wordpress.com/2014/09/03/…

@MaxVernon y

hey guys

if you have any time, can you have a look at this question, please? I ask my intern to see what he can do to improve the perf dba.stackexchange.com/questions/168776/…

2:24 PM

:36414385 ssshhhhhhhhhhhhhh

How do you report a username? dba.stackexchange.com/users/30342/spack Not really appropriate

Is that some kind of slur? I don't recognize it

2:40 PM

@Philᵀᴹ mods can edit a username

Noun: spack (plural spacks)

(Britain slang, pejorative) A clumsy, foolish, or mentally deficient person.
You spilt beer on your shirt, you spack!
For usage examples of this term, see Citations:spack.

Adjective: spack (comparative spacker, superlative am spacksten)

(regional, Northern Germany, usually of people) thin, scrawny (having an unusually low amount of both muscle and fat)
(regional, Northern Germany, of wood) dry, brittle

Although there's also a tool called spack for package management

it was once a 'popular' contraction for "spastic" where I come from

don't hear it much any more though

@Philᵀᴹ I don't think anyone outside of UK will find it inappropriate.

It looks innocent to me, really.

I had no idea about that meaning

Looks like it's not uncomomn as a surname: ancestry.co.uk/name-origin?surname=spack

2:50 PM

@JackDouglas Indeed

3:07 PM

@MaxVernon Thanks for the answer. I probably won't mark it as accepted because what I really want to know is why the bad CE is happening. It's still a quality answer though

Is that final join more or less a cross join with some predicate filtering in the join operator? I don't like the look of that spool

@JoeObbish we were testing different alternatives. Do you see some change when using ISNULL(ISNULL(... instead of that COALESCE?

@JoeObbish I don't expect it to be accepted. It's really just an attempt to learn. Thanks!

Interestingly, in 2008R2, the original query goes parallel. Also, in v.Next, the CE estimates 1 row will be returned.

@Lamak Your query with ISNULL(ISNULL(...)) instead of the COALESCE(X,Y,Z) is certainly far superior in my book. Of course without testing it on actual data it's pretty hard to say for sure.

it's a very interesting question

I'll second that!

and I'm waiting for all the answers

3:21 PM

I'll try to find the actual data. it may have been too long

@Lamak This is very surprising to me, but ISNULL makes a big difference. plan looks good

yeah, it surprised me too

I don't really ever use ISNULL. I would think it would get transformed to CASE but it doesn't

@JoeObbish yeah, in my case, I almost never use COEALESCE

didn't think it would change the plan so much though

was just more something like "well, let's try this just for kicks"

For me the issue is I often write code for SQL server and oracle

so I use things that work in both platforms by default

Max's rewrite adds a performance problem where there wasn't one

I think it's the final join

will check

@JoeObbish yeah, that's an issue

3:36 PM

I don't see an easy rewrite

a nested loop join will be forced and it'll be pushed to the join operator

it's effectively a cross join

seems like it

I'm not over the ISNULL thing. makes me uncomfortable

@JoeObbish that appears to be my specialty when it comes to tough questions like yours.

@JoeObbish that was my real intention

anyway, I've got the real data in front of me. happy to run more queries if you guys are curious about anything

too be honest I was just being cheeky when I brought up the question. didn't expect an answer

3:41 PM

How bad is my query?

I'm surprised it's been sitting there since December without any real attempt at an answer.

@MaxVernon It puts 87385 rows into a table spool and does a full scan of that table spool 539141 times. So 47112836285 rows would be processed by the join to produce 8 rows at the end :)

I WIN.

@EvanCarroll cs.ru.nl/~gerp/IS0/dictaat/…

Is Lamak's version prettier?

@MaxVernon His version finishes in 191 ms and avoids the impossibly huge cardinality estimate issue

The original query finishes in 329 ms, probably because it doesn't run in parallel

with TF 8649 the original finishes in 159 ms

To give more context, this was part of a bigger view for which I wanted to get reasonable cardinality estimates

so the performance wasn't that important as long as it wasn't terrible

3:48 PM

On a slightly different note, is the derived table needed? Is the plan shape better with it than without it?

How did you solve it originally?

@JoeObbish ISNULL is less opaque to CE than CASE.

My version is what I refer to as performance optimization in reverse.

POIR for short

@MaxVernon I told the developer that he wasn't allowed to make the view

Good one

3:53 PM

@AndriyM No difference. I think the derived table is there because that's left over from how I reduced the query to a MCVE

@paulwhite am I at all right about anything in my answer?

@MaxVernon Don't know. I haven't had time to look. I'll get to it, just not soon.

@MaxVernon Sure!

"The new query benefits(?) from parallelization"

since we need to process 47 billion rows

the query does benefit from parallelism ;)

@paulw thanks

@JoeObbish ROTFLMAO

Paul's comment about ISNULL isn't surprising since COALESCE() gets transformed and ISNULL doesn't

I'm trying to think of a reasonable way to distill this for developers...

"use ISNULL instead of COALESCE" sounds so ridiculous

3:57 PM

Yeah ISNULL is intrinsic.

@JoeObbish I see. I guessed there wouldn't be a difference, from how the plans looked in Max's answer, but wasn't sure. Thanks for confirming it.

There's also different typing behaviour, which can be important.

Note

Use COALESCE (Transact-SQL) to return the first non-null value.

from BOL

Thanks MS...

(that's in the ISNULL article)

Nice

@JoeObbish Well that's odd. I wonder why that's there.

4:01 PM

to give work for consultants

I guess I'm thinking about this wrong

now I have a new optimization trick

that's a good thing

It wasn't in the 2008 or 2005 version of that page.

I can only imagine some text like "...when you have 3 or more values" is missing.

@PaulWhite Because it would be annoying to write that with ISNULL? Or some other reason?

@JoeObbish Yeah just to avoid nesting. It just seems more like the sort of thing that would make some sort of sense.

The other nice thing about ISNULL that can be important is that it simplifies away if the expression is known not to be nullable. COALESCE can't do that, because it is implemented with the generic CASE.

I need to look at it more but my attempt at a rule would be "use COALESCE if you sometimes need to return NULL or if you need to do funny business with data types. otherwise use ISNULL"

@JoeObbish ...if you sometimes need to return NULL??

4:10 PM

Funny business with data types tends to happen with funny types of business data.

Which is all business data.

2

@PaulWhite well for COALESCE(A, B, C) you can get NULL if all of them are NULL

@JoeObbish SELECT ISNULL(NULL, NULL);

yeah I was just rereading the docs

for some reason I thought NULL wasn't appropriate for the second parameter

my new attempt at a rule would be "use COALESCE if you need the expression to be considered NULLable or if you need to do funny business with data types. otherwise use ISNULL"

How much does readability factor into your rules? Heavily nested ISNULLs are less intuitive.

4:16 PM

@Forrest It factors in if the two options are equivalent in performance

@Forrest Well that's subjective.

Has anyone mentioned that ISNULL isn't Standard SQL yet? ;)

I kind of did. That's why it wasn't in my toolkit

@PaulWhite Mind blown

@JoeObbish You only write Standard SQL at your place? Tough gig.

Next you'll tell me CROSS APPLY isn't either

4:19 PM

Heh.

Uh oh.

@PaulWhite Not that bad. In some places the code needs to work for both SQL Server and Oracle

In other places no such restriction

APPLY was added to Oracle in 12c

that's all I know

I'd like to see LITERAL JOIN in some product.

Just for clarity.

I want FIGURATIVE JOIN

That would be a logical next step.

But common usage would conflate the two and eventually render the distinction useless.

Literally useless.

4:24 PM

It's funny thinking of the concept of semantic drift applying to computer languages

We should meme that here.

In a thousand years, when people have uploaded themselves to machines, the useless professor stereotype won't be the person who can read Old English, but ANSI-92.

2

stackoverflow.blog/2017/03/30/…

I expected that to onebox.

no onebox for you

4:43 PM

New Zeland dance? Like the football men? I wanna see this.

4:53 PM

@JoeObbish I like your answer +1

ConcernedOfTunbridgeWells

@billinkc On the subject of Spack ...

Kylie Mole - The Comedy Company

@McNets Sorry, I just look like a rugby player.

5:18 PM

@TomV Thanks, I liked yours as well

5:32 PM

@MaxVernon IMO an important point was lost in your answer. Because JOIN_ID is the primary key of X_LAST_TABLE, it shouldn't matter how much obfuscation I do in the definition of dm.PRIMARY_ID

for each row in dm the join to X_LAST_TABLE must return either 0 rows or 1 row

I think that it's fair to say that ISNULL could be more accurate, but not fair to say that CASE should result in this kind of behavior

the number of rows returned by the query must be between 0 and 481577 due to the constraint on the table

SQL Server is inconsistent with itself here. It estimates 481577 rows for the outer table and one row per iteration for the inner loop

481577 * 1 = 4528030000, apparently

There's no general guarantee of consistency among cardinality estimates.

They can be computed on different, logically-equivalent subtrees at different times.

I thought that only applied to overall join order

You're saying that sometimes the result of a join won't be outer rows * inner rows per loop?

I agree, @Joe that the calculation should be easy, since that column is a primary key. However, nothing surprises me with "estimates" in SQL Server. They are, after all, called that for a reason.

@MaxVernon But people get confused by the fact that the estimate for the inner part of a nested loop is per loop

I thought that it was defined that way

yes, true. That part is very easy to overlook.

5:47 PM

but with this we're saying that it's meaningless

It's funny. I thought that as I learned more about SQL Server I would be able to glean more information from query plans. In reality it feels like as I learn more there's actually less useful information in query plans...

@JoeObbish I hear that. The one thing I know is that I seem to know less and less every day.

@JoeObbish It is possible, yes. Any part of the tree, down to a single node, can be substituted and a new estimate derived. When the lowest cost parts are stitched together, the result can be "inconsistent" when viewed from the highest level.

even though I'm pretty sure I'm learning stuff at a pretty good clip.

The more you know, the less you're certain of.

Well, it sounds like that's the answer

thanks for setting me straight

In theory is it possible to trace what's happening using some of the optimizer deep dive trace flags?

5:52 PM

@JoeObbish I'm not trying to blow your mind here. All the things you know are still useful, it's just that there's always more going on. An execution plan that exposed all possible details and lineages would be unfeasibly large and hard to work with, not to mention resource intensive to generate.

@JoeObbish More or less, yes. It is time-consuming, though.

One reason I haven't even looked at the stats-copy db for that quesiton.

It's worse when using the old CE.

Thanks for the weekend plans ;)

The old CE is mind-bogglingly complex.

Like more than the most complex thing ever.

In some cases, understanding it comes down to executing the assembly line by line and following the changes in registers and memory locations.

@PaulWhite At one point I was going to ask our MS contacts if there was anything out there that we could get under NDA that would help with some of this stuff

since some of the newer TFs don't work for the legacy CE

apparently their answer would have been to link me to some videos about query optimization

so I declined to make the request...

Yeah there's no 2363 etc.

that we know of!

I tried firing up a debugger once but I broke sql server

5:57 PM

It's not there. I've looked.

oh

There's almost certainly debug stuff in non-public builds, but they don't release those, even under NDA.

Source code access is the only way really :)

this also amounts to yet another argument to switch from the legacy CE to the new CE

@PaulWhite another q with pgadmin replaced with pgadmin-4: dba.stackexchange.com/posts/168402/revisions

@JoeObbish Yes I'm afraid it's all but inevitable. I am fond of old CE, but it's getting no love.

user image

6:02 PM

hahaha

An aggregate that produces more rows than its input ^

is that with legit stats?

Yes. It illustrates what I said earlier about things being stitched together.

It's from sqlperformance.com/2014/01/sql-plan/…

@PaulWhite without cheating I suppose (GROUPING_SETS)

No cheating. The source query is:

SELECT
    SOH.CustomerID,
    SUM(OrderQty)
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD
    ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE
    SOD.ProductID BETWEEN 711 AND 718
GROUP BY
    SOH.CustomerID;

It's a tough thing to write about, so it just features as a sort of aside in that article.

Replacing part of a tree with an indexed view is easier for people to grasp than some abstract idea of alternatives that only exist in the memo for a short time.

6:07 PM

@ypercubeᵀᴹ GROUPING SETS queries usually result in a table spool or the input being calculated multiple times

probably not a hard rule though

also you weren't successful. mind = blown

In class I talk about writing the same logical query a dozen different ways in SQL and why it is unrealistic to expect the final cardinality estimate to be the same in all cases.

@JoeObbish Oh. Sorry about that then.

Unless you enjoy that sort of thing. In which case: you're welcome.

@PaulWhite Well sure, that's not controversial at all (at least to me)

and yes, it was in a good way

Sweet as. Ok well I need some sleep. Catch you all later/earlier.

take it easy

thanks as always, @Paulw it was entertaining and I learned a load again.

I wanna know so badly who downvoted your question, @Joe. Strange, that's all I can say.

6:15 PM

@MaxVernon Me. Auto downvote for any post that contains a certain trace flag.

@PaulWhite ahhh. Without an explanation?

and that was rhetorical, btw

It's fully automatic. Nothing I can do. The number goes away, so does the DV :)

@PaulWhite like, as in, actually automatic?

the trace flag that shall not be named has been removed

@MaxVernon As far as you know.

I had a flag to process. Really going to zzz now. Bye!

6:20 PM

lol. nice. Sleep well, my friend!

3 hours later…

9:06 PM

@JoeObbish I don't think it always casts to float

11

A: Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

It seems that despite the implication in BOL that the left hand operand will be implicitly cast to float that this is not the case. The output of POWER() is cast to the type of the left hand operand, which is DECIMAL if you use 10.0. Using an explicit float works fine. SELECT POWER(1e1, 38); SEL...

Your answer says "So, whatever you pass as the first parameter is going to be implicitly cast to a float before the function is executed"

Yes, I think that is wrong

but I don't know what is right

because it seems to be casting in the OPs case

@JackDouglas I have a half-baked theory

dbfiddle.uk/…

perhaps it casts the final result as a float

@JoeObbish and then back again to the type of the first operand?

no, I don't think so

I'm not sure

like I said it's half-baked

documentation does seem to be wrong

hold on

I can't repro this

SELECT POWER(2.,64.) returns the correct result for me

9:16 PM

@JoeObbish ho ho

dbfiddle.uk/…

dbfiddle.uk/…

fixed in 2016 :)

ok

well that settles it enough for me

good work

thanks for your expert assistance — I didn't know what I was doing

9:51 PM

Results are the same between testing all of the above, including work arounds, on Microsoft SQL Server 2014 (SP1) 12.0 and Microsoft SQL Azure (RTM) - 12.0. I think the Azure database is the equivalent of 2016 or newer. — Triynko 17 mins ago

Anyone know if that's correct? ^^^^

@JackDouglas depends what they mean.

Azure lacks several features of the core (Standard, Enterprise) editions

But I remember an official ms blog post (posted here) that said that they are trying and applying new features faster in Azure than in other editions.

@ypercubeᵀᴹ is it the same code base but just lacking certain features?

I'm thinking that question needs re-tagging

« first day (2278 days earlier) ← previous day next day → last day (2573 days later) »

Transcript for

Mar '1731

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile