Once again I've run into a limitation of SQL Server that really threw me off. SQL Server's Row Byte size is limited somewhere right around 8k bytes for physical data stored in the database. This means if you have a few large varchar or nvarchar fields it's actually quite easy to outrun the size of a row. This doesn't apply to Text or Image fields which are stored separately, but it's still very frustrating.
Especially since I recently decided to ditch Text fields in favor of rather larger varchar fields because they are easier to manage for searching and generally perform better. Unfortunately I found out just today that it's quite likely that I'll overrun the row limit on several occasions. It seems ironic that a varchar field can be 8000 bytes long which would eat up just about all of the full row size in one chunk...
So, anybody have good guidelines on when to use Text fields vs. varchars? I came to the decision to change my text fields after reading up on varchars more closely and just liking the idea of not having to do crazy, unoptimized conversion gyrations to run queries into against the Text fields originally.
This is yet another instance where I'm surprised by some of the inflexibility that a SQL Backend provides. This limit seems pretty superficial based on some engine behavior that probably dates back to the Sybase days...
Live an learn. I'm off reconfiguring the Database one more time...
Other Posts you might also like