Maximum lenght of varchar(max)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Maximum lenght of varchar(max)

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-03 : 16:07:33

Hello,
am working with a billing system that passes a csv value to a function for processing.

In the old application using MS SQL 2000 we passed a maximum of lenght 8,000 characters. That was with varchar(8000)

Now with varchar(max), whats the maximum recommended values to be used with the "MAX" Factor ?

AS we have millions of records to process in batches

Thanks

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-04 : 02:58:17

2,147,483,648 bytes

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-04 : 05:30:28

So is it ok to work with batches of 10,000(s) or batches of 100,000(s) or in the millions.

Basically what we do is run a batch as

select top 100000 @s = isnull(@s + ',','') + Receipient
from  history

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-04 : 05:56:31

I think you should use the table datatype as parameter, or XML instead.
Always try to use normalized or structured data when possible.

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-04 : 05:58:19

The smaller the batches, the better the performance.

If you run a loop of batches of
5,000
10,000
20,000
30,000
100,000

in Management studio and test the execution time and performance. The smaller batches execute faster, better and utilize less memory

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-04 : 05:58:56

tnx

_____________________

Yes O !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources