cancel
Showing results for 
Search instead for 
Did you mean: 

issue with text datatype storage

suznCB
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (0)