Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1916

Re: Best Practices for Wide Columns in Tables

Show all headers | View raw

 (rja.carnegie@gmail.com) writes:
> As of SQL Server 2005 (about to be retired, and maybe
> me with it), you can declare varchar(max) which -
> typically? - is stored in a separate page (LOB_DATA) but 
> usually can be treated just like varchar(9) in your SQL 
> statements - likewise nvarchar, varbinary - or formats
> like varchar(999) which are stored either IN_ROW_DATA
> if it'll fit, or ROW_OVERFLOW_DATA if not.

By default, the MAX data types are stored on the data page, as long
as they are < 8000 bytes. There is a table option to change this.
 
> I think there /used/ to be an option to hold short
> values of an unlimited-length column as IN_ROW_DATA -
> maybe that was for [text] and [ntext] types?

For text/ntext/image, the default is to store out of row, but there is
an option to change this as well.
 



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Best Practices for Wide Columns in Tables Michael Cole <noone@invalid.com> - 2015-04-30 19:46 +1000
  Re: Best Practices for Wide Columns in Tables Erland Sommarskog <esquel@sommarskog.se> - 2015-04-30 12:29 +0000
    Re: Best Practices for Wide Columns in Tables rja.carnegie@gmail.com - 2015-05-06 19:46 -0700
      Re: Best Practices for Wide Columns in Tables Erland Sommarskog <esquel@sommarskog.se> - 2015-05-07 21:29 +0200

csiph-web