title: Partition data within an Azure SQL Data Warehouse table
body: <p>I'm trying to get a little hands on with partitioned tables within Azure SQL Data Warehouse.  But I'm seeing things that don't make sense to me.  I'm obviously doing something wrong, but I can't figure out what it is.</p>
  
  <p>My intention is to populate the first table (Marc.foo) with 10000 rows of data, examine the partitioning metadata, then SWITCH a partition to the second, empty table (Marc.foo2).</p>
  
  <p>I start off by creating two partitioned tables:</p>
  
  <pre><code>IF OBJECT_ID('Marc.foo', 'U') IS NOT NULL
    DROP TABLE Marc.foo
  GO
  
  IF OBJECT_ID('Marc.foo2', 'U') IS NOT NULL
    DROP TABLE Marc.foo2
  GO
  
  CREATE TABLE Marc.foo
  (
      id int NOT NULL
  )
  WITH 
  (   
       DISTRIBUTION = HASH (id),
       CLUSTERED COLUMNSTORE INDEX, 
       PARTITION (id RANGE RIGHT FOR VALUES (0, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000))
  )
  GO
  
  CREATE TABLE Marc.foo2
  (
      id int NOT NULL
  )
  WITH 
  (   
       DISTRIBUTION = HASH (id),
       CLUSTERED COLUMNSTORE INDEX, 
       PARTITION (id RANGE RIGHT FOR VALUES (0, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000))
  )
  GO
  </code></pre>
  
  <p>I then populate the first table (Marc.foo) with 10000 rows:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#numbers', 'U') IS NOT NULL
    DROP TABLE #numbers
  GO
  
  WITH 
      CTE_2 AS (SELECT 1 as id UNION ALL SELECT 1 as id), 
      CTE_4 AS (SELECT a.id FROM CTE_2 a, CTE_2 b), 
      CTE_16 AS (SELECT a.id FROM CTE_4 a, CTE_4 b), 
      CTE_256 AS (SELECT a.id FROM CTE_16 a, CTE_16 b), 
      CTE_64K AS (SELECT a.id FROM CTE_256 a, CTE_256 b)
  SELECT      id
  INTO        #numbers
  FROM        CTE_64K
  
  INSERT INTO Marc.foo(id)
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #numbers WHERE id &lt;=10000
  </code></pre>
  
  <p>Because I just loaded data into the table, I'm going to create statistics on the table:</p>
  
  <pre><code>CREATE STATISTICS stats_Marc_foo_id ON Marc.foo(id) WITH FULLSCAN
  </code></pre>
  
  <p>Now I examine the partitioning metadata:</p>
  
  <pre><code>SELECT      sch.name AS [schema_name],
              tbl.[name] AS [table_name],
              ds.type_desc, 
              prt.[partition_number],
              rng.[value] AS [current_partition_range_boundary_value],
              prt.[rows] AS [partition_rows]
  FROM        sys.schemas                             sch
              INNER JOIN sys.tables                   tbl ON  sch.schema_id       = tbl.schema_id
              INNER JOIN sys.partitions               prt ON  prt.[object_id]     = tbl.[object_id]
              INNER JOIN sys.indexes                  idx ON  prt.[object_id]     = idx.[object_id] AND prt.[index_id] = idx.[index_id]
              INNER JOIN sys.data_spaces              ds  ON  idx.[data_space_id] = ds.[data_space_id]
              INNER JOIN sys.partition_schemes        ps  ON  ds.[data_space_id]  = ps.[data_space_id]
              INNER JOIN sys.partition_functions      pf  ON  ps.[function_id]    = pf.[function_id]
              LEFT JOIN sys.partition_range_values    rng ON  pf.[function_id]    = rng.[function_id] AND rng.[boundary_id] = prt.[partition_number]
  WHERE       sch.name = 'Marc' AND
              tbl.name = 'foo'
  </code></pre>
  
  <p><strong>Question 1:</strong> This gives me what I expect in terms of current_partition_range_boundary_value, but partition_rows (which I expect to be 1000) is returning 5957 rows for each partition.</p>
  
  <p>Last, I try to SWITCH Partition 1 from Marc.foo to Marc.foo2</p>
  
  <pre><code>ALTER TABLE Marc.foo SWITCH PARTITION 1 to Marc.foo2 PARTITION 1
  </code></pre>
  
  <p>I expect that when I SELECT from Marc.foo2, I should see 1000 rows with id values from 1 to 1000.  But I get back zero rows.</p>
  
  <p><strong>Question 2:</strong>  What I have I done wrong?</p>