A standard
user-defined table can have up to 1,024 columns. The number of rows in the
table is limited only by the storage capacity of the server.
|
Wide
Tables
This feature was introduced with SQL
Server 2008 release on-wards. A wide table is just a table that uses column sets
and sparse columns. It still follows the same width restrictions per row (8019
bytes). To make a table wide you just add a column set to its definition. They
come into the picture whenever business needs to increase the total of columns more
than 1024 and wide table can have to 30,000.
The number of non-sparse columns is still
1024 and the max size of the row is still 8000 bytes.
Sparse columns are ordinary columns that have an optimized
storage for null values. Sparse columns reduce the space requirements for
null values at the cost of more overhead to retrieve non-null values.
Consider using sparse columns when the space saved is at least 20 percent to
40 percent.
True fact about Sparse columns
A sparse column set gathers all sparse columns into a new column
that is similar to a derived or computed column, but with additional
functionality – its data can be updated and selected from directly.
True fact about column set
|
Whenever designing the wide tables with
sparse columns, keep in mind that an additional 2 bytes of overhead are
required for each non-null sparse column in the table when a row is being
updated. As a result of this additional memory requirement, updates can fail
unexpectedly with error 576 when the total row size, including this memory
overhead, exceeds 8019, and no columns can be pushed off the row.
A wide table has defined a column set,
which is an untyped XML representation that combines all the sparse columns of
a table into a structured output. The number of indexes and statistics is also
increased to 1,000 and 30,000, respectively.
How
to create Wide Table
However, by leveraging the column set
functionality, we can create a table with greater than 1,024 columns. The AdventureWorks2012 example from Books
Online shows a nice example, where the column SpecialPurposeColumns is defined
as the column set that returns the 4 sparse columns defined in the table
definition:
USE AdventureWorks2012;
GO
-- create wide table with sparce and column set
CREATE TABLE
DocumentStore
(
DocID int
PRIMARY KEY,
Title
varchar(200) NOT NULL,
ProductionSpecification
varchar(20) SPARSE NULL,
ProductionLocation
smallint SPARSE
NULL,
MarketingSurveyGroup
varchar(20) SPARSE NULL,
MarketingProgramID
int SPARSE NULL,
SpecialPurposeColumns
XML COLUMN_SET
FOR ALL_SPARSE_COLUMNS
);
GO
-- Insert values
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire
Spec 1', 'AXZZ217', 27);
GO
-- Insert Values
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey
2142', 'Men 25
- 35');
GO
-- Fetch data from wide table
SELECT *
FROM DocumentStore;
GO
|
Limitation
of Wide Tables
Wide tables have the following
performance implications.
- Wide tables can increase the cost to maintain indexes on the table. We recommend that the number of indexes on a wide table be limited to the indexes that are required by the business logic.
- As the number of indexes increases, so does the DML compile-time and memory requirement. Nonclustered indexes should be filtered indexes that are applied to data subsets. For more information, see Create Filtered Indexes.
- Applications can dynamically add and remove columns from wide tables. When columns are added or removed, compiled query plans are also invalidated.
- When data is added and removed from a wide table, performance can be affected. Applications must be designed for the projected workload so these changes to the table data are minimized.
- Limit the execution of DML statements on a wide table that update multiple rows of a clustering key. These statements can require significant memory resources to compile and execute.
- Switch partition operations on wide tables can be slow and might require large amounts of memory to process.
- The performance and memory requirements are proportional to the total number of columns in both the source and target partitions.
- Update cursors that update specific columns in a wide table should list the columns explicitly in the FOR UPDATE clause. This will help optimize performance when you use cursors.
- There are size restrictions on the rows and it can be slower to retrieve data than if you use related tables (even those with one-to-one relationships).
- Transnational replication supports sparse columns, but it does not support column sets, which can be used with sparse columns.
- Merge replication does not support sparse columns or column sets. Change data capture supports sparse columns, but it does not support column sets.
To make a table into a wide table, you
must create sparse columns and also add a column set. If you don't create a
column set your table will still be limited to 1024 columns. Sparse
columns and column sets in SQL Server provide a way of accommodating the
less-structured data that has always proved to be tricky for the relational
model. Wide tables can increase the cost to maintain indexes on the table and
switching partition operations work very slow and require more memory to process.
To know more on the different kinds of the tables in SQL Servers at
To know more on the different kinds of the tables in SQL Servers at
References:
https://msdn.microsoft.com/en-us/library/ms189084(v=sql.110).aspx
