Why You Should Always Use Unicode For Dynamic SQL

[@RomanPeralta]

Long videos are like second breakfast-there's always more than you expect!

[@ErikDarlingData]

I hope this is a good thing. Second breakfast sounds like first nap.

[@jiridolezalcz]

Useful, many thanks. I wonder if there is any possibility for automation or code-smell-checking? Developer is a human beeing not fully concentrated for all of the details that should be met everytime.

[@ErikDarlingData]

I haven't seen any good ones publicly available, no.

[@DBNinj4]

Anyone complaining about long videos has obviously never spent hours troubleshooting code, only to find that the code is fine but the datatypes act differently under different collations.

[@ErikDarlingData]

HAHAHA, you know, that's a really funny point.

[@mattcargile]

I want your datalength!

[@ErikDarlingData]

That could get expensive.

[@ntobergta]

It’s about how rounded it is

[@ErikDarlingData]

heyyyy wait a minute

[@jackiecooper9439]

When making a dynamic SQL string using multiple NVARCHAR(MAX) variables why does it sometimes not take more than 4K characters? Did any1 else face this issue here? On stack overflow I read it's due to 8K page size. But it happens only on some complex dynamic queries and I am not able to recreate it in simple scenarios.

[@ErikDarlingData]

Well, the SO explanation is wrong. Leave that out of your brain. I think you're confusing what can be put into a string vs what can be printed from a string. PRINT statements have an 8000 byte (not character) maximum, so printing out unicode (nvarchar) strings with standard ASCII characters can only display the first 4000 (each character being two bytes instead of one). If you use print and substring, you can get deeper into longer strings. You can run into situations where using + to concatenate strings that don't have an explicitly declared length can suffer from implicit conversions from max to shorter byte lengths. That's why to be safe I end up explicitly casting shorter strings that I'm concatenating in to nvarchar max.

[@jackiecooper9439]

@@ErikDarlingData Thanks for replying. The LEN itself is 4k charcters. Eg: I have two NVARCHAR (MAX) variables @Sql1 and @Sql2 each are individually built using multiple sting concatenations using '+'. The final SQL string to be executed is built as follows: Declare @FinalSql NVARCHAR(max) Set @FinalSql = @Sql1 + @Sql2 Now the LEN( @Sql1) is 4k. So the query has lost all the characters after 4k characters, thus giving me an apt syntax error. I use PRINT CAST (@FinalSql as NTEXT) to print everything and @Sql1 portion is indeed cut off at precisely 4k characters.

[@jackiecooper9439]

@@ErikDarlingData So I guess the culprit is concatenations using '+'. Currently I'm tricking by; Set @SQL1 = @Sql1 /*contains the string almost upto 4k characters*/ + -- rest of the concatenations. Now the @Sql1 is correctly formed.

[@ErikDarlingData]

@@jackiecooper9439 well, I'm not entirely sure based on your description and solution what the problem is, but like I said before, tacking on shorter strings can often result in strange truncation issues. An alternative way to make sure the contents are fully displayed would be something like: SELECT ( SELECT [processing-instruction(_)] = @sql1 FOR XML PATH(''), TYPE );

[@clerincg]

Don't shorten your videos too much or I'll have to shorten my lunch break accordingly 😁 Erik, are you ever concerned the Unicode strings take twice as much storage as non-Unicode? If you will never (well, never say never, right??) have Unicode characters, is it worth the storage overhead?

[@ErikDarlingData]

No, I honestly don't care about that. It falls into the category of 32 bit problems, like index fragmentation, page splits, and other goofy memes from the bad old days.

[@TZH474]

Is that “live Elvis entertainment “?

[@ErikDarlingData]

You'd have to ask our AI overlords.

[@douglascoats7081]

I dont have an issue with your length. You are long enough lol seriously though who is actually complaining about 15 minute videos?

[@ErikDarlingData]

It's the internet people complain about everything