How to Create Static / Temp Table on PDW (Parallel Data Warehouse)

Microsoft PDW (Parallel Data Warehouse) is a special version of SQL Server which is specifically design to scale for massive amount of data (upto several petabytes). PDW can not be downloaded like regular SQL server because it comes as pre-packaged Hardware with Software installed. PDW can you SQL Server Like familiar environment but it comes with its own limitations too compared to regular SQL Server Editions (e.g. Standard/Enterprise)

One of the most common task user often do while writing t-sql is create static or temp tables. With PDW its has some syntax changes. Here are few facts about creating temp tables on PDW

• Global Temp tables not supported in PDW
• When creating TEMP Table you must specify LOCATION=USER_DB
• Creating NON CLUSTERED index not supported on temp table in PDW

Below is simple script how to create Temp Table on PDW. One thing to notice on that is use of LOCATION=USER_DB. This is required for creating temp table. But not required for creating static table.

Create and query Temp Table on PDW

use tempdb;

CREATE TABLE #TableA(
   yearId int NOT NULL,
   some1 varchar(50),
   stuff1 varchar(50))
WITH (LOCATION = USER_DB) --NOTE: must include LOCATION=USER_DB
insert into #TableA(yearid,some1,stuff1) values (2000,'aaa','bbb')
insert into #TableA(yearid,some1,stuff1) values (2001,'xxx','yyy')

CREATE CLUSTERED INDEX IX_yearID ON #TableA(yearId); --NOTE: NON CLUSTERED INDEX not supported with temp table

select * from #TableA where yearId=2001;

go

drop table #TableA;

Create and query Static Table on PDW

CREATE TABLE TableA(
   yearId int NOT NULL,
   some1 varchar(50),
   stuff1 varchar(50))

insert into TableA(yearid,some1,stuff1) values (2000,'aaa','bbb')
insert into TableA(yearid,some1,stuff1) values (2001,'xxx','yyy')

CREATE NON CLUSTERED INDEX IX_yearID ON TableA(yearId); --//CLUSTERED or NON CLUSTERED

select * from TableA where yearId=2001;

go

Here is the official syntax of create table command (SQL 2012 PDW)

******** Create a new static table ********
CREATE TABLE [ database_name . [ dbo ] . | dbo. ] table_name 
    ( 
        { column_name <data_type> 
        [ COLLATE Windows_collation_name ]
        [ NULL | NOT NULL ] 
        [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression  ] }
        [ ,...n ] 
    )
    [ WITH ( <table_option> [ ,...n ] ) ]
[;]

******** Create a new temporary table ********
CREATE TABLE [ database_name . [ dbo ] . | dbo. ] #table_name 
    ( 
        { column_name <data_type> 
        [ COLLATE Windows_collation_name ]
        [ NULL | NOT NULL ] } 
        [ ,...n ] 
    )
    WITH ( LOCATION = USER_DB [, <table_option> [ ,...n ] ] )  
[;]


<table_option> ::=
    CLUSTERED COLUMNSTORE INDEX
    | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) 
    | DISTRIBUTION = { HASH ( distribution_column_name ) | REPLICATE } 
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ]
        FOR VALUES ( [ boundary_value [,...n] ] ) )


<data type> ::= 
    datetimeoffset [ ( n ) ]
    | datetime2 [ ( n ) ]
    | datetime
    | smalldatetime
    | date
    | time [ ( n ) ]
    | float [ ( n ) ]
    | real [ ( n ) ]
    | decimal [ ( precision [ , scale ] ) ] 
    | money
    | smallmoney
    | bigint
    | int 
    | smallint
    | tinyint
    | bit
    | nvarchar [ ( n ) ]
    | nchar [ ( n ) ]
    | varchar [ ( n ) ]
    | char [ ( n ) ]
    | varbinary [ ( n ) ]
    | binary [ ( n ) ]