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)<\/p>\n
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<\/strong><\/p>\n Below is simple script how to create Temp Table on PDW<\/strong>. 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.<\/p>\n Here is the official syntax of create table command (SQL 2012 PDW)<\/p>\n 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 …<\/p>\n\n
Create and query Temp Table on PDW<\/h2>\n
use tempdb;\r\n\r\nCREATE TABLE #TableA(\r\n yearId int NOT NULL,\r\n some1 varchar(50),\r\n stuff1 varchar(50))\r\nWITH (LOCATION = USER_DB) --NOTE: must include LOCATION=USER_DB\r\ninsert into #TableA(yearid,some1,stuff1) values (2000,'aaa','bbb')\r\ninsert into #TableA(yearid,some1,stuff1) values (2001,'xxx','yyy')\r\n<\/pre>\n
CREATE CLUSTERED INDEX IX_yearID ON #TableA(yearId); --NOTE: NON CLUSTERED INDEX not supported with temp table<\/pre>\n
select * from #TableA where yearId=2001;\r\n\r\ngo<\/pre>\n
drop table #TableA;<\/pre>\n
Create and query Static Table on PDW<\/h2>\n
CREATE TABLE TableA(\r\n yearId int NOT NULL,\r\n some1 varchar(50),\r\n stuff1 varchar(50))\r\n\r\ninsert into TableA(yearid,some1,stuff1) values (2000,'aaa','bbb')\r\ninsert into TableA(yearid,some1,stuff1) values (2001,'xxx','yyy')<\/pre>\n
CREATE NON CLUSTERED INDEX IX_yearID ON TableA(yearId); --\/\/CLUSTERED or NON CLUSTERED<\/pre>\n
select * from TableA where yearId=2001;\r\n\r\ngo<\/pre>\n
******** Create a new static table ********\r\nCREATE TABLE [ database_name . [ dbo ] . | dbo. ] table_name \r\n ( \r\n { column_name <data_type> \r\n [ COLLATE Windows_collation_name ]\r\n [ NULL | NOT NULL ] \r\n [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] }\r\n [ ,...n ] \r\n )\r\n [ WITH ( <table_option> [ ,...n ] ) ]\r\n[;]\r\n\r\n******** Create a new temporary table ********\r\nCREATE TABLE [ database_name . [ dbo ] . | dbo. ] #table_name \r\n ( \r\n { column_name <data_type> \r\n [ COLLATE Windows_collation_name ]\r\n [ NULL | NOT NULL ] } \r\n [ ,...n ] \r\n )\r\n WITH ( LOCATION = USER_DB [, <table_option> [ ,...n ] ] ) \r\n[;]\r\n\r\n\r\n<table_option> ::=\r\n CLUSTERED COLUMNSTORE INDEX\r\n | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) \r\n | DISTRIBUTION = { HASH ( distribution_column_name ) | REPLICATE } \r\n | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ]\r\n FOR VALUES ( [ boundary_value [,...n] ] ) )\r\n\r\n\r\n<data type> ::= \r\n datetimeoffset [ ( n ) ]\r\n | datetime2 [ ( n ) ]\r\n | datetime\r\n | smalldatetime\r\n | date\r\n | time [ ( n ) ]\r\n | float [ ( n ) ]\r\n | real [ ( n ) ]\r\n | decimal [ ( precision [ , scale ] ) ] \r\n | money\r\n | smallmoney\r\n | bigint\r\n | int \r\n | smallint\r\n | tinyint\r\n | bit\r\n | nvarchar [ ( n ) ]\r\n | nchar [ ( n ) ]\r\n | varchar [ ( n ) ]\r\n | char [ ( n ) ]\r\n | varbinary [ ( n ) ]\r\n | binary [ ( n ) ]<\/pre>\n","protected":false},"excerpt":{"rendered":"