Re: How are unlimited fields stored?
Date: Thu, 24 Oct 2002 16:23:30 GMT
Message-ID: <68Vt9.1190$1r1.36136_at_newsc.telia.net>
Different products probably do this differently. As for MS SQL Server:
Max length for ordinary varchar and nvarchar is 8000 bytes. The variable length data is held at the end of the row structure. After the fixed length portion comes a null map and then a map specifying at which position each variable length column starts. A row can grow so it no longer fits the page. If that row is a part of an index, you have a page split. If it isn't, then the row is moved to some other row and a forward pointer is created from the old page (so that the non-clustered indexes doesn't have to be updated due to the new page address).
To store more data, one has to use the text, ntext and image datatypes. for these, the binary data is stored off-page (with a pointer on the page pointing to this structure). Up to 6.5, the data was quite simply a linked list of pages. As of 7.0 this is a b-tree (not at the page level, but inside the pages - one page can hold blob data for several rows). As of SQL Server 2000, one can specify "text in row" meaning that the blob data *can* be at the page together with the ordinary data, up to a certain size.
-- Tibor Karaszi "stu" <smcgouga_at_nospam.co.uk> wrote in message news:ap8aai$qb2$1$8302bc10_at_news.demon.co.uk...Received on Thu Oct 24 2002 - 18:23:30 CEST
> So to stop fragmentation when updating varchar(xx) fields the dbms must
> store the varchar(xx) fields in a separate structure from the constant
> length fields?
>
> cheers
> Stu
>
>
> "Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message
> news:6dae7e65.0210230817.664570b9_at_posting.google.com...
> > "stu" <smcgouga_at_nospam.co.uk> wrote in message
> news:<ap5mue$s7m$1$8300dec7_at_news.demon.co.uk>...
> > > How are large unlimited fields stored? I think im right when i say if
> you
> > > have a TEXT(30) and only use 3 chars the dbms will physically layout
30
> char
> > > space before the start of the next record. This is fine but how does
> the
> > > dbms handle memo fields that can be huge?
> > >
> > > Cheers
> > > Stu
> >
> > I seem to remember that text and memo are part of access (which I dont
> > have). Anyhow, normally there are text fields that allow varying
> > length as well. For example varchar(xx). The principle is that the
> > length of the data is kept together
> > with the data. Thus the db nows how long the data actually is. The
> > overhead is of course the extra bytes that is needed to remember the
> > size.
> >
> >
> > HTH
> > /Lennart
>
>