Database Reference
In-Depth Information
Columnstore indexes have several restrictions that you need to remember. They cannot be defined as
UNIQUE
,
cannot have sparse or computed columns, nor can they be created on an indexed view. Moreover, not all data types
are supported. The list of unsupported data types includes the following:
binary
varbinary
(n)text
image
(n)varchar(max)
timestamp
CLR types
sql_variant
xml
The following data types are not supported in SQL Server 2012; however, they are supported in SQL Server 2014:
uniqueidentifier
decimal
and
numeric
with precision greater than 18 digits
datetimeoffset
with precision greater than 2
Another limitation of nonclustered columnstore indexes is that a table with such an index becomes read-only.
You cannot change data in the table after the index is created. This limitation, however, is not as critical as it seems.
Columnstore indexes are a Data Warehouse feature, and data is usually static in Data Warehouses. You can always
drop a columnstore index before a data refresh and recreate it afterwards.
Tables with columnstore indexes support a partition switch, which is another option for importing data into the
table. You can create a staging table using it as the target for data import, add a columnstore index to a staging table
when the import is completed, and switch the staging table as the new partition to the main read-only table as the last
step of the operation. Listing 34-10 shows an example of this.
Listing 34-10.
Importing data into a table with a nonclustered columnstore index using a staging table and
partition switch
create partition function pfFacts(int)
as range left for values (1,2,3,4,5);
go
create partition scheme psFacts
as partition pfFacts
all to ([FG2014]);
go
create table dbo.FactTable
(
DateId int not null,
ArticleId int not null,
OrderId int not null,
Quantity decimal(9,3) not null,
UnitPrice money not null,
Amount money not null,
constraint PK_FactTable
primary key clustered(DateId, ArticleId, OrderId)
on psFacts(DateId)
)
go
