Pt2520 Unit 3 Datatype Analysis

Datatype Description smallint 1 byte is the minimum storage needed int Uses only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte bigint Uses only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte decimal This storage is exactly same as the vardecimal storage format datetime Uses the integer data representation by using two 4-byte integers. The integer value represents the number of days with base date of 1/1/1900. The first 2 bytes can represent up to the year 2079. Compression can always save 2 bytes here until that point. datetime2 Uses the integer data representation by using 6 to 9 bytes. The first 4 bytes represent the date. The bytes taken by the time will depend on the precision of the time that is specified.

char

Note that the Database Engine inserts the same padding character regardless of the collation that is used. binary Trailing zeroes are removed.

Table 4. Some of the MS SQL Datatypes Affected By Compression Some of the datatype that don’t yield any row level compression benefit are tinyint, smalldatetime, date, time, varchar, text, nvarchar,xml.

MS SQL Server Page Compression can be applied to tables, table partitions, indexes and index partitions. This compression technique can be viewed as an enhanced version of Dictionary Encoding discussed in [4]. The following two figures illustrate the effects of page compression on an uncompressed