SQL CE, minimize the database size!

by Mark Deraeve 12. October 2007 09:30

Before you start working on a SQL CE application, you should realize that there is more and thorough analysing needed. Because size is almost always a problem, we have to understand the importance of correctly settings up the database.What you should keep in mind is the following:

* bigint Integer (whole number) data from -2^63 (- 9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The storage size is 8 bytes.

* integer Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). The storage size is 4 bytes. 

* smallint Integer data from –32,768 to 32,767. The storage size is 2 bytes.

* tinyint Integer data from 0 to 255. The storage size is 1 byte.

* bit Integer data with either a 1 or 0 value.

* numeric (p, s) Fixed-precision and scale-numeric data from -10^38 +1 through 10^38 ?1. The p specifies precision and can vary between 1 and 38. The s specifies scale and can vary between 0 and p. Numeric always uses 19 bytes, regardless of its precision and scale. 

* money Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with an accuracy to a ten-thousandth of a monetary unit. The storage size is 8 bytes.

* float Floating point number data from -1.79E + 308 through 1.79E + 308 The storage size is 8 bytes.

* real Floating precision number data from -3.40E + 38 through 3.40E + 38.

* datetime Date and time data from January 1, 1753 to December 31, 9999 with an accuracy of one three-hundredth of one second or 3.33 milliseconds. Values are rounded to increments of .000, .003 or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date (January 1, 1900). The base date is the system's reference date. Values for datetime that are earlier than January 1, 1753 are not permitted. The other 4 bytes store the time of day as the number of milliseconds after midnight. Seconds have a valid range from 0 through 59.  

* national character(n) Synonym: nchar(n) Fixed-length Unicode data with a maximum length of 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* national character varying(n) Synonym: nvarchar(n) Variable-length Unicode data with a length of 1 to 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered.

* ntext Variable-length Unicode data with a maximum length of (2^30 - 2) / 2 (536,870,911) characters. The storage size, in bytes, is two times the number of characters entered.

* binary(n) Fixed-length binary data with a maximum length of 510 bytes. The default length is 1.

* varbinary(n) Variable-length binary data with a maximum length of 510 bytes. The default length is 1.

* image Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes.

* uniqueidentifier A GUID. Storage size is 16 bytes.

* IDENTITY [(s, i)] This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified, and the column cannot be updated. s (seed) = starting value i (increment) = increment value. 

* ROWGUIDCOL This is a property of a data column, not a distinct data type. This is a column in a table that is defined by using the uniqueidentifier data type. A table can only have one ROWGUIDCOL column.  

So if you add a column creationdate, but it is not needed by the system nor for reporting. Then you should consider deleting it. Keep in mind that for one record it is 4 bytes, but for 1000000 records it needs 4000000bytes or 3.8 mb.

In an application we used Netpad from psion teklogix with a fixed memory of 40 mb. Only 5 Mb is calculated for the DB. We don’t store million of records, but we do store ten thousands of records and a leaving out a datetime in a table could spare us 0.38 mb.

In case of synchronizing with an other DB, be sure to write all data to the mother DB and do not keep unnecessary data on the netpad.

When you estimate the size of a table or of a database, consider the following information:


Each row in the table has an overhead of 6 bytes.


Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.


Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.


Zero-length string columns occupy 1 byte in the row.

I understand that with the uprizing flash memory, capacities will grow larger, but so will systems become more complex and in need of more storage, therefore it always will be a good practise to try to minimize the size of the databases.

I’ll write more on this subject in a coming post. I’m currently optimizing a SQL Ce database.

source: http://support.microsoft.com/kb/827968


About the author

I started as a VB6 developer, programming all kinds of applications. I also got involved in the development of a e-market in ASP.

When .Net was introduced, I had the privilege to start in a team of professionals and develop a framework. I learned a lot and started to work as a c#.Net consultant from then on.

After 2 years I also got the chance to work for several months with SharePoint. SharePoint kept chasing me all the years after and when the product got more and more mature I started to love it. Then SharePoint was a daily occupation for me. I implemented a large quality document management system at PMRL being the only developer/technical analyst. Working close together with the QA department we succeeded in making and improving a very good Quality document management system. Its used up until this day.

I worked for 6 years as a .Net consultant for 2 of the largest consultancy companies in Belgium. One of my last projects with the consultancy was creating a custom LIMS system in c#.Net. This was a success and the customer asked me to stay and help to expand and maintain this LIMS. I had great years in this company. There was a focus on innovation and a lot of opportunities for improving and developing new IS systems. Apart from several small projects I further more improved the custom LIMS system, created a SOP library based on SharePoint and a web based Skill matrix system to handle the skills of the employees in the company. 

Then I was involved in transferring the LIMS to a new lab in Singapore. This was done in a short time frame. The deployment was again a success and it passed the GLP inspection tests. I learned a lot from the Quality engineers about validation of computerized systems.

At my current job I have a wide range of challenges. Mainly I'm still working with .Net (VB.net and C#.Net). But outside of the Microsoft world I also do some ABAB programming in our SAP system. Like developing Adobe Forms and the program behind it. Or making small changes to customized code.

I'm working for 2 years with objective C now. Creating apps for factories across Europe. People in the factory are working with IPad to take pictures and upload them to orders and more. Already more and more projects are coming my way. The iOS apps are supported by a home made .Net architecture that exposes WCF services. Its working great. My second app is almost ready for deployment in factories. Now I started an App for the management team so they can manage projects on their IPAD and IPHONE. Next generation apps are build with a .Net Web API background combined with EF 6.0.

Soon I will start a new challenge at BDO. In preparation, I started to look into AngularJS. And I'm liking it. It's very fast to use, lots of documentation and works like a charm. Let's see if I'll still be as enthusiastic in a few months. I almost finished my first AngularJS web site. 

My linkedIn!

Month List

Tag cloud

Locations of visitors to this page