issue with text datatype storage

[suznCB]

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_name	size	reserved	unused
tT1	15824 KB	16672 KB	848 KB

rowtotal	reserved	data	index_size	unused
659	17352 KB	424 KB	15864 KB	1040 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

[Abhishek_Panda]

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. 

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.