1

When I use SQL Server Text or Varchar(max) datatype in a column it creates databases that are too large : for example 1000 rows in one table containing varchar(max) use around 1000 Mb (1Gb) !!!!

Is that normal ?

thanks for help

Edit my text fields are about 5 to 50 kb MAX

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
riadh gomri
  • 869
  • 1
  • 15
  • 21
  • 2
    Are you sure it is `varchar` and not `char`? – juergen d Mar 23 '12 at 10:53
  • 1
    It depends on how much is in each record. If each one contains on average 1MB of data, then 1000 of them will make 1GB. – John N Mar 23 '12 at 10:54
  • I think you are asking if 4 explanation points in a row is normal... it is not. (also, use varchar and not char or text) – Hogan Mar 23 '12 at 11:08
  • 1
    I tried varchar(max) and text. The text fields are about 4 to 50 ko only. what's wrong ? – riadh gomri Mar 23 '12 at 11:14
  • 1
    You may be using a badly written DB library or program, which may be padding your varchar(max) fields with spaces. Check that the data you are inserting is not padded with spaces. – Ben Mar 23 '12 at 12:11
  • The DBA site may be a better place for this question. – JeffO Mar 23 '12 at 13:04
  • @juergen There is not chat(max) data type – Diego Mar 23 '12 at 13:33

3 Answers3

1

it depends on what's inside your table. If you have 1000 rows with 1mb of data, you will have 1000 Mb

Try running:

select datalength(yourField) from yourTable

the datalength function will give you how many bytes are occupied by the column

Santiago Alessandri
  • 6,630
  • 30
  • 46
Diego
  • 34,802
  • 21
  • 91
  • 134
  • 1
    Thanks this really helped me, i am trying to check record by recod to check if really the seize i see is the real seize of data i send – riadh gomri Mar 23 '12 at 13:59
  • Check this link for more references: http://msdn.microsoft.com/en-us/library/ms173486.aspx if my answer helepd you, remember to vote and mark as answer – Diego Mar 23 '12 at 14:04
0

Run sys.sp_spaceused @objname = N'YourTable'; to see the space used by your table.

If there is no unused space, then your data is really taking up that space

If there is a lot of unused space, you could try creating a new filegroup and moving the text/varchar(max) columns to this new file group, and shrinking the database DBCC SHRINKDB

After that your primary filegroup will take up less space, and you can shrink the "offending" file using DBCC SHRINKFILE, whenever problem arises.

Creating a new filegroup can be done from SSMS, in the database properties, files page. You can generate the sql script from there to see what's going on.

Moving text columns to a different filegroup can be made by using the TEXTIMAGE_ON { filegroup| "default" } in CREATE TABLE

Sorry: no alter table possible, no individual column to different filegroup, but all "big" columns at once. So you have to do it by hand or show your table in a new database diagram (in SSMS studio) and change the property ... sorry I don't know it's name, something like "Text/Image filegroup" (I suppose, I've got Spanish SSMS), but it's there when you see the table properties in the database diagram designer.

Advantages: less fragmentation on the main table, faster table/clustered index sacans and possiblity to shrink only the file you're interested in: the with the problematic "big" columns.

JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • Yes the table is taking up that place. What do you mean by filegroup ? Can you explain what to do ? Thanks for help – riadh gomri Mar 23 '12 at 13:28
0

varchar(max) is different with varchr(n);

a varchar(max) cell can contain 2 GByte data.

but for varchar(n): the max number of n is 8000, means max 8 KByte data.

but, any way, I don't know what kind of data you stored in your database.

so,in short, possibly normal.

Scott 混合理论
  • 2,263
  • 8
  • 34
  • 59