|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Size of column of datatype ImageI 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? All conversion is done in the client application and the datatype is set according to the datatype of the value. All this work fine. 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? Sample of the data: ID DATATYPE VALUE ---- - ------------------------ 1 2 15-02-1987 14:03:05 2 1 This is test data only 3 3 1544 BR Jonas Jonas Mandahl Pedersen (jo***@jmp.dk) writes:
> I have a table in my database where I store key / value pairs for a + two bytes for the length of the varchar. Plus some overhead bytes.> 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 If you don't set the option "text in row", there are 16 bits in the> 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? 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, esq***@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 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 Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98095185C834Yazorman@127.0.0.1... > Jonas Mandahl Pedersen (jo***@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, esq***@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 Hi Jonas
If you're actually calculating space used down to the byte, you also need to take into account that there are additional overhead bytes on each row. The text/image pointer is considered a variable length column, so you do not lose the 2 bytes. There is always a performance difference when you have to access additional pages beside the regular table data. If you have to do multiple reads of IMAGE pages for each row of data, the extra reads can add up very fast. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Jonas Mandahl Pedersen" <jo***@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" <esq***@sommarskog.se> wrote in message > news:Xns98095185C834Yazorman@127.0.0.1... >> Jonas Mandahl Pedersen (jo***@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, esq***@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. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98095185C834Yazorman@127.0.0.1... > Jonas Mandahl Pedersen (jo***@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, esq***@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 Kalen Delaney (replies@public_newsgroups.com) writes:
> The pointer to the IMAGE data is 16 bytes, not 16 bits. Oops! I did mean bytes, honestly! Must be a problem with my keyboard!> There is not always a minimum of 8000 bytes per row. Smaller amounts of Thanks for the correction, Kalen.> 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. -- Erland Sommarskog, SQL Server MVP, esq***@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  |
|||||||||||||||||||||||
Other interesting topics