Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 249

How it works...
The options on the partitions may look familiar as they correspond directly to the options on
the page in the index. SQL Server optimizer can process the join faster because the partitions
can be joined themselves based on the filegroup placement on each physical drive. The best
solution in terms of performance is to strip the data files of the partitions across more than
one disk by setting up an appropriate RAID. The data is sorted by the partition and can access
each partition at the same time, irrespective of single or multiple filegroups.
Depending on the configuration, hardware-based RAID generally provides good performance.
Hardware-based RAID also makes it much easier to manage multiple disks. This lets you treat
an array of disks as one disk. You might even be able to replace a failed drive without shutting
down the system. A disadvantage of a hardware-based solution is cost. You might also be
dependent upon one vendor.
Implementing sparse columns and wide
table features
Using SQL Server 2008, a new space saving storage option is available-which is referred
to as sparse columns-that can provide optimized and efficient storage for columns that
contain predominant NULL values. Querying and manipulation of sparse columns is similar
to regular columns by using standard INSERT / UPDATE / DELETE statements. The sparse
columns are normal columns in a table that have an optimized storage format by reducing
the space requirements.
In this recipe, we will look at how to implement sparse columns and wide table features.
The sparse columns are useful for the applications such as Windows SharePoint
Services that will help to manage the efficient way to store and access a large number
of user-defined properties.
Getting ready
The database compatibility must be 100 or 105 to implement sparse column features. The
table columns can be any structured data type and the following restrictions are applicable to
implement sparse column features:
F A sparse column must be nullable and the data types such as text, ntext, geometry,
image, or user-defined data type
F The column data cannot have ROWGUIDCOL or IDENTITY properties
F The column cannot have a default value or be bound to a rule or a computed column