Saving Text datatypes

• e.bides

  SSC Rookie

  Points: 26

  More actions

  May 20, 2009 at 10:50 pm

  #209692

  I checked Books online to see how Text Datatype is saved but nothing was mentioned regarding that. We all know that char datatype is saved with a fixed length. Which means if the char has been declared as having 10 bytes... no matter how short that char variable contains, it will always be saved as having 10 bytes (sql server pads the empty spaces to enable it to reach 10 bytes).

  Does it do the same for Text datatype?? Any info much appreciated.

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  May 21, 2009 at 8:27 am

  #998300

  I'm no expert on this stuff by any means, but BOL says...

  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.

  So, if you do the math, 2,147,483,647 bytes is around 2 gigabytes of data. What if you had one table with one million records, each having some data in a text field? If the maximum space were allocated for each record, regardless of the actual length of the data, that would be almost 2,000 terabytes for one table. I don't even know what is after that, flops, or something like that. In any event, I suspect the space allocated to a text field is less than the maximum possible.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  May 21, 2009 at 9:58 am

  #998370

  to add to what Greg said, SQL server keeps a pointer for each TEXT field;

  SQL server will try to store each fields data stuffed into a TEXT datatype in a varchar(8000)

  if it is small enough, otherwise it gets farmed out to the disk;

  either way, the pointer points to wherever that field in the row exists...in a varchar field or on disk within the database.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• e.bides

  SSC Rookie

  Points: 26

  More actions

  May 21, 2009 at 4:02 pm

  #998599

  Yeah i guess that's right... actually right after posting i thought that sql server shouldnt be allocating 2gb worth of data for each record otherwise capacity would be compromised very quickly...

  great! thanks guys!!