Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I read -

total fixed and variable length data are still limited to 8019 bytes total

But my data can possibly be more than this. What happens if it's more. Does it still store the data correctly?

Also can someone tell me how I can store for example over 4000 bytes of text data in a column. Is that possible?

share|improve this question

migrated from stackoverflow.com Jun 4 '13 at 15:43

This question came from our site for professional and enthusiast programmers.

    
Thanks. I will use that next time. I didn't even know it existed. –  Gemma Jun 4 '13 at 10:20
    
@Gemma you must have read about data types and their sizes.. –  raman Jun 4 '13 at 10:21

4 Answers 4

up vote 2 down vote accepted

Data exceeding the total of 8060 bytes will be pushed over to an 'overflow page', increasing the amount of pages required to be read from the buffer pool.

share|improve this answer

From Row-Overflow Data Exceeding 8 KB :

A table can contain a maximum of 8,060 bytes per row. In SQL Server 2008, this restriction is relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. The length of each one of these columns must still fall within the limit of 8,000 bytes; however, their combined widths can exceed the 8,060-byte limit

That means it's really hard to hit the 8kb limit. You could script it:

if object_id('YourTable') is not null
    drop table YourTable
go
create table YourTable (id int identity);
declare @i int = 1
declare @sql varchar(max)
while @i < 800
    begin
    set @sql =  'alter table YourTable add col' + cast(@i as varchar(24)) + 
        ' decimal(38);'
    exec (@sql)
    set @i = @i + 1
    end 

This will show you the error message:

Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'YourTable' failed because the minimum row size would be 8076, including 65 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

share|improve this answer

for a blob of text use varchar(max)

if you're storing XML use XML

There are BLOBs for binary data

These are often stored off-table (automatically) for various performance reasons.

So yet you can, but nevertheless, you might be doing something a bit wrong. If you share what you're trying to do, we can be more specific.

share|improve this answer
    
So is a varchar(max) always stored off-table? If I want to store in table what is the maximum I can store? –  Gemma Jun 4 '13 at 10:24
1  
Why does it matter? A varchar(max) allows you to store 2gb. The fact it's off table isn't necessarily relevant but if you explain why your so concerned about it I might be able to give a better answer –  LoztInSpace Jun 4 '13 at 10:40

You can use varchar(max), nvarchar(max) datatypes to store such data

share|improve this answer
    
So can I for example do varchar(7000)? What happens with my row width if I do three of these? –  Gemma Jun 4 '13 at 10:20
    
That doesn't answer the question as to what happens when the length of a row exceeds what can be stored on a single page. –  Martin Smith Jun 4 '13 at 10:21
    
@a_horse_with_no_name - But off row storage will still be needed with three varchar(7000) columns if they are all full. –  Martin Smith Jun 4 '13 at 10:23
    
So if I need to store 7000 bytes is that okay with varchar(max)? thanks –  Gemma Jun 4 '13 at 10:23
    
yeah you can use varchar(max). –  AnandPhadke Jun 4 '13 at 10:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.