i have to increase the size of all my stored procedure's varchar

Question

i have to increase the size of all my stored procedure's varchar

rajemessage 14195

Hall of Fame

Points: 3315
More actions
September 14, 2022 at 5:11 am

#4087393

hi
i have changed my collation to to utf-8 that is Modern_Spanish_100_CI_AI_SC_UTF8 from SQL_Latin1_General_CP1_CI_AS
now all the varchar used in stored procedure need to be doubled ( multiplied by 2) so i have decided to replace all of the varchar in stored procedure with varchar(max) because i can search of varchar(123..) and replace them in one go by varchar(max).
So my concern is, will it create any memory or speed or etc issue.
or is there any other way to update all varchars in storedprocs , view, udfs in one go with dboule the size.
one solution is using the sys.sql_modules definition which is not looking appropriate . pls suggest.

yours sincerly

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Ooh, I would not solve this by going to VARCHAR(MAX). That could cause all sorts of performance problems. If you really have to go through this exercise, the answer is to use NVARCHAR.

HOWEVER

You can't just do this in your procedure. You'll need to do it in the structures AND the procedures.

And no, no way to do this "in one go". This is a major undertaking.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

I'm pretty sure that you want to change to NVARCHAR(), instead. IIRC, UTF-8 is a Unicode encoding.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 3

changing in structure is quite easy , it can be done in stmt so i did not mention.
Instead of varchar(max) if i just double or triple it like varchar(300) then i do not find any problem, please tell me why u have suggested nvarchar.

frederico_fonseca SSCoach Points: 16157 More actions · Answer 4

to use UTF8 in SQL 2019 NVARCHAR is not required (see https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Unicode_Defn) - but increasing the size of the existing varchar MAY be required.

have a look at https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-utf-8-support-for-sql-server/ba-p/734928

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

frederico_fonseca wrote:

to use UTF8 in SQL 2019 NVARCHAR is not required (see https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Unicode_Defn) - but increasing the size of the existing varchar MAY be required.
have a look at https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-utf-8-support-for-sql-server/ba-p/734928

Oooooooo... thank you for that great link, Frederico. I especially found the following interesting...

With UTF-8 encoding, characters in the ASCII range (000000–00007F) require 1 byte, code points 000080–0007FF require 2 bytes, code points 000800–00FFFF require 3 bytes, and code points 0010000–0010FFFF require 4 bytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)