> I have a table in my database where I store key / value pairs for a
> dynamic form designer.
> It has the definition:
>
> ID INT,
> DATATYPE TINYINT,
> VALUE Varchar(250)
>
> ( I asume each row takes 4+1+length(value) bytes?

> Now one of my customers also would like to add dynamic pictures to their
> forms.
>
> So a simple solution is to change the structure to:
>
> ID INT,
> DATATYPE TINYINT,
> VALUE IMAGE
>
> How much will each record take for the new structure?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98095185C834Yazorman@127.0.0.1...
> Jonas Mandahl Pedersen (jonas@jmp.dk) writes:
>> I have a table in my database where I store key / value pairs for a
>> dynamic form designer.
>> It has the definition:
>>
>> ID INT,
>> DATATYPE TINYINT,
>> VALUE Varchar(250)
>>
>> ( I asume each row takes 4+1+length(value) bytes?
>
> + two bytes for the length of the varchar. Plus some overhead bytes.
>
>> Now one of my customers also would like to add dynamic pictures to their
>> forms.
>>
>> So a simple solution is to change the structure to:
>>
>> ID INT,
>> DATATYPE TINYINT,
>> VALUE IMAGE
>>
>> How much will each record take for the new structure?
>
> If you don't set the option "text in row", there are 16 bits in the
> row for a pointer to where the image data resides. The image takes
> up as many page as needed, so there is a minimum of 8000 bytes.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98095185C834Yazorman@127.0.0.1...
> Jonas Mandahl Pedersen (jonas@jmp.dk) writes:
>> I have a table in my database where I store key / value pairs for a
>> dynamic form designer.
....
>>
>> So a simple solution is to change the structure to:
>>
>> ID INT,
>> DATATYPE TINYINT,
>> VALUE IMAGE
>>
>> How much will each record take for the new structure?
>
> If you don't set the option "text in row", there are 16 bits in the
> row for a pointer to where the image data resides. The image takes
> up as many page as needed, so there is a minimum of 8000 bytes.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Jonas Mandahl Pedersen" <jonas@jmp.dk> wrote in message
news:O9fAYtjrGHA.2148@TK2MSFTNGP03.phx.gbl...
> Will this mean that the new structure will take 16(pointer to where the
> image data resides) - 2 (length of varchar) = 14 bytes more than the old
> structure?
>
> Do you think there iwll be any performance difference?
>
> Thanks
> Jonas
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns98095185C834Yazorman@127.0.0.1...
>> Jonas Mandahl Pedersen (jonas@jmp.dk) writes:
>>> I have a table in my database where I store key / value pairs for a
>>> dynamic form designer.
>>> It has the definition:
>>>
>>> ID INT,
>>> DATATYPE TINYINT,
>>> VALUE Varchar(250)
>>>
>>> ( I asume each row takes 4+1+length(value) bytes?
>>
>> + two bytes for the length of the varchar. Plus some overhead bytes.
>>
>>> Now one of my customers also would like to add dynamic pictures to their
>>> forms.
>>>
>>> So a simple solution is to change the structure to:
>>>
>>> ID INT,
>>> DATATYPE TINYINT,
>>> VALUE IMAGE
>>>
>>> How much will each record take for the new structure?
>>
>> If you don't set the option "text in row", there are 16 bits in the
>> row for a pointer to where the image data resides. The image takes
>> up as many page as needed, so there is a minimum of 8000 bytes.
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

> The pointer to the IMAGE data is 16 bytes, not 16 bits.

> There is not always a minimum of 8000 bytes per row. Smaller amounts of
> data can share the same pages, so you could have 5 rows, each with IMAGE
> data, and they only need one or two extra pages.