Database Reference
In-Depth Information
How it works...
SQL Server 2008 R2 provides great flexibility in how data compression is used. Row and page
compression can be configured at the table, index, indexed view, or partition level. The TSQL
statements that are demonstrated in this recipe use PAGE-level and ROW-level compression to
a table. Clustered indexes use the
CREATE
TABLE
statement and to modify the compression
settings use the
ALTER
TABLE
statement.
To obtain the savings on the storage space, the stored procedure
sp_estimate_data_
compression_savings
statement will perform an estimation of the amount of space saved
by compressing a table and its indexes. The main function of this system stored procedure is
to take a sample of the database and then compress the data in the
TEMPDB
database. It is a
resource-intensive operation and in order to obtain the prior information on how best we can
benefit out of this feature, always execute it on the production server while there is less traffic
on the server.
The SQL Server 2008 R2 compression feature has the added advantage of enhancing I/O
performance and extended compression capability for UCS-2 Unicode data, in addition to non-
Unicode data, which is useful to store data in a language that uses large character sets. There
is a suggestive CPU cost to reading and decompressing the compressed data, hence, it is
recommended to test the procedures before deploying the feature in a production environment.
There's more...
However, once the data compression procedures are complete, the space saved is
released to the respective database data files, without releasing the same to the file
system because the file size does not reduce automatically as part of data compression.
In order to release the unused space on the data file or file group, perform the
DBCC
SHRINKFILE
or
SHRINK
DATABASE
operation on the database, unless it is compulsory
as it will negate the database performance.
Designing a storage solution for
unstructured data and new collations
The data stored in the database is structured to maintain transactional consistency and
concurrency. Data that is stored outside the database such as text files, images, documents,
and videos is unstructured data.
Search WWH ::
Custom Search