Browse Prior Art Database

Fixed length varchar type for relation database

IP.com Disclosure Number: IPCOM000248681D
Publication Date: 2016-Dec-27
Document File: 1 page(s) / 41K

Publishing Venue

The IP.com Prior Art Database

Abstract

We introduce a new data type:fix-length varchar for relation database,Database will use less storage and improve performance with this new data type. The issue we reslove is overflow issue with traditional varchar data type.When impelement this new data type. we use fix length to store varchar data when the data length is less then some value,so it change to fix length data and we can use all the benifit of the fix length data.For the length which more then the value,it will use tranditional varchar type.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 62% of the total text.

1

Fixed length varchar type for relation database

There are 2 data types to store the character data in relation database. One is Char and the another is Varchar. Char have fix length and good performance

while Varchar performance is not good and could cause potential overflow. But we need Varchar to hold Variable-length data. With Fixed Varchar data type,

we can resolve the performance and overflow issue when held variable-length data Setting a fixed length of space for Varchar. and if it's bigger than this length, we use varchar. If data is smaller than the fixed length, we always use the fixed length of space to save data.Then we can use tech like reorder row format to enhance performance.Create a varchar column like varchar(1000) fixed(30). so when length of data for this column is less than 30, we always use 30 bytes. and treat it like a char column. when it's length is bigger than 30 bytes. we use varchar type. We have a table like below: col1 char(10) , col2 varchar(1000) fixed(30), col3 char(50), col4 varchar(500) fixed(50). it will be saved something like COL1,COL3,OFFSET1,OFFSET2,COL2,COL4. If length of col2 and col4 are smaller than their fixed size, the total row size would be fixed and equal to 140. so whenever we read a row and see it's length is 140 bytes. we can directly get data for col2 and col4 without checking the offset. and it would enhance read/write performance. and avoid overflow for length change under fixed size. If we read a row and see it's lengt...