SQL Server 2008 Data Compression - Feb 27, 2010 at 11:50 AM
by Shuchi Gauri
In SQL Server 2008 Data Compression comes in two flavors: Row Compression,
Page Compression. Explain them
Row Compression is used to optimize space in SQL Server regarding zeros and null
values. SQL Server does not occupy any space on the disk for zeros and null
values due to this feature. First one needs to apply compression on a table and
then apply row compression on it. This reduces the size of the table based on
the number of zeros or null values the table has.
Page Compression feature is used to compress the entire SQL Server page. It is
only applied when the page is full. Page compressions uses dictionary mechanism
to store commonly used data in a column. Eg: if a column has to store abc,
abcd, abcde then it ends up storing 1,1d,1de which saves up to 30% of space.
SQL Server 2008 Data Compression - May 05, 2009 at 22:00 PM by
Rajmeet Ghai
In SQL Server 2008 Data Compression comes in two flavors: Row Compression,
Page Compression. Explain them
SQL allocates 4 bytes for an integer data type irrespective whether the number
is small or large. Row compression is used for a better space allocation of
data. Using row compression, only the required bytes are allocated to store a
value.
Page compression can be either prefix compression or dictionary compression.
Page compression looks for column values with a common beginning compared to
the data page. Example, when the first name is stored in a column, using prefix
compression, the data page is likely to have all first names starting with the
same character.
Dictionary compression is applied after prefix compression. It looks for
repeating column values in the page. The page header has the representative of
the repeated value.
SQL Server 2008 Data Compression - May 18, 2009 at 10:00 AM by
Rajmeet Ghai
What is Data Compression?
Data compression is mainly used to save storage and increase query performance
by minimizing i/o operations.
SQL server 2008 supports two types of compression mechanisms:-
Row level- Allocates storage of the values of columns, based on
the nature of values. This means that the fixed length data types are stored as
variable length. For e.g. Data type Char(50) will occupy 50 characters for
storage irrespective of the size or characters of the value.
Page level – This level of Compression values are stored in a
non repeated from in a page and “referred” to in other pages for any
occurrences. It makes use of Column Prefix and Dictionary Compression. When
Column prefix is used, all values in the page has similar prefixes. For eg.ALL
names like John, Johnson will be stored in the same page.
SQL Server 2008 Data Compression - June 21, 2009 at 09:00 AM by
Amit Satpute
In SQL SERVE 2008 Data Compression comes in two flavors: Row Compression, Page
Compression. Explain them
Row compression changes the format of physical storage of data.
Enabling row compression minimize the metadata associated with each record and
the Numeric data types and fixed length strings are stored in variable-length
storage format like Varchar.
Syntax:
Alter TABLE MyTable REBUILD WITH (DATA_COMPRESSION=Row, MAXDOP=2);
MAXDOP: The maximum number of processors to be used during compression
operation.
Page Compression
Page compression allows common data to be shared between rows for a
given page. Its uses Row compression, Prefix Compression and Dictionary
Compression to compress data:
-
Prefix Compression. For every column in a page duplicate
prefixes are identified, saved in compression information headers and A
reference number is assigned to these prefixes which replaces the prefixes
where ever they occur.
-
Dictionary Compression. Duplicate values are searched through
out the page and stored in CI.
Also read
What is SQL Server 2005 Analysis Services (SSAS)? What are the new features with
SQL Server 2005 Analysis Services (SSAS)? What are SQL Server Analysis Services
cubes? Explain the purpose of synchronization feature provided in Analysis
Services 2005. Explain the new features of SQL Server 2005 Analysis Services
(SSAS)........
What is Untyped XML?
Provide examples for : Create a table with a untype XML column, Insert into an
untyped XML data type column
What is typed XML?
The XML data type comes with five methods. Explain them
Differentiate between Untyped XML and Typed XML.
Explain with an example how to apply defaults constraint to an XML data type
column.
You can add constraints to XML data type columns. Illustrate with an example.
SQL allocates 4 bytes for an integer data type irrespective whether the number
is small or large. Row compression is used for a better space allocation of
data..............
Catalog views can be used to return information like objects, logins permissions
etc used by SQL server database engine...............
Cursors can support various functionalities that are listed
here................
|
