Row size limits
-
Is this correct that a row can onlt contain upto 8000 bytes ??
Isn't this row size too small.
For instance, I want to add product description to my Products table. The description is about 3000 characters.
But as I add the description to the table, I get error that I have exceeded the row size.
So my question is : How do people add lengthy data to database tables.
-
Use the TEXT or NTEXT datatype. In SQL2005 use VARCHAR(MAX) or NVARCHAR(MAX).
This gives you 2GB of text.
In SQL2000 the text itself is not stored in the row, only the pointer to that text.
You can use sp_tableoption to force some of the description to be stored within the table. For example the majority of your descriptions may be under 300 characters long so you want to store the first 300 characters in your table to improve retrieval performance.
-
I have designed my table using :
ShortDescription Nvarchar (1000)
LongDescription Nvarchar (3000)
I can change my table structure if YOU gurantee that I will not get into size restrictions
So if I do use Text, what would be the row size limit then?
(I wonder why I did not use Text in the first place)
Kind regards
-
The row size limit comes into play if the DATA exceeds 8096 bytes not if the STRUCUTRE exceeds 8096 bytes.
I seem to recall that for TEXT and NTEXT only 16 bytes are used so you will need one hell of a lot of them to breach the 8096 limit.
The downside to TEXT/NTEXT is that updating them requires the use to text pointers rather than the simple syntax used for standard columns. It is nothing too onerous just a bit fiddly.
-
just my 2ct
nvarchar(..) is double byte so nvarchar(1000) takes max 2000 bytes + length indicator.
making it ntext will allow you to store up to 2G for that column.
Downsides are :
- not indexable
- no order by without conversion
So I'd first consider to get rid of the nvarchar !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I agree with alzdba, unless you plan to describe your products in chinese, japanese or other language requiring extensive character sets, you can get rid of Nvarchar in both columns and use VARCHAR, which cuts the space required for the same text to one half. Varchar can handle descriptions in european languages, even in those that are using various special characters (like scandinavian, baltic or slavic languages). 1000 varchar for short description and 3000 varchar for long description still leaves enough room for other columns, while nvarchar with the same length will cause problems as soon as you really want to exploit the full length.
-
I have found that NVARCHAR, NCHAR, and NTEXT are rarely useful even in multi-lingual applications.
The Welsh use CHRW(333) but will tollerate the standard ASCII equivalent.
The Indic languages Urdu, Bengali, Punjabi, Gujerati do not have standard fonts available for them so the discussion of which characters should represent them is academic.
If you need Chinese script then there is a case for the unicode characters. I don't know anything about Japanese.
-
Japanese is unicode as well, so its best stored in nvarchar/nchar/ntext fields.
I would store a product description in multiple rows. If you created a table just for descriptions, you could store both your long and short descriptions there, and use as many rows as required to store the full length of the description. This also keeps your Products table row size down, potentially increasing speed for most other searches.
Julian Kuiters
juliankuiters.id.au
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply