Specifications

Data Management CHAPTER 6 119
NOTE Parallel Data Warehouse does not use the Transact-SQL partition schema or parti-
tion function. Also, you can create a clustered index only when you use CREATE TABLE. To
create a nonclustered index, you use CREATE INDEX.
Here is an example of the syntax to create a replicated table:
CREATE TABLE DimProduct
(
ProductId BIGINT NOT NULL,
Description VARCHAR(50),
CategoryId INT NOT NULL,
ListPrice DECIMAL(12,2)
) WITH ( DISTRIBUTION = REPLICATE );
This syntax instructs Parallel Data Warehouse to create a table on all compute nodes. Sub-
sequent commands to insert or delete data affect data in each copy of the table.
Here is an example of the syntax to create a distributed table:
CREATE TABLE FactSales
( CustomerId BIGINT,
SalesId BIGINT,
ProductId BIGINT,
SaleDate DATE,
Quantity INT,
Amount DECIMAL(15,2)
) WITH (
DISTRIBUTE = HASH (CustomerId),
CLUSTERED INDEX (SaleDate),
PARTITION ( SaleDate
RANGE RIGHT FOR VALUES
( '2009-01-01','2009-02-01','2009-03-01','2009-04-01','2009-05-01','2009-06-01'
,'2009-07-01','2009-08-01','2009-09-01','2009-10-01','2009-11-01','2009-12-01')
));
The CREATE TABLE statement for Parallel Data Warehouse includes the following items:
■
DISTRIBUTION Species the column to hash for distributing rows across all com-
pute nodes in Parallel Data Warehouse
■
CLUSTERED INDEX Species the column for a clustered index—if you omit this
item from the statement, Parallel Data Warehouse stores the table as a heap
■
PARTITION Species the boundary values of the partition and the column to use for
partitioning the rows