February 20, 2006 at 6:44 am
I require a large string (XML greater than 8000 chars) to be stored in a table. I can have one text column in a table and store the string in it using insert/updatetext or have multiple text columns and store first 8000 chars in text1 and second 8000 chars in text2 etc. The reason for the second choice is I would not need to use updatetext which I beleive is a logged operation and is performance bottleneck. Suggestion on the best approach would be highly appreciated.
Thanks
February 21, 2006 at 2:46 am
Hi Naren
I'd suggest either using CLOBs (character large objects) or reading up about "select for XML" which may or may not be appropriate for your needs.
All the best
February 21, 2006 at 4:58 am
If you are thinking about making columns of datatype char or varchar(8000), but need more space than the 8000 width table restriction, try using a text or ntext datatype. Text datatypes can be over 2 billion bytes, should be able to hold your XML.
Like this:
CREATE TABLE my_xml_table (xml_col text null)
INSERT INTO my_xml_table VALUES(' 2 billion xml goes here')
Hope this helps,
Muaddib
February 21, 2006 at 7:27 am
Storing XML in text/ntext columns works like a champ. You can also use INSERT and UPDATE statements in stored procs just fine to update these columns. I do it all the time...
February 21, 2006 at 7:38 am
Sorry, my reply was a bit lazy. Text field is a CLOB.
In SQL Server 2005 there is an XML data type but I suppose that's not going to help you (but useful to know for when you make the inevitable change)
Regards
February 21, 2006 at 7:43 am
Thanks.
I should have been more specfic with the question.
option 1: I can have a table as follows
create table t1 (i int, t1 text) and put the XML string > 8000 chars in t1 using insert and updatetext. To get data from the table I can use
select substring(t1, 1,8000), substring(t1, 8001, 8000) etc and concatenate on the front end or use readtext which does not work in my case due to front end restrictions.
option 2: I can have the table defined as
create table(i int, t1 text, t2 text) and put first 8000 chars in t1, next 8000 chars in t2. This would avoid me from using updatetext which I beleive is an expensive logged operation (I may be wrong on this and please correct me). To get the data from the table I would then use
select t1, t2 ..
I know the second method does not seem like a good design but want to know your opinion. Also note I am using SQL server 2000 and so cannot use Xml data type or varchar(max) data type.
Thanks
February 22, 2006 at 2:05 am
create table t1 (i int, t1 text) and put the XML string > 8000 chars in t1 using insert and updatetext. To get data from the table I can use
select substring(t1, 1,8000), substring(t1, 8001, 8000) etc and concatenate on the front end or use readtext which does not work in my case due to front end restrictions.
option 2: I can have the table defined as
create table(i int, t1 text, t2 text) and put first 8000 chars in t1, next 8000 chars in t2. This would avoid me from using updatetext which I beleive is an expensive logged operation (I may be wrong on this and please correct me). To get the data from the table I would then use
select t1, t2 ..
I know the second method does not seem like a good design but want to know your opinion. Also note I am using SQL server 2000 and so cannot use Xml data type or varchar(max) data type.
Instead u can use ntext where it will allow to pass 2GB of data. Rather than passing 8000 each time using ntext.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply