The Heap™ – Consultancy ©®

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

Vérace

06:38

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…

Charlieface

12:24

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

ypercubeᵀᴹ

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

Paul White

12:51

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

Charlieface

13:26

@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

Paul White

13:55

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

Erik Darling

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

Paul White

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

Erik Darling

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

Paul White

Yep

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

Erik Darling

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

Paul White

14:06

One of my favourite examples of type sensitivity is:

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

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

Erik Darling

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

Paul White

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

Erik Darling

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

oh boy

i'm making quite a mess

Paul White

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

Charlieface

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

Paul White

14:17

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

Erik Darling

there is a lot of typing in that answer

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

Paul White

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

Erik Darling

... he said no?

Paul White

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

Erik Darling

i'll get an invoice ready

Paul White

14:32

about time

Erik Darling

might need a max type for this one

Paul White

😂

Erik Darling

14:44

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

Paul White

well, you asked if people wanted extra stuff for free

what result were you expecting

Erik Darling

"meh"

i guess i should have had that as an option

but i thought "nah" was close enough

Paul White

you do get four slots

that said, I voted "yes" because why not

Erik Darling