Varchar(max)

Question

Varchar(max)

hennie7863

SSCommitted

Points: 1653
More actions
December 16, 2009 at 8:14 am

#218741

Hi,
I'm working on a project in which we are building starmodels. In some dimensions i'm using varchar fields. We limited them with some length. We had an discussion about the length. We are reading from XML messages and sometimes it's not clear what the length of a XML field is. So ithought why not create varchar(MAX) fields for every varchar field. A little investigation told me that the length of a varchar field is limited by its used space and not reserved space, when the length exceeds 8060 characters an off data page jump is needed, and an update can give fragmentation.
Are there more disadvantages of using varchar(max)?
Greetz,
Hennie de Nooijer

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

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

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

If I'm not mistaken, I believe VARCHAR(MAX) is actually equivalent to the TEXT field type. My understanding is that Microsoft will be phasing out TEXT and replace it with VARCHAR(MAX).

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

No real disadvantage for using the max.

Text/ntext/image are deprecated and will be removed in the next version or two.

hemin.shah85 Mr or Mrs. 500 Points: 557 More actions · Answer 3

Yes, you are right. It could lead to page jumps, but that is an obvious thing. When data page is full it has to allocate new data page for the data.

Infact, SQL Server has inbuilt intelligence esp. for VARCHAR(MAX), regarding pagination and pointer management.

So, I guess if that is your requirement, you should go ahead; there shouldn;t be any problem.

GSquared SSC Guru Points: 260824 More actions · Answer 4

I have to ask: Why not use the XML data type? Why varchar for XML data?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

hennie7863 SSCommitted Points: 1653 More actions · Answer 5

Thnx for your answers. Your answers are confirming what i was thinking. I wasn't quite clear about XML and varchar. We extract specific fields out of XML messages and put in some dimension tables. Sometimes a restriction is defined in the XSD, sometimes not. That's why we had this discussion. There is also a request for future changes in the lengths for some fields.