Azure Synapse Tables Creation with Partitions, Distributions

Описание

Azure Synapse Tables Creation with Partitions, Distributions

This videos guides us on Creating Tables with ColumnStore Indexes, Distributions and Partitions.

Ref: https://www.sqlschool.com/AzureSynapse-Online-Training.html
Ref: https://www.sqlschool.com/AzureSynapse-Video-Training.html

T-SQL Script used in the video:

-- QUERY #1: HOW TO CREATE BASIC TABLE?
create table SynapseTable1 (col1 int, Col2 int)


-- QUERY #2: HOW TO CREATE TABLE WITH A CONSTRAINT?
CREATE TABLE SynapseTable2
(
col1 int CONSTRAINT constraint_col1 DEFAULT 0,
col2 nvarchar COLLATE Frisian_100_CS_AS
)
;


-- QUERY #3: HOW TO CREATE TABLE WITH COLUMN STORE INDEX: INDEX THAT STORES UNIQUE VALUES OF A COLUMN
CREATE TABLE SynapseTable3
(
mycolumn1 nvarchar,
mycolumn2 nvarchar
)
WITH ( CLUSTERED COLUMNSTORE INDEX )
;

INSERT INTO SynapseTable3 DEFAULT VALUES -- ERROR; IT WILL NOT WORK

INSERT INTO SynapseTable3 VALUES (1,1)
INSERT INTO SynapseTable3 VALUES ('1','1')
INSERT INTO SynapseTable3 VALUES (NULL,1)
INSERT INTO SynapseTable3 VALUES ('A',1)
INSERT INTO SynapseTable3 VALUES (N'A','B')

SELECT * FROM SynapseTable3

SELECT * FROM SYS.INDEXES -- NOTE: SP_HELPINDEX WILL NOT WORK IN SYNAPSE
SELECT OBJECT_NAME(OBJECT_ID) AS TABLE_NAME, name as Index_NAME FROM SYS.INDEXES


-- VERIFY THE COLUMNS, CONSTRAINTS, INDEXES FROM THE OBJECT EXPLORER.
-- FOREIGN KEYS ARE NOT SUPPORTED IN SYNAPSE


-- QUERY #4: HOW TO CREATE ROUND ROBIN TABLE? CTAS : CREATE TABLE AS SELECT
CREATE TABLE SynapseTable4
WITH
(
DISTRIBUTION = ROUND_ROBIN
)
AS SELECT * FROM SynapseTable3
OPTION (MAXDOP 3); -- DOP : DEGREE OF PARALLELISM.
-- NUMBER OF OPERATIONS THAT CAN BE PERFORMED AT A TIME


-- QUERY #5: HOW TO CREATE HASH TABLE:
CREATE TABLE SynapseTable5
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH
(
DISTRIBUTION = HASH (id), -- RANGE IS ASSIGNED AUTOMATICALLY.
CLUSTERED COLUMNSTORE INDEX -- EXAMPLE: 1 TO 10 : DISTRIBUTION 1, 11 TO 20 : DISTRIBUTION 2
)
;



-- QUERY #6: HOW TO CREATE A TEMPORARY HASH TABLE:
CREATE TABLE #SynapseTable6
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH
(
DISTRIBUTION = HASH (id),
CLUSTERED COLUMNSTORE INDEX
)
;

-- QUERY #7: HOW TO CREATE A REPLICATED TABLE?
CREATE TABLE SynapseTable7
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED INDEX (lastName)
);

-- IF YOU ARE FROM SQL SERVER DEV OR ADMIN BACKGROUND: TRY "SORT_IN_TEMPDB" OPTION @ INDEX CREATIONS.

-- QUERY #8: HOW TO CREATE NEW TABLE FROM EXISTING TABLE? -- CTAS: CREATE TABLE AS SELECT
CREATE TABLE SynapseTable8
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX -- SYNAPSE WILL DECIDE THE INDEXED COLUMN
)
AS
SELECT * FROM SynapseTable7


CREATE TABLE SynapseTable9
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX ORDER(id)
)
AS
SELECT * FROM SynapseTable7

-- QUERY #9: HOW TO CREATE PARTITIONED TABLES?
CREATE TABLE SynapseTable10
(
id int NOT NULL,
lastName varchar(20),
zipCode int)
WITH
(
PARTITION ( id RANGE LEFT FOR VALUES (10, 20, 30, 40)), -- 5 PARTITION RANGES [P1: VALUES UPTO 10; P2: 11 TO 20; P3:21 TO 30..]
CLUSTERED COLUMNSTORE INDEX
)


CREATE TABLE SynapseTable11
(
id int NOT NULL,
lastName varchar(20),
zipCode int)
WITH
(
PARTITION ( id RANGE RIGHT FOR VALUES (10, 20, 30, 40 )), -- 5 PARTITION RANGES [P1: UPTO 9; P2: 10 TO 19; P3:20 TO 29..]
CLUSTERED COLUMNSTORE INDEX
)
;

CREATE TABLE SynapseTable12
(
id int NOT NULL,
lastName varchar(20),
zipCode int)
WITH
(
DISTRIBUTION = REPLICATE,
PARTITION ( id RANGE RIGHT FOR VALUES (10, 20, 30, 40 )), -- 5 PARTITION RANGES [P1: VALUES LESS THAN 10; P2: 10 TO 20; P3:20 TO 30..]
CLUSTERED COLUMNSTORE INDEX
)
;

Azure Synapse Tables Creation with Partitions, Distributions
with 100% Practice Oreinted Training From SQL School.
For project oriented training details, please visit: www.sqlschool.com
or call us on +91 9030040801 (24 x 7)