The Heap™ – Consultancy ©® - 2022-10-02

« first day (4289 days earlier) ← previous day next day → last day (579 days later) »

6:38 AM

A chairde - Morning all!

@mustaccio Except when, perhaps, they're not? Or when politicians become involved?

Indiana Pi Bill

The Indiana Pi Bill is the popular name for bill #246 of the 1897 sitting of the Indiana General Assembly, one of the most notorious attempts to establish mathematical truth by legislative fiat. Despite its name, the main result claimed by the bill is a method to square the circle, although it does imply various incorrect values of the mathematical constant π, the ratio of the circumference of a circle to its diameter. The bill, written by a physician who was an amateur mathematician, never became law due to the intervention of Professor C. A. Waldo of Purdue University, who happened to be present...

6 hours later…

12:24 PM

Note to self: always use CONCAT instead of + if big varchar values are involved: for some unfathomable reason, varchar(8000) concatted to nvarchar(max) is truncated to nvarchar(4000) before being concatted. Whereas CONCAT does it correctly. dbfiddle.uk/mBJnTEX5

@Vérace snopes.com/fact-check/alabamas-slice-of-pi

12:51 PM

@Charlieface Such things can be expected when being sloppy with data types, relying on implicit conversion, and data precedence rules to avoid an error. In your example, nvarchar has a higher precedence than varchar, resulting in an implicit conversion of @z to nvarchar(4000) with truncation.

As can be expected with a newer function, CONCAT does have generally better behaviour (fewer surprises) than the older + with all its attendant backwards compatibility, but it's not surprise-free.

The one that's been annoying me most recently is the error from STRING_AGG when the aggregated string is not a LOB type, and the result exceeds 8000 bytes.

These issues are not new in principle though.

The CONCAT documentation has:

> If none of the input arguments has a supported large object (LOB) type, then the return type truncates to 8000 characters in length, regardless of the return type. This truncation preserves space and supports plan generation efficiency.

I have no idea what "preserves space" is intended to mean there.

But LOBs are fundamentally different from non-LOBs and require much additional internal machinery. That's what "plan generation efficiency" is getting at.

If I had my way, T-SQL would be a lot more strongly typed than it is.

Anyway, a better note to self would be: Always carefully consider data types.

1:26 PM

@PaulWhite I think it was more unexpected that it first converts to nvarchar(4000) then converts to nvarchar(max) when you'd expect it to do it in one step. Same happens with a literal of more than 4000 chars dbfiddle.uk/Ktk4u1wd whereas when you add N'' then it works fine dbfiddle.uk/bIovLwX1

1:55 PM

@Charlieface Yes, I think the point you're missing is when the implicit conversions happen, and why

The operator + requires arguments of the same type, so there's an implicit conversion before that can happen. There might be another implicit conversion of the result to the target variable's type.

the number of times this has bitten me with dynamic sql is terrible

i ran into something with pressuredetector recently where correctly N prefixed strings still ended up truncating

I'm so careful about N prefixes and whatnot these days. The extra typing and explicit conversions avoid a lot of potentially painful debugging.

People think my code is ridiculous of course

CASE expressions are another example

i've seen some clever people build up a varchar variable and then assign it to an nvarchar variable before executing

Yep

I'm not really defending T-SQL btw, it's almost as if it's designed to catch you out at times

case expressions when building strings are almost always a mistake

which leads to begin/end blocks

there was another one i had to deal with where building the string with case expressions worked in 2016 but ended up truncating in 2019

2:06 PM

One of my favourite examples of type sensitivity is:

29

Q: Why does SQL Server require the datatype length to be the same when using UNPIVOT?

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same? Let's say that I have the following sample data that I need to u...

sql-server unpivot

@ErikDarling It's so tricky sometimes, even if you know every part of the expression has to evaluate to the same type. It's not always obvious, or easy to determine, what that type is, and why.

i'm trying to be more mindful about casting all + concatenation as max but going back and fixing past uses is tough

i wrote about it a while back here

Converting to LOB is way more typing that it should be (pun not intended)

it would be nice to do +N(MAX)'stuff and things or some such

oh boy

i'm making quite a mess

yes, something like that would be an improvement over what we have today

@PaulWhite Which is why you would think (given the inherent differences between LOB and non-LOB) that varchar(n) + nvarchar(max) would cause the varchar to be converted directly to nvarchar(max).

2:17 PM

@Charlieface See my answer to the question above to avoid me retyping a lot of stuff

there is a lot of typing in that answer

the typing to code and picture ratio is quite out of character

Of course, people would complain about poor performance if SQL Server was always 'safe' about potentially-LOB strings

@ErikDarling That's quite neat. SQL Server needs better literal typing in general

I tried arguing that with Dr. No at one stage

It went how you'd expect

... he said no?

he did

could not comprehend why people didn't want to type CAST or CONVERT all the time

@ErikDarling That's oddly similar to Mr C. Face's example

i'll get an invoice ready

2:32 PM

about time

might need a max type for this one

😂

2:44 PM

jeez, just went and looked at the pressuredetector poll

i didn't know people were still so hyped about tempdb

temptdb

tempehdb

tampadb

timpanidb

tentdb

tensedb

trentdb

whatever

this game sucks

well, you asked if people wanted extra stuff for free

what result were you expecting

"meh"

i guess i should have had that as an option

but i thought "nah" was close enough

you do get four slots

that said, I voted "yes" because why not

3:06 PM

Gonna be fun figuring out what to show for that

3:31 PM

Morning

3:55 PM

Morning Z

2 hours later…

6:08 PM

Weekend went according to plan. It was probably last weekend with a nice weather this week and yet the amusement park was almost empty. No queues anywhere

2 hours later…

7:59 PM

🌕 🦇

the Bat signal? I'm on my way

1 hour later…

9:09 PM

@Zikato But how were the stacks? 🙃

3 hours later…

11:42 PM

Morning

« first day (4289 days earlier) ← previous day next day → last day (579 days later) »

Transcript for

Oct '222

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