Storing more than 8000 characters in a row
-
Hi ,
I have to create a new database that will have a table with many of coulmns those need to store more than 8K characters. Which datatype can be used for this scenario as there is a limitation of 8K bytes in a row for SQL 2K.
Can anything like BLOBS be used in SQL 2000?
Thanks in advance .
Regards,
Rohit
-
Your only choice is to use BLOBS. These are the text, ntext and image datatypes. For text it is ntext and text. See http://msdn2.microsoft.com/en-us/library/aa174534(SQL.80).aspx
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
For more reasons than I can count, you will likely regret the day that you used Text or NText if there's any chance of ever needing to edit the column.
Creating a sister table for 8k segments of text may be the way to go depending on what you are doing... which, by the way, you didn't state...
What is it that you are trying to actually do?
--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) -
There are certain fields in the database where detail information about an indiual or an organisation is stored. This may cross 10K length.
-
If the data is over 8K, and you want to store it in the db, then text and ntext can work for you. Jeff is right however, that it is a pain if you need to modify their values.
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Its only a pain to do in TSQL. ADO/ADO.Net mask much of the headache. Creating your own paging system just to avoid TEXT feels like reinventing the wheel.
-
Yes, I know that from our company. CV of an author of book, summary of the book, a few pages of text here and there. TEXT is the right datatype for this... unfortunately. It is pretty nasty to work with values stored in text columns.
If the individual column would not exceed 8k, but you need to have several such columns, then I would suggest to split data and store the additional info in additional tables, in VARCHAR columns.
-
There is possibility that multiple columns can contain more than 8K chars. Also we have standard edition. Will that be a constraint?
-
If that is what you must do, then the TEXT datatype is certainly one way to go.
To see if the Standard Edition will create a table with multiple text columns, why don't you just try making one?
CREATE TABLE DropMe (T1 TEXT, T2 TEXT)
DROP TABLE DropMe
--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) -
Once you are using ntext, text and image columns there is no real limit to their combined lengths. Each of them can be about .5GB, 1GB, 1GB respectively. They are not stored together with the data row, but are stored in the database on special BLOB pages. As for the limitations of the standard edition, I do not think that the number of BLOB columns is restricted, but Jeff's answer is the safest
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Thanks for your help. I can create multiple Text columns in a table with Standard Edition and inserted approx. 33K chars. in each column.
-
The limits in SQL Server 2000 are a bit bigger than that (from BOL)...
ntext
Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.
text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
image
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
So, the limits would be 1, 2, and 2 gig respectively.
--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) -
You are right Jeff. This is what I remembered too, but looked up the BOL for the compact edition. However, in both of the cases the important thing is that this limit will allow more than sufficient amount of text to be stored
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply