Create a Data Partition in Azure SQL Data Warehouse

45 minutes
  • 4 Learning Objectives

About this Hands-on Lab

In this lab, we learn how to select a distribution type in SQL DW. Then, we learn how to create a table and partitions.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Configure SQL Data Warehouse

Create a SQL Data Warehouse instance with the following criteria:

  • (US) West US location
  • Gen2 DW100c
  • Use existing SAMPLE data
Create a Test Database with Hash Distribution

In this task we are going to create a new empty table with a hash distribution. In order to do this task, we use the following code:

    CREATE TABLE [dbo].[Sample]
(   [Product]       char    NOT NULL
,   [ProductCategory]    int    NOT NULL
,   [Price]         int    NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductCategory])
)
;
Create a Partition for an Existing Table

In this step, we change a distribution type for an existing table and create a partition based on the day of the week. For this task, we use the following code:

CREATE TABLE [dbo].[DimDateNew]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([DateKey])
     ,  PARTITION       ( [DayNumberOfWeek] RANGE RIGHT FOR VALUES (1, 2, 3)
                        )
    )
AS
SELECT  *
FROM    [dbo].[DimDate]
OPTION  (LABEL  = 'CTAS : DimDateNew')
;
Confirm Partition Creation

For this final task we confirm our partition creation for the DimDate table. In order to complete this task, we use this code:

SELECT 
  o.name AS Table_name, 
  pnp.partition_number AS Partition_number, 
  sum(pnp.rows) AS Row_count 
FROM 
  sys.pdw_nodes_partitions AS pnp 
  JOIN sys.pdw_nodes_tables AS NTables ON pnp.object_id = NTables.object_id 
     AND pnp.pdw_node_id = NTables.pdw_node_id 
  JOIN sys.pdw_table_mappings AS TMap ON NTables.name = TMap.physical_name 
     AND substring(TMap.physical_name, 40, 10) = pnp.distribution_id 
  JOIN sys.objects AS o ON TMap.object_id = o.object_id 
WHERE 
  o.name in ('DimDateNew') 
GROUP BY 
  partition_number, 
  o.name, 
  pnp.data_compression_desc;    

We then use this code to verify everything:

SELECT 
  o.name AS Table_name, 
  pnp.partition_number AS Partition_number, 
  sum(pnp.rows) AS Row_count 
FROM 
  sys.pdw_nodes_partitions AS pnp 
  JOIN sys.pdw_nodes_tables AS NTables ON pnp.object_id = NTables.object_id 
     AND pnp.pdw_node_id = NTables.pdw_node_id 
  JOIN sys.pdw_table_mappings AS TMap ON NTables.name = TMap.physical_name 
     AND substring(TMap.physical_name, 40, 10) = pnp.distribution_id 
  JOIN sys.objects AS o ON TMap.object_id = o.object_id 
WHERE 
  o.name in ('DimDate') 
GROUP BY 
  partition_number, 
  o.name, 
  pnp.data_compression_desc;    

Additional Resources

Scenario

SQL Data Warehouse performance has been lagging at our company. We've been asked to spearhead an effort to create a proof-of-concept in SQL Data Warehouse to speed up performance.

As a part of this initiative we need to:

  • Create and configure a SQL Data Warehouse.
  • Create a test database with hash distribution.
  • Create partitions in the new database.
  • Populate the database with test data.
  • Confirm partition creation.

Instructions

  • Log in to the live environment with the provided Azure Labs credentials.
  • Provision a SQL Data Warehouse instance.
    • (US) West US location
    • Gen2 DW100c
    • Use existing SAMPLE data
  • Create a test database with hash distribution and partitions.
  • Change a distribution type for an existing partition.
  • Confirm partition creation.

For detailed instructions on how to complete these tasks, expand each learning objective below or click the Guide tab above the video player.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!
Thanks for reaching out!

You’ll hear from us shortly. In the meantime, why not check out what our customers have to say about ACG?