cancel
Showing results for 
Search instead for 
Did you mean: 

issue with text datatype storage

suznCB
Participant
0 Kudos
198

Dears, kindly help in this issue,

recently I noted that our database has some tables with columns of text datatype, but some of those tables has 0 rows, so I am wondering if I change datatype for those columns to varchar, so:

1- to what length can I set varchar (in our database Character Set = 2, cp850)

2- what is the benefits can I get by this changing?

for one of those tables let's say table T1, which has  a TEXT column, 

I run:

sp_spaceused T1,1

I got the result:

index_namesizereservedunused
tT115824 KB16672 KB848 KB
rowtotalreserveddataindex_size unused
65917352 KB424 KB15864 KB1040 KB

How index size has this value? I supposed that text datatype reserved a data page size foe each row and  @@maxpagesize in ours is 8 kb, so I multiplied 8 * 659 but I got a different value from the index size in the table above, also I checked  the default @@texttype in ours, and it is 32 kb, even if I multiple 32 * 659 I'll  get a different value,

could you please explain how index size is calculated?

kindly note that table has 659 rows but the text column is empty,

so, I run select datalength for that column I get 1 byte for each row?

still wondering how the index has 15864 KB value?

Regards

View Entire Topic
Abhishek_Panda
Discoverer
0 Kudos

Hello,

First thing, 'tT1' is not an index, although it displays under index_name column. This tT1 is rather a storage type for attachments, images, media, cluster column, etc. particularly supporting large data sets. I've attached a screenshot to help understand better about this storage type and indexes. If you see, in my case, tBBPCONT stores a whopping 500 GB dataset. 

 

Sybase table.PNG

When you fired sp_spaceused T1,1 this prints all related storage that T1 is associated with along with its actual storage which in your case is only 424KB, plus 15MB which in this case is its additional attachment/image table. 

So, to answer your query,

1- to what length can I set varchar (in our database Character Set = 2, cp850) -> You shouldn't. Not recommended as the actual data contained in such storage are not limited to characters/text and most likely would include different types of medias/attachments. 

2- what is the benefits can I get by this changing? -> No benefits. Rather, there is high risk of data loss.